Apache POI - 删除并向上移动Cell

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?

我正在使用Apache POi HSSF库导入Excel表并将其导出到我的应用程序（tableview）。我想在Excel中使用特定ID从Excel中删除一行。有用。我的问题是，在我删除一行后，有一个空行，它删除的行多于所选行。大家可以帮忙吗？

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

}

1 个解决方案

#1

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)

您可以使用DataFormatter类来格式化单元格值（这样您就可以比较任何单元格值）
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.

我 - 像你一样 - 比较细胞中的任何细胞;对我来说，典型的用例是搜索每一行的第一个col来查找id。如果您需要，请相应地调整代码。
I did the shift row in the end, which should work better.

我最后做了换挡行，这应该会更好。

#1