CODEWITHSUNDEEP
rubyaxlsx
Sagar Arlekar
Sagar Arlekar

Reputation: 484

Axlsx Ruby Gem - Creating a dropdown with custom values

I'm using axlsx gem to export data into excel from Ruby/Rails.

I need to add a dropdown with custom values - value1, value2, value3 etc. The following is the code I tried

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

wb.add_worksheet(name: "sample_sheet") do |sheet|
  sheet.add_data_validation("A1:A100", {
      :type => :list,
      :formula1 => "value1 value2 value3",
      :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')

The code creates the excel but when I open it I get this excel error excel error The values are not added to the dropdown.

Alternatively,

:formula1 => "B1:B2"

works fine, it adds the contents of B1 and B2 to the dropdown. In my use-case I need custom values to be added, they are not stored in the excel sheet.

What is the correct way to add custom values in data validation dropdown? Kindly help.

Upvotes: 2

Views: 2433

Answers (2)

randym
randym

Reputation: 2460

You need to change formula1 to the following:

'"My, List, Of, Values"'

Notice the double quotes inside the single quotes.

There is an example of how to do this here: https://github.com/randym/axlsx/blob/master/examples/data_validation.rb#L54

Upvotes: 3

Alexander Suraphel
Alexander Suraphel

Reputation: 10613

There seems to be no way to specify a values in your formula but the following solution will work in your case.

On the .axlsx file add another sheet to keep your custom values: 

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

Then change your code to: 

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

The $s at A$1:A$100 are necessary.

Upvotes: 1

Related Questions