-2

I want to read two integer values 100 and 200 in cell A1 and A2 using Intellij with Apache Poi.

Issue: I am getting java.lang.nullpointerexception at the line num1 below.

I've checked that I have apache poi jars added and correct import directives added. Excel row and column index starts at 1. Hence, getRow(1).getCell(1) refers to cell A1. Also, getSheetAt(0) refers to the first and only sheet that I have in my excel file. 

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import java.io.File;
import java.io.FileInputStream;
import java.lang.Exception;

int num1, num2, sum1;

File src=new File("C:/test/testdata.xlsx");
FileInputStream fis=new FileInputStream(src);
XSSFWorkbook wb=new XSSFWorkbook(fis);
XSSFSheet sheet = wb.getSheetAt(0);
num1 = Integer.parseInt(sheet.getRow(1).getCell(1).getStringCellValue());
num2 = Integer.parseInt(sheet.getRow(2).getCell(1).getStringCellValue());
sum1 = num1 + num2;
System.out.println("The sum: " + sum1);

When running the code successfully, I should see num1 and num2 populated from excel file, and the sum is printed in the console.

DktPhl2018
  • 155
  • 1
  • 1
  • 8
  • "Hence, getRow(1).getCell(1) refers to cell A1.": No. getRow(1).getCell(1) refers to cell B2 since both indexes are 0-based. See API doc [Sheet.getRow](https://poi.apache.org/apidocs/4.1/org/apache/poi/ss/usermodel/Sheet.html#getRow-int-) and [Row.getCell](https://poi.apache.org/apidocs/4.1/org/apache/poi/ss/usermodel/Row.html#getCell-int-). And both may return `null` if the row or the cell does not exists in the sheet. That's why the `NullPointerException`. So one should always check what he gets before using it further. – Axel Richter Jul 20 '19 at 16:37

1 Answers1

0

In case of Apache POI while reading from an excel file, apart from other parts of coding, you have to handle in the following manner.

Cell cell = sheet.getRow(1).getCell(1); 
if (cell.getCellTypeEnum() == CellType.STRING) {
    //write logic to handle
}
else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
    //write logic to handle
}

Also you need to check whether cell exists or not , you can make a null check.

Sambit
  • 7,625
  • 7
  • 34
  • 65