PivotTableBuilder on older Excel Versions

[sercon]

Hi,

The new PivotTableBuilder when creating .xlsx files works very well when opening in newer Excel versions! 👍👍👍

https://phpspreadsheet.readthedocs.io/en/latest/topics/pivot-tables/#creating-a-pivot-table

When opening on older version like eg. Excel 2016, it gives an error 'We found a problem with some content in... Do you want us to revover as much as we can?' Clicking yes, the spreadsheet opens, but the pivottables have been removed. Opening the same file in Excel 365 gives no issues.

[oleibman]

Thank you for the information. I'm not sure what, if anything, we can do about it, aside from possibly adding a note in the documentation. We certainly can't know, when we create a spreadsheet, how it will be used after it's created.

Since you seem to have multiple versions of Excel available (I no longer do), what happens when you use the newest version of Excel to create a spreadsheet with a pivot table and try to open it in the older version? If this results in a better outcome, please post that spreadsheet and an equivalent one created with PhpSpreadsheet here, and I will try to see if I can find any significant differences.

[sercon]

NotWorkingExcel2016.xlsx

WorkingExcel2016.xlsx

Hi,

I created 2 files.

• NotWorkingExcel2016.xlsx where the pivot is created with PivotTableBuilder.
• WorkingExcel2016.xlsx using the same spreadsheet opened in Excel 2016 where the pivot gets removed automatically. Then manually recreated the Pivot Table in Excel 2016 using the same datasource..

Pivotbuilder code:

Code used:
$builder1 = new \PhpOffice\PhpSpreadsheet\Worksheet\PivotTable\PivotTableBuilder($sheet, $rangeString);
$builder1->addRowField('Nr')->addRowField('WH')->addColumnField('YrWk')->addDataField('Quantity', \PhpOffice\PhpSpreadsheet\Worksheet\PivotTable\PivotField::SUBTOTAL_SUM);
$pivotTable1 = $builder1->build($PivotSheet1, 'A1', 'PivotQuantity');
//Settings Column Widths on Pivotsheet
foreach ($PivotSheet1->getColumnIterator() as $column) {
$PivotSheet1->getColumnDimension($column->getColumnIndex())->setAutoSize(true);
}
$PivotSheet1->setShowGridLines(false);

When unzipping both .xlsx files with 7 zip, i compared the files \xl\pivotTables\pivotTable1.xml

Biggest difference i see that in the working 2016 version all referenced values are also in the xml file. The file WorkingExcel2016.xlsx works in every Excel version i tested... 2016,2024 LTSC and 365.

[oleibman]

Thank you for the sample files. They clearly show a difference. Unfortunately, I don't know what to make of the differences, and it will take me some time before I'm ready to look at it.

For anyone else interested in looking into this, the "bad" file has something like the following in several places in pivotTable1.xml:

<pivotField axis="axisRow" showAll="0">
<items count="1">
<item t="default"/>
</items>
</pivotField>

The "good" file has something like the following in the same places:

<pivotField axis="axisRow" showAll="0">
<items count="6">
<item x="1"/>
<item x="0"/>
<item x="4"/>
<item x="3"/>
<item x="2"/>
<item t="default"/>

I'm not sure what the items are, but I think they correspond to the entries in the dropdown list for each column, in some kind of sorted order.

[oleibman]

@saifulferoz Might you be able to have a look at this?

[saifulferoz]

Ok, looking into this issue.

[oleibman]

@saifulferoz Very quick work! Thank you.

@sercon Can you verify that PR #4978 solves your problem?

[sercon]

Hi,

Thanks for the incredibly fast reaction and fix !!!!!!

Tested with PR #4978

Still generates an error:

error265840_01.xml

Errors were detected in file 'C:\Users\user\Documents\Test_20260831.xlsx'

Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view) Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)

Test_20260831_Repaired.xlsx
Test_20260831.xlsx

Test_20260831.xlsx is the newly created file with PR 4978 code. It indeed fills the items.
Test_20260831_Repaired.xlsx is the one where excel stripped the pivottable and i re-created exactly the same pivot on the same sheet With Excel 2016. Thought it might make it easier to compare.

If i check the differences in the \xl\pivotTables\pivotTable1.xml file.... it is already VERY close.
<location ref="A1:AK119" instead of <location ref="A1:A1"

Biggest difference seems to be in the missing rowItems tag?

[saifulferoz]

Thanks for the repaired file and the diff — both of your observations were correct, and they were the actual cause. I've pushed two more commits to #4978.

1. Missing rowItems / colItems

You were right that this was the biggest difference. We wrote rowFields and colFields without the item lists that accompany them. The definition now emits them in schema order:

<rowFields count="1"><field x="0"/></rowFields>
<rowItems count="1"><i t="grand"><x/></i></rowItems>
<colFields count="1"><field x="1"/></colFields>
<colItems count="1"><i t="grand"><x/></i></colItems>

Only the grand-total entry is written, since the real item rows are rebuilt on refresh — that is the smallest list that still describes the axis. Nothing is emitted for an axis with no fields.

2. <location ref="A1:A1">

Also correct. The code assumed a single-cell ref was a valid starting point that Excel would expand on load; your repaired file (A1:AK119) shows it isn't. The range is now derived from the fields placed on each axis, their distinct value counts, and the header and grand-total rows/columns. For a pivot with one row field and one column field of two values each, placed at A3, this now gives A3:D7 instead of A3:A3.

One thing worth recording, since it explains why this wasn't caught earlier: I validated both the old and new XML against the ISO-IEC 29500 schema with xmllint, and both validate. rowItems is minOccurs="0", so the missing element was never a schema violation — Excel is enforcing a consistency rule beyond the XSD. Schema validation is not a sufficient check for this class of bug, which is why your side-by-side against a real Excel-authored file was what actually found it.

Also in the branch: the numeric group bucket boundaries are now computed by index rather than by repeated addition. Grouping 0..1 by 0.1 previously drifted past the last boundary and emitted a spurious zero-width 1-1 bucket, leaving the pivot field and cache item counts disagreeing.

Tests cover the axis-item output and schema ordering, the omission case, and the location extent across four layouts; all verified to fail against the previous code. Everything runs on PHP 8.2 and 8.5.

I don't have Excel 2016 available, and as above, schema validation doesn't prove Excel will accept the file — so it would be very helpful if you could re-test with your data before this is called fixed. If it still gets stripped, could you attach the repaired pivotTable1.xml again? The location attributes (firstDataRow / firstDataCol) and dataFields are the parts I have no reference output for.

[oleibman]

@sercon Do your tests work with the latest changes to #4978?