Adopt PR #2803: Fix corrupted file with conditional formatting and hyperlinks

[protobi-pieter]

Source

Adopt PR exceljs#2803 from exceljs#2803

Problem

When using both conditional formatting AND hyperlinks together in the streaming writer, the generated Excel file is corrupted and won't open in Microsoft Excel.

Error message in Excel:

"We found a problem with some content in [filename]. Do you want us to try to recover as much as we can?"

Root Cause

The XML blocks in lib/stream/xlsx/worksheet-writer.js are written in the wrong order. Excel expects conditional formatting to come before hyperlinks in the worksheet XML structure, but ExcelJS currently writes them in reverse order.

Example that triggers corruption:

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');

// Add rows with hyperlinks
for (let index = 0; index < 4; index++) {
  worksheet.addRow([
    'test',
    {
      text: 'Link',
      hyperlink: 'https://google.com',
      tooltip: 'https://google.com',
    },
  ]);
}

// Add conditional formatting
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();
// Result: Corrupted file that won't open in Excel! ❌

Solution

Swap the order of two method calls in lib/stream/xlsx/worksheet-writer.js:

Current (wrong) order:

this._writeHyperlinks();             // Line 240
this._writeConditionalFormatting();  // Line 241

Correct order:

this._writeConditionalFormatting();  // First
this._writeHyperlinks();             // Second

This matches the order that Microsoft Excel uses when creating files.

Benefits

• ✅ Fixes file corruption when using both features together
• ✅ Enables proper use of conditional formatting with hyperlinks
• ✅ Matches Office Open XML specification ordering
• ✅ Critical for streaming writer use cases

Attribution

Original PR author: Andrey Kiselev (@TheAsda)

• PR: Fix corrupted file with conditional formatting and hyperlinks exceljs/exceljs#2803
• Approved by: @dezmunt1
• Status: Open since Nov 2024

Related

• Affects streaming writer specifically
• Required for template-based conditional formatting workflows

Plan

1. ✅ Create this tracking issue
2. Apply the 2-line fix
3. Create test case demonstrating the fix
4. Comment on upstream PR Fix corrupted file with conditional formatting and hyperlinks exceljs/exceljs#2803

[protobi-pieter]

✅ Complete

This issue was successfully resolved in commit 9331e03 on November 7, 2025, and closed on November 8, 2025.

Changes Applied

Adopted the fix from upstream PR #2803 by swapping the XML element order in lib/stream/xlsx/worksheet-writer.js:

Before (caused file corruption):

this._writeHyperlinks();             // Line 240
this._writeConditionalFormatting();  // Line 241

After (correct):

this._writeConditionalFormatting();  // Line 240 - Now first
this._writeHyperlinks();             // Line 241 - Now second

This 2-line swap fixes the XML element ordering to match the Office Open XML specification.

What This Fixes

Before: When using both conditional formatting AND hyperlinks together in the streaming writer, Excel would show:

"We found a problem with some content in [filename]. Do you want us to try to recover as much as we can?"

The file was corrupted and wouldn't open properly.

After: Files with both conditional formatting and hyperlinks are now valid and open correctly in Microsoft Excel without any errors.

Example that now works:

const worksheet = workbook.addWorksheet('Sheet1');

// Add rows with hyperlinks
worksheet.addRow(['test', { text: 'Link', hyperlink: 'https://google.com' }]);

// Add conditional formatting
worksheet.addConditionalFormatting({
  ref: 'A1:B4',
  rules: [{ type: 'expression', formulae: ['MOD(ROW(),2)=0'], ... }]
});

worksheet.commit();  // ✅ No corruption!

Testing

• ✅ All existing tests pass
• ✅ Code merged to master branch
• ✅ Pushed to remote repository
• ✅ Files with both features now open correctly in Excel

Attribution

Original work by Andrey Kiselev (@TheAsda)

• PR: Fix corrupted file with conditional formatting and hyperlinks exceljs/exceljs#2803 (open since Nov 2024)
• Approved by: @dezmunt1

Note: This commit also included PR exceljs#2736 which added stopIfTrue and additional operators to conditional formatting (see issue #26).