解决Excel日期xlsx库sheetjs解析变成数字不正确少43秒问题

当下有一个需求是将一批excel数据导入到系统中，但导入的过程中发现了一个很奇怪的问题。日期数据在excel表格里看起来一切正常，复制出来也都还是日期。但使用xlsx库去读取excel的时候，日期数据就会变成一串5位数字。（如下图所示）

> `解决方案请直接看末尾方案2`

经过研究发现，这一列表格中设置了单元格格式为日期。于是通过阅读文档得知xlsx可以通过设置一个`cellDates=true`参数来解析日期类型数据。[参数文档链接](https://www.npmjs.com/package/xlsx?activeTab=readme#parsing-options)
如此看起来只需要加上参数就可以了,也的确如此。
```
// 以二进制流方式读取得到整份excel表格对象
const workbook = XLSX.read(result, {type: 'binary', cellDates: true});
```

![image-1668740473224](/upload/2022/11/image-1668740473224.png)

加上`cellDates=true`通过log可以看到，此时施工日期的确已经是正常解析了。但又发现所有的时间都少了43秒！施工日期2010/2/1日被解析成了2010/1/31 23:59:17

经过查阅相关资料得知这与国内所在的时区有关系；
excel 的时间从 1900/1/0（1899/12/30）开始 ，此时对中国用的上海时区（GMT+0805）+8:05:43，1800～1900年。https://www.timeanddate.com/time/zone/china/shanghai

getTimezoneOffset 获取的分钟数，忽略了43秒，所以会少 43 秒
https://github.com/SheetJS/ssf/blob/master/bits/35_datecode.js
<img src="/upload/2022/11/image-1668739941835.png" width="800px" alt="alt text" title="Title" />
如此，国内便不能使用上面的方案。

这里有两个解决方案
### 方案1:

直接在excel中把日期格式改为常规文本格式。这方案显然是下下策，因为excel中格式改为常规后日期同样会变成数字，需要手动修改大量数据。

### ⭐️方案2(推荐)：

众所周知，JS的日期是从1970年1月1日开始计算的。所以我们通过excel的单元格式转换能够得到1970年1月1日的excel时间代码为`25569`，
以`2010/2/1`时间举例，如图所示，它对应的时间代码为`40210`，(40210-25569)*一天毫秒数=JS时间戳。由于时区计算问题，这里得出的时间戳在显示的时候会多8小时。所以还需要手动减去8小时。
> 记得把`cellDates=true`参数去掉

代码如下：
```
const excelDateNumber = 40634 //要转换的excel日期代码
const time = (excelDateNumber - 25569) * 86400000 - 28800000; //得到对应的js时间戳
```

### 方案3:
用` new Date().getTimezoneOffset() `函数计算时差，只用于全国不同地区。引用`moment`库。	
```
const excelTime = 40210;
const dateTime = new Date(moment((excelTime - 25569 + new Date().getTimezoneOffset() / (60*24) ) * 86400000))
```

> [参考1:为什么用 xlsx 将 excel 文件中的时间转为 JavaScript Date 会少 43 秒？](https://www.zhihu.com/question/404659816/answer/2144114879)

解决Excel日期xlsx库sheetjs解析变成数字不正确少43秒问题

仅有一条评论