NodeJS填充大Excel模板内存溢出问题及解决方案咨询

问题背景 #

我有一个NodeJS服务，需加载约26MB的Excel模板并填充数据后提供给React前端用户下载。但当3位用户同时下载，或单个用户选择大量数据下载时，会出现内存堆溢出导致服务崩溃。已知原因是xlsx-populate会将整个Excel模板加载到内存中，我尝试过ExcelJS、xlsx-template均遇到相同内存问题。尝试过流处理，但xlsx-populate无此功能；还考虑过在模板与数据间设置中间层（如让Excel引用JSON文件），但最终需生成完整的Excel文件。

优化方案 #

1. 避免临时文件冗余拷贝 #

原代码中先拷贝模板到临时文件再读取，会产生额外磁盘IO和内存占用，直接从模板文件加载工作簿，减少内存拷贝：

// 替换原代码中fs.copyFileSync + fs.readFile的逻辑
XlsxPopulate.fromFileAsync(srcPath)
  .then(workbook => {
    // 数据填充逻辑
  })

2. 流式返回生成结果 #

生成Excel时直接流式输出到响应，不写入本地临时文件，减少内存和磁盘资源占用：

// 替换workbook.toFileAsync(destPath)的逻辑
const buffer = await workbook.toBufferAsync();
res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
res.setHeader('Content-Disposition', `attachment; filename="${nameFile}.xlsx"`);
res.send(buffer);
// 手动解除引用，触发垃圾回收
workbook = null;

3. 分批次处理大数据量 #

如果数据量极大，将数据分成小批次处理，每处理一批后强制触发垃圾回收（需启动Node时添加--expose-gc参数）：

const batchSize = 1000;
for (let i = 0; i < response.data.length; i += batchSize) {
  const batch = response.data.slice(i, i + batchSize);
  batch.forEach(e => {
    // 填充行数据逻辑
  });
  // 触发垃圾回收
  if (global.gc) global.gc();
}

4. 切换到支持流式的Excel库（推荐） #

使用exceljs实现模板解析+流式生成，手动复刻模板样式，彻底解决内存溢出问题：

const ExcelJS = require('exceljs');

async function generateExcelStream(res, templatePath, data, resumoData) {
  const templateWorkbook = new ExcelJS.Workbook();
  await templateWorkbook.xlsx.readFile(templatePath);
  
  const worksheet = templateWorkbook.getWorksheet('DADOS');
  const resumoSheet = templateWorkbook.getWorksheet('resumo+ações');
  
  // 填充固定模板内容
  const dtNow = dayjs().format("DD/MM/YYYY");
  resumoSheet.getCell('M1').value = `Data Relatório: ${dtNow}`;
  worksheet.getCell('E1').value = dtNow;
  
  // 填充resumo数据
  let rowNum = 8;
  for (const e of resumoData) {
    const row = resumoSheet.getRow(rowNum);
    row.getCell(3).value = e.id || "";
    // 填充其他单元格逻辑...
    row.commit(); // 提交行，释放内存
    rowNum++;
  }
  
  // 填充DADOS数据
  rowNum = 3;
  for (const e of data) {
    const row = worksheet.getRow(rowNum);
    const dtString = e.dtOperacao.substring(0, 10);
    const mesString = dayjs(dtString, "DD/MM/YYYY", true).isValid()
      ? dayjs(dtString, "DD/MM/YYYY").format("MMMM")
      : "Invalid Date";
    row.getCell(2).value = "";
    // 填充其他单元格逻辑...
    row.commit();
    rowNum++;
  }
  
  // 流式输出到响应
  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  res.setHeader('Content-Disposition', `attachment; filename="${nameFile}.xlsx"`);
  await templateWorkbook.xlsx.write(res);
  res.end();
}

原问题代码 #

const express = require("express");
const XlsxPopulate = require("xlsx-populate");
const fs = require("fs");
const axios = require("axios");
const cors = require("cors");
const dayjs = require("dayjs");
const localizedFormat = require("dayjs/plugin/localizedFormat");
const customParseFormat = require("dayjs/plugin/customParseFormat");
const isSameOrAfter = require("dayjs/plugin/isSameOrAfter");
const isSameOrBefore = require("dayjs/plugin/isSameOrBefore");
const advancedFormat = require("dayjs/plugin/advancedFormat");
const ptBr = require("dayjs/locale/pt-br");
const { v4: uuidv4 } = require("uuid");
const app = express();
const dotenv = require("dotenv");
dotenv.config();
const PORT = process.env.VITE_API_PORT;
const IP = process.env.VITE_NUVEM_URL;
const VITE_API_URL_PORT = process.env.VITE_API_URL_PORT;

app.use(
  cors({
    origin: "*",
    methods: ["GET", "POST", "OPTIONS", "PUT", "PATCH", "DELETE"],
    allowedHeaders: [
      "Origin",
      "X-Requested-With",
      "Content-Type",
      "Accept",
      "Authorization",
    ],
    optionsSuccessStatus: 200,
  })
);

app.use(express.json());

app.get("/update-excel", async (req, res) => {
  const { filter, token } = req.query;

  if (!token) {
    return res.status(400).send("Token é obrigatório.");
  }

  console.log(req.query);

  const parsedFilter = JSON.parse(filter || "{}");
  console.log(parsedFilter);
  console.log(filter);

  const interval = setInterval(() => {
    res.write("gerando\n");
  }, 20000);

  syncDataList(token, parsedFilter, String(uuidv4()), res, interval);
});

async function syncDataList(token, filter, nameFile, res, interval) {
  try {
    const responseResumo = await axios.post(
      `${VITE_API_URL_PORT}/inventory/resume/filter`,
      filter,
      {
        headers: {
          Authorization: `Bearer ${token}`,
        },
        timeout: 300000,
      }
    );

    if (Array.isArray(responseResumo.data)) {
      const response = await axios.post(
        `${VITE_API_URL_PORT}/inventory/resume/download`,
        filter,
        {
          headers: {
            Authorization: `Bearer ${token}`,
          },
          timeout: 300000,
        }
      );

      if (Array.isArray(response.data)) {
        dayjs.extend(customParseFormat);
        dayjs.extend(localizedFormat);
        dayjs.extend(isSameOrAfter);
        dayjs.extend(isSameOrBefore);
        dayjs.extend(advancedFormat);
        dayjs.locale("pt-br");

        const distPath = "\\temp";
        const srcPath = "\\template.xlsx";
        const destPath = `${distPath}\\${nameFile}.xlsx`;

        if (!fs.existsSync(distPath)) {
          fs.mkdirSync(distPath);
        }

        fs.copyFileSync(srcPath, destPath);

        fs.readFile(destPath, (err, data) => {
          if (err) {
            console.error("Erro ao ler o arquivo:", err);
            return;
          }

          XlsxPopulate.fromDataAsync(data)
            .then((workbook) => {
              const worksheet = workbook.sheet("DADOS");
              const worksheetResumo = workbook.sheet("resumo+ações");

              let nuPonto = 3;
              let contador = 1;

              let nuPontoResumo = 8;

              const dtNow = dayjs().format("DD/MM/YYYY");

              const rowOne = worksheetResumo.row(1);
              rowOne.cell(13).value(`Data Relatório: ${dtNow}`);

              responseResumo.data.forEach((e) => {
                if (e) {
                  const row = worksheetResumo.row(nuPontoResumo);

                  row.cell(3).value(e.id || "");
                  //etc

                  nuPontoResumo++;
                } else {
                  console.error("Dados inválidos na linha", nuPontoResumo, e);
                }
              });

              const rowTwo = worksheet.row(1);
              rowTwo.cell(5).value(`${dtNow}`);

              response.data.forEach((e) => {
                if (e) {
                  const row = worksheet.row(nuPonto);
                  const dtString = e.dtOperacao.substring(0, 10);

                  const mesString = dayjs(
                    dtString,
                    "DD/MM/YYYY",
                    true
                  ).isValid()
                    ? dayjs(dtString, "DD/MM/YYYY").format("MMMM")
                    : "Invalid Date";

                  row.cell(2).value(""); //etc

                  nuPonto++;
                  contador++;
                } else {
                  console.error("Dados inválidos na linha", nuPonto, e);
                }
              });

              return workbook.toFileAsync(destPath);
            })
            .then(() => {
              console.log({
                naFile: `${nameFile}.xlsx`,
                address: destPath,
              });
              res.status(200).send({
                naFile: `${nameFile}.xlsx`,
                address: destPath,
              });
            })
            .catch((error) => {
              console.error("Erro ao processar o arquivo Excel:", error);
              res.status(500).send("Houve um erro ao gerar o arquivo excel!!!");
            });
        });
      } else {
        res.status(400).send({ error: "Lista pontos vazia!!!" });
      }
    } else {
      res.status(400).send({ error: "Lista resumo vazia!!!" });
    }
  } catch (error) {
    console.error("Erro ao sincronizar os dados ou gerar o Excel:", error);
    res.status(500).send("Houve um erro ao sincronizar os dados!!!");
  } finally {
    clearInterval(interval);
  }
}

app.listen(PORT, IP, () => {
  console.log(`Server running on ${IP}:${PORT}`);
});

内容的提问来源于stack exchange，提问作者lucas barbosa