JavaScript读取Excel时日期和数字格式错乱怎么处理

一、底层根源：为什么读取的数据会错乱

许多开发者在初次遇到该现象时，往往误以为是文件解析库损坏了数据。其实不然，错乱的根本原因源于 Excel 内部独特的底层数据存储协议与 JavaScript 原生类型机制之间的天然冲突：

• Excel 根本没有独立的“日期类型”：在 .xlsx 文件的底料 XML 中，日期实际上以“距离原点的浮点天数（Serial Number）”保存。以 1900 年日期系统为例，1900-01-01 被记为 1，而 2024-05-20 对应的数值是 45432。之所以在表格中能看到年月日，完全是因为单元格应用了特定的数字格式代码（Number Format Code，如 yyyy/mm/dd）。如果解析库以原始数据（Raw Value）输出，拿到的自然就是浮点天数。
• Lotus 1-2-3 遗留的历史闰年 Bug：为了向数十年前风靡的 Lotus 1-2-3 电子表格保持向后兼容，Excel 故意保留了一个错误逻辑：将 1900 年视为闰年，虚构了 1900-02-29 这一天（对应序列号 60）。直接按照每天 86400 秒计算 Unix 时间戳时，如果不做特殊矫正就会产生至少 1 天的时间漂移。
• JavaScript 的双精度浮点数（IEEE 754）安全边界：JavaScript 中的 Number 类型最大安全整数是 Number.MAX_SAFE_INTEGER（即 9007199254740991，共 16 位）。当表格中输入 18 位的身份证号或 19 位的雪花算法订单号且单元格未设为文本时，解析库默认将其转化为 JS Number，导致最后几位直接丢失并被置为 000，或者在输出 JSON 时被转换为科学计数法（如 1.10101e+17）。

图 1：Excel底层存储模型与JavaScript默认解析冲突机理

二、日期格式错乱的处理方案

处理日期主要有两种场景：一是希望直接获得 JavaScript 原生 Date 对象；二是直接获得类似 "2024-05-20" 的规范化日期字符串。针对业界最常用的 SheetJS（xlsx 库），有以下具体应对措施。

方案 1：开启解析配置参数（快速转为 Date 对象）

在调用 XLSX.read 时，默认情况下 cellDates 为 false。必须显式将其置为 true，解析库才会尝试根据单元格格式代码（如含有 yy、mm、dd）自动将序列号转换为 JS 原生 Date 对象。

// 关键配置：开启 cellDates 与 dateNF
const workbook = XLSX.read(dataBuffer, {
  type: 'array',
  cellDates: true,      // 将日期序列号自动解析为原生 Date
  dateNF: 'yyyy-mm-dd'  // 指定格式化字符串时的默认格式
});

// 提取数据为 JSON
const sheetData = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]);
console.log(sheetData[0].birthday); // 输出为 JS Date 实例

方案 2：基于序列号进行本地时区精准转换（推荐生产采用）

为了不受运行环境本地时区或国际化配置的影响，最稳健的做法是：解析时保持原始序列号，然后在应用层通过专用函数进行确定性数学转换。

Excel 日期序列号转 JS 时间戳的核心换算公式为：(serial - 25569) * 86400 * 1000。其中 25569 代表从 1900-01-01 到 Unix 纪元 1970-01-01 之间间隔的天数（已包含 1900 闰年修正）。

/**
 * 将 Excel 日期序列号精准转换为 'YYYY-MM-DD' 字符串
 * @param {number} serial Excel序列号，如 45432
 * @returns {string} 格式化日期字符串
 */
function formatExcelSerialDate(serial) {
  // 必须扣除1900年Lotus润年历史偏差天数
  const utcDays = Math.floor(serial - 25569);
  const utcValue = utcDays * 86400; // 秒数
  const dateInfo = new Date(utcValue * 1000);

  // 提取浮点小数部分计算当天的时间（时分秒）
  const fractionalDay = serial - Math.floor(serial) + 0.0000001;
  let totalSeconds = Math.floor(86400 * fractionalDay);

  const seconds = totalSeconds % 60;
  totalSeconds = Math.floor(totalSeconds / 60);
  const minutes = totalSeconds % 60;
  const hours = Math.floor(totalSeconds / 60);

  const year = dateInfo.getUTCFullYear();
  const month = String(dateInfo.getUTCMonth() + 1).padStart(2, '0');
  const day = String(dateInfo.getUTCDate()).padStart(2, '0');

  return `${year}-${month}-${day}`;
}

// 示例验证
console.log(formatExcelSerialDate(45432)); // 输出: "2024-05-20"

三、长数字、身份证号与小数精度处理方案

身份证号、银行卡号等长数字被截断或变成 1.23E+17，通常发生在“用户录入时直接以通用格式填写”或“读取时库强制将其按 Number 解析”。

1. 使用 raw: false 直接读取单元格格式化文本（.w 属性）

在 SheetJS 中，每一个单元格对象除了 v（原始数值，如 123456789012345678）以外，还包含 w（格式化后的文本字符串）。

• 当设置 raw: false 时，XLSX.utils.sheet_to_json 会优先读取单元格的 w 属性而不是原始的 v。
• 这样读取出的身份证号就是纯字符串，既不会变成科学计数法，也不会丢失结尾数值。

const jsonData = XLSX.utils.sheet_to_json(worksheet, {
  raw: false // 关键：禁用原始数值输出，强制使用单元格展示文本（格式化字符串）
});

2. 浮点运算精度漂移的归一化

对于单价、金额等小数，由于二进制浮点数无法精准表示十进制部分小数（如 0.1 或 0.2），读取出的原始值偶尔会出现 19.899999999999999。针对金额场景：

• 方案 A（展示层）：采用 Number(val).toFixed(2) 强制截断保留两位。
• 方案 B（交易计算层）：在读取阶段将金额乘以 100 并使用 Math.round 转为以“分”为单位的纯整数存储。

图 2：Excel格式异常排查与修复标准流程

四、常见格式异常与对应处理策略矩阵

在日常业务导入中，不同字段类型对应不同的底层表现与处理逻辑。下表梳理了最常见的四类错乱表现、核心诱因及针对性解决路径：

图 3：常见格式异常与对应处理策略矩阵

五、生产级完整代码封装示例

在实际工程中，不能指望所有导入表格的用户都严格设置单元格格式。最佳实践是实现一套结构化清洗管道：读取原始工作表，遍历每一个单元格，结合“字段映射契约（Schema）”对日期列、长数字列进行容错归一化。

import * as XLSX from 'xlsx';

/**
 * 字段清洗契约
 * 声明每一列的业务类型：'date' | 'string' | 'money' | 'number'
 */
const columnSchema = {
  '员工姓名': 'string',
  '身份证号': 'string', // 严禁按数字解析
  '入职日期': 'date',   // 需要按序列号/日期清洗
  '基本薪资': 'money'   // 涉及金额精度
};

/**
 * 完整读取并规整 Excel 数据
 * @param {ArrayBuffer} buffer 文件二进制内容
 * @returns {Array<Object>} 清洗后的纯净业务对象数组
 */
export function parseExcelWithSchema(buffer) {
  // 步骤 1：以较低底层侵入度读取 Workbook
  // 不盲目开启 cellDates，保留原始序列号与格式化文本
  const workbook = XLSX.read(buffer, {
    type: 'array',
    cellDates: false,
    cellNF: true,
    cellText: true
  });

  const firstSheetName = workbook.SheetNames[0];
  const worksheet = workbook.Sheets[firstSheetName];

  // 步骤 2：获取原始行数据（开启 raw: false 保证初步获取单元格可见文本）
  const rawList = XLSX.utils.sheet_to_json(worksheet, {
    raw: false,
    defval: ''
  });

  // 步骤 3：根据字段契约执行清洗
  return rawList.map((row) => {
    const cleanRow = {};

    for (const [key, val] of Object.entries(row)) {
      const type = columnSchema[key] || 'string';

      switch (type) {
        case 'date':
          cleanRow[key] = normalizeExcelDate(val);
          break;

        case 'string':
          // 彻底去除科学计数法隐患与首尾空白，保留长字符串纯文本
          cleanRow[key] = String(val).trim();
          break;

        case 'money':
          // 清除可能存在的货币符号与逗号，转为严格两位小数数值
          const cleanNum = String(val).replace(/[￥,$,\s]/g, '');
          cleanRow[key] = isNaN(Number(cleanNum)) ? 0 : Number(Number(cleanNum).toFixed(2));
          break;

        default:
          cleanRow[key] = val;
      }
    }
    return cleanRow;
  });
}

/**
 * 容错日期清洗函数
 * 兼容纯五位数字序列号、普通日期字符串与斜杠格式
 */
function normalizeExcelDate(val) {
  if (!val) return '';

  // 场景 A：本身是五位纯数字（Excel 序列号，如 45432）
  if (/^\d{5}(\.\d+)?$/.test(String(val))) {
    const serial = parseFloat(val);
    const utcDays = Math.floor(serial - 25569);
    const date = new Date(utcDays * 86400 * 1000);
    const y = date.getUTCFullYear();
    const m = String(date.getUTCMonth() + 1).padStart(2, '0');
    const d = String(date.getUTCDate()).padStart(2, '0');
    return `${y}-${m}-${d}`;
  }

  // 场景 B：斜杠格式转换为短横线（如 2024/5/20 -> 2024-05-20）
  if (typeof val === 'string' && val.includes('/')) {
    const parts = val.split(/[\/\s]/);
    if (parts.length >= 3) {
      const y = parts[0];
      const m = parts[1].padStart(2, '0');
      const d = parts[2].padStart(2, '0');
      return `${y}-${m}-${d}`;
    }
  }

  return String(val);
}

六、生产级 Excel 导入架构与工程防坑指南

在面对大量用户上传、跨平台协作（Windows Excel、Mac Numbers、WPS、LibreOffice）时，单靠代码修复容易陷入各种边缘特例。一个鲁棒的导入系统应当建立分层防护：

图 4：企业级导入架构与全流程数据清洗机制

工程落地四项核心总结

1. 能用格式化字符串就不要直接取裸数值：优先利用单元格的 w 属性（或 raw: false）读取用户在 Excel 里眼睛看到的真实文本，避免进入 JavaScript 浮点数解析体系。
2. 不要把日期解析完全托管给第三方库的时区策略：由于 Excel 无时区概念，第三方库通常默认当作 UTC 时间转换，在东八区往往会触发“少一天”的隐蔽缺陷。使用公式 (serial - 25569) * 86400 * 1000 手动拆解 UTC 年月日最为稳妥。
3. 长数字列在模板中强行约束为文本：提供给用户的导入模板中，务必对身份证号、订单号、银行卡号等单元格提前设置为“文本”格式，或者提示录入时先打一个半角单引号 '。
4. 浮点数杜绝直接累加计算：读取完价格或金额后，展示时通过 toFixed(2) 截取，计算时乘 100 转化为整型分或引入成熟的高精度数学库。