How to Read Empty Cells of an Excel File Using Poi

How to read empty cells of an excel file using POI

The Iterators return you the cells the actually exist in the file. If you're trying to replicate their positions, that almost certainly isn't what you want, instead you'll want to check each cell in turn

You'll likely want code something like:

workbook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
DataFormatter fmt = new DataFormatter();

for(int sn=0; sn<workbook.getNumberOfSheets(); sn++) {
   Sheet sheet = workbook.getSheetAt(sn);
   for (int rn=sheet.getFirstRowNum(); rn<=sheet.getLastRowNum(); rn++) {
      Row row = sheet.getRow(rn);
      if (row == null) {
         // There is no data in this row, handle as needed
      } else {
         // Row "rn" has data
         for (int cn=0; cn<row.getLastCellNum(); cn++) {
            Cell cell = row.getCell(cn);
            if (cell == null) {
              // This cell is empty/blank/un-used, handle as needed
            } else {
               String cellStr = fmt.formatCell(cell);
               // Do something with the value
            }
         }
      }
   }
}

This code will let you get at each cell in turn, and will also correctly format your cells (so that the numbers are formatted to look like they do in Excel)

Error: How to read empty cell in Excel

This is normal behavior for the 1-argument version of Row.getCell. If you look at the API doc, it specifically states that getCell will return null if the cell is not defined. Many java functions exhibit this sort of behavior, so there is nothing wrong with coding to take this into account. So, one version of your code could be something like:

boolean hasDataFlag = true;    
HSSFRow row = sheet.getRow(rowNumber);
hasDataFlag = (row != null);
HSSFCell cell = null;
if (hasDataFlag) cell = row.getCell(cellNumber);
hasDataFlag = (cell != null);
if (hasDataFlag) hasDataFlag = (cell.getCellType() != Cell.CELL_TYPE_BLANK);
if (hasDataFlag) {
    // process the cell here
}

Alternatively, you could use the other version of Row.getCell, which takes a second argument that specifies the missing cell policy. This version would allow you to specify that getCell return a null cell for blank cells. So, here is some althernative code:

HSSFRow row = sheet.getRow(rowNumber);
if (row != null) {
    HSSFCell cell = row.getCell(cellNumber, Row.RETURN_BLANK_AS_NULL);
    if (cell != null) {
        // process cell here
    }
}

Or, if you prefer, you could specify the policy as Row.CREATE_NULL_AS_BLANK. In that case, you would replace if (cell != null) with if (cell.getCellType() != Cell.CELL_TYPE_BLANK).

When reading a column in Excel file, this program reads data and empty columns

Sheet.rowIterator does not contain totally empty rows. But it of course contains rows only having part of the cells filled. And it contains rows having format or having cells having formats even if those cells are empty.

So, if the need is to skip empty cells, then your program must check whether the found cell is empty or not. Simplest possibility is to check whether the Cell is null or Cell.toString equals a empty String.

...
  while(rowiter.hasNext()) { // does not contain totally empty rows
   XSSFRow row = (XSSFRow) rowiter.next();
   if (count == 0) { //skip header row
    count++;
    continue;
   } else {
    XSSFCell cell = row.getCell(choice);
    if (cell == null || "".equals(cell.toString())) { // check whether cell is empty
     // cell is empty
    } else {
     System.out.println(cell.toString());
    }
   }
  }
...

Note: Relying on Cell.toString is not good practice. Instead do using DataFormatter to get the cell values as String.

Example:

...
import org.apache.poi.ss.usermodel.DataFormatter;
...
  while(rowiter.hasNext()) { // does not contain totally empty rows
   XSSFRow row = (XSSFRow) rowiter.next();
   if (count == 0) { //skip header row
    count++;
    continue;
   } else {
    XSSFCell cell = row.getCell(choice);
    String cellValue = dataFormatter.formatCellValue(cell);
    if ("".equals(cellValue)) { // check whether cell is empty
     // cell is empty
    } else {
     System.out.println(cellValue);
    }
   }
  }
...

I am not able to read blank cells from xlsx sheet in selenium using apache POI.(PS : I want to skip blank rows)

Blank cells in xlsx are typically set to null when using apache-poi, so you have to do a null check on your cell.

Cell cell = row.getCell(1);
if (cell != null) {
  String value = cell.getStringCellValue();
  System.out.println(value);
}

---

Related Topics