Solve the problem that Excel date xlsx library sheetjs parsing becomes incorrect and 43 seconds less

There is a current need to import a batch of excel data into the system, but a very strange problem was found during the import process. The date data looks normal in the excel table, and it is still a date when copied. But when using the xlsx library to read excel, the date data will become a string of 5 digits. (As shown below)

> `For the solution, please refer directly to the solution 2 at the end`

After research, it is found that the cell format in this list of tables is set to date. So by reading the document, I learned that xlsx can parse date type data by setting a `cellDates=true` parameter. [Parameter document link](https://ift.tt/jPoMgmv)

So it seems that you only need to add parameters, and it is true.

“`

// Read the entire excel table object in binary stream

const workbook = XLSX.read(result, {type: ‘binary’, cellDates: true});

“`


Adding `cellDates=true` can be seen through the log, and the construction date has indeed been parsed normally at this time. But found that all the time is 43 seconds less! The construction date of 2010/2/1 is parsed into 2010/1/31 23:59:17

After consulting the relevant information, it is known that this has something to do with the time zone of the country;

The time of excel starts from 1900/1/0 (1899/12/30). At this time, the Shanghai time zone (GMT+0805) used for China is +8:05:43, 1800-1900. https://ift.tt/cg1FsMh

The number of minutes obtained by getTimezoneOffset ignores 43 seconds, so it will be 43 seconds less

https://ift.tt/EeG4dvf

In this way, the above scheme cannot be used domestically.

Here are two solutions

### plan 1:

Change the date format to regular text format directly in excel. This solution is obviously a bad idea, because the date will also become a number after the format in excel is changed to normal, and a large amount of data needs to be manually modified.

### Option 2 (recommended):

As we all know, the date of JS is calculated from January 1, 1970. So we can get the excel time code of January 1, 1970 as `25569` through the cell format conversion of excel.

Take `2010/2/1` time as an example, as shown in the figure, its corresponding time code is `40210`, (40210-25569) * milliseconds a day = JS timestamp. Due to the time zone calculation problem, the timestamp obtained here will be 8 hours longer when displayed. So you need to subtract 8 hours manually.

> Remember to remove the `cellDates=true` parameter

code show as below:

“`

const excelDateNumber = 40634 //Excel date code to be converted

const time = (excelDateNumber – 25569) * 86400000 – 28800000; //Get the corresponding js timestamp

“`

### Option 3:

Use `new Date().getTimezoneOffset()` function to calculate the time difference, which is only used in different regions of the country. Reference the `moment` library.

“`

const excelTime = 40210;

const dateTime = new Date(moment((excelTime – 25569 + new Date().getTimezoneOffset() / (60*24) ) * 86400000))

“`

> [Reference 1: Why is it 43 seconds less when using xlsx to convert the time in the excel file to JavaScript Date? ](https://ift.tt/L5u7QlT)