Search code examples
c#excelnpoi

NPOI issue with FirstRowNum and LastRowNum returning -1 for a sheet that has rows

When trying to get the rows from an Excel sheet using NPOI, FirstRowNum and LastRowNum return -1.

IWorkbook workbook = null;
List<ImportedKPI> excelRows = new List<ImportedKPI>();
MemoryStream ms = new MemoryStream(array);
ISheet sheet = null;

workbook = WorkbookFactory.Create(ms);

sheet = workbook.GetSheet(mapping.Sheet);

//Do some stuff here and try to get rows  
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
    //sheet.FirstRowNum = -1 && sheet.LastRowNum = -1

Another worthy mention is that when I save open the file and close it (with LibreOffice) it asks if I want to save and after that it works. When comparing the byte arrays before/after save they are different.

Solution

  • Turns out not all excel parsers work for all types. And since I get excels by email I had to do a chain of responsibility pattern based on NPOI and ExcelDataReader nugets. This means that the when the excel comes I try to use a first class to parse it (ExcelDataReader first) and if it's not successful it moves on to NPOI and tries to parse it with that. Seems like the better way when there are many sources.