Bug 46536

Summary: XSSFSheet.shiftRows(...) is not working
Product: POI Reporter: Matthew <matthew.knl>
Component: XSSFAssignee: POI Developers List <dev>
Severity: critical    
Priority: P2    
Version: 3.5-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: the source excel file.
the output excel file.
error prompt by ms excel 2007
error fixed by ms excel 2007
xlsx file for test
excel error log
the xlsx file for test

Description Matthew 2009-01-14 23:34:20 UTC
I use following codes to shift rows, the result output file cannot be opened by MS Excel 2007. (In fact, it can be opened but which need to be recovered by MS Excel 2007.) And the result content is not as expected.


Workbook wb = new XSSFWorkbook("original2.xlsm");
Sheet sheet = wb.getSheet("Test");
sheet.shiftRows(1, sheet.getLastRowNum(), 3, false, true);
FileOutputStream output = new FileOutputStream("output2.xlsm");


Comment 1 Matthew 2009-01-14 23:34:43 UTC
Created attachment 23124 [details]
the source excel file.
Comment 2 Matthew 2009-01-14 23:35:15 UTC
Created attachment 23125 [details]
the output excel file.
Comment 3 Matthew 2009-01-14 23:35:35 UTC
Created attachment 23126 [details]
error prompt by ms excel 2007
Comment 4 Matthew 2009-01-14 23:35:50 UTC
Created attachment 23127 [details]
error fixed by ms excel 2007
Comment 5 Matthew 2009-02-05 00:01:02 UTC
I have to delay my company project because it rely heavily on this function. I raise the severity to critical and hope POI developers can get this bug resolved. Many thanks!
Comment 6 David Fisher 2009-02-05 09:04:40 UTC
Hi -

Same comment as for bug 46535.

I am not saying that this is a duplicate, just that I'd like you to try xlsx and provide the repair log.

Comment 7 Matthew 2009-02-05 21:56:26 UTC
Thanks for your reply!
Now I use a xlsx file for testing, it produces the same error, please check the attachment file.
Comment 8 Matthew 2009-02-05 21:58:14 UTC
Created attachment 23233 [details]
xlsx file for test
Comment 9 Matthew 2009-02-05 22:00:27 UTC
Created attachment 23234 [details]
excel error log
Comment 10 Matthew 2009-02-05 22:07:09 UTC
Created attachment 23235 [details]
the xlsx file for test
Comment 11 Yegor Kozlov 2009-02-08 08:40:02 UTC
Fixed in r742126

There were several reasons why the resulting file was corrupted.
When shifting cells with formulas, we need to check the following:

1. Update formulas on the sheet to point to the new location. 
2. Remove references to the shifted cells from the calculation chain