Apache POI - Delete and move up Cell

Question

I'm using the Apache POi HSSF library to import and export an Excel table to my application (tableview). I want to delete from Java a Row in Excel with a specific ID. It works. My Problem is, after i delete a row there are a empty row and it delete more than the selected row. Could everybody help?

FileInputStream inp = new FileInputStream(
            "...............";

    HSSFWorkbook wb = (HSSFWorkbook) WorkbookFactory.create(inp);
    HSSFSheet sheet = wb.getSheetAt(0);

    String selectedid = auftragTabelle.getSelectionModel().getSelectedItem().getId();

    int rowIndex = 0;
    int lastRowNum = sheet.getLastRowNum();

    for (Row row : sheet) {
        for (Cell cell : row) {
            if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                if (cell.getRichStringCellValue().getString().trim().equals(selectedid)) {
                    rowIndex = cell.getRowIndex();
                }
            }
        }
    }

    if (rowIndex >= 0 && rowIndex < lastRowNum) {
        sheet.shiftRows(rowIndex, lastRowNum, -1);
    }

    if (rowIndex <= lastRowNum) {
        HSSFRow removingRow = sheet.getRow(rowIndex);

        if (removingRow != null) {
            sheet.removeRow(removingRow);
        }
    }

    OutputStream out = new FileOutputStream(
            "............";

    wb.write(out);
    out.close();

}

JensS JensS · Accepted Answer · 2017-09-12T21:49:16

Here's an example of how I would do it:

public static void main(String[] args) throws InvalidFormatException, IOException {
    FileInputStream inp = new FileInputStream(FILENAME);

    HSSFWorkbook wb = (HSSFWorkbook) WorkbookFactory.create(inp);
    HSSFSheet sheet = wb.getSheetAt(0);

    String selectedid = "3";

    int rowIndex = getRowIndexOfId(sheet, selectedid);

    removeRow(sheet, rowIndex);

    OutputStream out = new FileOutputStream(FILENAME);

    wb.write(out);
    out.close();
}
private static int getRowIndexOfId(HSSFSheet sheet, String selectedid) {
    DataFormatter formatter = new DataFormatter();
    for (Row row : sheet) {
        for (Cell cell : row) {
            if (formatter.formatCellValue(cell).trim().equals(selectedid)) {
                return row.getRowNum();
            }
        }
    }
    return -1;
}
private static void removeRow(HSSFSheet sheet, int rowIndex) {
    if (rowIndex >= 0) {
        sheet.removeRow(sheet.getRow(rowIndex));
        if(rowIndex < sheet.getLastRowNum()) {
            sheet.shiftRows(rowIndex + 1, sheet.getLastRowNum(), -1);
        }
    }
}

A few comments:

You can use the class DataFormatter to format the cell values (so you can compare any cell value)
I - like you - compare any cell in a cell; the typical use case for me would be to search the first col of each row to find the id. SO if you want that, adjust the code accordingly.
I did the shift row in the end, which should work better.

Apache POI - Delete and move up Cell

1 Answers