In my little Ruby on Rails app I’m outputting a list of time entries
to an Excel sheet for billing purposes. To make this as pleasant as
possible for further calculations in Excel I want to output the
durations in a format recognized by Excel.
Preferably an hh:mm:ss format, where the hour part should always be in
hours, even when it exceeds 24. To do so in Excel (or at least 2007
and 2010) you can use the format ‘[u]:mm:ss’, which will display
correctly when inputting either a date or string in the right format.
The problem however is when I use the following code;
duration_format = Spreadsheet::Format.new(:number_format =>
“[u]:mm:ss”)
sheet.format_column(3,duration_format)
… bunch of code to fill the column #3 with either Time, DateTime or
strings in the correct format, neither works …
This will actually work to output a regular .xls file, however when
opening the file an error pops up stating that ‘Some number formats
may have been lost.’ and the column just defaults to the standard
format.
Note that I have also tried to use ‘u’ without the brackets, but this
will actually put a u in front of the mm:ss part, which is not quite
the wanted behavior.
Meanwhile I’ve also used a simple string output to u:mm:ss, but since
Excel does not recognize this as a time format, additional
calculations in Excel itself are not possible.
I’d love to hear suggestions on how to solve this, thanks in advance.