Set cell type in Axlsx

Question

I need to create a excel sheet with the following headers: Date, Check in, Break in, Break out, Check out, Note

Below those headers I want to add 100 empty rows of which the first column must be type: date and the other 5 columns must be type: text. For some reason only the first column of type: date is having the correct type when I open the file.

# template.xlsx.axlsx
wb = xlsx_package.workbook

wb.styles do |style|
  heading = style.add_style(b: true)
  @profiles.each do |profile|
    wb.add_worksheet(name: "#{profile.firstname} #{profile.lastname} #{profile.id}") do |sheet|
      sheet.add_row ['Datum', 'Check in', 'Break in', 'Break out', 'Check out', 'Notitie'], style: heading
      100.times do
        sheet.add_row ['', '', '', '', '', ''], types: [:date, :text, :text, :text, :text, :text]
      end
    end
  end
end

What am I missing here?

Answer 1

Try to specify the column type using col_style in the sheet (docs, related).

sheet.col_style(0, nil, type: :date, format_code: 'dd/mm/yyyy')                                                                                                                                            

You script with the changes:

# template.xlsx.axlsx
wb = xlsx_package.workbook

wb.styles do |style|
  heading = style.add_style(b: true)
  @profiles.each do |profile|
    wb.add_worksheet(name: "#{profile.firstname} #{profile.lastname} #{profile.id}") do |sheet|
      sheet.add_row ['Datum', 'Check in', 'Break in', 'Break out', 'Check out', 'Notitie'], style: heading
      sheet.col_style(0, nil, type: :date, format_code: 'dd/mm/yyyy')
      100.times do
        sheet.add_row ['', '', '', '', '', ''], types: [:date, :text, :text, :text, :text, :text]
      end
    end
  end
end