Bug 56482 - Excel 2010 format workbook saved as 2003 format with more than three Conditional Formatting Rules per record causes java.lang.IllegalArgumentException
Summary: Excel 2010 format workbook saved as 2003 format with more than three Conditio...
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.10-FINAL
Hardware: PC Linux
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2014-05-02 12:34 UTC by Pau Coma Ramirez
Modified: 2014-05-02 15:49 UTC (History)
0 users

Multi-Conditional-Formatting Rules saved as Old Excel Format (10.00 KB, application/octet-stream)
2014-05-02 12:34 UTC, Pau Coma Ramirez

Note You need to log in before you can comment on or make changes to this bug.
Description Pau Coma Ramirez 2014-05-02 12:34:04 UTC
Created attachment 31587 [details]
Multi-Conditional-Formatting Rules saved as Old Excel Format

It is possible to **create a file in Excel 2007, Excel 2010, LibreOffice Calc, etc... with more than 3 conditional formatting rules per cell** and then **Save it in the Excel 2003 format** , preserving those defined rules.

When POI detects the pre 2007 Excel format, it opens it with HSSFWorkbook and all the rest... and when it bumps into more than 3 rules defined it throws an exception.

If this same file is opened in LibreOffice for example and saved in 2007/2010 format and then opened again with the workbookFactory it creates an XSSFWorkbook and everything works ok.


** Example Files **

[Multi-Conditional-Formatting Rules saved as Old Excel Format XLS](http://www.filedropper.com/mcfrsaxls)
[Multi-Conditional-Formatting Rules saved as New Excel Format XLSX](http://www.filedropper.com/mcfrsaxlsx)


In [this blog post from excel support group](http://office.microsoft.com/en-us/excel-help/create-flexible-effective-conditional-formats-in-excel-2007-HA010217629.aspx) It states: 

  > In previous versions of Excel, you could have a maximum of three conditional formats. There's no such limit in Office Excel 2007; you may have as many conditional formats as you like.

  - Because **pre-2007/2010** Excel files were limited to 3 rules, the library object is also limited to 3. The [CFRecordsAggregate class](http://poi.apache.org/apidocs/org/apache/poi/hssf/record/aggregates/CFRecordsAggregate.html) states in its description:

  > CFRecordsAggregate - aggregates Conditional Formatting records CFHeaderRecord and number of **up to three CFRuleRecord records** together to simplify access to them.


Exception output:

java.lang.IllegalArgumentException: No more than 3 rules may be specified
	at org.apache.poi.hssf.record.aggregates.CFRecordsAggregate.<init>(CFRecordsAggregate.java:58)
	at org.apache.poi.hssf.record.aggregates.CFRecordsAggregate.createCFAggregate(CFRecordsAggregate.java:95)
	at org.apache.poi.hssf.record.aggregates.ConditionalFormattingTable.<init>(ConditionalFormattingTable.java:49)
	at org.apache.poi.hssf.model.InternalSheet.<init>(InternalSheet.java:143)
	at org.apache.poi.hssf.model.InternalSheet.createSheet(InternalSheet.java:121)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:311)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:264)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:199)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:342)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:323)
	at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:76)
Comment 1 Nick Burch 2014-05-02 15:49:43 UTC
Thanks for the test file and investigations. Changed from an error to a logged compatibility warning in r1591959.