handling empty columns in apache poi

Question

I have a program reading excel sheet from a java program.

I am iterating over cells as below:

Iterator cells = row.cellIterator();
String temp;
StringBuilder sb;
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");

while (cells.hasNext()) {
Cell cell = (Cell) cells.next();
temp = null;

    switch (cell.getCellType()) {
         case Cell.CELL_TYPE_STRING:
         temp = cell.getRichStringCellValue().getString();
         break;

    case Cell.CELL_TYPE_NUMERIC:

    if (DateUtil.isCellDateFormatted(cell)) {
        temp = sdf.format(cell.getDateCellValue());
       } else {
             temp = df.format(new BigDecimal(cell.getNumericCellValue()));
       }
          break;
          default:
     }
    if (temp == null || temp.equalsIgnoreCase("null")) {
        sb.append("").append(";");
    } else {
        sb.append(temp).append(";");
    }
 }

As seen, I am trying to create a string builder containing values from excel row in semicolon separated way.

Issue is, if a column value is empty, I want it as an empty value in the string builder with two consecutive semicolons.

However, the call

Cell cell = (Cell) cells.next();

simply ignores the empty cells and jumps over to next non empty cell.

So the line

if (temp == null || temp.equalsIgnoreCase("null"))

is never met.

How to get a handle on empty column values as well in the iterator ?

Answer 1

This is virtually a duplicate of this question, and so my answer to that question basically applies exactly to you too.

The Cell Iterator only iterates over cells that are defined in the file. If the cell has never been used in Excel, it probably won't appear in the file (Excel isn't always consistent...), so POI won't see it

If you want to make sure you hit every cell, you should lookup by index instead, and either check for null cells (indicating the cell has never existed in the file), or set a MissingCellPolicy to control how you want null and blank cells to be treated

So, if you really do want to get every cell, do something like:

Row r = sheet.getRow(myRowNum);
int lastColumn = Math.max(r.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT);

for (int cn=0; cn<lastColumn; cn++) {
   Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
   if (c == null) {
      // The spreadsheet is empty in this cell
   } else {
      // Do something useful with the cell's contents
   }
}

Answer 2

You can do this

int previous=0;
while(cell.hasNext())
{
    Cell cell = (Cell) cells.next();
    int current=cell.getColumnIndex();
    int numberofsemicolons=previous-current;
    for(numberofsemicolons)
    {
    sb.append("").append(";");
    }
    previous=current;
}

or you can do

int numberofcells=row.getFirstCellNum()-row.getLastCellNum();
for(i=0;i<=numberofcells;i++)
{
     Cell cell = (Cell) cells.next();
     int current=cell.getColumnIndex();
     while(i<current)
     {
        sb.append("").append(";");
        i++
     }
}

Answer 3

Answer posted by @Gagravarr works perfectly for me but MissingCellPolicy is an enum now, so while getting the cell value instead of using

Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL); 

I have used

Cell c =r.getCell(cn,Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);