The problem is that I have a server in NodeJS that only get a template in Excel that is like 26mb~ and populate this template and send to the user to download(using react in front etc.)

but the problem is getting memory heap if 3 users get the download or 1 if the user chooses more data to download.

I know that the cause for this it's exactly when xlsx populate put the entire Excel template in RAM and Node crashes, how the better approach do deal with it?

I have tried ExcelJS and got the same problem.

I tried xlsx-template and get the same memory error.

AI told me to use stream, but don't see this feature in xlsx-populate.

Other thing that I think it's same intermediate between the Excel template and the data, like creating an excel file which have references on JSON file or another data extension... but this file at the end need to be together with the Excel.

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