NPOI-获取Excel行计数以检查其是否为空 [英] NPOI - Get excel row count to check if it is empty

问题描述

我正在使用NPOI lib和C#读取xlsx文件.我需要提取一些excel列，并将提取的值保存到某种数据结构中.

I'm reading an xlsx file using NPOI lib, with C#. I need to extract some of the excel columns and save the extracted values into some kind of data structure.

我可以使用以下代码成功读取文件并从第二个(第一个仅包含标头)到最后一行获取所有值:

I can successfully read the file and get all the values from the 2nd (the first one contains only headers) to the last row 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++;
}

我现在想做的是检查excel文件是否为空或只有1行，以便正确处理该问题并显示一条消息

如果我对只有1行(标题)的excel文件运行代码，则会在

If I run my code against an excel file with only 1 row (headers), it breaks on

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

，出现以下错误:

Object reference not set to an instance of an object.

我还尝试使用行数

sheet.LastRowNum

，但不会返回正确的行数.例如，我创建了一个具有5行(1xHEADER + 4xDATA)的excel，该代码成功读取了excel值.在相同的excel上，我删除了4个数据行，然后再次启动excel文件上的代码. sheet.LastRowNum继续返回4作为结果，而不是返回1....我认为这与绑定到手动清洁工作表单元格的某些属性有关.

but it does not return the right number of rows. For example, I have created an excel with 5 rows (1xHEADER + 4xDATA), the code reads successfully the excel values. On the same excel I have removed the 4 data rows and then I have launched again the code on the excel file. sheet.LastRowNum keeps returning 4 as result instead of 1.... I think this is related to some property bound to the manually-cleaned sheet cells.

您是否有解决此问题的提示?

Do you have any hint to solve this issue?

推荐答案

我简化了吗?

 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;
        }

这篇关于NPOI-获取Excel行计数以检查其是否为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！