What is your way of checking for a null/empty cell?

Dec 2, 2011 at 11:13 AM

I have noticed in some .xls sheets even if there is no value in a cell it will still think there is a value there. Im not sure if NPOI is picking up formats or if the cell has been created in the past.

The only work around i have found is below. Also is it my code or a problem with NPOI? Im using 1.2.4

ISheet Accsheet = this.hssfworkbookAccount.GetSheetAt(0); //Dont have to know the name of Sheet1 if its relabeled. 0 gets sheet 1
IRow row1 = Accsheet1.GetRow(RowCountAcc);              
ICell cell2 = row.GetCell(2);
if (cell2 != null)                             //Checks if null. If there are formats/cell has been used before it will continue to below. What a pain!
{
   if (string.IsNullOrEmpty(cell2.ToString())) //Checks to see if there is a value in cell
   { /*Do nothing*/ }
   else
   {
       MyRefAcc = cell2.ToString();
   }
}

Dec 2, 2011 at 6:56 PM

NPOI.SS.UserModel.Cell has a CellType enum.  I do a factory-like check to see if cell.CellType == CellType.BLANK

There's also CellType.STRING, CellType.BOOLEAN, CellType.FORMULA, etc

Dec 2, 2011 at 9:32 PM
Edited Dec 2, 2011 at 9:45 PM

The cell2.CellType == CellType.BLANK still wont avoid a null reference exception. Do you have many try catch statements? Do you have a section of code?

ICell cell2 = row.GetCell(2);
if (cell2.CellType != CellType.BLANK) //Creates a nullreference exception
{
    MyValueAcc = cell2.ToString();
}

unless i do this below

ICell cell2 = row.GetCell(2);
if (cell2 != null)
{
   if (cell2.CellType != CellType.BLANK)
   {
       MyValueAcc = cell2.ToString();
   }
}

Dec 5, 2011 at 1:53 PM

In the event of an exception, I return String.Empty.  Certainly not the most eloquent solution, though.  You could simply do:  if(cell2 == null) return String.Empty

Just a preference, but I prefer checking for null first, and returning if the condition is null.  Then I don't have to do an else:

try
{
  if(cell2 == null)
    return String.Empty;

  if (cell2.CellType == CellType.BLANK) 
     return String.Empty;
  return cell2.ToString();
}
catch
{
  return String.Empty; //Or throw
}

Dec 5, 2011 at 2:02 PM

BTW, I'm not sure if you do this or not, but in my NPOI wrapper ( http://www.box.com/s/kbea3pvti84a31i8q389 ), whenver I go to read a cell, I initalize the cell by making the statement:

if (_currentCell == null)
{
   _currentCell = _currentRow.CreateCell(column);
}

This is probably why I'm not always encountering an exception when I read a cell, although it is admittedly heavy handed in a read-only circumstance; however, since in a read-only situation, I'm not calling SaveFile(), it doesn't seem to be a problem