0

Using NPOI like this:

static void xlsx()
    {
        XSSFWorkbook xsswb;
        using (FileStream file = new FileStream(@"test.xlsx", FileMode.Open, FileAccess.Read))
        {
            xsswb = new XSSFWorkbook(file);
        }

        ISheet sheet = xsswb.GetSheet("SheetOne");
        for (int row = 0; row <= 200; row++)
        {
            if (sheet.GetRow(row) != null) //null is when the row only contains empty cells 
            {
                for (var cell = 0; cell < 11; cell++)
                {
                    var cellvalue = sheet.GetRow(row).GetCell(cell);
                    if (cellvalue != null)
                    {

                        Console.WriteLine(string.Format("Row {0} {1} = {2}", row, cell, cellvalue.ToString()));
                    }
                    else
                    {
                        Console.WriteLine("null");
                    }
                }

            }
        }
    }

on a particular non empty workbook, I have no result written on the console: each rows returned is null.

If I create a new xlsx, fill it, and open it with this code, all works well. Other xlsx open well. The issue must be on the particular format of the xlsx I must import. (I cannot share it here since it contains reserved info)

If I open the test.xslx file with Excel, Save As, xlsx file, the above code works well. (the size of the file grows from 37kb to 69kb)

If I rename test.xlsx in test.xls and use HSSFWorkbook, NPOI correctly complains that the file is indeed a 2007+ excel file and that I should use XSSFWorkBook instead.

I cannot change the source excel file, since it is produced by a tool and my users should use it without any modification.

Any ideas? Thank you.

pomarc
  • 2,194
  • 3
  • 23
  • 30
  • Does the workbook that you are having trouble with actually have a sheet called "SheetOne"? If it has a different name, e.g. "Sheet1", then your loop won't run. – Brian Rogers Feb 23 '17 at 22:55
  • @BrianRogers Actually it will generate an exception at `sheet.GetRow(row)` as sheet is null. – krlzlx Feb 24 '17 at 14:14
  • @pomarc Your code should work. What do you mean by _"on a particular non empty workbook"_? What is the difference(s) between an excel file you create and the source excel produced by a tool you're talking about? – krlzlx Feb 24 '17 at 14:17
  • @krlzlx Yes, that is what I meant to say. Sorry, poorly worded comment. – Brian Rogers Feb 24 '17 at 15:10
  • Ho Brian, of course it has that sheet. As a matter of fact if it wouldn't have, it would have risen an exception. I have extracted from SAP another excel, and it can only be read until the 5th row,then all the subsequent rows appear null, even if the have data. I could not find a way to import it in NPOI, nor in excelreader, but I succeeded using EPPLUS. Probably SAP creates a file that has some errors in it, that some reader ignore and some don't. – pomarc Feb 24 '17 at 23:07

1 Answers1

0

I could not find a way to read those funny excels with NPOI. I've used epplus instead.

pomarc
  • 2,194
  • 3
  • 23
  • 30