RubyGems axlsx create a list of cells that represent time and add it programmatically

Question

I will try to simplify the question as much as I can. I am generating an xls file with axlsx, and inside the file there is a column that represents the length of a task with this format [h]:mm:ss

I want to that the cells have that format and in the last row I want to add all the rows of the column. Here is the template for the xls

wb = xlsx_package.workbook
wb.add_worksheet(name: "Length") do |sheet|
  time = wb.styles.add_style :num_fmt => 46

  sheet.add_row [
    "Length"
  ], :style => title
  @tasks.each do |task|
      sheet.add_row [
        Time.at(task.duration*60).utc.strftime("%k:%M:%S").strip
      ], :style => [
        time
      ]
  end
  range_amount = "D2:D#{@tasks.size+1}"
  sheet.add_row ["=SUM(#{range_amount})"], :style => [time]
end

The problem I have are

In LibreOffice the time cells add this character ' at the beginning (before the time) for example '0:50:00, so obviosly is not well formatted.

If I upload the file to oneDrive, I do not have that new character. The cells have this format [h]:mm:ss. However I am not able to SUM the range, The last cell has the value =SUM(D2:D174) but it shows 0 and if I change the format to [h]:mm:ss it change to 0:00:00

Thank you

Answer 1

My mistake was trying to manipulate strings as time, here is how I solved

wb = xlsx_package.workbook
wb.add_worksheet(name: "Length") do |sheet|
  time_title = sheet.styles.add_style(:bg_color => "55FF0000", :fg_color=>"FFFFFFFF", :border=>Axlsx::STYLE_THIN_BORDER, :alignment=>{:horizontal => :center}, :num_fmt => 46)
  time = wb.styles.add_style :num_fmt => 20

  sheet.add_row [
    "Length"
  ], :style => title
  @tasks.each do |task|
      sheet.add_row [
        "=#{task.duration}/1440"
      ], :style => [
        time
      ]
  end
  range_amount = "D2:D#{@tasks.size+1}"
  sheet.add_row ["=SUM(#{range_amount})", :style => [time_title]
end

I hope it helps to someone else