前端使用 xlsx.js 工具读取 excel 遇到时间日期少 43 秒的解决办法

在使用 xlsx 读取 excel 的时间格式的数据时，如 ‘2023-11-30’，‘2023/11/30’ ，默认会读取一串数字字符串，如：‘45260’，此时需要在 read 的时候传入一个配置项：

import { read } from 'xlsx'const workbook = read(fileData, {type: 'binary',cellDates: true, // 读取日期格式的数据
})

此时拿到的是标准的时间格式 ：‘Wed Nov 29 2023 23:59:17 GMT+0800(中国标准时间)’ ，这个时间格式是带时区的，有没有发现，只要输入年月日，读到的数据总是差 43 秒，解决思路也很粗暴，判断是这个时间，直接加 44 秒。

if(dateStr){if(dateStr?.includes('23:59:17')) {dateStr = dayjs(dateStr).add(44, 'second')}// 如果需要可以格式化成需要的格式const dayObj = dayjs(dateStr.toString())if(dayObj.isValid()) {dateStr = dayObj.format('YYYY-MM-DD')}return dateStr
}

附：element-plus el-upload 读取 xlsx 格式的 excel 文件的步骤

<template><el-uploadref="uploadRef"action="":auto-upload="false":on-change="onSelectFile":on-remove="onRemoveFile":file-list="fileList"accept=".xlsx"><el-button type="primary">导入</el-button></el-upload><br><el-button @click="handleExport">导出</el-button>
</template><script setup lang="ts">
import { ref } from 'vue'
import type { UploadFile, UploadRawFile } from 'element-plus'
import { read, utils, writeFile } from 'xlsx'type IExcel = Record<string, Array<Record<string, string>>>const fileList = ref<{name: string}[]>([])
const importData = ref<IExcel | null>(null)async function onSelectFile(file: UploadFile) {reset()if(file.raw) {if(file.raw.type !== 'application/vnd.openxmlformats-offocedocument.spreadsheetml.sheet') {return '请上传 xlsx 格式文件'}if(file.raw.size / 1024 / 1024 > 10) {return '文件格式不能超过 10M'}fileList.value.push({ name: file.raw.name })// 解析文件const raw = file.rawconst res = await readFile2Binary(raw)const resInfo: IExcel = {} // 解析结果if(res) {const workbook = read(res, {type: 'binary',cellDates: true,})workbook.SheetNames.forEach((sheetName) => {const excelData: Record<string, string>[] = utils.sheet_to_json(workbook.Sheets[sheetName])resInfo[sheetName] = excelData})// 检查数据的合法性// if(validXLSX(resInfo)) {//   importData.value = resInfo// }importData.value = resInfo}}
}// 重置
function reset() {fileList.value = []// ...
}
function onRemoveFile() {reset()
}/*** 将 el-upload 选择的文件读取成二进制* @param raw */
function readFile2Binary(raw: UploadRawFile) {return new Promise((resolve, reject) => {const reader = new FileReader()reader.readAsBinaryString(raw)reader.onload = (ev) => {if(ev.target) {resolve(ev.target.result)} else {reject()}}})
}/*** 导出*/
function handleExport() {const sheetList = {sheet1: [],sheet2: [],}const fileName = 'xxx.xlsx'const workbook = utils.book_new()for(const key in sheetList) {const sheetName = keyconst worksheet = utils.aoa_to_sheet(sheetList[key])utils.book_append_sheet(workbook, worksheet,sheetName)}writeFile(workbook, fileName, {bookType: 'xlsx',})
}
</script>