Excel 中的日期和 Bug

背景：最近需要处理 .xlsx 文件， 好家伙，遇到了两个和日期相关的 Bug

结论：

• excel 日期的存储方式以及 对 1900 年错误处理成闰年 bug。
• 数据转换需要注意时区 bug

存储方式

参考 zhuanlan.zhihu.com/p/69101012

Excel 将日期值存储为序列号，1900-01-01 序列号是 1。 而 2023-10-01 序列号是 45200。

再来看一段代码

import dayjs from 'dayjs';

const d = dayjs('1900-01-01');
dayjs('1900-01-01').diff(d, 'day') + 1; // 1
dayjs('2023-10-01').diff(d, 'day') + 1; // 45199

???????这不对啊，不是 45200 么，代码的结果是 45199。

实际这是一个 bug，Excel 会把 1900 当做闰年。见 learn.microsoft.com/en-us/offic…

百度百科实际上 1900 不是闰年。baike.baidu.com/item/%E9%97…

库 xlsx 数值转 Date

在这篇文章（www.yuque.com/liyatang/he…）中日期内容为 2023-10-01 ，最终读取出来是 2023-09-30T15:59:17.000Z。注意，这个是 0 时区表达，+8 区表达是 Sat Sep 30 2023 23:59:17 GMT+0800 (中国标准时间)。

这有点奇怪了，找到了源码 github.com/SheetJS/she…。

经过测试确实是 2023-09-30T15:59:17.000Z。

var basedate = /*#__PURE__*/ new Date(1899, 11, 30, 0, 0, 0); // 2209161600000
function datenum(v /*:Date*/, date1904 /*:?boolean*/) /*:number*/ {
  var epoch = /*#__PURE__*/ v.getTime();
  if (date1904) epoch -= 1462 * 24 * 60 * 60 * 1000;
  var dnthresh =
    /*#__PURE__*/ basedate.getTime() +
    /*#__PURE__*/ (v.getTimezoneOffset() -
      /*#__PURE__*/ basedate.getTimezoneOffset()) *
      60000;
  return (epoch - dnthresh) / (24 * 60 * 60 * 1000);
}
var refdate = /*#__PURE__*/ new Date();
var dnthresh =
  /*#__PURE__*/ basedate.getTime() +
  /*#__PURE__*/ (refdate.getTimezoneOffset() -
    /*#__PURE__*/ basedate.getTimezoneOffset()) *
    60000;
var refoffset = /*#__PURE__*/ refdate.getTimezoneOffset();
function numdate(v /*:number*/) /*:Date*/ {
  var out = new Date();
  out.setTime(v * 24 * 60 * 60 * 1000 + dnthresh);
  if (out.getTimezoneOffset() !== refoffset) {
    out.setTime(out.getTime() + (out.getTimezoneOffset() - refoffset) * 60000);
  }
  return out;
}

numdate(45200); // 2023-09-30T15:59:17.000Z

关键逻辑如下

• basedate 是 new Date(1899, 11, 30, 0, 0, 0)。因为 Excel 存在 1900 年 bug，所以退两天。
• numdate = v => new Date().setTime(v * 24 * 60 * 60 * 1000 + dnthresh)

那问题出在哪里？看上面的第一行代码就存在不一致了。 这是因为时区也存在 bug。原因这篇文章讲解 www.yuque.com/liyatang/he…

var basedate = /*#__PURE__*/ new Date(1899, 11, 30, 0, 0, 0); // 2209161600000

// 实际结果是
+basedate; // 2209190743000

那怎么解决？

看到别人提供的方案是这样，通过 fixPrecisionLoss 再修正下

var basedate = /*#__PURE__*/ new Date(1899, 11, 30, 0, 0, 0); // 2209161600000
function datenum(v /*:Date*/, date1904 /*:?boolean*/) /*:number*/ {
  var epoch = /*#__PURE__*/ v.getTime();
  if (date1904) epoch -= 1462 * 24 * 60 * 60 * 1000;
  var dnthresh =
    /*#__PURE__*/ basedate.getTime() +
    /*#__PURE__*/ (v.getTimezoneOffset() -
      /*#__PURE__*/ basedate.getTimezoneOffset()) *
      60000;
  return (epoch - dnthresh) / (24 * 60 * 60 * 1000);
}
var refdate = /*#__PURE__*/ new Date();
var dnthresh =
  /*#__PURE__*/ basedate.getTime() +
  /*#__PURE__*/ (refdate.getTimezoneOffset() -
    /*#__PURE__*/ basedate.getTimezoneOffset()) *
    60000;
var refoffset = /*#__PURE__*/ refdate.getTimezoneOffset();
function numdate(v /*:number*/) /*:Date*/ {
  var out = new Date();
  out.setTime(v * 24 * 60 * 60 * 1000 + dnthresh);
  if (out.getTimezoneOffset() !== refoffset) {
    out.setTime(out.getTime() + (out.getTimezoneOffset() - refoffset) * 60000);
  }
  return out;
}

numdate(45200);

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);
}

fixPrecisionLoss(numdate(45200)); // 2023-09-30T16:00:00.000Z

搞定。

库 excel 数字转 Date

思路和上面类似，无非

• 解决 Excel 1990 闰年问题
• 解决 时区 bug。

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
  );
}