format Date question #841

New Issue

2017-10-16T09:34:56Z

cyzeng-Luca commented

2017-10-16 09:34:56 +00:00

(Migrated from github.com)

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: ![image](https://user-images.githubusercontent.com/25954683/31604811-0b1b2c84-b297-11e7-83f5-db6af73d059e.png) 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 ? ![image](https://user-images.githubusercontent.com/25954683/31605233-4e6fffea-b298-11e7-930e-656c962499cd.png)

SheetJSDev commented

2017-10-16 14:39:03 +00:00

(Migrated from github.com)

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)

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?

![image](https://user-images.githubusercontent.com/25954683/31643502-761baf68-b323-11e7-937a-3e91a483350e.png) i do it with you,but something is wrong. the result in below: ![image](https://user-images.githubusercontent.com/25954683/31643543-b4f83a94-b323-11e7-86c0-ec69677c1e3a.png) ![image](https://user-images.githubusercontent.com/25954683/31643553-c28b3fbc-b323-11e7-84aa-cd5ed4c88406.png) 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)

Can you share the original file?

cyzeng-Luca commented

2017-10-17 02:19:03 +00:00

(Migrated from github.com)

sure!

sure! ![image](https://user-images.githubusercontent.com/25954683/31643685-95d655e6-b324-11e7-9857-7c1e5a4838a3.png)

cyzeng-Luca commented

2017-10-17 02:24:38 +00:00

(Migrated from github.com)

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)

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)

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. ![image](https://user-images.githubusercontent.com/25954683/31645054-67345bcc-b32c-11e7-9d81-887a53248794.png) the test process is right？

SheetJSDev commented

2017-10-17 03:35:51 +00:00

(Migrated from github.com)

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)

I install xlsx@0.11.6 again form npm.
step:

I move the code block with 14573 line to 14575 line form node_modules/xlsx/xlsx.js.
I replace the node_modules/xlsx/dist/xlsx.full.min with what you provide.
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. ![image](https://user-images.githubusercontent.com/25954683/31646296-b156e22c-b333-11e7-8ad7-318bade749d3.png) it didn't work well!

SheetJSDev commented

2017-10-17 04:21:08 +00:00

(Migrated from github.com)

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)

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. ![image](https://user-images.githubusercontent.com/25954683/31650477-1228e1b6-b34a-11e7-8d47-baccd64f59bd.png) 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. ![image](https://user-images.githubusercontent.com/25954683/31650672-0a114d0a-b34b-11e7-9279-d96277b9cb4e.png) 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)

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)

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.