Handle undefined/null values in pivot table data

[protobi-pieter]

Problem

When generating pivot tables with data that contains undefined or null values, ExcelJS throws an error:

Error: undefined not in sharedItems []

This occurs in lib/xlsx/xform/pivot-table/pivot-cache-records-xform.js when the code tries to find the index of undefined/null values in the sharedItems array.

Root Cause

The renderCell() function in pivot-cache-records-xform.js attempts to look up every cell value in the sharedItems array to get its index. When a cell contains undefined or null, this lookup fails because these values aren't added to sharedItems.

Expected Behavior

Excel's pivot table XML format has a specific way to represent missing/null values: the <m /> (missing) tag. When a cell value is undefined or null, the code should output <m /> instead of trying to find it in sharedItems.

Proposed Solution

In lib/xlsx/xform/pivot-table/pivot-cache-records-xform.js, modify the renderCell() function to handle undefined/null values:

function renderCell(value, sharedItems) {
  // Handle undefined/null values as missing items
  if (value === undefined || value === null) {
    return '<m />';
  }
  
  // Find the index in sharedItems for non-null values
  const index = sharedItems.indexOf(value);
  if (index === -1) {
    throw new Error(`${JSON.stringify(value)} not in sharedItems ${JSON.stringify(sharedItems)}`);
  }
  
  return `<x v="${index}" />`;
}

Use Case

In our workflow, we're generating pivot tables from database queries where some fields may be NULL (e.g., optional territory information, missing ZIP codes, etc.). The pivot table should gracefully handle these missing values rather than throwing an error.

Additional Context

Excel's PivotCacheRecords XML uses these tags:

• <x v="index" /> - reference to a shared item by index
• <m /> - missing/null value
• <s v="value" /> - inline string value (less common)
• <n v="123" /> - inline numeric value (less common)

The <m /> tag is the standard way Excel represents null/missing values in pivot table data.

Testing

After this fix, the following should work without errors:

// Data with null values
sheet.addRow(['Product A', null, 100]);
sheet.addRow(['Product B', 'East', 200]);
sheet.addRow([null, 'West', 150]);

// Create pivot table
pivotSheet.addPivotTable({
  sourceSheet: sheet,
  rows: ['Product', 'Region'],
  values: ['Sales'],
  metric: 'sum'
});

[protobi-pieter]

Problem Fixed: ExcelJS was throwing "undefined not in sharedItems" error when pivot table
data contained null/undefined values.

Solution: Added a check at the beginning of renderCell() to handle null/undefined values
by returning (Excel's standard tag for missing data).

Changes Made:

1. lib/xlsx/xform/pivot-table/pivot-cache-records-xform.js: Added 7 lines (55-60) to check
   for null/undefined and return
2. test/test-pivot-null-values.js: Created test file demonstrating the fix with data
   containing null/undefined values

Testing:

• ✅ Test runs successfully without errors
• ✅ Generated XML contains correct tags for missing values
• ✅ Excel will correctly interpret these as empty cells in the pivot table