Search code examples
ruby-on-railsdropdownaxlsxcaxlsx

Ruby on Rails: define custom cell name using rails axlsx

enter image description here

I have this data, and want to set B3:B6 as "procedures" (see image top left), how do I do this in rails using caxlsx_rails gem ??

I need to set dynamic dropdown in the excel, for examples if user select A, this "procedures" will show as dropdown in the next column, if user select B, group of cells named "procedures_2_" will show.

Solution

  • I am a bit unclear as to what you are asking but you can create a DefinedName as follows:

    require 'axlsx'

p = Axlsx::Package 
wb = p.workbook

codes = ['Procedure 1','Procedure 2 edited','Code 1', 'Code 2']
sheet_name = 'Defined Name Example'

wb.add_worksheet(name: sheet_name) do |s| 
  sheet.add_row ['ID','Code']
  codes.each.with_index(1) do |code,idx| 
    sheet.add_row [idx,code]
  end
  wb.add_defined_name("'#{sheet_name}'!$B$2:$B$#{codes.size + 1}", 
    local_sheet_id: s.index, 
    name: 'procedures')
end

    This will create a DefinedName ("procedures") just as your example shows