Data validations with axlsx gem

data_validations.rb

#!/usr/bin/env ruby -w -s
# -*- coding: utf-8 -*-
require 'axlsx'

## Some documentation ##

### Axlsx Gem ###

# Axlsx Docs:            http://rubydoc.info/gems/axlsx/frames
# Axlsx::DataValidation: http://rubydoc.info/gems/axlsx/Axlsx/DataValidation

### OOXML Param Reference ###

# Type:        http://www.schemacentral.com/sc/ooxml/t-ssml_ST_DataValidationType.html
# Operators:   http://www.schemacentral.com/sc/ooxml/t-ssml_ST_DataValidationOperator.html
# Error Style: http://www.schemacentral.com/sc/ooxml/t-ssml_ST_DataValidationErrorStyle.html

## Examples ##

# Initialize a new workbook
p = Axlsx::Package.new

### Dropdown from list ###

p.workbook.add_worksheet(name: "dropdown") do |ws|
  ws.add_row ["rank_type"]
  ws.add_data_validation("A2:A1000", {
    :type => :list,
    :formula1 => 'lists!A$2:A$4',
    :showDropDown => false,
    :showErrorMessage => true,
    :errorTitle => '',
    :error => 'Please use the dropdown selector to choose a valid rank type',
    :errorStyle => :stop,
    :showInputMessage => true,
    :promptTitle => 'Rank type',
    :prompt => 'Please select a valid rank type'})
end

### Numeric ###

p.workbook.add_worksheet(name: "numeric") do |ws|
  ws.add_row ["duration"]
  ws.add_data_validation("A2:A1000", {
    :type => :whole, 
    :operator => :greaterThan, 
    :formula1 => '0', 
    :showErrorMessage => true, 
    :errorTitle => 'Duration', 
    :error => 'Duration must be a number greater than or equal to 0', 
    :errorStyle => :stop, 
    :showInputMessage => true, 
    :promptTitle => '', 
    :prompt => 'Must be greater than 0'})
end

### Text ###

p.workbook.add_worksheet(name: "text") do |ws|
  ws.add_row ["name"]
  ws.add_data_validation("A2:A1000", {
    :type => :textLength, 
    :operator => :between, 
    :formula1 => '1', 
    :formula2 => '255', 
    :showErrorMessage => true, 
    :errorTitle => 'Name', 
    :error => 'Must be no longer than 255 characters', 
    :errorStyle => :stop, 
    :showInputMessage => true, 
    :promptTitle => '', 
    :prompt => 'Required, not longer than 255 characters'})
end

# Add our lists sheet
p.workbook.add_worksheet(name: "lists") do |ws|
  ws.add_row ["rank_type"]
  ws.add_row ["line_item"]
  ws.add_row ["sum"]
  ws.add_row ["all"]
end

# Write the workbook
p.serialize 'example_validations.xlsx'
p = nil

for dropdown worksheet, Because of :formula1 => 'lists!A2:A4', I am getting incorrect drop down. For each subsequent cell the formula is applied by incrementing cell positions of lists.
like for dropdown.A2 it is list.A2:A4
for dropdown.A3 it is list.A3:A5

Instead of :formula1 => 'lists!A2:A4', I am using :formula1 => 'lists!A$2:A$4',

@dinsaw thanks for you comment. I would have spent hours to find out what was going wrong.

@dinsaw thanks again, my example:

worksheet.add_data_validation("G2:G20000", { type: :list, formula1: "Comunes!A$1:A$65536" })

t
Thanks, @dinsaw your comment is very helpful

I added that correction to the example. Thanks!