Summary

Adds optional applyWidthHeightFormats parameter to pivot tables, allowing users to preserve worksheet column widths instead of using Excel's default pivot table style widths.

Problem

By default, Excel pivot table styles control column widths, overriding any custom column widths set on the worksheet. This is Excel's standard behavior (applyWidthHeightFormats='1'), but users often want to preserve their carefully crafted column widths.

Current behavior:

worksheet.getColumn(1).width = 30;  // Set custom width
worksheet.addPivotTable({...});     // Custom width ignored! 

The pivot table style resets all column widths, making custom sizing impossible.

Solution

Add optional applyWidthHeightFormats parameter (default: '1' for backwards compatibility):

worksheet.getColumn(1).width = 30;  // Set custom width
worksheet.addPivotTable({
  sourceSheet: dataSheet,
  rows: ['Region'],
  columns: ['Quarter'],
  values: ['Amount'],
  applyWidthHeightFormats: '0'  // ✅ Preserve custom widths
});

Implementation

1. API Change (lib/doc/pivot-table.js)

Added parameter to pivot table model with sensible default:

applyWidthHeightFormats: model.applyWidthHeightFormats !== undefined 
  ? model.applyWidthHeightFormats 
  : '1'  // Default to standard Excel behavior

2. XML Generation (lib/xlsx/xform/pivot-table/pivot-table-xform.js)

Pass the setting to Excel via OOXML attribute:

<pivotTableDefinition 
  ... 
  applyWidthHeightFormats="0">

Per OOXML spec:

• '1' (default) - Apply pivot table style widths/heights
• '0' - Preserve worksheet widths/heights

3. Test Case (test/test-pivot-table.js)

Added test demonstrating custom width preservation:

worksheet.getColumn(1).width = 30;
worksheet.getColumn(2).width = 15;

worksheet.addPivotTable({
  ...
  applyWidthHeightFormats: '0'
});

Use Cases

1. Custom Dashboard Layouts

// Wide label column, narrow data columns
worksheet.getColumn(1).width = 40;
worksheet.getColumn(2).width = 12;
worksheet.addPivotTable({..., applyWidthHeightFormats: '0'});

2. Print-Optimized Reports

// Precise column widths for PDF export
worksheet.columns = [
  { width: 25 },
  { width: 15 },
  { width: 15 },
];
worksheet.addPivotTable({..., applyWidthHeightFormats: '0'});

3. Responsive Layouts

// Adapt to different screen sizes
const isWideScreen = true;
worksheet.getColumn(1).width = isWideScreen ? 50 : 30;
worksheet.addPivotTable({..., applyWidthHeightFormats: '0'});

Backwards Compatibility

✅ 100% backwards compatible

• Default value '1' maintains existing behavior
• Existing code works identically
• Opt-in feature - only affects users who explicitly set it

// Before (still works exactly the same)
worksheet.addPivotTable({...});

// After (new optional feature)
worksheet.addPivotTable({..., applyWidthHeightFormats: '0'});

Standards Compliance

Follows Office Open XML Part 1 Section 18.10:

applyWidthHeightFormats (Apply Width and Height Formats)
Specifies a boolean value that indicates whether to apply width and height formatting from the PivotTable style.

This is a standard Excel feature exposed through the OOXML format.

Files Changed

• lib/doc/pivot-table.js - Accept parameter, set default
• lib/xlsx/xform/pivot-table/pivot-table-xform.js - Pass to XML
• test/test-pivot-table.js - Test case demonstrating feature

Checklist

• Includes test case
• All existing tests pass
• No breaking changes
• Backwards compatible (opt-in)
• Follows OOXML specification
• Tested manually with Excel

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