Number formats not supported in .ODS #1569
Labels
No Label
DBF
Dates
Defined Names
Features
Formula
HTML
Images
Infrastructure
Integration
International
ODS
Operations
Performance
PivotTables
Pro
Protection
Read Bug
SSF
SYLK
Style
Write Bug
good first issue
No Milestone
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies
No dependencies set.
Reference: sheetjs/sheetjs#1569
Loading…
Reference in New Issue
No description provided.
Delete Branch "%!s(<nil>)"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
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 $1,234.56 format would be a good start.
On Mon, Jul 29, 2019, 02:20 SheetJSDev notifications@github.com wrote:
Happy New Year! 🎆 Any updates on this? Currency formats are an important feature.
same issue
Any updates on this?
I think not
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
: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 LibreOfficeLibreOffice 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 literalY
in LibreOffice.YYY
is 4-digit year in Excel, but 2-digit year followed byY
in LibreOffice.Open Questions
Coming from LibreOffice, does the library need to translate between SheetJS and LibreOffice style number formats?
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
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:
bug.ods
Live demo: https://jsfiddle.net/m7hnapus/