.Net 前端使用 xlsx.js 读取 Excel 日期格式的数据与 Excel 文件中的数据不一致的问题

最新推荐文章于 2024-05-13 02:48:52 发布

qq_30317889

最新推荐文章于 2024-05-13 02:48:52 发布

阅读量3.2k

点赞数 5

分类专栏： JavaScript

本文链接：https://blog.csdn.net/qq_30317889/article/details/99643091

版权

JavaScript 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

由于项目中某些模块数据量比较大，上新系统时，或者数据批量更新时，若是每条数据都是收入录入会消费大量的人力和时间，所以这时候就需要“导入”功能，直接把数据整理为系统指定格式的 Excel 文档，直接导入系统，这样会很大程度上提高工作效率，缩短工作时间；

经多番查找，网上介绍 Excel 导入功能还是比较多，例如：
1.采用OleDB读取EXCEL文件；
2.引用的com组件：Microsoft.Office.Interop.Excel.dll 读取EXCEL文件；
3.将EXCEL文件转化成CSV（逗号分隔）的文件，用文件流读取(等价就是读取一个txt文本文件)。
https://zhidao.baidu.com/question/295557582.html

等等方法很多，但是这些都是在后台处理，而我需要在传入后台之前在前段要做部分验证，这样能减少后台验证和插入的时间，也能稍微缓解服务器压力；经多番查找，让我找到了 xlsx.js ，这个脚本是可以直接加载到项目中使用，使用起来很方便，也有完整的 Examples 可查，并有详细的说明；

具体怎么使用咱们就不多说了，这里就说说我遇到最头疼的问题，那真是简直了。。。。

因项目最开始是面向香港客户研发的产品，而咱们开发团队是内地，所以在时间上就有问题了， js 的 new Date() 在香港运行的结果是 “香港标准时间” ，在内地运行结果是 “中国标准时间”，按理来说，香港和内地都是使用东八区的时区，也就是 UTC+08:00,如图：
在这里插入图片描述
既然是同处一个时区，理论上应该是不会有任何问题的，但是现在就出现了一个让人匪夷所思的问题，如图：

可以看到 Excel 导入的 Start and End 与导入到数据库表的 start_time and end_time（只看时间部分，年月日是根据前面的 ts_date 加上的），出入有点大，白天黑夜的时间都颠倒了，怎么办？只能找问题然后解决问题（程序员日常）。。。

没办法，先调试看看什么情况，经调试发现，xlsx.js 在读取 excel 文件内容时，会先将 excel 内容解析为 xml,然后再解析xml中的单元格数据，什么字符串格式的就先不管了。

直接看日期格式和时间格式的数据，有意思的来了，调试到日期格式和时间格式数据时，发现解析出来的数据是整数值和小数值，比如：2019/06/29 解析整数值为： 43645，08:30:00 解析小数值为： 0.354166667，然后再将解析后的数值转换为标准时间格式的数据 yyyy/MM/dd hh:mm:ss ，并计算时区差，xlsx.js 部分代码展示：

function convertDate(input) {
	    var d = new Date(1900, 0, 0),
			isDateObject = typeof input === 'object',
			offset = ((isDateObject ? input.getTimezoneOffset() : (new Date()).getTimezoneOffset()) - d.getTimezoneOffset()) * 60000,
          return isDateObject ? ((input - d - offset ) / 86400000) + 1 : new Date(+d - offset + (input - 1) * 86400000);
	}
	//input:解析后的数值型数据

因为这里涉及到时区差的计算，还特地跑去查了 js处理时区的问题，目标时区时间计算有个公式：目标时区时间 = 本地时区时间 + 本地时区时差 - 目标时区时差，那么问题来了，香港和内地同处一个时区。。。。这一加一减的等于没加没减，还是没能解决这个问题，只能继续找资料了，找了很长时间，正当准备放弃，并“摔锅”给香港同事，让他们去想办法时，在 GitHub 上找到了关于 js-xlsx 的一个托管项目，并有详细的说明，简直就是官方 API 了，为快速找到解决办法，直接搜索 “Date”，终于在 Dates 的说明里面给找到了，Excel Date Code details(click to show) 是这么写的 “By default, Excel stores dates as numbers with a format code that specifies date processing. For example, the date 19-Feb-17 is stored as the number 42785 with a number format of d-mmm-yy. The SSF module understands number formats and performs the appropriate conversion.

XLSX also supports a special date type d where the data is an ISO 8601 date string. The formatter converts the date back to a number.

The default behavior for all parsers is to generate number cells. Setting cellDates to true will force the generators to store dates.” 翻译过来大致是：“默认情况下，Excel将日期存储为具有指定日期处理的格式代码的数字。例如，日期19-Feb-17存储为数字42785，数字格式为d-mmm-yy。 SSF模块了解数字格式并执行适当的转换。

XLSX还支持特殊日期类型d，其中数据是ISO 8601日期字符串。格式化程序将日期转换回数字。

所有解析器的默认行为是生成数字单元格。将cellDates设置为true将强制生成器存储日期。”（来着谷歌翻译，小编的蹩脚英语翻译不出来 ⊙﹏⊙）
这我们就找到了日期格式和时间格式为什么要转为数值型数据的根源了，注意上面黑色字体部分“SSF模块了解数字格式并执行适当的转换”，是不是就可以理解为，xlsx.js 解析为 xml 的日期格式和时间格式为数值型数据，然后可以根据 SSF的数字格式转回 yyyy-mm-dd hh:mm:ss 的时间格式？

接下来就找找 SSF 的数字格式转换说明，在 Interface 标签中找到了：“XLSX.SSF is an embedded version of the format library.” 在 SSF 页面的 Usage 标签中写着 SSF 的格式转换公式，并附带说明：
Usage:
SSF.format(fmt, val, opts) formats val using the format fmt.

If fmt is a string, it will be parsed and evaluated. If fmt is a number, the actual format will be the corresponding entry in the internal format table. For a raw numeric format like 000, the value should be passed as a string.(谷歌翻译：如果fmt是一个字符串，它将被解析和评估。如果fmt是数字，则实际格式将是内部格式表中的相应条目。对于像000这样的原始数字格式，该值应作为字符串传递。)

Date arguments are interpreted in the local time of the JS client.
The options argument may contain the following keys:
在这里插入图片描述
说明中写道：fmt 是个字符串，可以被解析和评估，那我是不是可以传日期格式(yyyy-mm-dd)或者是时间格式(hh:mm:ss),val 直接传入日期或时间解析后的数值型数据，然后返回的结果就是我们传入格式的 yyyy-mm-dd 格式的数据？想象还是挺美好的。。。接着往下看，直接找 Examples ，有两个例子:
1.Basic Demo
2.Custom Formats Builder
打开 Badic Demo 如图:可以输入 Format Code and Value,那就按照我们上面预想到的直接传 Format code：yyyy-mm-dd ,value : 43645,Formatted Number 显示的就是和我们预想的一样，会直接将数值型数据转为日期格式数据，Formatted Text 就是传入的 Value.这不就是我们上面预想的一样的；如图：
在这里插入图片描述

在看看 Custom Formats Builder,这个就更明了了，有各种数据类型的转换，这里我们就看 Date 和 Time 两个类型的转换，其他的类型，有兴趣的都可以了解;
首先 Date 类型，在 Format Code 文本框中输入我们要的日期格式字符串“yyyy-mm-dd”,然后再 Unformatted 列第一行修改为我们上面的例子数据 43645，Formatted 结果是实时的，可以看到转换后的结果为：2019-06-29 这不就是我们要的结果么。
在这里插入图片描述
在看 Time 类型：
在 Format Code 文本框中输入我们要的日期格式字符串“hh:mm:ss”,然后再 Unformatted 列第一行修改为我们上面的例子数据 0.354166667，Formatted 结果是实时的，可以看到转换后的结果为：08:30:00 这不就是我们要的结果么。
在这里插入图片描述

经上面演示，关于 xlsx.js 导入后的日期和时间与Excel 文件中的日期、时间不对应的问题，就找到了解决办法，接下来就是解决实际的问题了，废话不多说，直接上代码：
1.首先下载 SSF.js 并加载到项目中
2.修改 xlsx.js 中 date 取值的代码，将解析后的数值型数据直接转为我们想要的数据并返回，不用在计算什么时区：

case 'date':
	if (val > 1) {   // val > 1 表示 日期格式的数据，val < = 1 表示 时间格式的数据，也就是只有 时分秒，没有年月日
			 val = new Date(SSF.format("yyyy-mm-dd", val) + " GMT+0800");  // 写死 东八区的时区：GMT+0800
			 //val = convertDate(val);// 原处理方法，这个方法上面有贴代码
		 	 break;
	 } else {
		 	val = new Date("1900-01-01 " + SSF.format("HH:mm:ss", val) + " GMT+0800");
		  	//val = convertDate(val); 原处理方法，这个方法上面有贴代码
			break;
	 }

这样问题就解决了，导入的是什么数据，xlsx 读取的就是什么数据，不用在做任何的计算；

上面仅限我个人遇到问题的解决办法，若是那位同仁有更好的解决方案，还望赐教！

当然了这个托管的 xlsx 项目还有很多可以学习了解的内容，这里就不一一进行说明了，感兴趣的朋友可以直接去 GitHub 上下载项目包，根据自己的需求进行功能调试！

qq_30317889

关注

5
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
.Net 前端使用 xlsx.js 读取 Excel 日期格式的数据与 Excel 文件中的数据不一致的问题

由于项目中某些模块数据量比较大，上新系统时，或者数据批量更新时，若是每条数据都是收入录入会消费大量的人力和时间，所以这时候就需要“导入”功能，直接把数据整理为系统指定格式的 Excel 文档，直接导入系统，这样会很大程度上提高工作效率，缩短工作时间；经多番查找，网上介绍 Excel 导入功能还是比较多，例如：1.采用OleDB读取EXCEL文件；2.引用的com组件：Microsoft.Of...
复制链接

扫一扫