POI读取Excel时对cell中数据的判断

最新推荐文章于 2021-06-06 09:58:02 发布

原创最新推荐文章于 2021-06-06 09:58:02 发布 · 1.8k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#POI #cell #java

Java 专栏收录该内容

15 篇文章

订阅专栏

本文介绍了如何使用Apache POI库在Java中迭代Excel单元格，并控制对缺失或空白单元格的处理。强调了在需要确保遍历每个单元格时，应获取行的首尾列信息并使用MissingCellPolicy。同时，详细阐述了获取单元格内容的方法，需根据单元格类型调用相应的getter方法以避免异常。

[size=large]Iterate over cells, with control of missing / blank cells[/size]

In some cases, when iterating, you need full control over how missing or blank rows and cells are treated, and you need to ensure you visit every cell and not just those defined in the file. (The CellIterator will only return the cells defined in the file, which is largely those with values or stylings, but it depends on Excel).

In cases such as these, you should fetch the first and last column information for a row, then call getCell(int, MissingCellPolicy) to fetch the cell. Use a MissingCellPolicy to control how blank or null cells are handled.

    
// Decide which rows to process
    int rowStart = Math.min(15, sheet.getFirstRowNum());
    int rowEnd = Math.max(1400, sheet.getLastRowNum());

    for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
       Row r = sheet.getRow(rowNum);
       if (r == null) {
          // This whole row is empty
          // Handle it as needed
          continue;
       }

       int lastColumn = Math.max(r.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT);

       for (int cn = 0; cn < lastColumn; cn++) {
          Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
          if (c == null) {
             // The spreadsheet is empty in this cell
          } else {
             // Do something useful with the cell's contents
          }
       }
    }

[size=large]Getting the cell contents[/size]

To get the contents of a cell, you first need to know what kind of cell it is (asking a string cell for its numeric contents will get you a NumberFormatException for example). So, you will want to switch on the cell's type, and then call the appropriate getter for that cell.

In the code below, we loop over every cell in one sheet, print out the cell's reference (eg A3), and then the cell's contents.


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

    Sheet sheet1 = wb.getSheetAt(0);
    for (Row row : sheet1) {
        for (Cell cell : row) {
            CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
            System.out.print(cellRef.formatAsString());
            System.out.print(" - ");

            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    System.out.println(cell.getRichStringCellValue().getString());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        System.out.println(cell.getDateCellValue());
                    } else {
                        System.out.println(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.println(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    System.out.println(cell.getCellFormula());
                    break;
                default:
                    System.out.println();
            }
        }
    }