java - 使用 Apache POI 对 XLSX 文件中的行进行排序

Question

我尝试使用Apache POI给定列对工作表进行排序。据我所知，没有任何用于此目的的内置解决方案。工作表的第一行包含合并的单元格和分组的列。我尝试按第二列对单元格进行排序。

public static void sortSheet(XSSFWorkbook workbook, Sheet sheet) {
    //copy all rows to temp
    List<Row> rows = Lists.newArrayList(sheet.rowIterator());
    //sort rows in the temp
    rows.sort(Comparator.comparing(cells -> cells.getCell(2).getStringCellValue()));
    //remove all rows from sheet
    removeAllRows(sheet);
    //create new rows with values of sorted rows from temp
    for (int i = 0; i < rows.size(); i++) {
        Row newRow = sheet.createRow(i);
        Row sourceRow = rows.get(i);
        // Loop through source columns to add to new row
        for (int j = 0; j < sourceRow.getLastCellNum(); j++) {
            // Grab a copy of the old/new cell
            Cell oldCell = sourceRow.getCell(j);
            Cell newCell = newRow.createCell(j);

            // If the old cell is null jump to next cell
            if (oldCell == null) {
                newCell = null;
                continue;
            }

            // Copy style from old cell and apply to new cell
            CellStyle newCellStyle = workbook.createCellStyle();
            newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
            newCell.setCellStyle(newCellStyle);

            // If there is a cell comment, copy
            if (oldCell.getCellComment() != null) {
                newCell.setCellComment(oldCell.getCellComment());
            }

            // If there is a cell hyperlink, copy
            if (oldCell.getHyperlink() != null) {
                newCell.setHyperlink(oldCell.getHyperlink());
            }

            // Set the cell data type
            newCell.setCellType(oldCell.getCellType());

            // Set the cell data value
            switch (oldCell.getCellType()) {
                case BLANK:
                    newCell.setCellValue(oldCell.getStringCellValue());
                    break;
                case BOOLEAN:
                    newCell.setCellValue(oldCell.getBooleanCellValue());
                    break;
                case ERROR:
                    newCell.setCellErrorValue(oldCell.getErrorCellValue());
                    break;
                case FORMULA:
                    newCell.setCellFormula(oldCell.getCellFormula());
                    break;
                case NUMERIC:
                    newCell.setCellValue(oldCell.getNumericCellValue());
                    break;
                case STRING:
                    newCell.setCellValue(oldCell.getRichStringCellValue());
                    break;
            }
        }

        // If there are are any merged regions in the source row, copy to new row
        for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
            CellRangeAddress cellRangeAddress = sheet.getMergedRegion(j);
            if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
                CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                        (newRow.getRowNum() +
                                (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()
                                )),
                        cellRangeAddress.getFirstColumn(),
                        cellRangeAddress.getLastColumn());
                sheet.addMergedRegion(newCellRangeAddress);
            }
        }
    }

}

private static void removeAllRows(Sheet sheet) {
    for (int i = 0; i < sheet.getLastRowNum(); i++) {
        sheet.removeRow(sheet.getRow(i));
    }
}

原始出处：Apache-POI 对 excel 中的行进行排序

我在执行过程中收到以下错误消息：

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.CTRowImpl.getR(Unknown Source)
    at org.apache.poi.xssf.usermodel.XSSFRow.getRowNum(XSSFRow.java:378)
    at WriteToExistingFile.sortSheet(WriteToExistingFile.java:234)
    at WriteToExistingFile.write(WriteToExistingFile.java:145)

在这一行抛出异常：

if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {

标签： javasortingapache-poixlsx