如何使用POI避免使用空单元格的NullPointerException？

I'm trying to get some values in my java program from an excel .xlsx file using Apache POI, but I'm having trouble because my loop encounters an empty cell sometimes, then I get a NullPointerException. How can I "test" the cell before even reading it ? Here's a piece of my code :

我正在尝试使用Apache POI从excel .xlsx文件中获取我的java程序中的一些值,但是我遇到了麻烦,因为我的循环有时会遇到一个空单元格,然后我得到一个NullPointerException。如何在阅读之前“测试”细胞?这是我的一段代码:

FileInputStream file = new FileInputStream(new File(file));
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
int rows;
rows = sheet.getPhysicalNumberOfRows();
for (int i=1;i<rows;i++){
    Row row = sheet.getRow(i);
    Cell cell = row.getCell(2); // Here is the NullPointerException
    String cellString = cell.getStringCellValue();
    myArrayList.add(cellString);
}

Which brings me to :

这让我想到:

java.lang.NullPointerException
at analyse.Analyser.getExcelWords3(Analyser.java:73)
at analyse.Analyser.main(Analyser.java:21)

I want to know if there's a possibility to check if the cell is empty before trying to read it, then I won't get the NPE. Thank you in advance !

我想知道在尝试阅读之前是否有可能检查单元格是否为空,然后我就不会得到NPE。先谢谢你 !

4 个解决方案

#1

wrap your code in a try / catch statement that is what it's there for.. https://docs.oracle.com/javase/tutorial/essential/exceptions/catch.html

将你的代码包装在try / catch语句中,这就是它的用途.. https://docs.oracle.com/javase/tutorial/essential/exceptions/catch.html

some untested code below to give you the idea:

下面的一些未经测试的代码给你的想法:

for (int i=1;i<rows;i++){
    try{
        Row row = sheet.getRow(i);
        Cell cell = row.getCell(2); // Here is the NullPointerException
        String cellString = cell.getStringCellValue();
        myArrayList.add(cellString);
    }catch(NullPointerException NPE)
    {
        //what to do when the exception occurs?
    }
}

#2

Look at this method:

看看这个方法:

/**
 * Returns the cell at the given (0 based) index, with the specified {@link org.apache.poi.ss.usermodel.Row.MissingCellPolicy}
 *
 * @return the cell at the given (0 based) index
 * @throws IllegalArgumentException if cellnum < 0 or the specified MissingCellPolicy is invalid
 * @see Row#RETURN_NULL_AND_BLANK
 * @see Row#RETURN_BLANK_AS_NULL
 * @see Row#CREATE_NULL_AS_BLANK
 */
public XSSFCell getCell(int cellnum, MissingCellPolicy policy) {

It should help you.

它应该对你有所帮助。

#3

`       FileInputStream file = new FileInputStream(new File(file));
     XSSFWorkbook workbook = new XSSFWorkbook(file);
     XSSFSheet sheet = workbook.getSheetAt(0);
     int rows;
     String cellString = null;
     rows = sheet.getPhysicalNumberOfRows();
    for (int i=1;i<rows;i++){
    Row row = sheet.getRow(i);
    if(row.getCell(2) == null)
    {
    cellString = null;
    }
    else
    {
    Cell cell = row.getCell(2); //if 3rd column of excelsheet then     getCell(2)..
    cellString = cell.getStringCellValue();
    }
    myArrayList.add(cellString);
    }
      if excel cell contain empty/blank then it will be stored as null`

#4

To avoid NullPointerException add this Row.MissingCellPolicy.CREATE_NULL_AS_BLANK

为了避免NullPointerException,请添加此Row.MissingCellPolicy.CREATE_NULL_AS_BLANK

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

This will create a blank cell instead of giving you NPE

这将创建一个空白单元格,而不是给你NPE

#1