[I] Feature Request: Add XSSFOptimiser (XSSF equivalent of HSSFOptimiser) [poi]

["cowwoc (via GitHub)" <gi...@apache.org>]

cowwoc opened a new issue, #951:
URL: https://github.com/apache/poi/issues/951

   # Feature Request: Add XSSFOptimiser (XSSF equivalent of HSSFOptimiser)
   
   ## Summary
   
   HSSFOptimiser provides methods to optimise cell styles and fonts in HSSF (xls) workbooks by removing duplicates and unused entries. There is no equivalent for XSSF (xlsx) workbooks. This feature request proposes adding XSSFOptimiser to provide similar optimization capabilities for XSSF workbooks.
   
   ## Problem Description
   
   When programmatically creating or modifying XSSF workbooks, it's easy to accidentally create "style explosion" - hundreds or thousands of duplicate cell styles and fonts. This happens when code creates new styles for each cell instead of reusing existing styles.
   
   For example, this common pattern causes style explosion:
   ```java
   for (int i = 0; i < 1000; i++) {
       CellStyle style = workbook.createCellStyle(); // Creates new style each time!
       Font font = workbook.createFont();
       font.setBold(true);
       style.setFont(font);
       cell.setCellStyle(style);
   }
   ```
   
   Style explosion causes:
   - Slow file opening in Excel
   - Excel hangs or crashes with large workbooks
   - Increased file size
   - The famous "Too many different cell formats" error
   
   HSSFOptimiser solves this for HSSF workbooks. No equivalent exists for XSSF.
   
   ## Proposed Solution
   
   Add `XSSFOptimiser` class in `org.apache.poi.xssf.usermodel` package with methods:
   
   1. `optimiseCellStyles(XSSFWorkbook)` - Remaps cells using duplicate styles to canonical (first) occurrence
   2. `optimiseFonts(XSSFWorkbook)` - Updates style font references to canonical fonts
   
   ### Implementation Notes
   
   Unlike HSSF, XSSF's StylesTable maintains a separate Java list from the underlying CTStylesheet XML. Removing entries from CTStylesheet can cause `XmlValueDisconnectedException` on save because the Java objects become disconnected from their XML backing.
   
   The proposed implementation takes a **safe remapping approach**:
   - Identifies duplicate styles/fonts by comparing all properties
   - Remaps cells to use canonical (first occurrence) styles
   - Does NOT remove entries from XML (avoids disconnection issues)
   - Prevents further style explosion
   
   This approach is safe and effective:
   - Workbooks save correctly without XML exceptions
   - Cells reference deduplicated styles
   - Further modifications won't create additional duplicates if using canonical styles
   
   ## Usage Example
   
   ```java
   XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("bloated.xlsx"));
   
   // Optimize fonts first, then styles
   XSSFOptimiser.optimiseFonts(workbook);
   XSSFOptimiser.optimiseCellStyles(workbook);
   
   workbook.write(new FileOutputStream("optimized.xlsx"));
   ```
   
   ## Test Coverage
   
   The implementation includes comprehensive tests:
   - `testOptimiseCellStylesRemapsDuplicates` - Verifies duplicate styles are consolidated
   - `testOptimiseCellStylesHandlesUnusedStyles` - Verifies no exception on unused styles
   - `testOptimiseCellStylesPreservesDifferentStyles` - Verifies distinct styles preserved
   - `testOptimiseFontsRemapsDuplicates` - Verifies duplicate fonts are detected
   - `testWorkbookSaveableAfterOptimization` - Verifies no save exceptions
   - `testOptimizationPreservesFormulas` - Verifies formulas and formatting preserved
   
   ## Contribution
   
   I have a working implementation ready to contribute as a PR. The code:
   - Follows POI coding conventions
   - Includes Apache License headers
   - Has full test coverage
   - Is modeled after HSSFOptimiser
   
   Would this contribution be welcome? Happy to submit a PR if so.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

Re: [I] Feature Request: Add XSSFOptimiser (XSSF equivalent of HSSFOptimiser) [poi]

["pjfanning (via GitHub)" <gi...@apache.org>]

pjfanning closed issue #951: Feature Request: Add XSSFOptimiser (XSSF equivalent of HSSFOptimiser)
URL: https://github.com/apache/poi/issues/951


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org