Rendering a date time field always displays sets the time to midnight using to_spreadsheet (Axlsx)

1
votes

My Rails 4.2 site allows users to download an XLSX spreadsheet. To render the spreadsheet, I am using the to_spreadsheet gem which uses Axlsx to render spreadsheets.

I am trying to display a date in the format d/m/yyyy h:mm by using number format 22 which seems to be the only number format that renders both date and time as per Axlsx documentation.

I'm formatting the string using strftime("%F %T") and setting the num_fmt to 22 in the HTML source but the time is always rendered as midnight:

Controller:

# just render one row with one cell containing the current time
@data = [{
  # for the sake of example, convert a string to datetime
  date: "01/02/2016 14:16:15".to_datetime.strftime("%F %T")
}]

View

%table
  %thead
    %tr
      %th Date
  %tbody
    - @data.each do |row|
      %tr
        %td.date=row[date]

- format_xls 'table' do
  - format 'td.date', num_fmt: 22

Spreadsheet

╭──────────────────────╮
│         Date         │
╞══════════════════════╡
│     01/02/2016 00:00 │ <----- Should be "01/02/2016 14:16:15"
└──────────────────────┘

I have tried removing the num_fmt and the date and time are displayed correctly. However this is interpreted as a string by Excel which removes the ability for users to perform additional calculations on the cells.

The result is the same in both MS Excel and OO Calc.

Is this an issue with to_spreadsheet, axlsx, or am I not setting the format correctly?

1
ruby-on-railsrubyexcelaxlsx

1 Answers

0
votes

I solved this with some help from the devs via github here: https://github.com/glebm/to_spreadsheet/issues/27

It turns out you must give your table cell a datetime class if you want the time to be retained. So it would require changing the HAML to the following:

%table
  %thead
    %tr
      %th Date
  %tbody
    - @data.each do |row|
      %tr
        %td.datetime=row[date]

- format_xls 'table' do
  - format 'td.datetime', num_fmt: 22

This then renders the correct date and time:

╭──────────────────────╮
│         Date         │
╞══════════════════════╡
│     01/02/2016 14:16 │
└──────────────────────┘