导出Excel（xls或xlsx或CSV）表，并在列上应用验证/规则

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

我想导出模板Excel工作表，以便用户可以填写它并可以上传他们的数据。

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).

在导出的工作表中，我想对列值进行一些验证或规则。因此，用户只能根据应用的规则填充值，该值只能是数字或4-5个唯一值（例如颜色 - 仅蓝色，绿色，黑色）。

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.

到目前为止，我已经浏览了Roo和Spreadsheet gem的文档，找到了在列上定义规则的任何方法，但没有找到任何内容。现在只需在用户上传时验证工作表，如果插入了无效值，则显示错误。

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

寻找像这样的解决方案 - 如何在使用POI生成的Excel工作表中创建依赖的下拉？

Any help is welcomed.

欢迎任何帮助。

1 个解决方案

#1

Resolved this requirement using Axlsx gem.

使用Axlsx gem解决了这个要求。

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

Axlsx提供了强大的功能。对于验证，gem有Axlsx :: DataValidation。

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

这允许为数据类型添加验证:: whole，：decimal，：date，：time，：textLength，：list，：custom。对于每个人也可以指定公式。

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.

谢谢。

#1