ExcelJS生成的XLSX文件在Excel中缺失数据的问题求助

问题现象 #

• 生成的export-yyyy-mm-dd.xlsx文件可在Ubuntu的LibreOffice Calc正常打开，但在Windows和iOS的Excel中打开时报错，且Total工作表的唯一一行汇总数据缺失
• 文件包含Total和Details两个工作表：
  • 两个工作表均有列标题上方的“伪”标题行
  • Details工作表含13列字符串或数字数据
  • Total工作表为6列汇总数字数据，仅列标题下有一行数据
• 仅保留任意一个工作表时，文件可正常打开，说明问题与单个工作表的创建或内容无关，出在多工作表的交互逻辑上

已尝试的排查操作 #

• 将纯数值从字符串格式改为数字格式
• 将百分比单元格从字符串改为数字格式
• 升级ExcelJS包（从v3.9到v4.4）
• 删除工作表创建时的配置设置
• 删除两个工作表的“伪”标题行
• 单独删除Total工作表
• 单独删除Details工作表

分块生成流程 #

由于数据量较大，采用分块方式生成文件：

1. 创建工作簿
2. 创建两个工作表并添加标题和列标题
3. 循环处理分块数据填充Details工作表（每次200条），同时统计Total工作表所需的汇总值并写入文件缓冲区
4. 使用统计得到的汇总值填充Total工作表，写入文件缓冲区
5. 结束流传输

关键代码 #

Total工作表填充代码 #

const file = new Workbook();

function createFirstSheetHeaders(): Partial<Column>[] {
  return [
    {
      key: "usersCount",
      width: 50,
    },
    {
      key: "ratio",
      width: 40,
    },
    {
      key: "firstCount",
      width: 40,
    },
    {
      key: "total",
      width: 40,
    },
    {
      key: "secondCount",
      width: 40,
    },
    {
      key: "saved",
      width: 50,
    },
  ];
}

function firstSheetHeadersContent(): {
  usersCount: string;
  ratio: string;
  firstCount: string;
  total: string;
  secondCount: string;
  saved: string;
} {
  return {
    usersCount: "Users count",
    ratio: "Ratio",
    firstCount: "First count",
    total: "Total",
    secondCount: "Second count",
    saved: "Saved",
  };
}

const header = "Export from 04-03-2024 for the period from 01-01-2024 to 04-07-2024";

const firstSheet = file.addWorksheet("Total", {
  pageSetup: { orientation: "landscape" },
});
firstSheet.columns = createFirstSheetHeaders();
firstSheet.mergeCells("A1:F1");
firstSheet.getCell("A1").value = header;
firstSheet.addRow(firstSheetHeadersContent(t));

function formatTotalDataForRow(
  usersCountInput: number,
  ratioInput: number,
  dailyTotal: number,
  firstCount: number,
  secondCount: number
): {
  usersCount: number;
  ratio: number;
  firstCount: number;
  total: number;
  secondCount: number;
  saved: number;
} {
  const parsedTransportEmissionRatio = Number(ratioInput.toFixed(4));

  const total = dailyTotal * firstCount;
  const saved = dailyTotal * secondCount;

  return {
    usersCount: usersCountInput,
    ratio: parsedTransportEmissionRatio,
    firstCount: firstCount,
    total: total,
    secondCount: secondCount,
    saved: saved,
  };

const totalData = formatTotalDataForRow(
    usersCount,
    ratioInput,
    dailyTotal,
    firstCount,
    secondCount
  );

firstSheet.addRow(totalData);
firstSheet.getCell("B3").numFmt = "0.00%";

流处理代码 #

const firstSheetBuffer = await firstSheet.workbook.xlsx.writeBuffer();
if (Buffer.isBuffer(firstSheetBuffer)) {
  passThrough.write(firstSheetBuffer);
} else {
  const err = new Error("ExcelJs does not return a Buffer");
  passThrough.destroy(err);
  throw err;
}
passThrough.end();
return file;

请求协助排查该问题的根本原因。

内容的提问来源于stack exchange，提问作者FE-P