Formatting dates in XLSX.utils.sheet_to_json makes date lose 1 day #3019

New Issue

Blanay · 2023-10-20T11:22:58Z

Blanay commented

2023-10-20 11:22:58 +00:00

Hello !
I know this is a topic that has been mentionned multiple times but I can't seem to find a fix.
Here's the code :
const workbook = XLSX.read(data, {type: 'array', cellDates: true, cellText: false, cellNF: true});
const firstSheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[firstSheetName];
const jsonData = XLSX.utils.sheet_to_json(worksheet, {raw: false, dateNF: 'd"/"m"/"yyyy'});

Everytime I try to run this code, I get the right date format, but the date loses one day.
For example, in the original XLSX file:

But once I print the jsonData, I get:

Any solution to this ?
I'm using the 0.20.0 version's standalone file on Chrome (that was supposed to fix this issue):
https://cdn.sheetjs.com/xlsx-0.20.0/package/dist/xlsx.full.min.js

Hello ! I know this is a topic that has been mentionned multiple times but I can't seem to find a fix. Here's the code : `const workbook = XLSX.read(data, {type: 'array', cellDates: true, cellText: false, cellNF: true});` `const firstSheetName = workbook.SheetNames[0];` `const worksheet = workbook.Sheets[firstSheetName];` `const jsonData = XLSX.utils.sheet_to_json(worksheet, {raw: false, dateNF: 'd"/"m"/"yyyy'});` Everytime I try to run this code, I get the right date format, but the date loses one day. For example, in the original XLSX file: ![image](/attachments/3a4aa285-9e5b-4160-a1c4-3b353de431a4) But once I print the jsonData, I get: ![image](/attachments/d0c90264-2e62-4f50-a1ab-636d765bb4bb) ![image](/attachments/2d749407-1c97-46be-9e33-3ce2d7a3f8a0) Any solution to this ? I'm using the 0.20.0 version's standalone file on Chrome (that was supposed to fix this issue): https://cdn.sheetjs.com/xlsx-0.20.0/package/dist/xlsx.full.min.js

image.png

1.8 KiB

image.png

2.3 KiB

image.png

2.3 KiB

sheetjs commented

2023-10-20 16:23:59 +00:00

Can you share the file?

Blanay commented

2023-10-20 16:29:23 +00:00

If you're talking about the XLSX file, here's the one I did the tests with.

Template_upload_passagers.xlsx

19 KiB

sheetjs commented

2023-10-20 16:45:55 +00:00

Assuming you are talking about column E, can you display the raw date before generating the final result?

const workbook = XLSX.read(data, {type: 'array', cellDates: true, cellText: false, cellNF: true});
const firstSheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[firstSheetName];

console.log(JSON.stringify(worksheet["E2"]));
console.log(JSON.stringify(worksheet["E3"]));

Also, since you are passing cellNF: true, the default date format will be added to cells. You need to wipe the number format for date cells after reading and before generating the data:

const range = XLSX.utils.decode_range(worksheet["!ref"]);
for(let R = range.s.r; R <= range.e.r; ++R) {
  for(let C = range.s.c; C <= range.e.c; ++C) {
    const addr = XLSX.utils.encode_cell({c:C,r:R});
    if(!worksheet[addr] || worksheet[addr].t != "d") continue;
    delete worksheet[addr].z;
  }
}

Assuming you are talking about column E, can you display the raw date before generating the final result? ```js const workbook = XLSX.read(data, {type: 'array', cellDates: true, cellText: false, cellNF: true}); const firstSheetName = workbook.SheetNames[0]; const worksheet = workbook.Sheets[firstSheetName]; console.log(JSON.stringify(worksheet["E2"])); console.log(JSON.stringify(worksheet["E3"])); ``` Also, since you are passing `cellNF: true`, the default date format will be added to cells. You need to wipe the number format for date cells after reading and before generating the data: ```js const range = XLSX.utils.decode_range(worksheet["!ref"]); for(let R = range.s.r; R <= range.e.r; ++R) { for(let C = range.s.c; C <= range.e.c; ++C) { const addr = XLSX.utils.encode_cell({c:C,r:R}); if(!worksheet[addr] || worksheet[addr].t != "d") continue; delete worksheet[addr].z; } } ```

sheetjs referenced this issue

2023-10-20 16:48:51 +00:00

How to correctly format date to yyyy-mm-dd from xlsx to csv #3013

Blanay commented

2023-10-20 17:07:10 +00:00

I don't really need the cellNF: true, it was just for testing purpose. If I remove it, will I still need to use the second code snippet that you sent me ?
Here's the raw date before generating the final result using the first code snippet (without cellNF: true):

Same thing but with cellNF: true

I don't really need the cellNF: true, it was just for testing purpose. If I remove it, will I still need to use the second code snippet that you sent me ? Here's the raw date before generating the final result using the first code snippet (without cellNF: true): ![image](/attachments/451436b5-ff1e-4e73-b414-d6ea52e963b9) Same thing but with cellNF: true ![image](/attachments/39695b56-5653-479a-a65d-f2bb59caf99c)

image.png

6.3 KiB

image.png

4.8 KiB

image.png

6.6 KiB

sheetjs commented

2023-10-20 17:40:33 +00:00

We are unable to reproduce the issue locally. Can you also share your browser locale? Run the following in the console

console.log(Intl.DateTimeFormat().resolvedOptions().timeZone)

NodeJS

Sample script:

const XLSX = require("xlsx");
const workbook = XLSX.readFile("Template_upload_passagers.xlsx", {cellDates: true, cellText: false, cellNF: true});
const firstSheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[firstSheetName];
const jsonData = XLSX.utils.sheet_to_json(worksheet, {raw: false, dateNF: 'd"/"m"/"yyyy'});
console.log(jsonData.map(row => row.BirthDate));

Save that script to i3019.js, copy your template to the same directory, run npm i --save https://cdn.sheetjs.com/xlsx-0.20.0/xlsx-0.20.0.tgz, then run node i3019.js.

This prints the dates using the m/d/yy format:

[ '12/24/02', '12/25/02' ]

Modified Date Formats

If you add that loop:

const XLSX = require("xlsx");
const workbook = XLSX.readFile("Template_upload_passagers.xlsx", {cellDates: true, cellText: false, cellNF: true});
const firstSheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[firstSheetName];
const range = XLSX.utils.decode_range(worksheet["!ref"]);
for(let R = range.s.r; R <= range.e.r; ++R) {
  for(let C = range.s.c; C <= range.e.c; ++C) {
    const addr = XLSX.utils.encode_cell({c:C,r:R});
    if(!worksheet[addr] || worksheet[addr].t != "d") continue;
    delete worksheet[addr].z;
  }
}
const jsonData = XLSX.utils.sheet_to_json(worksheet, {raw: false, dateNF: 'd"/"m"/"yyyy'});
console.log(jsonData.map(row => row.BirthDate));

you will see the dates in the format you specified:

[ '24/12/2002', '25/12/2002' ]

Browser

Save the following to index.html:

<body>
<script src="https://cdn.sheetjs.com/xlsx-0.20.0/package/dist/xlsx.full.min.js"></script>
<script>
(async() => {
  const f = await fetch("Template_upload_passagers.xlsx");
  const ab = await f.arrayBuffer();
  const workbook = XLSX.read(ab, {cellDates: true, cellText: false, cellNF: true});
  const firstSheetName = workbook.SheetNames[0];
  const worksheet = workbook.Sheets[firstSheetName];
  const range = XLSX.utils.decode_range(worksheet["!ref"]);
  const jsonData = XLSX.utils.sheet_to_json(worksheet, {raw: false, dateNF: 'd"/"m"/"yyyy'});
  console.log(jsonData.map(row => row.BirthDate));  
})();
</script>
</body>

Copy the Template_upload_passagers.xlsx file into the same folder, then start a local web server:

npx http-server .

It will display a URL (typically http://localhost:8080). Open that URL in a web browser and check the console. Chrome 117 shows:

Modified Date Formats

If you add that loop:

<body>
<script src="https://cdn.sheetjs.com/xlsx-0.20.0/package/dist/xlsx.full.min.js"></script>
<script>
(async() => {
  const f = await fetch("Template_upload_passagers.xlsx");
  const ab = await f.arrayBuffer();
  const workbook = XLSX.read(ab, {cellDates: true, cellText: false, cellNF: true});
  const firstSheetName = workbook.SheetNames[0];
  const worksheet = workbook.Sheets[firstSheetName];
  const range = XLSX.utils.decode_range(worksheet["!ref"]);
  for(let R = range.s.r; R <= range.e.r; ++R) {
   for(let C = range.s.c; C <= range.e.c; ++C) {
      const addr = XLSX.utils.encode_cell({c:C,r:R});
      if(!worksheet[addr] || worksheet[addr].t != "d") continue;
      delete worksheet[addr].z;
    }
  }
  const jsonData = XLSX.utils.sheet_to_json(worksheet, {raw: false, dateNF: 'd"/"m"/"yyyy'});
  console.log(jsonData.map(row => row.BirthDate)); 
})();
</script>
</body>

The console will show the modified date formats:

We are unable to reproduce the issue locally. Can you also share your browser locale? Run the following in the console ```js console.log(Intl.DateTimeFormat().resolvedOptions().timeZone) ``` ### NodeJS Sample script: ```js const XLSX = require("xlsx"); const workbook = XLSX.readFile("Template_upload_passagers.xlsx", {cellDates: true, cellText: false, cellNF: true}); const firstSheetName = workbook.SheetNames[0]; const worksheet = workbook.Sheets[firstSheetName]; const jsonData = XLSX.utils.sheet_to_json(worksheet, {raw: false, dateNF: 'd"/"m"/"yyyy'}); console.log(jsonData.map(row => row.BirthDate)); ``` Save that script to `i3019.js`, copy your template to the same directory, run `npm i --save https://cdn.sheetjs.com/xlsx-0.20.0/xlsx-0.20.0.tgz`, then run `node i3019.js`. This prints the dates using the `m/d/yy` format: ```js [ '12/24/02', '12/25/02' ] ``` #### Modified Date Formats If you add that loop: ```js const XLSX = require("xlsx"); const workbook = XLSX.readFile("Template_upload_passagers.xlsx", {cellDates: true, cellText: false, cellNF: true}); const firstSheetName = workbook.SheetNames[0]; const worksheet = workbook.Sheets[firstSheetName]; const range = XLSX.utils.decode_range(worksheet["!ref"]); for(let R = range.s.r; R <= range.e.r; ++R) { for(let C = range.s.c; C <= range.e.c; ++C) { const addr = XLSX.utils.encode_cell({c:C,r:R}); if(!worksheet[addr] || worksheet[addr].t != "d") continue; delete worksheet[addr].z; } } const jsonData = XLSX.utils.sheet_to_json(worksheet, {raw: false, dateNF: 'd"/"m"/"yyyy'}); console.log(jsonData.map(row => row.BirthDate)); ``` you will see the dates in the format you specified: ```js [ '24/12/2002', '25/12/2002' ] ``` ### Browser Save the following to `index.html`: ```html <body> <script src="https://cdn.sheetjs.com/xlsx-0.20.0/package/dist/xlsx.full.min.js"></script> <script> (async() => { const f = await fetch("Template_upload_passagers.xlsx"); const ab = await f.arrayBuffer(); const workbook = XLSX.read(ab, {cellDates: true, cellText: false, cellNF: true}); const firstSheetName = workbook.SheetNames[0]; const worksheet = workbook.Sheets[firstSheetName]; const range = XLSX.utils.decode_range(worksheet["!ref"]); const jsonData = XLSX.utils.sheet_to_json(worksheet, {raw: false, dateNF: 'd"/"m"/"yyyy'}); console.log(jsonData.map(row => row.BirthDate)); })(); </script> </body> ``` Copy the `Template_upload_passagers.xlsx` file into the same folder, then start a local web server: ```bash npx http-server . ``` It will display a URL (typically http://localhost:8080). Open that URL in a web browser and check the console. Chrome 117 shows: ![i3019-a.png](/attachments/4cc43437-e0fe-4107-9fd2-416944933c03) #### Modified Date Formats If you add that loop: ```html <body> <script src="https://cdn.sheetjs.com/xlsx-0.20.0/package/dist/xlsx.full.min.js"></script> <script> (async() => { const f = await fetch("Template_upload_passagers.xlsx"); const ab = await f.arrayBuffer(); const workbook = XLSX.read(ab, {cellDates: true, cellText: false, cellNF: true}); const firstSheetName = workbook.SheetNames[0]; const worksheet = workbook.Sheets[firstSheetName]; const range = XLSX.utils.decode_range(worksheet["!ref"]); for(let R = range.s.r; R <= range.e.r; ++R) { for(let C = range.s.c; C <= range.e.c; ++C) { const addr = XLSX.utils.encode_cell({c:C,r:R}); if(!worksheet[addr] || worksheet[addr].t != "d") continue; delete worksheet[addr].z; } } const jsonData = XLSX.utils.sheet_to_json(worksheet, {raw: false, dateNF: 'd"/"m"/"yyyy'}); console.log(jsonData.map(row => row.BirthDate)); })(); </script> </body> ``` The console will show the modified date formats: ![i3019-b.png](/attachments/3214bffe-e789-4a67-870a-5bb5f0d28234)

i3019-a.png

26 KiB

i3019-b.png

30 KiB

Blanay commented

2023-10-20 22:01:21 +00:00

Hi, here's the result of my browser locale:
Europe/Paris

I tried using your code, and I still get the issue.
Here's the code:

axios.get(properties.xlsx_file, {responseType: 'arraybuffer'})  // properties.xlsx_file represents the url of the file
  		.then(response => {
      	const data = response.data;
        const workbook = XLSX.read(data, {cellDates: true, cellText: false, cellNF: true});
        
        // Variables to define converted file properties
        const filename = "converted.csv";
        const mimetype = 'text/csv';

        const firstSheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[firstSheetName];
        const range = XLSX.utils.decode_range(worksheet["!ref"]);
        for(let R = range.s.r; R <= range.e.r; ++R) {
          for(let C = range.s.c; C <= range.e.c; ++C) {
            const addr = XLSX.utils.encode_cell({c:C,r:R});
            if(!worksheet[addr] || worksheet[addr].t != "d") continue;
            delete worksheet[addr].z;
          }
        }
        const jsonData = XLSX.utils.sheet_to_json(worksheet, {raw: false, dateNF: 'd"/"m"/"yyyy'});
        console.log(jsonData.map(row => row.BirthDate));

        // Converting the XLSX file to csv
		const csvData = Papa.unparse(jsonData, {delimiter: ','});
        var blob = new Blob([csvData], {type: 'text/csv'});
        var reader = new FileReader();
        
        reader.readAsDataURL(blob)

        reader.onloadend = function () {

            var base64String = reader.result;
            var base64Substring = base64String.substr(base64String.indexOf(',') + 1)
            console.log(base64Substring);
            context.uploadContent(filename, base64Substring, uploadFile);

        }

  })
  .catch(error => {
    console.error('Error fetching the XLSX file:', error);
  });


function uploadFile(err, url){
    	instance.publishState('csv_file', url);
        if(err){
        	console.log(err);
        }
}

To quickly explain the rest of the code, I'm trying to convert an xlsx file to a csv and upload it into a database (I'm building a plugin for a No-Code application on the Bubble.io platform, hence the properties.xlsx_file, context.uploadContent and instance.publishState. Don't mind those.)
Running the above code and looking into the browser console gives me the following:

Could it be because of my browser locale ?

Hi, here's the result of my browser locale: Europe/Paris I tried using your code, and I still get the issue. Here's the code: ```javascript axios.get(properties.xlsx_file, {responseType: 'arraybuffer'}) // properties.xlsx_file represents the url of the file .then(response => { const data = response.data; const workbook = XLSX.read(data, {cellDates: true, cellText: false, cellNF: true}); // Variables to define converted file properties const filename = "converted.csv"; const mimetype = 'text/csv'; const firstSheetName = workbook.SheetNames[0]; const worksheet = workbook.Sheets[firstSheetName]; const range = XLSX.utils.decode_range(worksheet["!ref"]); for(let R = range.s.r; R <= range.e.r; ++R) { for(let C = range.s.c; C <= range.e.c; ++C) { const addr = XLSX.utils.encode_cell({c:C,r:R}); if(!worksheet[addr] || worksheet[addr].t != "d") continue; delete worksheet[addr].z; } } const jsonData = XLSX.utils.sheet_to_json(worksheet, {raw: false, dateNF: 'd"/"m"/"yyyy'}); console.log(jsonData.map(row => row.BirthDate)); // Converting the XLSX file to csv const csvData = Papa.unparse(jsonData, {delimiter: ','}); var blob = new Blob([csvData], {type: 'text/csv'}); var reader = new FileReader(); reader.readAsDataURL(blob) reader.onloadend = function () { var base64String = reader.result; var base64Substring = base64String.substr(base64String.indexOf(',') + 1) console.log(base64Substring); context.uploadContent(filename, base64Substring, uploadFile); } }) .catch(error => { console.error('Error fetching the XLSX file:', error); }); function uploadFile(err, url){ instance.publishState('csv_file', url); if(err){ console.log(err); } } ``` To quickly explain the rest of the code, I'm trying to convert an xlsx file to a csv and upload it into a database (I'm building a plugin for a No-Code application on the Bubble.io platform, hence the properties.xlsx_file, context.uploadContent and instance.publishState. Don't mind those.) Running the above code and looking into the browser console gives me the following: ![image](/attachments/2e3c391a-2501-455d-8709-8039e9f1a1fd) Could it be because of my browser locale ?

image.png

8.1 KiB

sheetjs commented

2023-10-20 22:24:06 +00:00

Europe/Paris is currently in Central European Standard Time which is UTC+2. Pass the option UTC: true to sheet_to_json to force the correct interpretation.

https://docs.sheetjs.com/docs/csf/features/dates#utc-option more details.

We will revisit the default option for that setting in the future.

`Europe/Paris` is currently in Central European Standard Time which is UTC+2. Pass the option `UTC: true` to `sheet_to_json` to force the correct interpretation. https://docs.sheetjs.com/docs/csf/features/dates#utc-option more details. We will revisit the default option for that setting in the future.

sheetjs closed this issue

2023-10-20 22:24:06 +00:00

sheetjs commented

2023-10-20 22:33:36 +00:00

Reflecting on this a bit, it probably makes sense to force UTC: true when raw: false is set.

Reflecting on this a bit, it probably makes sense to force `UTC: true` when `raw: false` is set.

sheetjs reopened this issue

2023-10-20 22:33:36 +00:00

Blanay commented

2023-10-21 01:35:35 +00:00

Awesome ! My dates don't lose one day anymore ! Thank you very much for all the help that you've provided :)