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++;
}
What I'd like to do now is check if the excel file is empty or if it has only 1 row in order to properly handle the issue and display a message
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
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 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?
Am I oversimplifying?
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;
}