前端处理 xlsx，标红备注不在话下

背景：最近在处理和 .xlsx 相关的需求，需要具备 .xlsx 的读写能力。写的话包含标红和批注。

结论：

• 库 xlsx 功能强大。社区版能做基本操作，付费版有更高级的能力，比如标红。
• 库 exceljs 功能简洁够用。可基于 exceljs 做二次封装。读写注意日期时区问题。

---

.xlsx 文件的读写能力，前端已经具备。以前这种都是交给后端来解析，前端扬眉吐气了一把。

作者这次面对的需求是这样的

• 读取 xlsx 文件，并且能正确获取日期类型的数据。

• 写 xlsx 文件，并且能标红和备注

选型 1 -sheetjs(xlsx)

首先搜索到库的是 sheetjs，功能非常强大。 看到 antd 也在依赖此库。

github 地址是 github.com/SheetJS/she…

读写能力

最终代码和 Demo 在 codesandbox.io/s/zealous-v…

读能力

我们的 .xlsx 文件内容如下

使用 xlsx 读文件

async function xlsxToJSON(file: File): Promise<any[][]> {
  const ab = await file.arrayBuffer();
  const workbook = XLSX.read(ab);
  // 读取第一个 sheet
  const sheet1 = workbook.Sheets[workbook.SheetNames[0]];
  // 转成数组形式的数据
  const json: any[][] = XLSX.utils.sheet_to_json(sheet1, {
    header: 1,
  });

  return json;
}

得到的结果是这样。可以看到 A 列因为是字符串，能读取正确。

但是 B 列得到的是数字。这是因为 xlsx 文件存储日期就是数字。 原因看这里 www.yuque.com/liyatang/he…。

改动下代码， 关键代码是 XLSX.read(ab, { cellDates: true })。

async function xlsxToJSON(file: File): Promise<any[][]> {
  const ab = await file.arrayBuffer();
  const workbook = XLSX.read(ab, { cellDates: true });
  // 读取第一个 sheet
  const sheet1 = workbook.Sheets[workbook.SheetNames[0]];
  // 转成数组形式的数据
  const json: any[][] = XLSX.utils.sheet_to_json(sheet1, {
    header: 1,
  });

  return json;
}

得到的结果是这样。可以看到把日期数据读取成 Date 对象了。但是好像日期不对。2023-10-01 读取出来的 Date 是 2023-09-30T15:59:17.000Z，换成更熟悉的表达是 Sat Sep 30 2023 23:59:17 GMT+0800 (中国标准时间)。确实发现少了几十秒。

了解后，是时区 bug 的问题。更多看这里 www.yuque.com/liyatang/he…。

解决代码如下

/**
 * 时区 bug
 * https://zhuanlan.zhihu.com/p/89914219
 * https://github.com/SheetJS/sheetjs/issues/2350
 */
const importBugHotfixDiff = (function () {
  function getTimezoneOffsetMS(date) {
    const time = date.getTime();
    const utcTime = Date.UTC(
      date.getFullYear(),
      date.getMonth(),
      date.getDate(),
      date.getHours(),
      date.getMinutes(),
      date.getSeconds(),
      date.getMilliseconds()
    );
    return time - utcTime;
  }

  const baseDate = new Date(1899, 11, 30, 0, 0, 0);
  const nD = new Date();
  const dnthreshAsIs =
    (nD.getTimezoneOffset() - baseDate.getTimezoneOffset()) * 60000;
  const dnthreshToBe = getTimezoneOffsetMS(nD) - getTimezoneOffsetMS(baseDate);
  return dnthreshAsIs - dnthreshToBe;
})();

function fixPrecisionLoss(date) {
  return new Date(date.getTime() - importBugHotfixDiff);
}

export { fixPrecisionLoss };

再改下一开始的代码是这样，关键代码是 fixPrecisionLoss。

async function xlsxToJSON(
  file: File,
  options?: xlsxToJSONOptions
): Promise<any[][]> {
  const { formatDate = defaultFormatDate } = options || {};

  const XLSX = await importXLSX();

  const ab = await file.arrayBuffer();
  // cellDates 日期转换成 Date
  const workbook = XLSX.read(ab, { cellDates: true });
  // 读取第一个 sheet
  const sheet1 = workbook.Sheets[workbook.SheetNames[0]];
  // 转成数组形式的数据
  const json: any[][] = XLSX.utils.sheet_to_json(sheet1, {
    header: 1
  });

  // 日期数据转换，修复时区的bug。
  json.forEach((row, rowIndex) => {
    row.forEach((cell, colIndex) => {
      if (cell instanceof Date) {
        const nd = fixPrecisionLoss(cell);
        json[rowIndex][colIndex] = formatDate(nd);
      }
    });
  });

  return json;
}

得到的结果是这样。大功告成。

写能力

上代码

JSONToXlsx([["日期"], ["2023-10-01"], [new Date("2023-10-01")]]);

async function JSONToXlsx(json: any[][], options?: JSONToXlsxOptions) {
  const {
    filename = "date_file.xlsx",
    sheetName = "sheet1",
    formatDate = defaultFormatDate
  } = options || {};

  const XLSX = await importXLSX();

  const nJson = cloneDeep(json);

  // 日期数据转换，修复时区的bug。
  nJson.forEach((row, rowIndex) => {
    row.forEach((cell, colIndex) => {
      if (cell instanceof Date) {
        nJson[rowIndex][colIndex] = formatDate(cell);
      }
    });
  });

  const ws = XLSX.utils.aoa_to_sheet(nJson);

  ws.A2.c = [
    {
      t: "这里错啦，这里错啦"
    }
  ];
  ws.A2.s = {
    font: {
      color: {
        rgb: "FF0187FA"
      }
    }
  };

  console.log("ws", ws);

  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, sheetName);

  XLSX.writeFile(wb, filename, {
    cellStyles: true
  });
}

得到的结果是

批注实现了。 但是标红并没有成功。后来找了很久才知道需要 xlsx Pro 即付费版本才支持修改颜色。

于是得寻找其他方案

体积

库比较大，记得异步加载哦。

兼容性

docs.sheetjs.com/docs/gettin…

For broad compatibility with JavaScript engines, the library is written using ECMAScript 3 language dialect. A "shim" script provides implementations of functions for older browsers and environments.

为了与JavaScript引擎进行广泛的兼容性，该库是使用Ecmascript 3语言方言编写的。 一个“垫片”脚本为较旧的浏览器和环境提供了实现功能。

从描述上看，连 IE 都支持，兼容性非常好。

选型 2-exceljs

最终代码和 Demo 在 codesandbox.io/s/zealous-v…

exceljs 能力满足，结果如下

由于 exceljs 和 xlsx 关注的问题都类似，只是 API 不一样而已，所以这里不打算讲的太细，更多还是看代码吧。

这里讲写关键点。

1 exceljs 同样存在时区问题，解决时区的方法见下面的 numberToDate方法。了解更多看 www.yuque.com/liyatang/he…。

2 exceljs 对于日期类型返回 number (xlsx 可调整参数返回 Date)，所以需要调用方告知那一列数据需要做转换。具体见 XlsxToJSONOptions 的 isDateCell。

3 exceljs 导出对于 Date 对象也是按字符串到处 YYYY-MM-DD。

4 exceljs 更原始，读文件需要额外使用 FileReader 处理，写文件需要额外使用 file-saver Blob 处理。

5 写文件的注意文件名的约束。比如需要做一些处理规避特殊字符 fileName.replace(/[<>\:;?/*|]/g, "-")。

function numberToDate(excelDate: number) {
  if (!isNumber(excelDate)) {
    return excelDate;
  }
  // let utc_days = Math.floor(excelDate - (25567 + 2))
  // Excel 的 bug, 会把 1900 年当作闰年
  // https://docs.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year
  // 东半球 & 0 时区 => 25567 + 2
  // 西半球 => 25567 + 1
  const step = new Date().getTimezoneOffset() <= 0 ? 25567 + 2 : 25567 + 1;
  const utc_days = Math.floor(excelDate - step);
  // 86400 => 24 * 60 * 60 => 一天的总秒数
  const utc_value = utc_days * 86400;
  // 一天的总毫秒数
  const date_info = new Date(utc_value * 1000);

  // 误差处理
  const fractional_day = excelDate - Math.floor(excelDate) + 0.0000001;
  // 自 1970 年至今的总秒数
  let total_seconds = Math.floor(86400 * fractional_day);
  const seconds = total_seconds % 60;
  total_seconds -= seconds;
  const hours = Math.floor(total_seconds / (60 * 60));
  const minutes = Math.floor(total_seconds / 60) % 60;

  return new Date(
    date_info.getFullYear(),
    date_info.getMonth(),
    date_info.getDate(),
    hours,
    minutes,
    seconds
  );
}

---

代码备份

swhc6f.zip