format Date question #841

New Issue

Closed

opened 2017-10-16 09:34:56 +00:00 by cyzeng-Luca · 13 comments

cyzeng-Luca commented 2017-10-16 09:34:56 +00:00 (Migrated from github.com)

Copy Link

I lookup many issues about format Date, but not understand what to modify my code.
My code in Below:

I want to format Date by 'YYYY-MM-DD' not "m/d/yy" in what XLSX.utils.sheet_to_json return,like excel is "2017/10/16", i get the return object as the same "2017/10/16" not "10/16/17".
now,i will get JS Date object by the code. may you give me a example ?

I lookup many issues about format Date, but not understand what to modify my code. My code in Below:  I want to format Date by 'YYYY-MM-DD' not "m/d/yy" in what XLSX.utils.sheet_to_json return,like excel is "2017/10/16", i get the return object as the same "2017/10/16" not "10/16/17". now,i will get JS Date object by the code. may you give me a example ? 

SheetJSDev commented 2017-10-16 14:39:03 +00:00 (Migrated from github.com)

Copy Link

On the read side, you also need to pass cellText:false:

var wb = XLSX.read(binary, {type:'binary', cellDates:true, cellNF: false, cellText:false});

In the conversion, you shouldn't set raw:

XLSX.utils.sheet_to_json(ws, {dateNF:"YYYY-MM-DD"})

raw:true will always return the raw values, so you don't want to pass that to the conversion. When that happens, the conversion uses the stored formatted text value. Those strings are generated by default, which is why you need both cellText:false (so the text isn't generated) and cellNF:false (so the date formats aren't generated).

On the read side, you also need to pass `cellText:false`: ```js var wb = XLSX.read(binary, {type:'binary', cellDates:true, cellNF: false, cellText:false}); ``` In the conversion, you shouldn't set `raw`: ```js XLSX.utils.sheet_to_json(ws, {dateNF:"YYYY-MM-DD"}) ``` `raw:true` will always return the raw values, so you don't want to pass that to the conversion. When that happens, the conversion uses the stored formatted text value. Those strings are generated by default, which is why you need both `cellText:false` (so the text isn't generated) and `cellNF:false` (so the date formats aren't generated).

👍 8 ❤️ 6 🎉 5

cyzeng-Luca commented 2017-10-17 02:15:04 +00:00 (Migrated from github.com)

Copy Link

i do it with you,but something is wrong.
the result in below:


i think i miss some plugin like SSF? I only install XLSX@0.11.6，that's reason?

 i do it with you,but something is wrong. the result in below:   i think i miss some plugin like SSF? I only install XLSX@0.11.6，that's reason?

SheetJSDev commented 2017-10-17 02:16:33 +00:00 (Migrated from github.com)

Copy Link

Can you share the original file?

Can you share the original file?

cyzeng-Luca commented 2017-10-17 02:19:03 +00:00 (Migrated from github.com)

Copy Link

sure!

sure! 

cyzeng-Luca commented 2017-10-17 02:24:38 +00:00 (Migrated from github.com)

Copy Link

i upload .zip ,the original is .rar

会员信息模板-201611301634 (7).zip

i upload .zip ,the original is .rar [会员信息模板-201611301634 (7).zip](https://github.com/SheetJS/js-xlsx/files/1389566/-201611301634.7.zip)

SheetJSDev commented 2017-10-17 02:40:12 +00:00 (Migrated from github.com)

Copy Link

Thanks for sharing! It's an XLS file and the issue is that the cellDates and cellText logic are tied together. The offending block is https://github.com/SheetJS/js-xlsx/blob/master/bits/76_xls.js#L75-L88 The part which analyzes the number format is https://github.com/SheetJS/js-xlsx/blob/master/bits/76_xls.js#L85-L87 and should be moved outside of the loop:

                        else p.w = SSF._general(p.v);
                }
                else p.w = SSF.format(fmtid,p.v, {date1904:!!date1904});
-               if(opts.cellDates && fmtid && p.t == 'n' && SSF.is_date(SSF._table[fmtid] || String(fmtid))) {
-                       var _d = SSF.parse_date_code(p.v); if(_d) { p.t = 'd'; p.v = new Date(_d.y, _d.m-1,_d.d,_d.H,_d.M,_d.S,_d.u); }
-               }
        } catch(e) { if(opts.WTF) throw e; }
+       if(opts.cellDates && fmtid && p.t == 'n' && SSF.is_date(SSF._table[fmtid] || String(fmtid))) {
+               var _d = SSF.parse_date_code(p.v); if(_d) { p.t = 'd'; p.v = new Date(_d.y, _d.m-1,_d.d,_d.H,_d.M,_d.S,_d.u); }
+       }
 }
 
 function make_cell(val, ixfe, t)/*:Cell*/ {

Here's an updated version: xlsx.full.min.js.zip -- can you unzip and test against this version? If it works feel free to submit this as a patch with that change

Thanks for sharing! It's an XLS file and the issue is that the cellDates and cellText logic are tied together. The offending block is https://github.com/SheetJS/js-xlsx/blob/master/bits/76_xls.js#L75-L88 The part which analyzes the number format is https://github.com/SheetJS/js-xlsx/blob/master/bits/76_xls.js#L85-L87 and should be moved outside of the loop: ```diff else p.w = SSF._general(p.v); } else p.w = SSF.format(fmtid,p.v, {date1904:!!date1904}); - if(opts.cellDates && fmtid && p.t == 'n' && SSF.is_date(SSF._table[fmtid] || String(fmtid))) { - var _d = SSF.parse_date_code(p.v); if(_d) { p.t = 'd'; p.v = new Date(_d.y, _d.m-1,_d.d,_d.H,_d.M,_d.S,_d.u); } - } } catch(e) { if(opts.WTF) throw e; } + if(opts.cellDates && fmtid && p.t == 'n' && SSF.is_date(SSF._table[fmtid] || String(fmtid))) { + var _d = SSF.parse_date_code(p.v); if(_d) { p.t = 'd'; p.v = new Date(_d.y, _d.m-1,_d.d,_d.H,_d.M,_d.S,_d.u); } + } } function make_cell(val, ixfe, t)/*:Cell*/ { ``` Here's an updated version: [`xlsx.full.min.js`.zip](https://github.com/SheetJS/js-xlsx/files/1389592/xlsx.full.min.js.zip) -- can you unzip and test against this version? If it works feel free to submit this as a patch with that change

cyzeng-Luca commented 2017-10-17 03:15:21 +00:00 (Migrated from github.com)

Copy Link

I replace the node_modules/xlsx/dist/xlsx.full.min with what you provide,And change "main": "./dist/xlsx.full.min". that right?
then, i npm run dev to start my item,but it throws error when i import excel.

the test process is right？

I replace the node_modules/xlsx/dist/xlsx.full.min with what you provide,And change "main": "./dist/xlsx.full.min". that right? then, i npm run dev to start my item,but it throws error when i import excel.  the test process is right？

SheetJSDev commented 2017-10-17 03:35:51 +00:00 (Migrated from github.com)

Copy Link

You shouldn't have to change main -- is your webpack config pulling in the dist/xlsx.full.min.js script or is it bundling the xlsx.js script? If it is bundling the main script, you can modify xlsx.js directly -- the block in https://github.com/SheetJS/js-xlsx/blob/master/xlsx.js#L14573-L14575 should be moved as shown in the diff above

You shouldn't have to change main -- is your webpack config pulling in the dist/xlsx.full.min.js script or is it bundling the xlsx.js script? If it is bundling the main script, you can modify xlsx.js directly -- the block in https://github.com/SheetJS/js-xlsx/blob/master/xlsx.js#L14573-L14575 should be moved as shown in the diff above

cyzeng-Luca commented 2017-10-17 04:07:53 +00:00 (Migrated from github.com)

Copy Link

I install xlsx@0.11.6 again form npm.
step:

1. I move the code block with 14573 line to 14575 line form node_modules/xlsx/xlsx.js.
2. I replace the node_modules/xlsx/dist/xlsx.full.min with what you provide.
3. I move the code block with 14573 line to 14575 line form node_modules/xlsx/dist/xlsx.js.
   
   it didn't work well!

I install xlsx@0.11.6 again form npm. step: 1. I move the code block with 14573 line to 14575 line form node_modules/xlsx/xlsx.js. 2. I replace the node_modules/xlsx/dist/xlsx.full.min with what you provide. 3. I move the code block with 14573 line to 14575 line form node_modules/xlsx/dist/xlsx.js.  it didn't work well!

SheetJSDev commented 2017-10-17 04:21:08 +00:00 (Migrated from github.com)

Copy Link

You should have access to nodejs on your computer if you are using webpack. Move your example file to test.xls and download the xlsx.full.min.js file. Additionally, install xlsx from npm and test:

/* using the version from npm */
var XLSX1 = require('xlsx');
var ws1 = XLSX1.readFile('test.xls', {cellDates:true, cellNF:false, cellText:false}).Sheets.huiyuan;
console.log("old", XLSX1.utils.sheet_to_json(ws1, {dateNF:'YYYY-MM-DD'})[0]['生日']);

/* using the modified xlsx.full.min.js */
var XLSX2 = require('./xlsx.full.min');
var ws2 = XLSX2.readFile('test.xls', {cellDates:true, cellNF:false, cellText:false}).Sheets.huiyuan;
console.log("new", XLSX2.utils.sheet_to_json(ws2, {dateNF:'YYYY-MM-DD'})[0]['生日']);

Running from node, I see the output:

$ node issue841.js 
old 41946
new 2014-11-03

Do you see the same thing?

You should have access to nodejs on your computer if you are using webpack. Move your example file to `test.xls` and download the `xlsx.full.min.js` file. Additionally, install `xlsx` from npm and test: ```js /* using the version from npm */ var XLSX1 = require('xlsx'); var ws1 = XLSX1.readFile('test.xls', {cellDates:true, cellNF:false, cellText:false}).Sheets.huiyuan; console.log("old", XLSX1.utils.sheet_to_json(ws1, {dateNF:'YYYY-MM-DD'})[0]['生日']); /* using the modified xlsx.full.min.js */ var XLSX2 = require('./xlsx.full.min'); var ws2 = XLSX2.readFile('test.xls', {cellDates:true, cellNF:false, cellText:false}).Sheets.huiyuan; console.log("new", XLSX2.utils.sheet_to_json(ws2, {dateNF:'YYYY-MM-DD'})[0]['生日']); ``` Running from node, I see the output: ``` $ node issue841.js old 41946 new 2014-11-03 ``` Do you see the same thing?

cyzeng-Luca commented 2017-10-17 06:55:03 +00:00 (Migrated from github.com)

Copy Link

I test in nodejs,the new one work well.

I mean that i want to use the new one which is fixed in my item with webpack,should i modify something? i think if i modify the pakege.json "main": "./xlsx" to "main": "./dist/xlsx.full.min" which is new one or modify webpack seting alias will solve the bug.

what can i do will use the new one which is fixed in my item?
crying....

I test in nodejs,the new one work well.  I mean that i want to use the new one which is fixed in my item with webpack,should i modify something? i think if i modify the pakege.json "main": "./xlsx" to "main": "./dist/xlsx.full.min" which is new one or modify webpack seting alias will solve the bug.  what can i do will use the new one which is fixed in my item? crying....

SheetJSDev commented 2017-10-17 07:13:31 +00:00 (Migrated from github.com)

Copy Link

Does the import import * as XLSX from 'xlsx' work?

Does the import `import * as XLSX from 'xlsx'` work?

cyzeng-Luca commented 2017-10-17 07:47:45 +00:00 (Migrated from github.com)

Copy Link

not work if i import XLSX from 'util/xlsx.full.min.js. i will wait for updating the package.

not work if i import XLSX from 'util/xlsx.full.min.js. i will wait for updating the package.

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

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.20.3

v0.20.2

v0.20.1

v0.20.0

v0.19.3

v0.19.2

v0.19.1

v0.19.0

v0.18.12

v0.18.11

v0.18.10

v0.18.9

v0.18.8

v0.18.7

v0.18.5

v0.18.4

v0.18.3

v0.18.2

v0.18.1

v0.18.0

v0.17.5

v0.17.4

v0.17.3

v0.17.2

v0.17.1

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.13

v0.12.12

v0.12.11

v0.12.10

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.1

v0.12.0

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.9

v0.11.8

v0.11.7

v0.11.6

v0.11.5

v0.11.4

v0.11.3

v0.11.2

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

v0.9.13

v0.9.12

v0.9.11

v0.9.10

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.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.11

v0.7.10

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.1

v0.5.10

v0.5.9

v0.5.8

v0.5.7

v0.5.0

v0.4.3

Labels

Clear labels   

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 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

Clear milestone

No items

No Milestone

Assignees

Clear assignees

No Assignees

1 Participants

Notifications

Subscribe

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#841

No description provided.