How to get an Excel Blank Cell Value in Apache POI?

Question

I have a huge excel file with tons of columns which looks like this :-

Column1 Column2 Column3 Column4 Column5 abc             def             ghi         mno             pqr ...... 

This is the code that I wrote to print these values:

try {     FileInputStream inputStr = new FileInputStream(fileName);     XSSFWorkbook xssfWork = new XSSFWorkbook(inputStr) ;     XSSFSheet sheet1 = xssfWork.getSheetAt(0);     Iterator rowItr = sheet1.rowIterator();      while ( rowItr.hasNext() ) {         XSSFRow row = (XSSFRow) rowItr.next();         System.out.println("ROW:-->");         Iterator cellItr = row.cellIterator();          while ( cellItr.hasNext() ) {             XSSFCell cell = (XSSFCell) cellItr.next();             System.out.println("CELL:-->"+cell.toString());         }     } } catch (Exception e) {     e.printStackTrace(); } 

The output generated by this code is :-

ROW:--> CELL:-->Column1 CELL:-->Column2 CELL:-->Column3 CELL:-->Column4 CELL:-->Column5 ROW:--> CELL:-->abc CELL:-->def CELL:-->ghi ROW:--> CELL:-->mno CELL:-->pqr 

So, If we look at the output above we can note that the cells where I left blank values was not picked up by the POI library , is there a way in which I can get these values as null. or a way to recognize that the values presented skipped blank cells?

Thanks.

Answer 1

If you want to get all cells, no matter if they exist or not, then the iterator isn't for you. Instead, you need to manually fetch the appropriate cells, likely with a missing cell policy

for(Row row : sheet) {    for(int cn=0; cn<row.getLastCellNum(); cn++) {        // If the cell is missing from the file, generate a blank one        // (Works by specifying a MissingCellPolicy)        Cell cell = row.getCell(cn, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);        // Print the cell for debugging        System.out.println("CELL: " + cn + " --> " + cell.toString());    } } 

There's more details on all of this in the Apache POI documentation on iterating over cells

Answer 2

I have been frustrated by this same problem. Here is what I found with poi-3.7-20101029 and poi-3.8.

RowIterator and CellIterator do not support iterating over NULL cells or rows -- only physically defined cells (which can be BLANK).

The solution that returns what I expect requires using the 0-based Row.getCell([int], Row.CREATE_NULL_AS_BLANK), much like Chavira's answer alludes to (assuming 8 cell rows). Or you can use the Cell.columnIndex value while iterating to check for jumping numbers...

Annoyingly, after creating blank cells using method #1, the iterators will return the now created BLANK cells. I consider it a bug that MissingCellPolicy is ignored by CellIterator.