Bug 63152 - Too many identical CellStyles cause .xls to be incorrectly rendered in Office 365
Summary: Too many identical CellStyles cause .xls to be incorrectly rendered in Office...
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 4.0.0-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2019-02-07 10:01 UTC by Anthony V
Modified: 2019-09-06 15:10 UTC (History)
1 user (show)

A test case to reproduce the issue (1.70 KB, text/plain)
2019-02-07 10:01 UTC, Anthony V
Another test case with style changes and SXSSF (1.75 KB, text/plain)
2019-09-06 15:10 UTC, blackbear.coleman

Note You need to log in before you can comment on or make changes to this bug.
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.