Excel export hour format
Opened this issue · 4 comments
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.
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
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