cell with =24/2 formatted as hour results in "ValueError: invalid literal for int() with base 10: 'H0'"

[matkoniecz]

I am trying example from readme and on

data = get_data("test.ods")

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.7/dist-packages/pyexcel_ods/__init__.py", line 344, in get_data
    return read_data(afile, file_type=file_type, **keywords)
  File "/usr/local/lib/python2.7/dist-packages/pyexcel_io/__init__.py", line 314, in get_data
    data[key] = list(data[key])
  File "/usr/local/lib/python2.7/dist-packages/pyexcel_ods/__init__.py", line 184, in to_array
    cell_value = self._read_cell(cell)
  File "/usr/local/lib/python2.7/dist-packages/pyexcel_ods/__init__.py", line 217, in _read_cell
    n_value = VALUE_CONVERTERS[cell_type](value)
  File "/usr/local/lib/python2.7/dist-packages/pyexcel_ods/__init__.py", line 78, in time_value
    minute = int(value[5:7])
ValueError: invalid literal for int() with base 10: 'H0'

To reproduce:

• create a new file
• put =24/2 into A1 cell
• put 23:00 into A2 cell
• copy format from A2 cell to A1 cell
• save as test.ods
• attempt to load with

from pyexcel_ods import get_data
data = get_data("your_file.ods")

Ods file was created with LibreOffice Calc - Version: 5.0.4.2, Build ID: 1:5.0.4rc2-0ubuntu1trusty1, Locale: pl-PL (en_US.UTF-8). Running on Ubuntu 14.04.

Would it be useful to upload this .ods file somewhere or is it reproducible also in other environments?

[chfw]

It was reproduced here with even LibreOffice Spreadsheet 4.1. Here is what it carries:

<table:table-cell table:formula="of:=24/2" office:value-type="time" 
office:time-value="PT288H00M00S"><text:p>00:00:00</text:p></table:table-cell>

'PT288H00M00S' violates this spec: PThhHmmMss,ffffS hence kills the 'time_value' converter.

Will find a workround for it.

[matkoniecz]

Have you checked whatever there is already LibreOffice bug report for that problem? It will probably lay unfixed for years but...

[chfw]

Just googled a bit and seems google didn't know much about it.

I found the simpler step to reproduce:

1. create a new file
2. put =24/2 in A1
3. format A1 as time
4. then you will see 00:00:00 in the cell while the formula field is 24/2.
5. then save it and load it with get_data from pyexcel_ods

[chfw]

Facing various choices that I could do, I think it makes sense to throw a readable exception to the pyexcel_ods user/developer that he/she needs to fix the formatting in the ods file. I could not give a default value for such field, could I?

[matkoniecz]

see https://bugs.documentfoundation.org/show_bug.cgi?id=97631

[chfw]

Good job. I will code up a nice exception handler at the mean time.

[matkoniecz]

Somebody posted in https://bugs.documentfoundation.org/show_bug.cgi?id=97631 claim that this behaviour of LO is not violating spec.

[chfw]

Had a look. day field could be '[0-9]+' instead of '[0-9]{2}'.

For future reference, I am copying the iso link here: https://www.w3.org/TR/2004/REC-xmlschema-2-20041028/#duration

Will make a fix.

[matkoniecz]

Thanks!

[chfw]

released to pypi. thanks for your persistence.