https://bz.apache.org/bugzilla/show_bug.cgi?id=59581
Bug ID: 59581
Summary: Sheet.shiftRows() causes Error on opening sheet in
Excel
Product: POI
Version: 3.14-FINAL
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
Assignee: dev@poi.apache.org
Reporter: jmarkmu...@yahoo.com
Created attachment 33850
--> https://bz.apache.org/bugzilla/attachment.cgi?id=33850&action=edit
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org
For additional commands, e-mail: dev-h...@poi.apache.org
