使用Node.js+XLSX上传Excel到MySQL时的日期格式错误排查

核心问题1：Excel日期被解析为数字 #

Excel的日期本质是1900年1月0日为基准的序列号（比如36809对应2000-10-01），xlsx库默认读取单元格原始值，哪怕你在Excel里设置了日期显示格式，解析后仍会得到数字。

解决方法：强制转成MySQL兼容的日期字符串 #

两种可靠处理方式：

• 利用xlsx内置转换规则：

  const XLSX = require('xlsx');
  
  // 解析工作表时指定格式
  const worksheet = workbook.Sheets[sheetName];
  const data = XLSX.utils.sheet_to_json(worksheet, {
    raw: false, // 关闭原始值读取，让xlsx自动识别日期单元格
    dateNF: 'yyyy-mm-dd hh:mm:ss' // 指定输出为MySQL兼容的日期格式
  });
  

  raw: false会让xlsx把日期单元格转为JS Date对象，再通过dateNF直接输出标准日期字符串。

• 手动转换序列号（备用方案）：
  如果上述方法不生效，遍历数据时对日期字段做二次处理：

  function convertExcelDate(excelNum) {
    // 修正Excel 1900年基准的bug
    const date = new Date((excelNum - 25568) * 86400000);
    return date.toISOString().slice(0, 19).replace('T', ' ');
  }
  
  data.forEach(item => {
    // 假设日期字段为create_time，判断是否为Excel日期序列号
    if (typeof item.create_time === 'number' && item.create_time > 25568) {
      item.create_time = convertExcelDate(item.create_time);
    }
  });
  

核心问题2：日期错误引发外键约束异常 #

日期格式错误导致主表插入失败后，若代码未中断流程继续执行外键表插入，就会触发ER_NO_REFERENCED_ROW_2（找不到主表对应行）。

解决方法：事务控制+错误中断 #

用MySQL事务包裹所有插入操作，出错立即回滚并终止后续逻辑：

const connection = await mysql.createConnection(dbConfig);

try {
  await connection.beginTransaction();
  
  // 先插入主表
  const mainResult = await connection.query(
    'INSERT INTO main_table (create_time) VALUES (?)', 
    [item.create_time]
  );
  const mainId = mainResult.insertId;

  // 再插入关联外键表
  await connection.query(
    'INSERT INTO foreign_table (main_id) VALUES (?)', 
    [mainId]
  );

  await connection.commit();
} catch (err) {
  await connection.rollback();
  throw err; // 抛出错误交给上层统一处理
} finally {
  connection.end();
}

核心问题3：ERR_HTTP_HEADERS_SENT响应重复发送 #

这个问题是因为错误处理逻辑不严谨，比如catch块发送响应后，后续代码又执行了res.send()/res.json()。

解决方法：确保单次请求仅发送一次响应 #

app.post('/upload', async (req, res) => {
  try {
    // 上传、解析、插入逻辑
    return res.status(200).json({ success: true, msg: '数据导入成功' });
  } catch (err) {
    console.error(err);
    // 发送响应后立即return，终止后续代码执行
    return res.status(500).json({ success: false, msg: err.message });
  }
  // 禁止在try/catch外部添加响应逻辑
});

额外验证步骤 #

1. 打印解析后的数据，确认日期字段是YYYY-MM-DD HH:MM:SS格式的字符串。
2. 检查MySQL表的日期字段类型（DATE/DATETIME/TIMESTAMP），确保格式匹配。
3. 验证Excel单元格格式：右键单元格→设置单元格格式→确认是「日期」类型，而非自定义格式伪装的文本显示（这种情况单元格实际值仍是数字）。

内容的提问来源于stack exchange，提问作者Gian Pranata