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.
