最近在优化后台数据导出功能，把原先后台生成 Excel 的功能（PhpExcel太吃内存），改用 ajax 加载每页数据然后前端 JS 生成 Excel。

找到一个前端生成 Excel 的类库 SheetJS：https://github.com/SheetJS/js-xlsx，以及一篇使用介绍的博文：https://www.cnblogs.com/liuxianan/p/js-excel.html

在导出日期时间的数据列时，想当然的使用了 Date 类型，然后设置显示格式为 yyyy-mm-dd hh:mm:ss。可是最终结果却显示有偏差，莫名比原来的值多了43秒。

搜索了一下这个问题，发现早有人在 github 上提出来：https://github.com/SheetJS/js-xlsx/issues/1470。细看 SheetJS 官方的 README.md，其实这不算是一个 BUG，而是因为 Excel 采用了不同的时间显示方式，跟时区有一定关系。

问题代码如下：

<script src="xlsx.core.min.js"></script>
<script>
    var sheetData = {
        '!ref': 'A1:B3',
        'A1': { v: 'String型日期' },
        'A2': { v: '2019-08-12 14:50:10', t: 's' },
        'A3': { v: '2019-09-20 09:10:05', t: 's' },
        'B1': { v: 'Date型日期' },
        'B2': { v: '2019-08-12 14:50:10', t: 'd', z: 'yyyy-mm-dd hh:mm:ss' },
        'B3': { v: '2019-09-20 09:10:05', t: 'd', z: 'yyyy-mm-dd hh:mm:ss' },
    };

    var sheetName = 'Sheet1';
    var filename = "test.xlsx";

    var workbook = {
        SheetNames: [sheetName],
        Sheets: {}
    };

    workbook.Sheets[sheetName] = sheetData;

    XLSX.writeFile(workbook, filename, { compression: true });
</script>

A列为字符串型数据（即原始值），B列为 Date 类型数据，一样的日期时间，导出 Excel 后最终显示如下，B列比A列多了43秒：

解决这个问题最快的办法就是，遇到日期时间的数据列，直接使用 String 类型导出就好，不要用 Date。

但是这样会有另一个问题，String 列无法像 Date 列一样做筛选，如下图：

那有没有一个完美的解决方案呢？有两个办法。

第一种：根据时区判断，在导入的日期时间值上减去差值。如我们的北京时间要减去43秒，最终显示到 Excel 上就不会有偏差了。

第二种：一个变通的方法，使用 Excel 公式拼接日期和时间，跳过时区的转换过程。如原始值 2019-08-12 14:50:10，用公式拼接：=DATEVALUE("2019-08-12")+TIMEVALUE("14:50:10")。

如下图：

且可以做正常的日期时间筛选：

数据类型是 String，设置了显示格式，改造后的代码如下：

<script src="xlsx.core.min.js"></script>
<script>
var sheetData = {
    '!ref': 'A1:C3',
    'A1': { v: 'String型日期' },
    'A2': { v: '2019-08-12 14:50:10', t: 's' },
    'A3': { v: '2019-09-20 09:10:05', t: 's' },
    'B1': { v: 'Date型日期' },
    'B2': { v: '2019-08-12 14:50:10', t: 'd', z: 'yyyy-mm-dd hh:mm:ss' },
    'B3': { v: '2019-09-20 09:10:05', t: 'd', z: 'yyyy-mm-dd hh:mm:ss' },
    'C1': { v: '公式拼接' },
    'C2': { v: '2019-08-12 14:50:10', t: 's', z: 'yyyy-mm-dd hh:mm:ss', f: 'DATEVALUE("2019-08-12")+TIMEVALUE("14:50:10")' },
    'C3': { v: '2019-09-20 09:10:05', t: 's', z: 'yyyy-mm-dd hh:mm:ss', f: 'DATEVALUE("2019-09-20")+TIMEVALUE("09:10:05")' },
};

var sheetName = 'Sheet1';
var filename = "test.xlsx";

var workbook = {
    SheetNames: [sheetName],
    Sheets: {}
};

workbook.Sheets[sheetName] = sheetData;

XLSX.writeFile(workbook, filename, { compression: true });
</script>

 

2021年8月8日更新

前几天对 Excel 的日期类型数据存储进行了深入了解，得知 Excel 是将日期类型转换成浮点数来存储，如 2021-08-07 12:45:28
会被转换为 44415.5315740741 再存储（可参考 Excel如何存储日期和时间数据？）。

SheetJS 默认将字符串的日期转换成 js 的 Date 对象，然后转换成对应的浮点数。这里的转换涉及到时区问题，所以前面才会莫名其妙多出43秒。

这两天仔细看文档，发现 SheetJS 作者提供了一个可选参数 cellDates，使用这个参数后，原来的字符串日期就不会被转换成 js 的 Date 对象，而是转换成 ISO 8601 标准格式的日期来存储，避免时区不同造成误差。如 2021-08-07 12:45:28 会被转换成 2021-08-07T12:45:28.000Z。

最后，添加上这个 cellDates 参数来试试吧（倒数第2行）：

<script src="xlsx.core.min.js"></script>
<script>
var sheetData = {
    '!ref': 'A1:C3',
    'A1': { v: 'String型日期' },
    'A2': { v: '2019-08-12 14:50:10', t: 's' },
    'A3': { v: '2019-09-20 09:10:05', t: 's' },
    'B1': { v: 'Date型日期' },
    'B2': { v: '2019-08-12 14:50:10', t: 'd', z: 'yyyy-mm-dd hh:mm:ss' },
    'B3': { v: '2019-09-20 09:10:05', t: 'd', z: 'yyyy-mm-dd hh:mm:ss' },
    'C1': { v: '公式拼接' },
    'C2': { v: '2019-08-12 14:50:10', t: 's', z: 'yyyy-mm-dd hh:mm:ss', f: 'DATEVALUE("2019-08-12")+TIMEVALUE("14:50:10")' },
    'C3': { v: '2019-09-20 09:10:05', t: 's', z: 'yyyy-mm-dd hh:mm:ss', f: 'DATEVALUE("2019-09-20")+TIMEVALUE("09:10:05")' },
};

var sheetName = 'Sheet1';
var filename = "test.xlsx";

var workbook = {
    SheetNames: [sheetName],
    Sheets: {}
};

workbook.Sheets[sheetName] = sheetData;

XLSX.writeFile(workbook, filename, { compression: true, cellDates: true });
</script>

结果：

生成的 test.xlsx 实际上是一个 zip 压缩包，可以使用 7-Zip 进行解压，再用浏览器打开解压出的文件 xl\worksheets\sheet1.xml，可以看到 B2、B3 单元格的原始数据，就是 ISO 8601 格式的日期：