ThinkJS
首发于ThinkJS
如何解析你,Excel的Date呀

如何解析你,Excel的Date呀

0. 简单的背景介绍

不久前,我们接了一个自己做前端后端产品的活,从此过上了可怜巴巴敲代码开开心心收获知识的日子呢。


1.那是一个平平无奇的周一下午

用户小姐姐在群里说,系统筛选工卡有效期不好使。(系统:不不不,不是我的锅

我看了一下数据库,发现,我们原定的有效期格式是这样的


整整齐齐。


数据库中当时的数据是这样的


甚至是这样的



看到这种情况,我觉得肯定是输入的时候输的不太对(年轻…

于是我决定从Excel下手


小姐姐们的操作流程是先用我们的系统导出一份Excel,编辑之后再导入系统的,那只要我把这工卡一列的格式限制为日期,就一定可以统一格式的,嗯。


我们项目使用了js-xlsx处理表格的导入导出,下面是导出Excel的伪代码:

import * as XLSX from 'xlsx';

const xlsxMineType = 'application/vnd.openxmlformats-
officedocument.spreadsheetml.sheet'
const data = 数据.map((s: any) => ({
ID: s.id,
工卡有效期: s.card_expired,
……
}));

const sheet = XLSX.utils.json_to_sheet(data);
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, sheet, '员工信息表');
const wbbuf = XLSX.write(wb, {
  type: 'base64'
});

this.success({ name: "员工信息表.xlsx", data: wbbuf, type:xlsxMineType });
 

通过 json_to_sheet 可以拿到包含单元格信息的对象

{ 
 A2: { t: 'n', v: 3776 },
 B2: { t: 's', v: '2019-04-01' },
 A3: { t: 'n', v: 3831 },
 B3: { t: 's', v: '2019-04-01' },
 A1: { t: 's', v: 'ID' },
 B1: { t: 's', v: '工卡有效期' },
 '!ref': 'A1:B3' 
}

对象中以单元格位置作为key,每个单元格的值(v)、类型(t)等等属性作为value。其中单元格的类型支持:

b Boolean, n Number, e error, s String, d Date


看起来Date类型十分符合上面的要求,就尝试了一下:

const sheet = XLSX.utils.json_to_sheet(data);
// 筛选出除表头的工卡列
Object.keys(sheet).filter(item => /^B/.test(item) && item !== "B1").forEach(key => {
   sheet[key].t = "d";
})



然鹅,如果工卡有效期本来就为空,这时候导出,打开Excel会报错,并且空的位置会变成NaN


拿什么拯救你,我的Excel…


翻阅了各种中英文文档、Issue,导出一百多个员工信息表之后,我发现Excel真的很奇妙,或许应该在js上来格式化导入的数据,而不是限制单元格的类型。

如果不控制单元格类型的话,那么当管理员输入日期的时候,这个单元格可能是:文本、常规、日期、自定义类型,所以只要保证不管单元格是什么格式,程序都能拿到正确的数据就好了。

当管理员使的工卡有效期的单元格类型是文本或者常规的时候,则比较简单,程序可以按预期解析出来一个相应的字符串,用moment解析一下,就可以获得想要的格式的数据了。


那么当有效期单元格的类型是日期和自定义的时候,我们拿到的数据是像下图一样



这也就是之前数据库中奇怪的数字的由来,这个数字的意义,其实是当前日期距离1900年1月0日的天数。还需要注意的是,Excel中有个bug:


它以为1900年是闰年,所以我们拿到的天数都会多了一天,因为转换之前还需要先进行减一操作…

item.工卡有效期 = new Date(1900, 0, expried - 1)

这样之后就可以拿到正确的日期啦。咕叽。


比心公子小锅锅赞助的头图~

编辑于 2018-12-06

文章被以下专栏收录

    ThinkJS 是一款基于 Koa 2.0 面向未来的企业级 Node.js 框架,致力于整合了大量的项目最佳实践,让企业级开发变得如此简单、高效。本专栏会分享一些在 ThinkJS 项目开发过程中总结的一些经验以及问题,同时也非常欢迎大家投稿。