date format with ifmt=14
#326
Closed
opened 7 years ago by railty
·
4 comments
No Branch/Tag Specified
master
sankhavaramsaitulasiram/feat-fix-2752
maybeswapnil/issue2737
gh-pages
scottysseus/2560_2
ivan-trusov/fix-basedate
nandanv2702/issue_1300
protobi/master
ThomasChan/master
grantfayvor/master
tom-groves/bug-1105/rounding-error
mgreter/master
v0.87
v0.9.9
v0.9.8
v0.9.7
v0.9.6
v0.9.5
v0.9.4
v0.9.3
v0.9.2
v0.9.13
v0.9.12
v0.9.11
v0.9.10
v0.9.1
v0.9.0
v0.8.8
v0.8.7
v0.8.6
v0.8.5
v0.8.4
v0.8.3
v0.8.2
v0.8.1
v0.8.0
v0.7.9
v0.7.7
v0.7.6-i
v0.7.6-h
v0.7.6-a
v0.7.6
v0.7.5
v0.7.4
v0.7.3
v0.7.2
v0.7.11
v0.7.10
v0.7.1
v0.5.9
v0.5.8
v0.5.7
v0.5.10
v0.5.0
v0.4.3
v0.18.6
v0.18.5
v0.18.4+deno
v0.18.4
v0.18.3
v0.18.2
v0.18.1
v0.18.0+deno
v0.17.5
v0.17.0
v0.16.8
v0.16.7
v0.16.6
v0.16.5
v0.16.3
v0.16.2
v0.16.1
v0.16.0
v0.15.6
v0.15.5
v0.15.2
v0.14.0
v0.13.5
v0.13.4
v0.13.3
v0.13.1
v0.13.0
v0.12.9
v0.12.8
v0.12.7
v0.12.6
v0.12.5
v0.12.4
v0.12.3
v0.12.2
v0.12.13
v0.12.12
v0.12.11
v0.12.10
v0.12.1
v0.12.0
v0.11.9
v0.11.8
v0.11.7
v0.11.6
v0.11.5
v0.11.4
v0.11.3
v0.11.2
v0.11.19
v0.11.18
v0.11.17
v0.11.16
v0.11.15
v0.11.14
v0.11.13
v0.11.12
v0.11.11
v0.11.10
v0.11.1
v0.11.0
v0.10.9
v0.10.8
v0.10.7
v0.10.6
v0.10.5
v0.10.4
v0.10.3
v0.10.2
v0.10.1
v0.10.0
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
Milestone
Set milestone
Clear milestone
No items
No Milestone
Projects
Set Project
Clear projects
No project
Assignees
Assign users
Clear assignees
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#326
Reference in New Issue
There is no content yet.
Delete Branch '%!s(<nil>)'
Deleting a branch is permanent. It CANNOT be undone. Continue?
No
Yes
first of all, great project
I have this xls file which is excel 97 to 2003 version, one of the cell is date format, it shows "2015-09-13" in excel and libreoffice, but shows "15-09-13" in js-xlsx. the cell value is {t:'n', XF:{ifmt:14}}. I have checked the spec and it is indeed 'yy-mm-dd'. js-xlsx did the exactly correct thing according to the spec. it is excel or libreoffice didn't follow the spec.
anyone had similar experiences? what to do? the end user doesn't care the spec, they want it shows as excel. Right now I post processed it by text = value.w.replace(/(\d+)/(\d+)/(\d+)/, '20$3-$1-$2')
I can upload an excel sample if needed.
another question is can js-xlsx write the 97-2003 version xls file? if yes, please provide an example. I cannot get the options work
Thanks in advance
I think this is a bug. We can set the table_fmt[14] = 'MM/dd/yyyy' , but when call read(), parse_zip() -> make_ssf() will be call too.make_ssf() will init my custom options.
And I resolve this question by modify source code.
This is not a bug, but rather a deficiency in XLS and XLSX files. See https://github.com/SheetJS/js-xlsx/issues/560 for a longer discussion. The format code 14 is supposed to represent a "localized" date, which means the output depends on your computer settings. However, Excel doesn't store that information in the file! XLS has a country property but that isn't sufficient to determine the regional settings :(
Here are some examples of the same cell when I change my computer settings:
Russian:
Portugese:
English:
The short term answer is to tinker with the file; we're working on a better solution with localization
@SheetJSDev ok,tks.
We're adding an override
dateNF
option to the parsers so you can override the display value but preserve the original format in the file