Export Excel (xls or xlsx or CSV) sheet with validation/ rule applied on column

Question

I want to export a template Excel sheet, so that user can fill it and can upload with their data.

In exported sheet, I want to put some validation or rule on column values. So that, user only can fill the value as per rules applied which can be only numbers or 4-5 unique value (for example colors - Blue, Green, Black only).

So far I have gone through documentation of Roo and Spreadsheet gem, to find any way to define rules on columns, but didn't find anything. For now just validating sheet when uploaded by user and showing error if invalid value is inserted.

Looking for solution something like this - How to create dependent drop downs in excel sheet generated using POI?

Any help is welcomed.

Answer 1

Resolved this requirement using Axlsx gem.

Axlsx provide great features. For validations gem have Axlsx::DataValidation.

Which allows to add validation for datatypes: :whole, :decimal, :date, :time, :textLength, :list, :custom. For each one can specify formula also.

To get only few colors value for color column I added validations and exported sheet save sheet and exported as below:

p = Axlsx::Package.new

p.workbook.add_worksheet(name: "dropdown") do |ws|
  ws.add_row ["Color"]
  ws.add_data_validation("A2:A1000", {
    :type => :list,
    :formula1 => 'Red orange Blue White',
    :showDropDown => false,
    :showErrorMessage => true,
    :errorTitle => '',
    :error => 'Please use the dropdown selector to choose a valid color',
    :errorStyle => :stop,
    :showInputMessage => true,
    :promptTitle => 'Color',
    :prompt => 'Please select a valid color'})
end

p.serialize 'data_validation.xlsx'

Till now, I only can specify limited range of rows for validations to applied in sheet.

If someone can add how specify whole column for validation, this answer will be a perfect solution.

Thanks.