CODEWITHSUNDEEP
c#excelapache-poinpoi
ldn
ldn

Reputation: 157

POI check cell is whether empty or not?

Well, i'm trying to read cell from excel sheet. And if the cell has no value or empty it will return false. I tried "null" (sheet.getrow(a).getcell(b) == null and sheet.getrow(a).getcell(b).celltype == celltype.Blank) but when cell has blank spaces or its filled with color it returns false.

Thanks, i've been stuck with this for days. (if you need code i can edit it).

Upvotes: 2

Views: 4631

Answers (1)

Brian Rogers
Brian Rogers

Reputation: 129657

Whether a cell is "empty" depends partly on whether the cell actually exists (i.e. not null), what type of cell it is (string / number / blank, etc.) and what value is in the cell, depending on its type. I would make some extension methods to make this determination easier. You can tweak them to work however you want. For example, if you consider a cell with no value but filled with color to be non-empty, you can add a check for that inside the IsNullOrEmpty method.

public static class NpoiExtensions
{
    public static bool IsCellNullOrEmpty(this ISheet sheet, int rowIndex, int cellIndex)
    {
        if (sheet != null)
        {
            IRow row = sheet.GetRow(rowIndex);
            if (row != null)
            {
                ICell cell = row.GetCell(cellIndex);
                return cell.IsNullOrEmpty();
            }
        }
        return true;
    }

    public static bool IsNullOrEmpty(this ICell cell)
    {
        if (cell != null)
        {
            // Uncomment the following lines if you consider a cell 
            // with no value but filled with color to be non-empty.
            //if (cell.CellStyle != null && cell.CellStyle.FillBackgroundColorColor != null)
            //    return false;

            switch (cell.CellType)
            {
                case CellType.String:
                    return string.IsNullOrWhiteSpace(cell.StringCellValue);
                case CellType.Boolean:
                case CellType.Numeric:
                case CellType.Formula:
                case CellType.Error:
                    return false;
            }
        }
        // null, blank or unknown
        return true;
    }
}

With these methods in place, your code becomes much simpler:

if (sheet.IsCellNullOrEmpty(a, b))
{
    Console.WriteLine("Cell at row " + a + " column " + b + " is empty.");
}

Upvotes: 5

Related Questions