Apache POI - Delete Row from Application in Excel

Question

I using Apache POI libary to import, export an Excel table to application (tableView). I have some problems with delete rows from application in Excel. The remove of rows works fine, but if i remove a row and restart the application it imports the last entry twice. If i delete 4 or 5 entries, at restart the last entry will 4 or 5 times displaying. I think the last Row Number will not save or updated. The remove of the row in the excel file works well, only in my tableview, after i remove a row and import the file - the last entrie will shows twice. Start application lastRowNum()= 6; -> remove an entry (row) and reastart the import -> lastRowNum() = 6; Is it an Excel Bug or something like that? I would be so grateful for any help. Thanks a lot.

remove method:

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

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

    String selectedid = "4";
    int rowIndex = getRowIndexOfId(sheet, selectedid);

    removeRow(sheet, rowIndex);

    inp.close();

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

    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);
        }
    }

}

import (without InputStream and variables):

    Iterator<Row> rows = sheet.iterator();

    System.out.println(sheet.getLastRowNum());

    while (rows.hasNext()) {
        nextRow = rows.next();
        Iterator<Cell> cellIterator = nextRow.cellIterator();

        while (cellIterator.hasNext()) {
            cell = (HSSFCell) cellIterator.next();

            if (nextRow.getRowNum() == 0) {
                nextRow = rows.next();
                continue;
            }

            try {

                if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING
                        || cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {

                    auftragsnr = nextRow.getCell(0).getStringCellValue();
                    empfaenger = nextRow.getCell(1).getStringCellValue();
                    auftrag1 = nextRow.getCell(2).getStringCellValue();
                    beschreibung = nextRow.getCell(3).getStringCellValue();
                    zimmernr = nextRow.getCell(4).getNumericCellValue();
                    datum = nextRow.getCell(5).getDateCellValue();
                    absender = nextRow.getCell(6).getStringCellValue();
                    status = nextRow.getCell(7).getStringCellValue();

                    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd/MM/yyyy");

                    Instant instant = datum.toInstant();
                    ZonedDateTime zdt = instant.atZone(ZoneId.systemDefault());
                    date = zdt.toLocalDate();

                }

            } catch (NullPointerException e) {
                System.out.println("leer");
            }

        }

        if (nextRow.getRowNum() <= sheet.getLastRowNum()) {

            tableview.add(
                    new Auftrag(auftragsnr, empfaenger, auftrag1, beschreibung, zimmernr, date, absender, status));

            excelFile.close();

        }
    }

}

Dragan Jovanović Dragan Jovanović · Accepted Answer · 2017-09-13T09:37:32

I had same problem, and resolved it by adding line
sheet.createRow(sheet.getLastRowNum()+1); // adds empty row on last position
after call to
sheet.removeRow(removingRow);
I think you should not take getLastRowNum() method too seriously in this context, because idea with excel's sheets is that they should be considered as unlimited.

Apache POI - Delete Row from Application in Excel

1 Answers