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]

Reply via email to