Bug 53972 - Cannot add data validation if XLS created in Microsoft Excel for Mac
Summary: Cannot add data validation if XLS created in Microsoft Excel for Mac
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.10-dev
Hardware: All All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-10-05 15:23 UTC by Andrew Novikov
Modified: 2013-06-25 14:48 UTC (History)
1 user (show)



Attachments
Fix for DV exception if PLV records exists in XLS (1.13 KB, application/octet-stream)
2012-10-05 15:23 UTC, Andrew Novikov
Details
Sample spreadsheet that breaks unpatched version of POI for unit test. (45.00 KB, application/octet-stream)
2013-04-10 18:42 UTC, Andrew Novikov
Details
Fix for DV exception when PLV record exists in XLS, includes unit tests (4.79 KB, patch)
2013-04-10 18:44 UTC, Andrew Novikov
Details | Diff

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