错误：如何在Excel中读取空单元格

I'm trying to read data from excel using POI. How can I check if that is an empty cell?

我正在尝试使用POI从excel读取数据。如何检查这是否为空单元格？

I don't know what is missing I think this should be working:

我不知道遗失了什么我认为这应该是有效的：

java.util.Iterator<Row> rows = worksheet.rowIterator();

HSSFRow row = (HSSFRow) rows.next();
HSSFCell cellF1 = (HSSFCell) row.getCell(5);
if(cellF1.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
  String val = "";
}

I got error in if statement (null pointer), but only if I use this I can check that:

我在if语句（空指针）中遇到错误，但只有在我使用它时我才能检查：

   while (rows.hasNext()) {
    HSSFRow row = (HSSFRow) rows.next();
    java.util.Iterator<Cell> cells = row.cellIterator();
    while (cells.hasNext()) {
      HSSFCell cell = (HSSFCell) cells.next();
      if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
        String emptytype = "";
        System.out.println("empty");
      }
    }
  }

5 个解决方案

#1

This is normal behavior for the 1-argument version of Row.getCell. If you look at the API doc, it specifically states that getCell will return null if the cell is not defined. Many java functions exhibit this sort of behavior, so there is nothing wrong with coding to take this into account. So, one version of your code could be something like:

这是Row.getCell的1参数版本的正常行为。如果查看API文档，它会明确声明如果未定义单元格，getCell将返回null。许多java函数都表现出这种行为，因此将编码考虑在内并没有任何问题。因此，您的代码的一个版本可能是这样的：

boolean hasDataFlag = true;    
HSSFRow row = sheet.getRow(rowNumber);
hasDataFlag = (row != null);
HSSFCell cell = null;
if (hasDataFlag) cell = row.getCell(cellNumber);
hasDataFlag = (cell != null);
if (hasDataFlag) hasDataFlag = (cell.getCellType() != Cell.CELL_TYPE_BLANK);
if (hasDataFlag) {
    // process the cell here
}

Alternatively, you could use the other version of Row.getCell, which takes a second argument that specifies the missing cell policy. This version would allow you to specify that getCell return a null cell for blank cells. So, here is some althernative code:

或者，您可以使用Row.getCell的另一个版本，它使用第二个参数指定缺少的单元格策略。此版本允许您指定getCell为空单元格返回空单元格。所以，这里有一些althernative代码：

HSSFRow row = sheet.getRow(rowNumber);
if (row != null) {
    HSSFCell cell = row.getCell(cellNumber, Row.RETURN_BLANK_AS_NULL);
    if (cell != null) {
        // process cell here
    }
}

Or, if you prefer, you could specify the policy as Row.CREATE_NULL_AS_BLANK. In that case, you would replace if (cell != null) with if (cell.getCellType() != Cell.CELL_TYPE_BLANK).

或者，如果您愿意，可以将策略指定为Row.CREATE_NULL_AS_BLANK。在这种情况下，您将使用if（cell.getCellType（）！= Cell.CELL_TYPE_BLANK）替换if（cell！= null）。

#2

If you use the CellIterator, you'll only get the cells that have been defined at some point (no null cells, but you will get blank cells). If you want to get all cells, fetch them by index

如果您使用CellIterator，您将只获得已在某一点定义的单元格（没有空单元格，但您将获得空白单元格）。如果要获取所有单元格，请通过索引获取它们

By index, you'd do something like:

按索引，您可以执行以下操作：

 Sheet sheet = workbook.getSheetAt(0);
 for (int rowNumber = sheet.getFirstRowNum(); rowNumber <= sheet.getLastRowNum(); rowNumber++) {
    Row row = sheet.getRow(rowNumber);
    if (row == null) {
         // This row is completely empty
    } else {
         // The row has data
         for (int cellNumber = row.getFirstCellNum(); cellNumber <= row.getLastCellNum(); cellNumber++) {
             Cell cell = row.getCell(cellNumber);
             if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                 // This cell is empty
             } else {
                 // This cell has data in it
             }
         }
    }
 }

#3

int column, rownumber = 0; // just for showing the example code

Row row = sheet1.getRow(rownumber); // just for showing the example code

if (row!= null)
    mycell = row.getCell(0);
if (mycell != null && mycell.getCellType() != Cell.CELL_TYPE_BLANK) {
    System.out.println(mycell.getStringCellValue());
}
else{
    System.out.println("whatever");  
}

#4

public String getCellData(String File_Name,int Sheet_Num, int Row_Num, int Col_Num) throws IOException{

public String getCellData（String File_Name，int Sheet_Num，int Row_Num，int Col_Num）throws IOException {

    FileInputStream fileIn = new FileInputStream(System.getProperty("user.dir")+"\\"+File_Name+".xlsx");
    XSSFWorkbook workbook = new XSSFWorkbook(fileIn);
    XSSFSheet sheet=workbook.getSheetAt(Sheet_Num-1);
    XSSFRow row = sheet.getRow(Row_Num-1);
    XSSFCell cell= row.getCell(Col_Num-1);
    String celldata;
    if(cell!=null){
    celldata= cell.getStringCellValue();
    }
    else 
        celldata ="";
    fileIn.close();
    return celldata;

    }

#5

Cell cell = row.getCell(x, Row.CREATE_NULL_AS_BLANK);

THis trick heped me a lot. See if it's useful for you...

这个骗局让我很开心。看看它对你有用......