Exceljs : 'We found a problem with some content in "file.xlsx"(...)'

Question

I'm having some issue with an Exceljs generated xlsx file. I've created csv files in my project for a while without any problem. But now I'm creating an xlsx file and, while I can open it clean on my Ubuntu/LibreOffice Calc, there is an error when trying to do the same on Excel (on Windows and IOS all the same).

My file's name is "export-yyyy-mm-dd.xlsx" with two sheets named "Total" and "Details", respectively.

Both sheets have a "fake" title line above columns headers. The second file has 13 columns of either string or number data for all my users while the first sheet aggregates those data in 6 number columns, with just one line under the headers.

It is that first sheet one line of data that is missing when opening the file with Excel.

What I've tried so far :

• move plain numeric values from string format to number format
• move the one percentage cell from string to number format too
• update my Exceljs package (from 3.9 to 4.4)
• remove the sheets setup on creation
• remove the "fake" titles from both sheets
• remove the first sheet
• remove the second sheet

I'm out of new ideas right now so : what might be causing this problem ?

Here is a simplified version of the code that generates the first sheet :

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%";
}

Edit : If i only include one of the two sheets, it works just fine, whether it's the first or the second sheet. So the issue might not be related to the way this sheet is created / its content.

Due to the nature of the data and the potential huge size of it, I generate my file like this :

• I create the file/workbook
• I create both sheets with their titles and columns' headers
• I loop on my chunked data to fill my SECOND sheet, 200 users at a time. While I do this, in increase my totals used for the first sheet. I also write in my file buffer
• I used the totals previously created to fill my FIRST sheet. I write in my file buffer
• I end my passthrough

Here's what I do when the first sheet is filled :

  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;

Answer 1

It turns out my problem was with s3 and not with ExcelJs. I need to commit() every row, every sheet and the whole document when any of them is finished. I also need to create my file as a WorkbookWriter (not a simple Workbook) and to pass it my stream on creation, like this :

  const file = new stream.xlsx.WorkbookWriter({
    stream: passThrough,
  });

Doing it any other way seemed to kind of corrupt the data enough for Excel to have trouble reading it, but not enough to trouble LibreOffice.