|Summary:||Cannot add data validation if XLS created in Microsoft Excel for Mac|
|Product:||POI||Reporter:||Andrew Novikov <mymuss>|
|Component:||HSSF||Assignee:||POI Developers List <dev>|
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