Data Validation List Excel

data_validation_list.rb

## AXLSX Gem Example
require 'axlsx'

excel = Axlsx::Package.new
wb = excel.workbook

months_sheet = wb.add_worksheet(:name => 'DropDown Values') { |ws| ws.sheet_protection.password = 'pa55w0rd' }
months_sheet.add_row ['value1', 'value2', 'value3']

wb.add_worksheet(name: "sample_sheet") do |sheet|
  sheet.add_data_validation("A1:A100", {
      :type => :list,
      :formula1 => "'DropDown Values'!A$1:C$1",
      :showDropDown => false,
      :showErrorMessage => true,
      :errorTitle => '',
      :error => 'Please use the dropdown selector to choose the value',
      :errorStyle => :stop,
      :showInputMessage => true,
      :prompt => 'Choose the value from the dropdown'
  })
end

excel.serialize('sample_excel.xlsx')

#--------------------------------------------------------------------------------------
## Write_xlsx Gem Example

require 'write_xlsx'

workbook = WriteXLSX.new('write_xlsx_example.xlsx')

worksheet = workbook.add_worksheet

worksheet.data_validation('A1:A100',
{
    :validate        => 'list',
    :value           => ['open', 'high', 'close'],
    #:input_title     => 'Input an integer:',
    :input_message   => 'Choose the value from the dropdown',
    :error_message   => 'Please use the dropdown selector to choose the value'
})

workbook.close