Summary

Adds support for multiple pivot tables per workbook. Previously limited to one pivot table per file, users can now create multiple pivot tables from the same or different source worksheets.

This resolves a fundamental limitation mentioned in the original pivot table PR (#2551) and addresses user requests for multi-table support.

Motivation

The current implementation throws an error when attempting to create more than one pivot table:

Error: A pivot table was already added. At this time, ExcelJS supports at most one pivot table per file.

However, Excel natively supports multiple pivot tables, and users frequently need to create different views of the same data source.

Changes

1. Unique Cache IDs (lib/doc/pivot-table.js)

• Changed from hardcoded cacheId: '10' to dynamic cacheId: String(10 + worksheet.workbook.pivotTables.length)
• Each pivot table now gets its own cache ID (10, 11, 12, etc.)

2. Unique Pivot Table UIDs (lib/xlsx/xform/pivot-table/pivot-table-xform.js)

• Replaced hardcoded UUID with uuidv4() for each pivot table
• Prevents Excel from treating all pivot tables as identical

3. Complete Cache Field Data (lib/doc/pivot-table.js)

• Changed from generating sharedItems only for fields used by that pivot table
• Now generates sharedItems for ALL fields in source worksheet
• Allows any field to be used in any pivot table configuration

4. Correct Worksheet Relationships (lib/doc/worksheet.js, lib/xlsx/xform/sheet/worksheet-xform.js)

• Added tableNumber property to track correct pivot table file references
• Changed from pivotTable1.xml (hardcoded) to pivotTable${tableNumber}.xml
• Fixed worksheet relationship XML to reference correct files

5. Fixed State Mutation Bug (lib/doc/pivot-table.js)

• Changed splice() to slice() in makeCacheFields()
• Prevents corrupting source worksheet column data on subsequent pivot table creation

6. Remove One-Table Limit (lib/doc/pivot-table.js)

• Removed validation check that enforced single pivot table limit

Test Plan

Added comprehensive test case test/test-pivot-multiple-from-same-source.js:

• Creates 3 pivot tables from the same source worksheet
• Each with completely different field configurations
• Verifies Excel can open file and display all tables correctly
• All existing tests continue to pass

Example Usage

const workbook = new Excel.Workbook();

// Source data
const dataSheet = workbook.addWorksheet('Sales Data');
dataSheet.addRows([
  ['Region', 'Quarter', 'Product', 'Amount'],
  ['East', 'Q1', 'Widget', 1000],
  ['West', 'Q2', 'Gadget', 2000],
  // ... more data
]);

// Pivot Table 1: By Region
const pivot1 = workbook.addWorksheet('By Region');
pivot1.addPivotTable({
  sourceSheet: dataSheet,
  rows: ['Region'],
  columns: ['Quarter'],
  values: ['Amount']
});

// Pivot Table 2: By Product (different configuration)
const pivot2 = workbook.addWorksheet('By Product');
pivot2.addPivotTable({
  sourceSheet: dataSheet,
  rows: ['Product'],
  columns: ['Quarter'],
  values: ['Amount']
});

// Both pivot tables work correctly!
await workbook.xlsx.writeFile('multiple-pivots.xlsx');

Backwards Compatibility

✅ Fully backwards compatible - Existing code with single pivot tables works identically
✅ No breaking changes - All existing tests pass
✅ Opt-in feature - Multiple tables only if user creates them

Files Changed

• lib/doc/pivot-table.js - Core pivot table generation logic
• lib/doc/worksheet.js - Track table numbers
• lib/xlsx/xform/pivot-table/pivot-table-xform.js - Unique UIDs
• lib/xlsx/xform/sheet/worksheet-xform.js - Correct file references
• test/test-pivot-multiple-from-same-source.js - Test case

Related Issues

• Add pivot table with limitations #2551 - Original pivot table PR (mentioned limitation)
• Addresses user requests for multi-table support

Checklist

• Includes unit/integration tests
• All existing tests pass
• No breaking changes
• Tested manually with Excel
• Backwards compatible

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