This is an automated email from the ASF dual-hosted git repository.
pjfanning pushed a commit to branch trunk
in repository https://gitbox.apache.org/repos/asf/poi.git
The following commit(s) were added to refs/heads/trunk by this push:
new 727b6cfc53 XSSF: remove the unused pivot cache when removing a sheet
with a pivot table (#1563)
727b6cfc53 is described below
commit 727b6cfc5327f23ffe864c5a476fc5b78f93e5e4
Author: PJ Fanning <[email protected]>
AuthorDate: Wed Nov 11 00:02:17 2026 +0100
XSSF: remove the unused pivot cache when removing a sheet with a pivot
table (#1563)
removeSheetAt left the workbook's pivotCache entry and relationship in
place,
so the pivotCacheDefinition and pivotCacheRecords parts, which hold a full
copy
of the pivot's source data, stayed in the written file. The removed sheet's
pivot tables now leave the workbook's pivot table list, and a cache that no
remaining pivot table uses is removed together with its parts.
As the list can now shrink, createPivotTable picks free part numbers and the
next unused cacheId instead of deriving them from the list size.
Point 2 of #1554.
Co-authored-by: Claude Opus 5.5 <[email protected]>
---
.../org/apache/poi/xssf/usermodel/XSSFSheet.java | 8 +--
.../apache/poi/xssf/usermodel/XSSFWorkbook.java | 58 ++++++++++++++++++--
.../poi/xssf/usermodel/TestXSSFWorkbook.java | 61 ++++++++++++++++++++++
3 files changed, 120 insertions(+), 7 deletions(-)
diff --git
a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
index b443d97331..1832620c60 100644
--- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
+++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
@@ -4752,14 +4752,15 @@ public class XSSFSheet extends POIXMLDocumentPart
implements Sheet, OoxmlSheetEx
int tableId = getWorkbook().getPivotTables().size()+1;
//Create relationship between pivotTable and the worksheet
XSSFPivotTable pivotTable = (XSSFPivotTable)
createRelationship(XSSFRelation.PIVOT_TABLE,
- getWorkbook().getXssfFactory(), tableId);
+ getWorkbook().getXssfFactory(),
getNextPartNumber(XSSFRelation.PIVOT_TABLE, tableId));
pivotTable.setParentSheet(this);
wb.addPivotTable(pivotTable);
XSSFWorkbook workbook = getWorkbook();
//Create relationship between the pivot cache definition and the
workbook
XSSFPivotCacheDefinition pivotCacheDefinition =
(XSSFPivotCacheDefinition) workbook.
- createRelationship(XSSFRelation.PIVOT_CACHE_DEFINITION,
getWorkbook().getXssfFactory(), tableId);
+ createRelationship(XSSFRelation.PIVOT_CACHE_DEFINITION,
getWorkbook().getXssfFactory(),
+ getNextPartNumber(XSSFRelation.PIVOT_CACHE_DEFINITION,
tableId));
String rId = workbook.getRelationId(pivotCacheDefinition);
//Create relationship between pivotTable and pivotCacheDefinition
without creating a new instance
PackagePart pivotPackagePart = pivotTable.getPackagePart();
@@ -4773,7 +4774,8 @@ public class XSSFSheet extends POIXMLDocumentPart
implements Sheet, OoxmlSheetEx
//Create relationship between pivotcacherecord and pivotcachedefinition
XSSFPivotCacheRecords pivotCacheRecords = (XSSFPivotCacheRecords)
pivotCacheDefinition.
- createRelationship(XSSFRelation.PIVOT_CACHE_RECORDS,
getWorkbook().getXssfFactory(), tableId);
+ createRelationship(XSSFRelation.PIVOT_CACHE_RECORDS,
getWorkbook().getXssfFactory(),
+ getNextPartNumber(XSSFRelation.PIVOT_CACHE_RECORDS,
tableId));
//Set relationships id for pivotCacheDefinition to pivotCacheRecords
pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().setId(pivotCacheDefinition.getRelationId(pivotCacheRecords));
diff --git
a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
index 0bfb949552..bb28c39502 100644
--- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
+++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
@@ -30,12 +30,14 @@ import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
+import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.NoSuchElementException;
+import java.util.Set;
import java.util.Spliterator;
import java.util.regex.Pattern;
@@ -1445,12 +1447,20 @@ public class XSSFWorkbook extends POIXMLDocument
implements Workbook, Date1904Su
public void removeSheetAt(int index) {
validateSheetIndex(index);
+ XSSFSheet sheet = getSheetAt(index);
+ List<XSSFPivotTable> removedPivotTables = sheet.getPivotTables();
+
onSheetDelete(index);
- XSSFSheet sheet = getSheetAt(index);
removeRelation(sheet);
sheets.remove(index);
+ // removing the sheet also removes the pivot caches only its pivot
tables used
+ if (!removedPivotTables.isEmpty()) {
+ pivotTables.removeAll(removedPivotTables);
+ removeUnusedPivotCaches(removedPivotTables);
+ }
+
// removing the sheet also removes the pictures only it used
if (pictures != null) {
pictures.removeIf(p ->
!getPackage().containPart(p.getPackagePart().getPartName()));
@@ -1478,6 +1488,43 @@ public class XSSFWorkbook extends POIXMLDocument
implements Workbook, Date1904Su
}
}
+ /**
+ * Remove the pivot caches of the given (removed) pivot tables which no
remaining pivot table uses:
+ * the {@code pivotCache} entry in the workbook, the workbook relationship
and with it the
+ * pivot cache definition and records parts, which hold a copy of the
pivot's source data.
+ */
+ private void removeUnusedPivotCaches(List<XSSFPivotTable>
removedPivotTables) {
+ if (!workbook.isSetPivotCaches()) {
+ return;
+ }
+ Set<Long> usedCacheIds = new HashSet<>();
+ for (XSSFPivotTable pivotTable : pivotTables) {
+
usedCacheIds.add(pivotTable.getCTPivotTableDefinition().getCacheId());
+ }
+ Set<Long> unusedCacheIds = new HashSet<>();
+ for (XSSFPivotTable pivotTable : removedPivotTables) {
+ long cacheId = pivotTable.getCTPivotTableDefinition().getCacheId();
+ if (!usedCacheIds.contains(cacheId)) {
+ unusedCacheIds.add(cacheId);
+ }
+ }
+ CTPivotCaches caches = workbook.getPivotCaches();
+ for (int i = caches.sizeOfPivotCacheArray() - 1; i >= 0; i--) {
+ CTPivotCache cache = caches.getPivotCacheArray(i);
+ if (!unusedCacheIds.contains(cache.getCacheId())) {
+ continue;
+ }
+ String rId = cache.getId();
+ caches.removePivotCache(i);
+ if (rId != null && getRelationById(rId) != null) {
+ removeRelation(rId);
+ }
+ }
+ if (caches.sizeOfPivotCacheArray() == 0) {
+ workbook.unsetPivotCaches();
+ }
+ }
+
/**
* Gracefully remove references to the sheet being deleted
*
@@ -2463,10 +2510,13 @@ public class XSSFWorkbook extends POIXMLDocument
implements Workbook, Date1904Su
} else {
caches = ctWorkbook.addNewPivotCaches();
}
+ // the cache ids of removed or loaded pivot tables need not be
contiguous
+ long cacheId = pivotTables.size() + 1;
+ for (CTPivotCache existing : caches.getPivotCacheArray()) {
+ cacheId = Math.max(cacheId, existing.getCacheId() + 1);
+ }
CTPivotCache cache = caches.addNewPivotCache();
-
- final int tableId = pivotTables.size() + 1;
- cache.setCacheId(tableId);
+ cache.setCacheId(cacheId);
cache.setId(rId);
return cache;
}
diff --git
a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
index 4330228184..fe69b40fe3 100644
---
a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
+++
b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
@@ -43,6 +43,7 @@ import
org.apache.poi.openxml4j.opc.internal.InvalidZipException;
import org.apache.poi.openxml4j.opc.internal.MemoryPackagePart;
import org.apache.poi.openxml4j.opc.internal.PackagePropertiesPart;
import org.apache.poi.openxml4j.util.ZipInputStreamZipEntrySource;
+import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.tests.usermodel.BaseTestXWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellReferenceType;
@@ -1285,6 +1286,66 @@ public final class TestXSSFWorkbook extends
BaseTestXWorkbook {
}
}
+ @Test
+ void testRemoveSheetRemovesPivotCache() throws IOException {
+ try (XSSFWorkbook wb =
openSampleWorkbook("ExcelPivotTableSample.xlsx")) {
+ // Tabelle2 has the pivot table with cache 0, Tabelle3 the one
with cache 2
+ wb.removeSheetAt(wb.getSheetIndex("Tabelle2"));
+ assertEquals(1, wb.getPivotTables().size());
+ assertEquals(2,
wb.getPivotTables().get(0).getCTPivotTableDefinition().getCacheId());
+
+ try (XSSFWorkbook wb2 = writeOutAndReadBack(wb)) {
+ CTPivotCache[] caches =
wb2.getCTWorkbook().getPivotCaches().getPivotCacheArray();
+ assertEquals(1, caches.length);
+ assertEquals(2, caches[0].getCacheId());
+ assertEquals(0,
wb2.getPackage().getPartsByName(Pattern.compile("/xl/pivotTables/pivotTable1.xml")).size());
+ assertEquals(0,
wb2.getPackage().getPartsByName(Pattern.compile("/xl/pivotCache/pivotCache.*1.xml")).size());
+ assertEquals(2,
wb2.getPackage().getPartsByName(Pattern.compile("/xl/pivotCache/pivotCache.*2.xml")).size());
+ XSSFPivotTable pivotTable =
wb2.getSheet("Tabelle3").getPivotTables().get(0);
+ assertNotNull(pivotTable.getPivotCacheDefinition());
+
+ // removing the last pivot table removes the pivotCaches
element
+ wb2.removeSheetAt(wb2.getSheetIndex("Tabelle3"));
+ assertFalse(wb2.getCTWorkbook().isSetPivotCaches(),
"pivotCaches should be removed");
+ assertEquals(0,
wb2.getPackage().getPartsByName(Pattern.compile("/xl/pivot\\w+/pivot\\w+\\.xml")).size());
+ }
+ }
+ }
+
+ @Test
+ void testRemoveSheetRemovesCreatedPivotCache() throws IOException {
+ try (XSSFWorkbook wb = new XSSFWorkbook()) {
+ XSSFSheet data = wb.createSheet("Data");
+ for (int r = 0; r < 3; r++) {
+ XSSFRow row = data.createRow(r);
+ row.createCell(0).setCellValue(r == 0 ? "Name" : "secret" + r);
+ row.createCell(1).setCellValue(r == 0 ? "Value" :
String.valueOf(r));
+ }
+ AreaReference source = new AreaReference("Data!A1:B3",
SpreadsheetVersion.EXCEL2007);
+ wb.createSheet("Pivot1").createPivotTable(source, new
CellReference("A1"));
+ wb.createSheet("Pivot2").createPivotTable(source, new
CellReference("A1"));
+
+ wb.removeSheetAt(wb.getSheetIndex("Pivot1"));
+ assertEquals(1, wb.getPivotTables().size());
+ assertEquals(1,
wb.getCTWorkbook().getPivotCaches().sizeOfPivotCacheArray());
+ assertEquals(3,
wb.getCTWorkbook().getPivotCaches().getPivotCacheArray(0).getCacheId());
+ assertEquals(0,
wb.getPackage().getPartsByName(Pattern.compile("/xl/pivot\\w+/pivot\\w+1\\.xml")).size());
+
+ // a new pivot table gets ids and part names which don't clash
with the remaining one
+ XSSFPivotTable created =
wb.createSheet("Pivot3").createPivotTable(source, new CellReference("A1"));
+ assertEquals(4, created.getCTPivotTableDefinition().getCacheId());
+
+ try (XSSFWorkbook wb2 = writeOutAndReadBack(wb)) {
+ assertEquals(2, wb2.getPivotTables().size());
+ assertEquals(2,
wb2.getCTWorkbook().getPivotCaches().sizeOfPivotCacheArray());
+ assertEquals(6,
wb2.getPackage().getPartsByName(Pattern.compile("/xl/pivot\\w+/pivot\\w+\\.xml")).size());
+ for (XSSFPivotTable pivotTable : wb2.getPivotTables()) {
+ assertNotNull(pivotTable.getPivotCacheDefinition());
+ }
+ }
+ }
+ }
+
/**
* See bug #61700
*/
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]