Clever Geek Handbook

NPOI - Get excel row count to check if it is empty

I am reading a file xlsx

using NPOI

lib, with C#

. I need to extract some of the excel columns and store the extracted values ​​into some kind of data structure.

I can successfully read the file and get all values ​​from the second (the first contains only headers) to the last line with the following code:

...
workbook = new XSSFWorkbook(fs);
sheet = (XSSFSheet)workbook.GetSheetAt(0);
....
int rowIndex = 1;  //--- SKIP FIRST ROW (index == 0) AS IT CONTAINS TEXT HEADERS
while (sheet.GetRow(rowIndex) != null) {
    for (int i = 0; i < this.columns.Count; i++){
       int colIndex = this.columns[i].colIndex;
       ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
       cell.SetCellType(CellType.String);
       String cellValue = cell.StringCellValue;
       this.columns[i].values.Add(cellValue); //--- Here I'm adding the value to a custom data structure
    }
    rowIndex++;
}

Now I would like to check if the excel file is empty or has only 1 line in order to properly handle the problem and display the message

If I run my code in an excel file with only 1 line (headers), it breaks into

cell.SetCellType(CellType.String); //--- here cell is null

with the following error:

Object reference not set to an instance of an object.

I also tried to get the row count with

sheet.LastRowNum

but it doesn't return the correct number of rows. For example, I created excel with 5 lines (1xHEADER + 4xDATA), the code reads excel values ​​successfully. On the same excel, I deleted 4 rows of data and then ran the code again in the excel file. sheet.LastRowNum

returns 4

as result instead of 1

.... I think it has to do with some property related to manually cleared sheet cells.

Do you have any hint of a solution to this problem?

+3


source to share


4 answers


I think it would be wise to use sheet.LastRowNum, which should return the number of rows in the current sheet



+3


source


Am I simplifying?



 bool hasContent = false;

 while (sheet.GetRow(rowIndex) != null)
        {
            var row = rows.Current as XSSFRow;
            //all cells are empty, so is a 'blank row'
            if (row.Cells.All(d => d.CellType == CellType.Blank)) continue;  


            hasContent = true;
        }
+2


source


You can get the number of lines using this code:

public int GetTotalRowCount(bool warrant = false)
{
    IRow headerRow = activeSheet.GetRow(0);
    if (headerRow != null)
    {
        int rowCount = activeSheet.LastRowNum + 1;
        return rowCount;
    }
    return 0;
}
0


source


Here's a way to get both the actual index of the last row and the number of physically existing rows:

    public static int LastRowIndex(this ISheet aExcelSheet)
    {
        IEnumerator rowIter = aExcelSheet.GetRowEnumerator();
        return rowIter.MoveNext()
        ? aExcelSheet.LastRowNum
        : -1;
    }

    public static int RowsSpanCount(this ISheet aExcelSheet)
    {
        return aExcelSheet.LastRowIndex() + 1;
    }

    public static int PhysicalRowsCount(this ISheet aExcelSheet )
    {
        if (aExcelSheet == null)
        {
            return 0;
        }

        int rowsCount = 0;
        IEnumerator rowEnumerator = aExcelSheet.GetRowEnumerator();
        while (rowEnumerator.MoveNext())
        {
            ++rowsCount;
        }

        return rowsCount;
    }
0


source






More articles:


All Articles