NPOI for reading XLS

Question

I am working on some project where I use NPOI library for reading some data from excel. Everything was working good but then I tried to read .xls and it stopped working. (Everything was well for .xlsx).

The last thing i tried is this:

 IWorkbook hssfwb;
                using (FileStream file = new FileStream(@filePath, FileMode.Open, FileAccess.Read))
                {
                     hssfwb = WorkbookFactory.Create(file);
                }

The exception I get is:

Your stream was neither an OLE2 stream, nor an OOXML stream.

What I need is a way to open .xls files using NPOI library. Thanks in advance.

Sounds like the file is invalid. If you open it in Excel and save it again does it work? — stuartd
@stuartd Excel file is generated by some business program, i can't even save it, but i can read it and copy paste its content. When i try to save it I get a message form excel you can't save a file in this format. — Petar Mijović
Well, that's your problem: if Excel can't handle the file, then NPOI won't be able to. Can you work out what the underlying type is, maybe HTML or CSV? — stuartd
@stuartd Any suggestions on how to do that? I need to use this file as it's my only way to get database content from someone else software. Export to excel is something they created. This file is product of that action — Petar Mijović

minni minni · Accepted Answer · 2020-03-19T10:57:18

To operate with the old Excel 97-2003 format (Files ending with ".xls"), you can use the HSSFWorkbook class.

If you check the file name's extension, you can handle both file types when reading the file into a IWorkbook model:

        MemoryStream fileStream = new MemoryStream(fileContent);
        IWorkbook workbook = null;

        if (fileName.ToLower().EndsWith(".xlsx"))
        {
            // new OOXML format
            workbook = new XSSFWorkbook(fileStream);
        }
        else
        {
            // old Excel 97 format
            workbook = new HSSFWorkbook(fileStream);
        }

From ther on you can handle the content of the spreadsheet as usual:

        // get first sheet
        ISheet sheet = workbook.GetSheetAt(0);

        // get header names as string array
        IRow headerRow = sheet.GetRow(rowNumber);
        List<ICell> headerCells = headerRow.Cells;
        List<string> rowData = new List<string>();
        foreach (ICell cell in headerCells)
        {
            rowData.Add(cell.ToString());
        }

NPOI for reading XLS

1 Answers