Summary

I faced the issue with stream writer creating corrupted file. Through some experiments and inspecting differences between the file created by the library and handmade file using Microsoft Excel I found out that the corruption appears when you use conditional formatting together with hyperlinks. The issue is that the order of blocks in the worksheet is wrong. This pull request solves this issue.

Test plan

First we create file using Microsoft Excel with links and some conditional formatting eg. =MOD(ROW();2)=0 to color every odd row:

Then we inspect the file and see that the order in sheet is: first conditional formatting, second hyperlinks

After that we create similar file using the library:

const ExcelJS = require("exceljs");
const fs = require("fs");

const fileStream = fs.createWriteStream("output.xlsx");

const workbook = new ExcelJS.stream.xlsx.WorkbookWriter({
  stream: fileStream,
  useSharedStrings: false,
  useStyles: true,
});
const worksheet = workbook.addWorksheet("Sheet1");
for (let index = 0; index < 4; index++) {
  worksheet.addRow([
    "test",
    {
      text: "Link",
      hyperlink: "https://google.com",
      tooltip: "https://google.com",
    },
  ]);
}

worksheet.addConditionalFormatting({
  ref: `A1:B4`,
  rules: [
    {
      priority: 1,
      type: "expression",
      formulae: ["MOD(ROW(),2)=0"],
      style: {
        fill: {
          type: "pattern",
          pattern: "solid",
          bgColor: "FFEDEDED",
        },
      },
    },
  ],
});

worksheet.commit();
workbook.commit();

If we open the resulting file with Excel we see the error:

If we inspect the underlying xml file we see that the order is different:

So then we can change the order in worksheet-writer.js and try the script again:

The file generates successfully and opens in Excel: