Number formats not supported in .ODS #1569

Closed
opened 2019-07-19 03:47:56 +00:00 by dandv · 8 comments
dandv commented 2019-07-19 03:47:56 +00:00 (Migrated from github.com)
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

image

```js 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 ``` ![image](https://user-images.githubusercontent.com/33569/61508103-4ee37580-a99d-11e9-9203-d43b5c2a8af8.png)
SheetJSDev commented 2019-07-29 09:19:41 +00:00 (Migrated from github.com)

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

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 commented 2019-07-29 13:37:29 +00:00 (Migrated from github.com)

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

On Mon, Jul 29, 2019, 02:20 SheetJSDev notifications@github.com 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
https://github.com/SheetJS/js-xlsx/issues/1569?email_source=notifications&email_token=AAAIGINLRPQF4GXSQSIDIKLQB2Y4TA5CNFSM4IFCBZT2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD3ADYEQ#issuecomment-515914770,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAAIGIN7GN2MQNCZOBQLHMTQB2Y4TANCNFSM4IFCBZTQ
.

The $1,234.56 format would be a good start. On Mon, Jul 29, 2019, 02:20 SheetJSDev <notifications@github.com> 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 > <https://github.com/SheetJS/js-xlsx/issues/1569?email_source=notifications&email_token=AAAIGINLRPQF4GXSQSIDIKLQB2Y4TA5CNFSM4IFCBZT2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD3ADYEQ#issuecomment-515914770>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/AAAIGIN7GN2MQNCZOBQLHMTQB2Y4TANCNFSM4IFCBZTQ> > . >
dandv commented 2020-01-02 01:56:50 +00:00 (Migrated from github.com)

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

Happy New Year! 🎆 Any updates on this? Currency formats are an important feature.
GusMartins499 commented 2021-08-18 18:17:26 +00:00 (Migrated from github.com)
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

image

same issue

> ```js > 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 > ``` > > ![image](https://user-images.githubusercontent.com/33569/61508103-4ee37580-a99d-11e9-9203-d43b5c2a8af8.png) same issue
XiaoSunXS commented 2022-05-30 12:44:29 +00:00 (Migrated from github.com)

Any updates on this?

Any updates on this?
GusMartins499 commented 2022-05-30 13:15:58 +00:00 (Migrated from github.com)

Any updates on this?

I think not

> Any updates on this? I think not
SheetJSDev commented 2022-05-30 22:16:48 +00:00 (Migrated from github.com)

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

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`: ```xml <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 commented 2022-06-08 22:16:27 +00:00 (Migrated from github.com)

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/

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: ```js 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](https://github.com/SheetJS/sheetjs/files/8865333/bug.ods) Live demo: https://jsfiddle.net/m7hnapus/
Sign in to join this conversation.
No Milestone
No Assignees
1 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: sheetjs/sheetjs#1569
No description provided.