Axlxs gem - Automatically add date columns

Question

I am using axlsx gem for creating excel spreadsheets. In my case, spreadsheet is created for notes model. The spreadsheet uses three model attributes as columns: devise number, volume and date. Each date column represents volume of the devise. I want to make automatic addition of date columns when comes new data from notes table. Like that:

The current state of excel table looks like this:

And this is code which was written using axlsx:

wb = xlsx_package.workbook
wb.styles do |style|
    date_cell = style.add_style(format_code: "yyyy-mm-dd hh:mm")
    wb.add_worksheet(name: "Notes") do |sheet|
        sheet.add_row ["Devise number", "Volume", "Date"]
        @notes.each do |note|
            sheet.add_row [note.devise.number, note.volume, note.created_at],  
            style: [nil,nil, date_cell]
        end
    end
end

So, how to make automatic addition of date columns to table? Thank you, guys.

andrewhaines andrewhaines · Accepted Answer · 2018-04-17T11:14:36

I think you'll want to do something like this:

wb = xlsx_package.workbook
wb.styles do |style|
  date_cell = style.add_style(format_code: "yyyy-mm-dd hh:mm")
  # Set your dynamic headers
  @header_cols = []
  @header_cols << "Devise number"
  @notes.each do |n|
    @header_cols << n.date # Adds all dates from all notes to header columns
  end
  # Set dynamic format array based on headers
  @formats = []
  @formats << nil
  @header_cols.count.times do |f|
    @formats << date_cell
  end
  wb.add_worksheet(name: "Notes") do |sheet|
    sheet.add_row @header_cols # Use your dynamic column list
      @notes.each do |note|
        cols = []
        cols << note.devise.number # Always include this in the first place
        @header_cols.each do |c|
          if c == note.date
            cols << note.created_at
          else
            cols << "N/a" # Whatever blank value you want to appear
          end
        end
        sheet.add_row cols,
        style: @formats
      end
  end
end

This isn't tested. Making sure the created_at and date fields can be matched is important. And specifying the number of @formats columns to include based on the number of header columns might need to be adjusted. But it should get you on the right path!

Axlxs gem - Automatically add date columns

1 Answers