Number formats not supported in .ODS

[dandv]

const XLSX = require('xlsx');

const wb = XLSX.utils.book_new();

const ws = XLSX.utils.json_to_sheet([{ dummy: '' }]);

XLSX.utils.book_append_sheet(wb, ws, 'Bug sheet');

ws.A1 = { t: 'n', f: '-10*20', z: '$0.00' };

XLSX.writeFile(wb, 'bug.ods');  // change extension to .xlsx and the format *is* output

[SheetJSDev]

The ODS number format representation is quite different from Excel, if there are specific formats you have in mind we can take a look

[dandv]

The $1,234.56 format would be a good start.

…

On Mon, Jul 29, 2019, 02:20 SheetJSDev ***@***.***> wrote: The ODS number format representation is quite different from Excel, if there are specific formats you have in mind we can take a look — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#1569>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAAIGIN7GN2MQNCZOBQLHMTQB2Y4TANCNFSM4IFCBZTQ> .

[dandv]

Happy New Year! 🎆 Any updates on this? Currency formats are an important feature.

[GusMartins499]

const XLSX = require('xlsx');

const wb = XLSX.utils.book_new();

const ws = XLSX.utils.json_to_sheet([{ dummy: '' }]);

XLSX.utils.book_append_sheet(wb, ws, 'Bug sheet');

ws.A1 = { t: 'n', f: '-10*20', z: '$0.00' };

XLSX.writeFile(wb, 'bug.ods');  // change extension to .xlsx and the format *is* output

same issue

[XiaoSunXS]

Any updates on this?

[GusMartins499]

Any updates on this?

I think not

[SheetJSDev]

There are multiple parts to this discussion. The SheetJS number format representation aligns with the text string in Excel.

OpenDocument vs Excel

OpenDocument stores number formats as a stream of tokens in XML. For example, this is the format for LibreOffice WW QQ:

    <number:date-style style:name="N121">
      <number:week-of-year/>
      <number:text> </number:text>
      <number:quarter number:style="long"/>
    </number:date-style>

Excel does not have support for the "week of year" (you can use the WEEKNUM function to get the same info, but it is not available as a token in the Number Format).

This is not particularly difficult, but it would require some decision on how to map unsupported features between ODS and the SheetJS number format.

LibreOffice vs OpenDocument

LibreOffice translates the OpenDocument XML representation into its own Number Format string.
The bulk of the issues lie in this chasm.

The ODF spec asserts that the default calendar system for the locale should be used. LibreOffice always appears to follow the gengō scheme. This has unexpected behavioral differences where en-US era is 2022 in Excel and ODF but 5782 in LibreOffice

LibreOffice vs Excel

In addition to the aforementioned issues, there are subtle mapping issues like

EEEE (corresponds to one rendering of long Era in Excel but two renderings in LibreOffice)

Y is 2-digit year in Excel, but literal Y in LibreOffice. YYY is 4-digit year in Excel, but 2-digit year followed by Y in LibreOffice.

Open Questions

1. Coming from LibreOffice, does the library need to translate between SheetJS and LibreOffice style number formats?

2. How should unsupported features be handled?

Note that this same issue affects Numbers. For example, the Duration cell formats in Numbers support week/day/hr/min/sec/ms while Excel only supports absolute hr/min/sec

[SheetJSDev]

08f5678 adds support for basic ODS number format read and write.

Issues like missing formats will require a general consensus and revisions to the SSF number formatting library. We'll probably have to expand support since Numbers also supports special formats like the number of weeks.

Style maps (ODF equivalent of multi-part formats in Excel) are not emitted. The main show-stopper is LO support. As it turns out, there are a number of bugs in LibreOffice support for ODF, to the extent that Excel itself seems to have better support for ODS than LibreOffice! We'll try to raise more issues with LO and hopefully the project will more closely align with the spec, at which point we'll revisit more of the ODF minutiae.

As for the literal example, @dandv there are LO bugs around automatic recalculation, but seeding the initial value produces a file that behaves as expected:

const wb = XLSX.utils.book_new();
const ws = XLSX.utils.json_to_sheet([{ dummy: '' }]);
XLSX.utils.book_append_sheet(wb, ws, 'Bug sheet');
ws.A1 = { t: 'n', v: -200, f: '-10*20', z: '$0.00' };
XLSX.writeFile(wb, 'bug.ods');

bug.ods

Live demo: https://jsfiddle.net/m7hnapus/