Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

date format with ifmt=14 #326

Closed
railty opened this issue Nov 29, 2015 · 4 comments
Closed

date format with ifmt=14 #326

railty opened this issue Nov 29, 2015 · 4 comments
Labels
Dates International Read Bug SSF

Comments

@railty
Copy link

railty commented Nov 29, 2015

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
@hstarorg
Copy link

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.

@SheetJSDev
Copy link
Contributor

This is not a bug, but rather a deficiency in XLS and XLSX files. See #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

@hstarorg
Copy link

@SheetJSDev ok,tks.

@SheetJSDev
Copy link
Contributor

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

@SheetJSDev SheetJSDev mentioned this issue May 17, 2017
Closed
@SheetJSDev SheetJSDev mentioned this issue Jun 24, 2017
Closed
@SheetJSDev SheetJSDev mentioned this issue Jul 6, 2017
Closed
@arbruijn arbruijn mentioned this issue Mar 5, 2020
Open
RimaCiklum pushed a commit to Folcon/js-xlsx that referenced this issue Aug 20, 2020
@SheetJSDev @RimaCiklum
version bump 0.9.13: string formatting
91b46a2 
- dateNF parse option controls interpretation of code 14
- SSF updated to 0.9.1
- SYLK write formulae
- DIF support Excel-style data storage
- ODS/FODS automatic styles for date formatting

Issues:
- Fixes protobi#181 h/t @charlesno
- Fixes SheetJS#200 h/t @JohnJeong123
- Fixes SheetJS#208 h/t @jerryhe88
- Fixes SheetJS#262 h/t @JohnJeong123
- Fixes SheetJS#269 h/t @calebeaires
- Fixes SheetJS#326 h/t @railty
- Fixes SheetJS#392 h/t @FourLeafClover
- Fixes SheetJS#449 h/t @dougschiller
- Fixes SheetJS#560 h/t @dpackage
@SheetJSDev SheetJSDev mentioned this issue Apr 12, 2022
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Dates International Read Bug SSF
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@hstarorg @railty @SheetJSDev @reviewher