ODS exports numbers as text?

[matthijskooijman]

I've been trying to export data with numbers in them, and found I could not do calculations with them in the resulting ODS spreadsheet (the values are left-aligned instead of right aligned, and have a ' in front of them).

Here's my testcase:

data = tablib.Dataset()
data.append((1, 2.1))
f = open('tmp.ods', 'w')
f.write(data.ods)

Opening the resulting ODS in libreoffice shows:

Note the leading ' in the field value. I suspect that this leading ' is not written by tablib itself, but tablib writes out table cells as text. Here's a snippet from content.xml in the generated ODS:

<table:table-cell><text:p>1</text:p></table:table-cell><table:table-cell><text:p>2.1</text:p></table:table-cell>

The above screenshot is with the libreoffice locale set to "English (USA)". I originallly had it set to "Dutch (Netherlands)", which uses , as the decimal separator, so I suspected it was a decimal separator problem, but switching to a local with . as the decimal separator did not help.

Exporting in the xlsx format does give a proper numerical value that can be used in calculations (with both locales), so I'll be using that as a workaround for now.

[claudep]

I would appreciate if you could test my pull request.

[jrecasens]

@claudep Would you please also add support for excel?

xlsx and xls export_type also exports numbers as text:

[matthijskooijman]

xlsx and xls export_type also exports numbers as text:

Are you sure? I believe I switched from ODS to XLSX because that can export numbers. Looking at my code, I am explicitly passing widget=import_export.widgets.DecimalWidget(), but I'm not entirely sure if that was needed for exporting numbers, or that's just because of some other changes I have (I have a custom MonetaryResourceWidget that I need to revert for exporting, so replace the widget with DecimalWidget). But you could try if setting the widget explicitly helps.

[claudep]

There is already a test that export and reimport various data as xlsx and ensure that data format is kept: https://github.com/jazzband/tablib/blob/bbc273951cefc616f1a865dd1c21003859a9da14/tests/test_tablib.py#L1046