Summary

Fixes two critical issues with pivot table data handling:

1. XML special character escaping - Prevents Excel corruption errors when data contains &, <, >, ", '
2. Null/undefined value handling - Prevents crashes when source data contains missing values

Both issues cause Excel to display "We found a problem with some content" or crash when opening files generated by ExcelJS.

Problem

Issue 1: XML Special Characters Not Escaped

When pivot table source data contains XML special characters (e.g., company names like "Smith & Co"), ExcelJS generates invalid XML:

<s v="Smith & Co"/>  ❌ Invalid XML - unescaped ampersand

Excel rejects the file with: "We found a problem with some content in 'file.xlsx'"

Issue 2: Null/Undefined Values Cause Crashes

When source data contains null or undefined:

['Widget', null, 100],  // Missing category

ExcelJS throws:

Error: undefined not in sharedItems

Solution

1. XML Escaping (lib/xlsx/xform/pivot-table/cache-field.js)

Added escapeXml() method to properly escape special characters in cache field values:

escapeXml(str) {
  return str
    .replace(/&/g, '&')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;')
    .replace(/"/g, '&quot;')
    .replace(/'/g, '&apos;');
}

Now generates valid XML:

<s v="Smith &amp; Co"/>  ✅ Valid XML

2. Null/Undefined Handling (lib/xlsx/xform/pivot-table/pivot-cache-records-xform.js)

Added check before sharedItems lookup:

if (cellValue === null || cellValue === undefined) {
  return '<m />';  // Excel's standard for missing values
}

Follows OOXML standard for representing missing data in PivotCacheRecords.

Test Plan

XML Escaping Test (test/test-pivot-xml-escape.js)

Tests data containing all XML special characters:

['Smith & Co', 'Price < $100', 'Size > 10"', "It's quoted", '<tag>']

Verifies:

• ✅ File generates without errors
• ✅ Excel opens file successfully
• ✅ All characters display correctly

Null Values Test (test/test-pivot-null-values.js)

Tests data with missing values:

['Widget', null, 100],
['Gadget', undefined, 200]

Verifies:

• ✅ No crash during generation
• ✅ Excel opens file successfully
• ✅ Missing values handled per OOXML spec

Backwards Compatibility

✅ Fully backwards compatible
✅ No breaking changes
✅ All existing tests pass
✅ Handles edge cases that previously crashed

Real-World Impact

These fixes address common production scenarios:

Before: ❌ Crashes with data like:

• Company names: "AT&T", "Ben & Jerry's"
• Comparisons: "Price < $50", "Size > 10 inches"
• Quoted text: "The 'best' option"
• Missing data: null, undefined

After: ✅ Works perfectly with all real-world data

Files Changed

• lib/xlsx/xform/pivot-table/cache-field.js - XML escaping
• lib/xlsx/xform/pivot-table/pivot-cache-records-xform.js - Null handling
• test/test-pivot-xml-escape.js - XML escaping tests
• test/test-pivot-null-values.js - Null handling tests

Standards Compliance

• Follows OOXML Part 1 Section 18.10 for PivotCache format
• Uses standard <m /> tag for missing values (per OOXML spec)
• XML escaping follows XML 1.0 specification

Checklist

• Includes unit/integration tests
• All existing tests pass
• No breaking changes
• Tested manually with Excel
• Follows OOXML standards

Fork Context: This PR originates from @protobi/exceljs, a temporary fork with pivot table enhancements. We're submitting all improvements back to upstream.