Bug 46547

Summary: ClassCastException in HSSFSheet.shiftRows(...)
Product: POI Reporter: Matthew <matthew.knl>
Component: HSSFAssignee: POI Developers List <dev>
Severity: normal CC: bstox
Priority: P2    
Version: 3.5-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: excel file which causes the error.
same bugfix rebased to 3.2 final

Description Matthew 2009-01-16 00:30:23 UTC
I use following code to execute shiftRows(...) method in HSSFSheet instance, it throws ClassCastException.


FileInputStream fis = new FileInputStream("9000000001.xls");
POIFSFileSystem fs = new POIFSFileSystem(fis);
Workbook wb = new HSSFWorkbook(fs);
Sheet sheet = wb.getSheet("HouseShippingInstruction");
sheet.shiftRows(1, sheet.getLastRowNum(), 1, false, true);


java.lang.ClassCastException: org.apache.poi.hssf.record.aggregates.DataValidityTable cannot be cast to org.apache.poi.hssf.record.Record
	at org.apache.poi.hssf.model.RecordOrderer.findInsertPosForNewCondFormatTable(RecordOrderer.java:163)
	at org.apache.poi.hssf.model.RecordOrderer.findSheetInsertPos(RecordOrderer.java:102)
	at org.apache.poi.hssf.model.RecordOrderer.addNewSheetRecord(RecordOrderer.java:90)
	at org.apache.poi.hssf.model.Sheet.getConditionalFormattingTable(Sheet.java:493)
	at org.apache.poi.hssf.model.Sheet.updateFormulasAfterCellShift(Sheet.java:448)
	at org.apache.poi.hssf.usermodel.HSSFSheet.shiftRows(HSSFSheet.java:1238)
	at org.apache.poi.hssf.usermodel.HSSFSheet.shiftRows(HSSFSheet.java:1133)


Thanks a lot!
Comment 1 Matthew 2009-01-16 00:30:59 UTC
Created attachment 23131 [details]
excel file which causes the error.
Comment 2 Josh Micich 2009-01-19 17:57:18 UTC
There are two bugs here.  

The first (little) one is that the ConditionalFormattingTable is being created while moving sheet rows even though it will remain empty.  A simple fix would be to check the field before calling the lazy getter in Sheet.updateFormulasAfterCellShift().

The second (more serious) bug is that POI cannot add conditional formatting to the supplied spreadsheet.  There is probably something a little unusual in ordering of BIFF records in this spreadsheet that RecordOrderer.java was not expecting.
Comment 3 Josh Micich 2009-01-20 16:39:09 UTC
Both bugs fixed in svn r736175.

Testcases added.
Comment 4 Josh Micich 2009-02-19 19:56:46 UTC
*** Bug 46740 has been marked as a duplicate of this bug. ***
Comment 5 Lion Liang 2009-12-29 01:32:04 UTC
Hi Josh,

This bug is also issued with POI 3.2. Can you or someone please fix this bug in POI 3.2 as well, or please give me some instruction how to fix it in POI 3.2 by modifying the source code.

I have to use POI 3.2 since my project is working on JDK 1.4, but JDK 1.5 is the prerequisite to the POI 3.5.

Thanks for your time.
-Lion Liang
Comment 6 Josh Micich 2009-12-29 12:01:04 UTC
Created attachment 24770 [details]
same bugfix rebased to 3.2 final

You'll have to build your own patched version of POI from the sources.  Source code for POI 3.2 can be found here:

Assuming you unzipped the source to a folder called /poi-3.2-FINAL/, you can use patch like this:

patch -p1 -i patch46547-rebasedTo703644.txt -d poi-3.2-FINAL

Alternatively, you can do the patch by hand since the changes are very simple.
Comment 7 Josh Micich 2009-12-29 12:54:14 UTC
You should be able to use the same technique to take any POI bug-fix and apply it to your local version.  However, keep in mind that there have been *many* bugs fixed since version 3.2 so this may not be a practical approach to keeping you up and running.

Another approach to consider involves converting the latest POI release to run on a 1.4 JRE.  There are tools that can do this. For example Retroweaver is a one that has been successfully used on other Apache projects.
Comment 8 Lion Liang 2009-12-30 01:52:50 UTC
Hi Josh,

Thanks very much for your instructions.

shiftRows function works for me now in POI 3.2.

- Lion Liang
Comment 9 Josh Micich 2009-12-30 12:47:06 UTC
Changed to FIXED because this status applies to the original bug which was fixed during development of 3.5.  WORKSFORME actually means "The details provided by the bug poster are insufficient to produce the alleged bug."
Comment 10 Lion Liang 2010-01-17 21:27:41 UTC
Hi Josh,

Thanks for your thorough help on this fix. Just wondering if I could request an official build of POI 3.2 to include this change? 

I could have this fixed locally and rebuild a version of POI 3.2, but I thought there was possibly some other users experienced with this issue and they would like to request a new 3.2 build not just fixed locally.

Lion Liang

(In reply to comment #9)
> Changed to FIXED because this status applies to the original bug which was
> fixed during development of 3.5.  WORKSFORME actually means "The details
> provided by the bug poster are insufficient to produce the alleged bug."