java - 使用 POI 删除 excel 中的一行

Question

我试图省略 .xlsx 文件中存在具有特定值的特定单元格的行(我在代码中用 TODO 标记了它)。但是我得到 org.apache.xmlbeans.impl.values.XmlValueDisconnectedException 异常。我无法弄清楚它说的是什么以及问题出在哪里？

这是个异常(exception):

`Exception in thread "main" org.apache.xmlbeans.impl.values.XmlValueDisconnectedException
at org.apache.xmlbeans.impl.values.XmlObjectBase.check_orphaned(XmlObjectBase.java:1258)
at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTCellImpl.getF(Unknown Source)
at org.apache.poi.xssf.usermodel.XSSFCell.isFormulaCell(XSSFCell.java:543)
at org.apache.poi.xssf.usermodel.XSSFCell.getCellType(XSSFCell.java:562)
at org.apache.poi.xssf.usermodel.XSSFRow.getCell(XSSFRow.java:210)
at org.apache.poi.xssf.usermodel.XSSFRow.getCell(XSSFRow.java:37)
at migration.IndikatorResultNormalization.roundOff(IndikatorResultNormalization.java:143)
at migration.IndikatorResultNormalization.lambda$0(IndikatorResultNormalization.java:48)
at java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:184)
at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
at java.util.Iterator.forEachRemaining(Iterator.java:116)
at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801)
at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
at java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151)
at java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174)
at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
at java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:418)
at migration.IndikatorResultNormalization.readPaths(IndikatorResultNormalization.java:45)
at migration.IndikatorResultNormalization.main(IndikatorResultNormalization.java:34)

`

代码如下:

public void cleanSheet(final Path filePath) throws IOException {

    String newOutName = filePath.getFileName().toString();
    String sub = newOutName.substring(newOutName.indexOf("_"), newOutName.length() - 5);
    newOutName.replace("RESULT", "cmdg").replace(sub, "");
    newOutName.replace("RESULT", "cmdg");
    String outPathStr =
        filePath.resolveSibling("out")
            .resolve(filePath.getFileName())
            .toString()
            .replace("RESULT_", "")
            .replaceAll("_[^_]*$", "_cmdg.xlsx");
    try (XSSFWorkbook workbook = new XSSFWorkbook(filePath.toString()); FileOutputStream outFile = new FileOutputStream(outPathStr);) {
        CellStyle cellStyle = workbook.createCellStyle();
        DataFormat poiFormat = workbook.createDataFormat();
        String excelFormatPattern = "0.00000";
        cellStyle.setDataFormat(poiFormat.getFormat(excelFormatPattern));
        XSSFSheet spreadsheet = workbook.getSheetAt(0);
        int lastRowNum = spreadsheet.getLastRowNum();
        workbook.setSheetName(0, "Tabelle1");
        for (Row row : spreadsheet) {
            for (Cell cell : row) {
                cell = row.getCell(cell.getColumnIndex(), Row.RETURN_BLANK_AS_NULL);
                if (cell.getColumnIndex() == 2) {
                    if (cell.getCellType() == Cell.CELL_TYPE_STRING &&
                        cell.getStringCellValue().equalsIgnoreCase("Id")) {
                        cell.setCellValue("sort");
                    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        String textConcat =
                            String.format("%.0f", row.getCell(2).getNumericCellValue()) + " " + row.getCell(3).getStringCellValue();
                        cell.setCellValue(textConcat);
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        sortSheet(row, spreadsheet, 2, 1);
                    }
                } else if (cell.getColumnIndex() >= 6 && cell.getColumnIndex() < row.getLastCellNum()) {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        Double newValue = Double.valueOf((Math.round(cell.getNumericCellValue() * 100000d) / 100000d));
                        cell.setCellValue(newValue);
                        cell.setCellStyle(cellStyle);
                    }
                }
                // TODO remove 0 values und shift up
                else if (cell.getColumnIndex() == 5 && cell.getRowIndex() != 0) {
                    if (cell.getNumericCellValue() == 0.0) {
                        spreadsheet.shiftRows(cell.getRowIndex() + 1, lastRowNum, -1);
                        lastRowNum--;
                    }
                }
            } // end for Cell
        } // end for Row
        workbook.write(outFile);
    }
}

Answer 1

当您遍历行并同时删除/移动它们时，POI 可能会变得困惑。

请尽量记住第一遍中受影响的行，然后在遍历所有行后进行移位。