I have encountered an issue with an xlsx file generated using Exceljs. While I have been creating csv files in my project without any problems, creating an xlsx file now poses an error. The xlsx file opens clean on Ubuntu/LibreOffice Calc, but there is an error when opening it on Excel (Windows and IOS).
The file is named "export-yyyy-mm-dd.xlsx" and consists of two sheets titled "Total" and "Details". Both sheets have a "fake" title line above the column headers. The second sheet contains 13 columns of string or number data for all the users, while the first sheet aggregates this data into 6 number columns with just one line under the headers.
The missing data when opening the file with Excel seems to be the one line of data in the first sheet.
I have tried several solutions so far:
- Converting plain numeric values from string to number format
- Converting the percentage cell from string to number format
- Updating the Exceljs package (from 3.9 to 4.4)
- Removing the sheets setup on creation
- Removing the "fake" titles from both sheets
- Removing the first sheet
- Removing the second sheet
I am currently out of ideas, so what could be causing this issue?
Below is a simplified version of the code used to generate 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: Including only one of the two sheets works fine, whether it's the first or the second sheet. Therefore, the issue may not be related to the way this sheet is created or its content.
Due to the nature of the data and its potential large size, I generate my file in the following manner:
- Create the file/workbook
- Create both sheets with their titles and column headers
- Loop through chunked data to fill the SECOND sheet, 200 users at a time, increasing totals used for the first sheet. Write in the file buffer during this process.
- Use the previously generated totals to fill the FIRST sheet. Write in the file buffer.
- End the 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;