java - 如何使用 POI 库从 Excel 中读取筛选后的行

Question

我正在我的java代码中使用POI库读取excel文件。到目前为止还好。但现在我有一个要求。 Excel 文件包含许多记录(例如 1000 行)。它还具有列标题(第一行)。现在我正在对其进行excel过滤。假设我有一个“年份”列，并且正在过滤year=2019 的所有行。我得到 15 行。问题:我只想在 java 代码中处理这 15 行。 poi 库中是否有任何方法或方法可以知道正在读取的行是否被过滤或(另一种方式，即未过滤)。谢谢。

我已经有了工作代码，但现在我正在寻找如何仅读取过滤行。除了在图书馆和论坛中搜索之外，还没有尝试过任何新的尝试。

以下代码位于方法内。我不习惯使用 stackoverflow 进行格式化，因此请忽略任何格式化问题。

    // For storing data into CSV files
    StringBuffer data = new StringBuffer();
    try {
        SimpleDateFormat dtFormat = new SimpleDateFormat(CommonConstants.YYYY_MM_DD); // "yyyy-MM-dd"
        String doubleQuotes = "\"";
        FileOutputStream fos = new FileOutputStream(outputFile);
        // Get the workbook object for XLSX file
        XSSFWorkbook wBook = new XSSFWorkbook(new FileInputStream(inputFile));
        wBook.setMissingCellPolicy(Row.RETURN_BLANK_AS_NULL);

        // Get first sheet from the workbook
        //XSSFSheet sheet = wBook.getSheetAt(0);
        XSSFSheet sheet = wBook.getSheet(CommonConstants.METADATA_WORKSHEET);
        //Row row;
        //Cell cell;
        // Iterate through each rows from first sheet
        int rows = sheet.getLastRowNum();
        int totalRows = 0;
        int colTitelNumber = 0;
        Row firstRowRecord = sheet.getRow(1);
        for (int cn = 0; cn < firstRowRecord.getLastCellNum(); cn++) {
            Cell cellObj = firstRowRecord.getCell(cn);
            if(cellObj != null) {
                String str = cellObj.toString();
                if(CommonConstants.COLUMN_TITEL.equalsIgnoreCase(str)) {
                    colTitelNumber = cn;
                    break;
                }
            }
        }
        // Start with row Number 1. We don't need 0th number row as it is for Humans to read but not required for processing.
        for (int rowNumber = 1; rowNumber <= rows; rowNumber++) {
            StringBuffer rowData = new StringBuffer();
            boolean skipRow = false;
            Row rowRecord = sheet.getRow(rowNumber);
            if (rowRecord == null) {
                LOG.error("Empty/Null record found");
            } else {
                for (int cn = 0; cn < rowRecord.getLastCellNum(); cn++) {
                    Cell cellObj = rowRecord.getCell(cn);
                    if(cellObj == null) {
                        if(cn == colTitelNumber) {
                            skipRow = true;
                            break; // The first column cell value is empty/null. Which means Titel column cell doesn't have value so don't add this row in csv.
                        }
                        rowData.append(CommonConstants.CSV_SEPARTOR);
                        continue;
                    }
                    switch (cellObj.getCellType()) {
                        case Cell.CELL_TYPE_BOOLEAN:
                            rowData.append(cellObj.getBooleanCellValue() + CommonConstants.CSV_SEPARTOR);
                            //LOG.error("Boolean:" + cellObj.getBooleanCellValue());
                            break;

                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(cellObj)) {
                                Date date = cellObj.getDateCellValue();
                                rowData.append(dtFormat.format(date).toString() + CommonConstants.CSV_SEPARTOR);
                                //LOG.error("Date:" + cellObj.getDateCellValue());
                            } else {
                                rowData.append(cellObj.getNumericCellValue() + CommonConstants.CSV_SEPARTOR);
                                //LOG.error("Numeric:" + cellObj.getNumericCellValue());
                            }
                            break;

                        case Cell.CELL_TYPE_STRING:
                            String cellValue = cellObj.getStringCellValue();
                            // If string contains double quotes then replace it with pair of double quotes.
                            cellValue = cellValue.replaceAll(doubleQuotes, doubleQuotes + doubleQuotes);
                            // If string contains comma then surround the string with double quotes.
                            rowData.append(doubleQuotes + cellValue + doubleQuotes + CommonConstants.CSV_SEPARTOR);
                            //LOG.error("String:" + cellObj.getStringCellValue());
                            break;

                        case Cell.CELL_TYPE_BLANK:
                            rowData.append("" + CommonConstants.CSV_SEPARTOR);
                            //LOG.error("Blank:" + cellObj.toString());
                            break;

                        default:
                            rowData.append(cellObj + CommonConstants.CSV_SEPARTOR);
                    }
                }
                if(!skipRow) {
                    rowData.append("\r\n");
                    data.append(rowData); // Appending one entire row to main data string buffer.
                    totalRows++;
                }
            }
        }
        pTransferObj.put(CommonConstants.TOTAL_ROWS, (totalRows));
        fos.write(data.toString().getBytes());
        fos.close();
        wBook.close();
    } catch (Exception ex) {
        LOG.error("Exception Caught while generating CSV file", ex);
    }

Answer 1

工作表中不可见的所有行的高度均为零。因此，如果只需要读取可见行，可以通过 Row.getZeroHeight 检查.

示例

工作表:

代码:

import java.io.FileInputStream;

import org.apache.poi.ss.usermodel.*;

class ReadExcelOnlyVisibleRows {

 public static void main(String[] args) throws Exception {

  Workbook workbook  = WorkbookFactory.create(new FileInputStream("SAMPLE.xlsx"));

  DataFormatter dataFormatter = new DataFormatter();

  CreationHelper creationHelper = workbook.getCreationHelper();

  FormulaEvaluator formulaEvaluator = creationHelper.createFormulaEvaluator();

  Sheet sheet = workbook.getSheetAt(0);

  for (Row row : sheet) {
   if (!row.getZeroHeight()) { // if row.getZeroHeight() is true then this row is not visible
    for (Cell cell : row) {
     String cellContent = dataFormatter.formatCellValue(cell, formulaEvaluator);
     System.out.print(cellContent + "\t");
    }
    System.out.println();
   }
  }

  workbook.close();

 }
}

结果:

F1    F2    F3      F4  
V2    2     2-Mai   FALSE   
V4    4     4-Mai   FALSE   
V2    6     6-Mai   FALSE   
V4    8     8-Mai   FALSE