Cells' Date format issue when saving a file as CSV #2515

New Issue

2022-02-04T11:36:03Z

IGrobenskiTT commented

2022-02-04 11:36:03 +00:00

(Migrated from github.com)

SCENARIO: our reactjs app has an option to export the same data source as .CSV file or as a .XLSX file. Data source (JSON array) is identical in both cases and the same method is doing the export logic for both file types.

Before a file is finally exported, I'm formatting certain columns based on the settings provided, such that I'm setting certain columns' formats to type DATE with a specific date formatting style - so something like this =>

... 
worksheet[ref].t = 'd'; 
worksheet[ref].z=dateCellFormat;
...

where dateCellFormat is => const dateCellFormat = 'm/d/yy';

ISSUE: when saving a file as .XLSX file then date formatting is correctly applied to cells that I'm targeting, but when saving that same data source as .CSV it is not.
In the case of .XLSX file, when I open it in excel, it says that the cell's format is of type DATE (with m/d/yy formatting applied correctly), so that works as intended, but when I open the .CSV export in excel, then it says that the cell's format is of type CUSTOM (with m/d/yyyy h:mm formatting applied, which seems like a bug to me since nowhere in code am I applying such formatting).

Once again, json data source is the SAME for both file types and the same method is doing the export logic for both file types.

QUESTION: is there something that I'm missing ? Should I format cells in some different fashion when exporting to .csv or did I just find a bug ?

**SCENARIO**: our reactjs app has an option to export the same data source as .CSV file or as a .XLSX file. Data source (JSON array) is **identical** in both cases and the same method is doing the export logic for both file types. Before a file is finally exported, I'm formatting certain columns based on the settings provided, such that I'm setting certain columns' formats to type DATE with a specific date formatting style - so something like this => ``` ... worksheet[ref].t = 'd'; worksheet[ref].z=dateCellFormat; ... ``` where `dateCellFormat `is => `const dateCellFormat = 'm/d/yy';` **ISSUE**: when saving a file as .XLSX file then date formatting is correctly applied to cells that I'm targeting, but when saving that same data source as .CSV it is not. In the case of .XLSX file, when I open it in excel, it says that the cell's format is of type _DATE_ (with _m/d/yy_ formatting applied correctly), so that works as intended, but when I open the .CSV export in excel, then it says that the cell's format is of type _CUSTOM_ (with _m/d/yyyy h:mm_ formatting applied, which seems like a bug to me since nowhere in code am I applying such formatting). Once again, json data source is the SAME for both file types and the same method is doing the export logic for both file types. **QUESTION**: is there something that I'm missing ? Should I format cells in some different fashion when exporting to .csv or did I just find a bug ?

👍 1

reviewher commented

2022-02-04 19:43:53 +00:00

(Migrated from github.com)

Can't reproduce: https://jsfiddle.net/vh7son9z/

["xlsx", "csv"].forEach(ext => {
  var ws = { "!ref": "A1", A1: { t:"d", z:"m/d/yy", v: new Date() } };
  var wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, `issue2515.${ext}`);
});

It generates both the XLSX and the CSV file. Opening the CSV file looks like:

Can't reproduce: https://jsfiddle.net/vh7son9z/ ```js ["xlsx", "csv"].forEach(ext => { var ws = { "!ref": "A1", A1: { t:"d", z:"m/d/yy", v: new Date() } }; var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, `issue2515.${ext}`); }); ``` It generates both the XLSX and the CSV file. Opening the CSV file looks like: <img width="840" alt="csvosx" src="https://user-images.githubusercontent.com/24845478/152592778-cbb087a3-1d1c-419a-8eb7-14be258f2691.png">

IGrobenskiTT commented

2022-02-07 11:44:27 +00:00

(Migrated from github.com)

Can't reproduce: https://jsfiddle.net/vh7son9z/

["xlsx", "csv"].forEach(ext => {
  var ws = { "!ref": "A1", A1: { t:"d", z:"m/d/yy", v: new Date() } };
  var wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, `issue2515.${ext}`);
});

It generates both the XLSX and the CSV file. Opening the CSV file looks like:

Thanks to your comment now I see what I forgot to outline in my original post, the cell's v (value) is not of type Date , but actually, a string containing a date. And that's where the bug happens.

So if you cast it to Date, and set the cell's value to that, then it works in both file types, but if you pass Date formatted as a string, then your code only works in XLS, but not in CSV . This is confusing, at least, if a certain cell formatting logic works in xls , it should work in csv as well, right ? Thanks for your feedback once again

I updated your fiddle so that you can see what I'm talking about (you'll see that it displays as intended in xls file, but not in csv file) : https://jsfiddle.net/4xfuL9rz/

> Can't reproduce: https://jsfiddle.net/vh7son9z/ > > ```js > ["xlsx", "csv"].forEach(ext => { > var ws = { "!ref": "A1", A1: { t:"d", z:"m/d/yy", v: new Date() } }; > var wb = XLSX.utils.book_new(); > XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, `issue2515.${ext}`); > }); > ``` > > It generates both the XLSX and the CSV file. Opening the CSV file looks like: > > Thanks to your comment now I see what I forgot to outline in my original post, the cell's v (value) is not of type _Date_ , but actually, a _string_ containing a date. And that's where the bug happens. So if you cast it to Date, and set the cell's value to that, then it works in both file types, but if you pass **Date formatted as a string**, **then your code only works in XLS**, **but not in CSV** . This is confusing, at least, if a certain cell formatting logic works in xls , it should work in csv as well, right ? Thanks for your feedback once again I updated your fiddle so that you can see what I'm talking about (you'll see that it displays as intended in xls file, but not in csv file) : https://jsfiddle.net/4xfuL9rz/

Sign in to join this conversation.