Bug 63152

Summary: Too many identical CellStyles cause .xls to be incorrectly rendered in Office 365
Product: POI Reporter: Anthony V <anthonyv.be>
Component: HSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: normal CC: blackbear.coleman
Priority: P2    
Version: 4.0.0-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: A test case to reproduce the issue
Another test case with style changes and SXSSF

Description Anthony V 2019-02-07 10:01:48 UTC
Created attachment 36429 [details]
A test case to reproduce the issue

Please find a reproducer in attachment.
This is with Java 11 and POI 4.0.1.

When I create too many identical CellStyle instances, Office 365 only applies the first X of them. For example, the reproducer creates the same CellStyle for 44 Cells. However, in Office 365, only the first 43 have the CellStyle applied. Note that LibreOffice renders this correctly. I don't have older versions of Office available, to see how they render the file.

In the reproducer there are 2 workarounds/hacks I found:
1) apply HSSFOptimiser.optimiseCellStyles(wb); before saving the Workbook
2) make each CellStyle different by creating a new Font for each: style.setFont(wb.createFont());

What I would like, is that POI throws an Exception at some point (I propose some kind of IOException upon Workbook.write), when there are too many identical CellStyles. The exception message could then explain that Office 365 won't properly render the file, and that it's best to properly manage CellStyles by creating "logical CellStyles", but that a quick workaround is to invoke HSSFOptimiser.optimiseCellStyles just before saving the file.
Comment 1 blackbear.coleman 2019-09-06 15:10:01 UTC
Created attachment 36766 [details]
Another test case with style changes and SXSSF

An example which shows that even if the style changes, they are not rendered properly for HSSFWorksheet and confirmation that they are rendered properly for SXSSFWorksheet.