Exporting Date-Times in Iso Format #640

New Issue

2017-04-21T13:59:27Z

SCullman commented

2017-04-21 13:59:27 +00:00

(Migrated from github.com)

I want to export date/time values to JSON formatted as ISO 8601 values

My current script:

const XLSX = require('xlsx');
...
const file = XLSX.readFile(path,{cellDates:true, cellNF:false});
for (sheet in file.Sheets){
     //remove prerendered values, otherwise dateNF is ignored
     for (cellref in sheet){
         const c = sheet[cellref];
         if(c.t==="d"){
             delete c.w;
             delete c.z; 
         }
    }
   const format = "YYYY-MM-DD hh:mm:ss";
   //const format = "YYYY-MM-DDThh:mm:ss"; throws exception as it doesn't recognize T
   const data = XLSX.utils.sheet_to_json(sheet, {header: 1, dateNF:format});
   .....
}

The cells itself already contain the correct datetime inside the v(alue) attribute.

I suggest an option like
XLSX.utils.sheet_to_json(sheet, {header: 1, ExportDatesAsISO:true});

Test data:
datum.xlsx

I want to export date/time values to JSON formatted as ISO 8601 values My current script: ```javascript const XLSX = require('xlsx'); ... const file = XLSX.readFile(path,{cellDates:true, cellNF:false}); for (sheet in file.Sheets){ //remove prerendered values, otherwise dateNF is ignored for (cellref in sheet){ const c = sheet[cellref]; if(c.t==="d"){ delete c.w; delete c.z; } } const format = "YYYY-MM-DD hh:mm:ss"; //const format = "YYYY-MM-DDThh:mm:ss"; throws exception as it doesn't recognize T const data = XLSX.utils.sheet_to_json(sheet, {header: 1, dateNF:format}); ..... } ``` The cells itself already contain the correct datetime inside the v(alue) attribute. I suggest an option like XLSX.utils.sheet_to_json(sheet, {header: 1, ExportDatesAsISO:true}); Test data: [datum.xlsx](https://github.com/SheetJS/js-xlsx/files/947214/datum.xlsx)

👍 2

SCullman commented

2017-04-21 17:32:39 +00:00

(Migrated from github.com)

This script would work for me but feels just wrong:

const XLSX = require('xlsx');
...
const file = XLSX.readFile(path,{cellDates:true});
for (sheet in file.Sheets){
     for (cellref in sheet){
         const c = sheet[cellref];
         if(c.t==="d"){
             c.w = c.v;
         }
    }
   const data = XLSX.utils.sheet_to_json(sheet, {header: 1});
   .....
}

This script would work for me but feels just wrong: ```javascript const XLSX = require('xlsx'); ... const file = XLSX.readFile(path,{cellDates:true}); for (sheet in file.Sheets){ for (cellref in sheet){ const c = sheet[cellref]; if(c.t==="d"){ c.w = c.v; } } const data = XLSX.utils.sheet_to_json(sheet, {header: 1}); ..... } ```

👍 1

SheetJSDev commented

2017-04-21 17:50:00 +00:00

(Migrated from github.com)

Would it make more sense to add an option that suppresses the formatted text for all cells? something like cellText which would not generate the .w text if it is set to false?

Would it make more sense to add an option that suppresses the formatted text for all cells? something like `cellText` which would not generate the `.w` text if it is set to `false`?

SCullman commented

2017-04-21 18:05:23 +00:00

(Migrated from github.com)

This would help; the parsing through the cells could be avoided.
But even then it is not possible to return the date in ISO format, as YYYY-MM-DDThh:mm:ss is not accepted as dateNF.

This would help; the parsing through the cells could be avoided. But even then it is not possible to return the date in ISO format, as YYYY-MM-DD**T**hh:mm:ss is not accepted as _dateNF_.

SheetJSDev commented

2017-04-21 22:25:56 +00:00

(Migrated from github.com)

@SCullman so the first part was pushed (cellText:false will not generate the format).

For your specific format, the correct code is YYYY-MM-DD"T"hh:mm:ss. The double-quotes are necessary to indicate that T is a literal T and not a format element. If you try to enter YYYY-MM-DDThh:mm:ss and save the file, the actual format is encoded in the xml as

yyyy\-mm\-dd&quot;T&quot;hh:mm:ss

@SCullman so the first part was pushed (`cellText:false` will not generate the format). For your specific format, the correct code is `YYYY-MM-DD"T"hh:mm:ss`. The double-quotes are necessary to indicate that T is a literal T and not a format element. If you try to enter `YYYY-MM-DDThh:mm:ss` and save the file, the actual format is encoded in the xml as ```xml yyyy\-mm\-dd"T"hh:mm:ss ```

SCullman commented

2017-04-22 06:46:38 +00:00

(Migrated from github.com)

Thank you, works perfect!

Sign in to join this conversation.