Apache POI delete row and append row

Question

I am using apache-poi(3.16 and 4.0.0) to generate an excel file. I need to delete the specified line (remove row and move to the last row) and append the data after the last line. When I moved the line, I found that it did not work as expected.

These are my codes (rowNum < lastRowNum):

shiftRows1

sheet.shiftRows(rowNum, rowNum, sheet.getLastRowNum() - rowNum, true, false);

shiftRows2

sheet.shiftRows(rowNum + 1, sheet.getLastRowNum(), -1, true, false);

appendRow

setRowValue(sheet.getLastRowNum() + 1, objectArray);

My Question

shiftRows1 + appendRow + 4.0.0/3.16 + XSSF : clear rowNum and delete lastRowNum
shiftRows1 + appendRow + 4.0.0/3.16 + HSSF : clear rowNum and clear lastRowNum
shiftRows2 + appendRow + 4.0.0 + XSSF : damaged file; after repair, delete lastRowNum and clear rows >= rowNum
shiftRows2 + appendRow + 4.0.0/3.16 + HSSF : delete rowNum and can not append row(lastRowNum always equal to the lastRowNum before delete)
shiftRows2 + appendRow + 3.16 + XSSF : working

After test, the shiftRows1 cannot be used. Why HSSF not update lastRowNum after appendRow? how can i achive it use shiftRows2 and 3.16?

Sheet.shiftRows always had, actually has and always will have bugs because of its complexity. You found multiple of them in different apache poi versions. Some are fixed in newest apache poi 4.1.2. But not all, see bz.apache.org/bugzilla/…. So at first you always should using newest apache poi version. And you should have a look at the bug list when using Sheet.shiftRows. — Axel Richter
Yes, I will try your suggestions. In addition, I have dealt with this problem in other ways. — 西门吹雪
I tried apache poi 4.1.2, but it does not work with HSSF. — 西门吹雪

西门吹雪西门吹雪 · Accepted Answer · 2020-06-11T07:00:52

I dealt with this problem in the following way(apache-poi 3.16):

private void removeRow(int rowNum) {
    Row row = sheet.getRow(rowNum);
    if (row == null) return;
    sheet.removeRow(row);

    int lastRowNum = sheet.getLastRowNum();
    if (rowNum < lastRowNum) {
        // bad way: shift the removed row down
        sheet.shiftRows(rowNum + 1, lastRowNum, -1, true, false);
        // remove lastRow: avoid Sheet#getLastRowNum() return same value after Sheet#shiftRows
        if (workbook instanceof HSSFWorkbook) removeRow(lastRowNum);
    }
}

Apache POI delete row and append row

1 Answers