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..
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?
}
}
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)
{
//code
}
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.
Returns:
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) {
continue;
}
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){
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue("Apple");
}
Related Topics
How to Sort Integer Digits in Ascending Order Without Strings or Arrays
Checking If a Document Exists in a Firestore Collection
Set Layout Width Percentage of the Total Screen Width
Java Classnotfoundexception With Maven Dependency
Java - Check If Jtextfield Is Empty or Not
How to Multiply a and B With My Recursive Method
Can We Use Multipart and @Requestbody Together in Spring
How to Use Junit to Test Asynchronous Processes
How Is This Wrong - Hackerrank Loop in Java
Java - How to Find Students With Their Highest Marks Writing a Method in a Student Class
Get Current Week Start and End Date in Java - (Monday to Sunday)
Pdf to Byte Array and Vice Versa
How to Put a Scanner Input into an Array... for Example a Couple of Numbers
How to Post Form Data With Spring Resttemplate
Duplicate Entry Exception: Spring Hibernate/Jpa Cascade Save Many to One