Bug 53972

Summary: Cannot add data validation if XLS created in Microsoft Excel for Mac
Product: POI Reporter: Andrew Novikov <mymuss>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal CC: mymuss
Priority: P2    
Version: 3.10-dev   
Target Milestone: ---   
Hardware: All   
OS: All   
Attachments: Fix for DV exception if PLV records exists in XLS
Sample spreadsheet that breaks unpatched version of POI for unit test.
Fix for DV exception when PLV record exists in XLS, includes unit tests

Description Andrew Novikov 2012-10-05 15:23:36 UTC
Created attachment 29450 [details]
Fix for DV exception if PLV records exists in XLS

At least in some cases Microsoft Excel for Mac adds a PLV record that is not recognized by POI (record type 0x8C8). If that happens the HSSFSheet.addValidationData() call throws the following exception:

java.lang.IllegalStateException: Unexpected (org.apache.poi.hssf.record.UnknownRecord) while looking for DV Table insert pos
        at org.apache.poi.hssf.model.RecordOrderer.findDataValidationTableInsertPos(RecordOrderer.java:311)
        at org.apache.poi.hssf.model.RecordOrderer.findSheetInsertPos(RecordOrderer.java:99)
        at org.apache.poi.hssf.model.RecordOrderer.addNewSheetRecord(RecordOrderer.java:93)
        at org.apache.poi.hssf.model.InternalSheet.getOrCreateDataValidityTable(InternalSheet.java:1629)
        at org.apache.poi.hssf.usermodel.HSSFSheet.addValidationData(HSSFSheet.java:371)

Reproducible in POI 3.8 as well as 3.9-beta1.

A patch that fixes the problem in 3.9-beta1 is attached.
Comment 1 Yegor Kozlov 2012-10-10 11:57:15 UTC
Can you attach a file that results in the exception ? The proposed fix looks good but I'd like to back it by a unit test.

Yegor
Comment 2 Yegor Kozlov 2012-10-26 13:50:24 UTC
changing status to NEEDINFO until a test file is provided
Comment 3 Andrew Novikov 2013-04-10 18:40:47 UTC
This issue still exists in 3.9 as well as 4.0-dev. I'm attaching a new patch that now includes a unit test as well as a sample Excel file that breaks unpatched builds of POI but passes on the 4.0-dev patched. The 3.9-dev patched has been deployed to our production systems about six months ago and we hadn't had of any issues since then.

I was unable to recreate the problem in a brand new spreadsheet so I'm attaching one of the actual spreadsheets used in our application with sensitive information redacted. The binary file is not in the diff, it's a separate attachement, please put into test-data/spreadsheet/ directory.
Comment 4 Andrew Novikov 2013-04-10 18:42:48 UTC
Created attachment 30177 [details]
Sample spreadsheet that breaks unpatched version of POI for unit test.

Please put into test-data/spreadsheet/ directory before running unit tests.
Comment 5 Andrew Novikov 2013-04-10 18:44:13 UTC
Created attachment 30178 [details]
Fix for DV exception when PLV record exists in XLS, includes unit tests
Comment 6 Nick Burch 2013-06-25 14:48:36 UTC
Thanks, patch applied in r1496499.