https://bz.apache.org/bugzilla/show_bug.cgi?id=64516
Bug ID: 64516
Summary: XSSFSheet.shiftRows has a bug when shifting rows
affect the order of the rows.
Product: POI
Version: 4.1.2-FINAL
Hardware: PC
OS: All
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
Assignee: dev@poi.apache.org
Reporter: axel.richter.pri...@web.de
Target Milestone: ---
Created attachment 37303
--> https://bz.apache.org/bugzilla/attachment.cgi?id=37303&action=edit
Sample Excel file
In current Apache POI 4.1.2 XSSFSheet.shiftRows has a bug when shifting rows
affect the order of the rows. For example if one shift one row down to the end
(after the last row). So before shifting the sheet data conain:
<sheetData>
<row r = "1"...
<row r = "2"...
<row r = "3"...
<row r = "4"...
<row r = "5"...
</sheetData>
Now we do:
XSSFSheet sheet...
...
sheet.shiftRows(2, 2, sheet.getLastRowNum() - rowNum + 1);
After that we get:
<sheetData>
<row r = "1"...
<row r = "2"...
<row r = "6"...
<row r = "4"...
<row r = "5"...
</sheetData>
As you see, the formerly row r = "3" becaomes r = "6". But the order of sheet
data row list is not changed. That leads to a corrupt file in Excel since row
numbering and row order does not match. LibreOffice Calc is able handling that
properly.
That is because XSSFSheet.rebuildRows only rebuild the _rows map but does not
rebuild the underlying CTSheetData CTRow order.
Following complete example shows this. It also provides a rebuildRow method
which provides rebuilding the underlying CTSheetData CTRow order too.
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;
import java.io.*;
import java.lang.reflect.*;
import java.util.*;
class XSSFShiftRowsBug {
static void rebuildRows(XSSFSheet sheet) throws Exception {
//rebuild the CTSheetData CTRow order
SortedMap<Long, CTRow> ctRows = new TreeMap<Long, CTRow>();
CTSheetData sheetData = sheet.getCTWorksheet().getSheetData();
for (CTRow ctRow : sheetData.getRowList()) {
Long rownumL = ctRow.getR();
ctRows.put(rownumL, ctRow);
}
List<CTRow> ctRowList = new ArrayList<CTRow>(ctRows.values());
CTRow[] ctRowArray = new CTRow[ctRowList.size()];
ctRowArray = ctRowList.toArray(ctRowArray);
sheetData.setRowArray(ctRowArray);
//rebuild the _rows map
Field rows = XSSFSheet.class.getDeclaredField("_rows");
rows.setAccessible(true);
@SuppressWarnings("unchecked")
SortedMap<Integer, XSSFRow> _rows = (SortedMap<Integer,
XSSFRow>)rows.get(sheet);
_rows.clear();
Constructor rowConstructor =
XSSFRow.class.getDeclaredConstructor(CTRow.class, XSSFSheet.class);
rowConstructor.setAccessible(true);
for (CTRow ctRow : sheetData.getRowList()) {
XSSFRow row = (XSSFRow)rowConstructor.newInstance(ctRow, sheet);
Integer rownumI = Math.toIntExact(row.getRowNum());
_rows.put(rownumI, row);
}
}
static void shiftRowToEnd(Sheet sheet, int rowNum) throws Exception {
if (rowNum >= sheet.getLastRowNum()) return;
sheet.shiftRows(rowNum, rowNum, sheet.getLastRowNum() - rowNum + 1, true,
false);
//if (sheet instanceof XSSFSheet) rebuildRows((XSSFSheet)sheet);
}
public static void main(String[] args) throws Exception {
InputStream inp = new FileInputStream("Test.xlsx"); String filePath =
"Test_1.xlsx";
Workbook workbook = WorkbookFactory.create(inp);
Sheet sheet = workbook.getSheetAt(0);
shiftRowToEnd(sheet, 2);
FileOutputStream out = new FileOutputStream(filePath);
workbook.write(out);
out.close();
workbook.close();
}
}
--
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
