Bug 59581 - Sheet.shiftRows() causes Error on opening sheet in Excel
Summary: Sheet.shiftRows() causes Error on opening sheet in Excel
Status: RESOLVED DUPLICATE of bug 57423
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.14-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-05-19 15:29 UTC by Mark Murphy
Modified: 2016-05-19 19:40 UTC (History)
0 users



Attachments
The resulting spreadsheet as POI writes it (3.27 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-05-19 15:29 UTC, Mark Murphy
Details
The spreadsheet as Excel recovered it. (7.56 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-05-19 15:30 UTC, Mark Murphy
Details
The recovered spreadsheet with XML modified manually by me (7.56 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-05-19 15:31 UTC, Mark Murphy
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Mark Murphy 2016-05-19 15:29:09 UTC
Created attachment 33850 [details]
The resulting spreadsheet as POI writes it

The following code runs through to completion normally

        Workbook wb = new XSSFWorkbook();
        Sheet sh = wb.createSheet("Sheet1");
		
        Row row = sh.createRow(0);
	row.createCell(0).setCellValue(1.0);
	row.createCell(1).setCellValue(2.0);
	row = sh.createRow(1);
	row.createCell(0).setCellValue(1.0);
	row.createCell(1).setCellValue(2.0);
	row = sh.createRow(2);
	row.createCell(0).setCellFormula("SUM(A1,B1)");
	row.createCell(1).setCellFormula("SUM(A1,B1)");
	row = sh.createRow(3);
	row.createCell(0).setCellFormula("SUM(A3,B3)");
	row.createCell(1).setCellFormula("SUM(A3,B3)");
		
	sh.shiftRows(3, 3, -2);

	FileOutputStream fileOut = new FileOutputStream(
			"Shift_Row_Test.xlsx");
	wb.write(fileOut);
	wb.close();
	fileOut.close();

The XML output produced appears correct. Unfortunately when I try to open the resulting file in Excel, it produces an error:

Excel found unreadable content in 'Shift_Row_Test.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

Allowing recovery removes only the row that was shifted. The resulting spreadsheet contains Rows at indexes 0 and 2. Strangely enough, if I manually add back the data that was removed, Excel reads the spreadsheet correctly! I believe that the problem is that POI writes the rows out of order after the shift (1, 3, 2).

The attachments are:
Shift_Row_Test.xlsx  - The way POI wrote it. 
Shift_Row_Test_fix.xlsx - As Excel recovered it.
Shift_Row_Test_fix_mod.xlsx - As I modified the fix file to add back row 2.

The only one Excel won't read is Shift_Row_Test.xlsx. I played around with the files based on the idea that Excel is puking on the rows being out of order, and I can make Shift_Row_Test.xlsx work if I put the rows back in order but make no other changes. I can make Shift_Ros_Test_fix_mod.xlsx fail to read in Excel if I put the rows in the order that POI writes them after the shift.
Comment 1 Mark Murphy 2016-05-19 15:30:09 UTC
Created attachment 33851 [details]
The spreadsheet as Excel recovered it.
Comment 2 Mark Murphy 2016-05-19 15:31:33 UTC
Created attachment 33852 [details]
The recovered spreadsheet with XML modified manually by me
Comment 3 Mark Murphy 2016-05-19 19:40:02 UTC

*** This bug has been marked as a duplicate of bug 57423 ***