How to Avoid Nullpointerexception With an Empty Cell Using Poi

How to avoid NullPointerException with an empty cell using POI?

wrap your code in a try / catch statement that is what it's there for..

some untested code below to give you the idea:

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();
}catch(NullPointerException NPE)
//what to do when the exception occurs?

NullPointerException when accessing a cell with Apache POI

Try setting the Row.MissingCellPolicy:

  • static Row.MissingCellPolicy CREATE_NULL_AS_BLANK

    A new, blank cell is created for missing cells.

  • static Row.MissingCellPolicy RETURN_BLANK_AS_NULL

    Missing cells are returned as null, as are blank cells

  • static Row.MissingCellPolicy RETURN_NULL_AND_BLANK

    Missing cells are returned as null, Blank cells are returned as normal

This question has a good discussion on the topic.

null pointer exception apache poi

The issue is that you never test if the cell is null!

if (cell == null)
System.out.println("Cell is Empty in Column:" + cols);

} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)

As a general matter, you should be careful while handling Cell.getCellType() function, since an empty cell could be either null or be a CELL_TYPE_BLANK.

Try to write to a null/blank cell in excel leads to java.lang.NullPointerException

After many days, I found the root of the problem. The problem was when the row was null... with the following

row = sheet1.getRow(i+1); 
if(row == null){
row = sheet1.createRow(i+1);

the program run well.

How to handle null pointer exception while reading excel date column value using getDateCellValue() method of Apche poi

It is there in the Apache poi documentation itself that the method getDateCellValue() you are calling returns null for blank cells.So, most probably the format() method must be throwing null pointer exception because of this. Whenever your code encounters an empty cell value when the column type is 'date', it returns null which the format() method tries to format and throws exception.

java.util.Date getDateCellValue()

Get the value of the cell as a date.

For strings we throw an exception. For blank cells we return a null.

the value of the cell as a date

So in short , you need to add a null check in your code to handle this case.

    String valueOfColumn;
if(cell != null ) {
if(StringUtils.equalsIgnoreCase(excelData.getColumnType(), "date") && cell.getDateCellValue() != null) {
valueOfColumn = format(cell.getDateCellValue(), "dd/MM/yyyy");
} else {
valueOfColumn = formatter.formatCellValue(cell)
excelData.setColumnValue(valueOfColumn );
else {
// handle else case for cell being null

getCellType() of Poi throws NullPointerException, when cell value is blank

You just have to check if the cell is null, before calling any methods on it:

if (cel == null) {

Replace the continue part with what you need, this way it simply ignores the cell.

Getting null pointer exception in creating cell in Apache POI

Unfortunately, the cell is currently null not a blank cell. Placing data on the specified sheet makes it blank. You might want to create the cell first then check if it is a blank cell rather than null.

**XSSFCell cell = row.createCell(1);

if (cell == Cell.CELL_TYPE_BLANK){

Related Topics

Leave a reply