Summary: | Sheet.shiftRows() causes Error on opening sheet in Excel | ||
---|---|---|---|
Product: | POI | Reporter: | Mark Murphy <jmarkmurph> |
Component: | XSSF | Assignee: | POI Developers List <dev> |
Status: | RESOLVED DUPLICATE | ||
Severity: | normal | ||
Priority: | P2 | ||
Version: | 3.14-FINAL | ||
Target Milestone: | --- | ||
Hardware: | PC | ||
OS: | All | ||
Attachments: |
The resulting spreadsheet as POI writes it
The spreadsheet as Excel recovered it. The recovered spreadsheet with XML modified manually by me |
Created attachment 33851 [details]
The spreadsheet as Excel recovered it.
Created attachment 33852 [details]
The recovered spreadsheet with XML modified manually by me
|
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.