Issue parsing #181

New Issue

2015-02-25T12:24:50Z

chneau commented

2015-02-25 12:24:50 +00:00

(Migrated from github.com)

Hi,

I have an strange issue for this special line:

that become this :
blabla,town,,blabla3,,12ok
blabla2,23/02/2015,,blabla4,,2/23/15
You can notice that the second 23/2/15 become 2/23/15.

Here is the link of the xlsx file : http://goo.gl/dc35Yq

Kind regards,
Charles

Hi, I have an strange issue for this special line: ![image](https://cloud.githubusercontent.com/assets/5753629/6370471/cf3d641c-bce8-11e4-9489-6c17b6875f6d.png) that become this : blabla,town,,blabla3,,12ok blabla2,23/02/2015,,blabla4,,2/23/15 You can notice that the second 23/2/15 become 2/23/15. Here is the link of the xlsx file : http://goo.gl/dc35Yq Kind regards, Charles

SheetJSDev commented

2015-02-25 16:02:30 +00:00

(Migrated from github.com)

@CharlesNo this is what shows up when I open the file:

Some date formats are based on the regional settings of the computer (so it will look different in different systems). Currently the default US regional short date setting m/d/yy is manually forced, although it is a good idea to add an option to indicate system settings.

@CharlesNo this is what shows up when I open the file: ![](http://i.imgur.com/CsobYqp.png) Some date formats are based on the regional settings of the computer (so it will look different in different systems). Currently the default US regional short date setting `m/d/yy` is manually forced, although it is a good idea to add an option to indicate system settings.

chneau commented

2015-03-02 14:32:01 +00:00

(Migrated from github.com)

@SheetJSDev thank you for your very quick answer

I did a hack for myself on the xslx.js on parse_date_code, juste inverted two index in the return :

        out.m = dout[2];
        out.d = dout[1];

And so, with moment.js, use moment(dateNoMoreUS, 'D-M-YY'), and it works pretty well for the moment (haha).

Edit:
Finally nope, but I found more details about the problem:
Ok I did this hack on xlsx.js, now the problem is that my 'UK' date we can see on the post above on cell B2 have the day and month reversed too.
What I see on a English Excel:

What I get after parsing :
02/04/2015
and
3/2/15
So I don't know why, hope it can helps you, looks like each cell have a regional setting ?
(I revert my own 'hack', certainly I have to hack elsewhere)

Edit2:
On the function format, at the end of the method before the return,
I added this :
if (f[1] == 'm/d/yy') f[1] = 'dd/mm/yyyy';
It does the trick for me for the moment, and it's a way easier when all dates are the same format.

Kind regards,
Charles.

@SheetJSDev thank you for your very quick answer I did a hack for myself on the xslx.js on parse_date_code, juste inverted two index in the return : ``` out.m = dout[2]; out.d = dout[1]; ``` And so, with moment.js, use moment(dateNoMoreUS, 'D-M-YY'), and it works pretty well for the moment (haha). Edit: Finally nope, but I found more details about the problem: Ok I did this hack on xlsx.js, now the problem is that my 'UK' date we can see on the post above on cell B2 have the day and month reversed too. What I see on a English Excel: ![image](https://cloud.githubusercontent.com/assets/5753629/6442738/fefe775e-c0e9-11e4-90c3-c42b94bc7b5e.png) What I get after parsing : 02/04/2015 and 3/2/15 So I don't know why, hope it can helps you, looks like each cell have a regional setting ? (I revert my own 'hack', certainly I have to hack elsewhere) Edit2: On the function format, at the end of the method before the return, I added this : if (f[1] == 'm/d/yy') f[1] = 'dd/mm/yyyy'; It does the trick for me for the moment, and it's a way easier when all dates are the same format. Kind regards, Charles.

SheetJSDev commented

2015-03-02 15:39:12 +00:00

(Migrated from github.com)

@CharlesNo it's not the cell, it's actually the number format. To see what's going on, after the line cf = styles.CellXf[tag.s]; add a line if(cf) console.log(p, cf.numFmtId);

In the first date cell, you see { t: 'n', v: 42058 } 172 which indicates a custom date format. In the second cell, you see { t: 'n', v: 42058 } 14, which references the localized date format. Gotta love the magic here :(

If you want to hack it, the easiest way is to override the format table:

var table_fmt = {
    // ... entries here ...
    14: 'm/d/yy', // <-- this is the line you want to change

It looks like the format for your region is dd/mm/yyyy;@, so just replacing that entry should do the trick in the very short term.

PS: @pietersv fun times here: ECMA-376 gives an incorrect date format for code 14 (and does not indicate that the format is localized) and [MS-XLS] 2.5.165 directs people to ECMA-376.

@CharlesNo it's not the cell, it's actually the number format. To see what's going on, after the line `cf = styles.CellXf[tag.s];` add a line `if(cf) console.log(p, cf.numFmtId);` In the first date cell, you see `{ t: 'n', v: 42058 } 172` which indicates a custom date format. In the second cell, you see `{ t: 'n', v: 42058 } 14`, which references the localized date format. Gotta love the magic here :( If you want to hack it, the easiest way is to override the format table: ``` var table_fmt = { // ... entries here ... 14: 'm/d/yy', // <-- this is the line you want to change ``` It looks like the format for your region is `dd/mm/yyyy;@`, so just replacing that entry should do the trick in the very short term. PS: @pietersv fun times here: ECMA-376 gives an incorrect date format for code 14 (and does not indicate that the format is localized) and [MS-XLS] 2.5.165 directs people to ECMA-376.

chneau commented

2015-03-02 15:52:50 +00:00

(Migrated from github.com)

@SheetJSDev Thank you very much, I should have take a look upper in the code, I'm sure I scrolled on it without minding...

Thank you again, if you need xlsx files for helping you to debug, don't hesitate to ask me.

Regards,
Charles.

@SheetJSDev Thank you very much, I should have take a look upper in the code, I'm sure I scrolled on it without minding... Thank you again, if you need xlsx files for helping you to debug, don't hesitate to ask me. Regards, Charles.

SheetJSDev commented

2017-04-30 16:04:24 +00:00

(Migrated from github.com)

We are pushing a change that allows you to specify a dateNF option to the parser to override the date format. It will correctly write a file with the original format, but the JS API will use the specified format instead.

We are pushing a change that allows you to specify a `dateNF` option to the parser to override the date format. It will correctly write a file with the original format, but the JS API will use the specified format instead.

👍 1

Sign in to join this conversation.