Summary

Adds optionalapplyWidthHeightFormats 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 widthworksheet.addPivotTable({...});// Custom width ignored!

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

Solution

Add optionalapplyWidthHeightFormats parameter (default:'1' for backwards compatibility):

worksheet.getColumn(1).width=30;// Set custom widthworksheet.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">

PerOOXML 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 columnsworksheet.getColumn(1).width=40;worksheet.getColumn(2).width=12;worksheet.addPivotTable({...,applyWidthHeightFormats:'0'});

2. Print-Optimized Reports

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

3. Responsive Layouts

// Adapt to different screen sizesconstisWideScreen=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

FollowsOffice 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.