java - 如何使用 Java 中的 POI HSSF 库删除 Excel 中行数据之间的空行

Question

我需要帮助来删除创建的记录列表之间的空行。我已经编写了删除空行的代码，但是它只能删除第一个空行。这是我完成的代码:

private static void writeExcelFile(String[] keyValue, String fileName, String contents,
        ArrayList<String> listProperties, ArrayList<String> listPropertiesDescription,
        ArrayList<String> listPropertiesFileName) {

    int rownum = 2;
    HSSFSheet firstSheet;
    HSSFWorkbook workbook = null;

    workbook = new HSSFWorkbook();
    firstSheet = workbook.createSheet("Resourcebundle");
    Row headerRow = firstSheet.createRow((short) 1);
    headerRow.setHeightInPoints(30);

    headerRow.createCell((short) 0).setCellValue("Properties Name");
    headerRow.createCell((short) 1).setCellValue("Properties Description");
    headerRow.createCell((short) 2).setCellValue("Properties File Name");

    System.out.println("listPropertiesDescription ::  " + listPropertiesDescription.size());
    System.out.println("listPropertiesFileName ::  " + listPropertiesFileName.size());
    System.out.println("listProperties all list ::  " + listProperties.toString());
    System.out.println("listPropertiesDescription all list ::  "
            + listPropertiesDescription.toString());

    int indexProperties = 0;
    for (int i = rownum; i < listProperties.size(); i++) {

        //            Row row = firstSheet.getRow(i + 1);
        Row row = firstSheet.getRow(i);
        //            System.out.println("row ::  " + row);

        if (row == null) {
            //                row = firstSheet.createRow(i + 1);
            row = firstSheet.createRow(i);

        }

        System.out.println("check index :: " + indexProperties);

        for (int j = 0; j < 1; j++) {
            Cell cell = row.getCell(j);
            System.out.println("cell ::  " + cell);

            if (cell == null) {
                row.createCell(j).setCellValue(
                        listProperties.get(indexProperties + 1).toString().trim());
                row.createCell(j + 1).setCellValue(
                        listPropertiesDescription.get(indexProperties + 1).toString().trim());
                row.createCell(j + 2).setCellValue(
                        listPropertiesFileName.get(indexProperties + 1).toString().trim());
            }
            j++;
        }
        indexProperties++;

        System.out.println("check index below ::  " + indexProperties);

        i++;

    }

    int lastRowCount = firstSheet.getLastRowNum();

    for (int i = rownum; i < lastRowCount; i++) {
        HSSFRow row = firstSheet.getRow(i);

        if (row == null) {
            removeRow(firstSheet, i);
            //                firstSheet.shiftRows(i + 1, lastRowCount, -1);
            //                i--; // Since you move row at i+1 to i 
        }
    }

    FileOutputStream fos = null;
    try {
        File file = new File("OnlineHelp Master Excel.xls");

        //if file doesnt exists, then create it
        if (!file.exists()) {
            file.createNewFile();
        }

        String fileRB = outputLocation.concat("\\" + file);
        fos = new FileOutputStream(new File(fileRB));
        workbook.write(fos);
        fos.close();

    } catch (Exception e) {
        e.printStackTrace();
    }

}

public static void removeRow(HSSFSheet sheet, int rowIndex) {
    int lastRowNum = sheet.getLastRowNum();
    if (rowIndex >= 0 && rowIndex < lastRowNum) {
        sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
    }
    if (rowIndex == lastRowNum) {
        HSSFRow removingRow = sheet.getRow(rowIndex);
        if (removingRow != null) {
            sheet.removeRow(removingRow);
        }
    }
}

这里我还附上了上面代码的结果:

结果:

Row | Result
1   | result a
2   | (empty row)
3   | result b
4   | (empty row)
5   | result b
6   | (empty row)

它只能删除结果 a 下面的空行，其余的仍然存在。

在这方面确实需要帮助。谢谢!

艾玛

Answer 1

我知道这是一个很旧的线程，但今天早上遇到了这个问题。没想到 HSSF 库会出现空行。因此，发布此答案，以便遇到该问题的其他成员也能得到答案。

这是我的解决方案 - 基本上是复制/粘贴周围找到的部件。

        HSSFSheet sheet = new HSSFWorkbook(new ByteArrayInputStream(content)).getSheetAt(0);
        int headerRowIndex = sheet.getFirstRowNum();

        List<String> columnNames = getColumnNames(sheet);
        List<Map<String, String>> sheetData = new ArrayList<>();

        sheet.forEach(row -> {
            if (row.getRowNum() != headerRowIndex && !isRowEmpty(row)) {
                sheetData.add(getRowData(row, columnNames));
            }
        });

...以及方法:

        private boolean isRowEmpty(Row row) {
           for (int cellIndex = row.getFirstCellNum(); cellIndex < row.getLastCellNum(); cellIndex++) {
               Cell cell = row.getCell(cellIndex);
               if (cell != null && cell.getCellTypeEnum() != CellType.BLANK) {
                   return false;
               }
           }
           return true;
        }

我的测试文件之前有 6 个空行。他们现在都走了:)享受吧!