How to Check If an Excel Cell Is Empty Using Apache Poi

To check the cell is empty in Apache POI library

As suggested in comment, you need to create cell & check for its null value.
You may want to change getExcelContent method as below.

 public String getExcellContent(String sheetName, int row, int col){     
        String data = null;
        sheet1  = wb.getSheet(sheetName);  
        XSSFCell gcell=null;
        XSSFRow row1=sheet1.getRow(row);//Create Row.
        if(row1!=null)
        {

        gcell=row1.getCell(col);//Create Cell
        if(gcell!=null)
        {
        int getType = gcell.getCellType();
        if(getType == 0) {
            Double data1 = gcell.getNumericCellValue();
            int x1  =  data1.intValue();
            data = String.valueOf(x1);
        }
        else if(getType == 1) {
            data = gcell.getStringCellValue();
        }else {
            data = "Nothing";
        }
        }
        }
        return data;        
    }

This is working for me. Give it a try.

Java Apache POI Blank Cells

In your code, you are using cellIterator

Iterator<Cell> cells = row.cellIterator();

cellIterator does not include cells which are null.

Instead of using cellIterator, you have to start loop from the first cell to the last cell and then check whether cell is null or not.

You can refer to below sample code -

for (int colNum = 0; colNum < row.getLastCellNum(); colNum++) {

   Cell cell = row.getCell(colNum, Row.CREATE_NULL_AS_BLANK);
   String cellValue = null;

// do whatever you want to do with the cell or its value

}

Apache POI - Handling Empty fields

There are only two Cell.setCellValue methods which will throw NPE when the given value is null. This are the two only ones which take primitive data types as value parameters but you put the appropriate object wrapper classes so unboxing is necessary. This are Cell.setCellValue(boolean value) and Cell.setCellValue(double value).
This is because unboxing from Boolean to boolean and Double to double fails for null.

For example

Boolean value = null;
cell.setCellValue(value); //NPE because unboxing `Boolean` to `boolean` fails

and

Double value = null;
cell.setCellValue(value); //NPE because unboxing `Double` to `double` fails

All other Cell.setCellValue methods which take non primitive data classes as value parameters simple create blank cells when null is given.

So if pieCase.getActualAmountReturned() returns a Double, then

...
if (pieCase.getActualAmountReturned() == null) {
 userRow.createCell(2).setBlank();
} else {
 userRow.createCell(2).setCellValue(pieCase.getActualAmountReturned());
}
...

Not able to check in excel using Apache POI if specific cell as per row and column is empty in Selenium with Java

I've found solution for the above question and only need to change few code and now it is working as expected:

          for(int j=2; j<7; j++)
            {
            row = sheet.getRow(j - 1);
            if(row==null)
                row = sheet.createRow(j - 1);

            cell = row.getCell(col_Num);
            //it will check if cell contains no value then create cell and set value                    
            if(cell == null)
            {
                cell = row.createCell(col_Num);
                cell.setCellValue(value);
                break;
            }

            }

---

Related Topics