Excel export hour format

[fellipec]

When we export to Excel, the Print time is formatted like hhh:mm:ss

But as far as I could research, Excel accept time up to 9999:59:59 without leading zeros (exception is 0x:xx:xx)

I can't sum the time right out of the bat with the format PrintHistory exports, and I hope this is not a complicated fix.

A workaround in Excel is to add a new column with the formula =REPLACE([@[Print time]];0;;1) then copy this values and paste over the old values, and then copy a cell with number 1 and then paste special, values, multiply over the pasted cells to force Excel to re-evalute the text into time.

[guysoft]

This is done here: https://github.com/imrahil/OctoPrint-PrintHistory/blob/devel/octoprint_printhistory/export.py#L106

I am not sure how to do the excel stuff, his is what the library supports: https://xlsxwriter.readthedocs.io/working_with_dates_and_time.html

[fellipec]

I did some changes in the code to try to fix it:

            for row, historyDetails in enumerate(history_dict):
                for column, field in enumerate(fields):
                    if field == "timestamp":
                        value = formatTimestamp(historyDetails.get(field, '-'))
                        worksheet.write(row + 1, column, (value if value is not None else '-'))
                    elif field == "printTime":
                        value = formatPrintTime(historyDetails.get(field, '-'))
                        worksheet.write_datetime(row + 1, column, (value if value is not None else '-'),'hh:mm:ss')
                    else:
                        value = historyDetails.get(field, '-')
                        worksheet.write(row + 1, column, (value if value is not None else '-'))

            workbook.close()

def formatPrintTime(valueInSeconds):
     import datetime
     if valueInSeconds is not None:
         tmp = valueInSeconds
         hours = int(tmp/3600)
         tmp = tmp % 3600
         minutes = int(tmp / 60)
         tmp = tmp % 60
         seconds = int(tmp)
         printtime = datetime.datetime.strptime(str(hours).zfill(2) + ":" + str(minutes).zfill(2) + ":" + str(seconds).zfill(2),'%H:%M:%S')
         #return str(hours).zfill(3) + ":" + str(minutes).zfill(2) + ":" + str(seconds).zfill(2)
         return printtime
     else:
         return "-"

But unfortunatelly I got errors in the line 113 workbook.close() that I simply can't figure out

Traceback (most recent call last):
  File "/home/pi/OctoPrint/venv/lib/python3.9/site-packages/flask/app.py", line 2077, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/pi/OctoPrint/venv/lib/python3.9/site-packages/flask/app.py", line 1525, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/pi/OctoPrint/venv/lib/python3.9/site-packages/flask/app.py", line 1523, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/pi/OctoPrint/venv/lib/python3.9/site-packages/flask/app.py", line 1509, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "/home/pi/OctoPrint/venv/lib/python3.9/site-packages/octoprint_printhistory/__init__.py", line 239, in exportHistoryData
    return export.exportHistoryData(self, exportType)
  File "/home/pi/OctoPrint/venv/lib/python3.9/site-packages/octoprint_printhistory/export.py", line 113, in exportHistoryData
    workbook.close()
  File "/home/pi/OctoPrint/venv/lib/python3.9/site-packages/xlsxwriter/workbook.py", line 324, in close
    self._store_workbook()
  File "/home/pi/OctoPrint/venv/lib/python3.9/site-packages/xlsxwriter/workbook.py", line 709, in _store_workbook
    xml_files = packager._create_package()
  File "/home/pi/OctoPrint/venv/lib/python3.9/site-packages/xlsxwriter/packager.py", line 137, in _create_package
    self._write_worksheet_files()
  File "/home/pi/OctoPrint/venv/lib/python3.9/site-packages/xlsxwriter/packager.py", line 193, in _write_worksheet_files
    worksheet._assemble_xml_file()
  File "/home/pi/OctoPrint/venv/lib/python3.9/site-packages/xlsxwriter/worksheet.py", line 4225, in _assemble_xml_file
    self._write_sheet_data()
  File "/home/pi/OctoPrint/venv/lib/python3.9/site-packages/xlsxwriter/worksheet.py", line 5874, in _write_sheet_data
    self._write_rows()
  File "/home/pi/OctoPrint/venv/lib/python3.9/site-packages/xlsxwriter/worksheet.py", line 6071, in _write_rows
    self._write_cell(row_num, col_num, col_ref)
  File "/home/pi/OctoPrint/venv/lib/python3.9/site-packages/xlsxwriter/worksheet.py", line 6236, in _write_cell
    xf_index = cell.format._get_xf_index()
AttributeError: 'str' object has no attribute '_get_xf_index'

I've no idea what this error means, is something inside the xlswriter. Anyways, if you know what I did wrong, please tell.

Thanks for your time! export.py.txt

[justin-chandra]

@fellipec , @guysoft I made a change here that might fix the problem: https://github.com/imrahil/OctoPrint-PrintHistory/pull/97

[fellipec]

@fellipec , @guysoft I made a change here that might fix the problem: #97

I tested removing the zfill, Excel still import that as a text field, but at least when you format it to a time format, it works fine. I would be happy with this simple solution, thanks