Here's how to read Excel 97-2003 document (XLS) using NPOI libraries

In the last article, we have discussed about the NPOI library and then learned how to read Excel 2007 file formats using the free, open sourced NPOI library. I hope, the code snippet was clear and easy to understand.

In this article, we will learn how to read Excel 97-2003 workbooks easily using the free Apache NPOI libraries in your C#/.NET applications.

Basic concepts about NPOI library

Before starting with the code, you should have the basic knowledge about the NPOI library. NPOI is the .NET version of POI Java project, originally hosted at http://poi.apache.org. It is a free, open source project which can help you to read/write Word, Excel, PowerPoint document files. You can find the source code of NPOI project hosted at https://github.com/tonyqus/npoi. The libraries can be downloaded from NuGet from this URL: https://www.nuget.org/packages/NPOI.

You may like to read:

• How to read Excel document using C#/.NET (COM APIs)?
• How to read Excel 2007 document (XLSX) using NPOI libraries?
• How to read Excel 97-2003 document (XLS) using NPOI libraries?
• How to read Excel 95 or older document (XLS) using NPOI libraries?
• Solution for 'POI only supports BIFF8 format' exception

Reading Excel 97-2003 document format using NPOI

In earlier post, related to 'Excel 2007' format, we learned about NPOI.XSSF.Extractor.XSSFExcelExtractor class, which was used to extract the workbook content of that file type. But for the 'Excel 97-2003' file format, having file extension of .xls, the API is different. To read this type of workbooks, you will need to use the NPOI.HSSF.ExcelExtractor class. It extends base POIOLE2TextExtractor and inherits IExcelExtractor interface. The exposed property 'Text' provides you the document content that includes all the sheets.

The API 'ExcelExtractor' takes an input of type NPOI.POIFS.FileSystem.POIFSFileSystem, which again takes an inut of type System.IO.FileStream. To read the content of the Excel workbook, first create the instance of the FileStream passing the file path as input. Then create the instance of POIFSFileSystem and pass it to the ExcelExtractor. Then, call the property 'Text' to get the content of the document. Here's the code for your easy reference. You may have to handle the exceptions that you encounter while accessing/reading the content.

/// <summary>Gets the text from 97-2003 format excel file.</summary>

/// <param name="filePath">The file path of the Excel sheet.</param>

/// <returns>The text contents of the Excel sheets</returns>

public static string GetTextFromExcel97To2003Format(string filePath)

{

    FileStream fileStream = null;

    POIFSFileSystem fileSystem = null;

    ExcelExtractor excelExtractor = null;

    try

    {

        using (fileStream = new FileStream(filePath, FileMode.Open))

        {

            fileSystem = new POIFSFileSystem(fileStream);

            excelExtractor = new ExcelExtractor(fileSystem);

            excelExtractor.IncludeBlankCells = false; // optional

            excelExtractor.IncludeCellComments = false; // optional

            excelExtractor.IncludeHeaderFooter = false; // optional

            excelExtractor.IncludeSheetNames = false; // optional

            return excelExtractor.Text;

        }

    }

    catch

    {

        // handle the exception

    }

    finally

    {

        if (excelExtractor != null)

        {

            excelExtractor.Close();

            excelExtractor = null;

        }

        fileSystem = null;

        if (fileStream != null)

        {

            fileStream.Close();

            fileStream.Dispose();

            fileStream = null;

        }

    }

    return string.Empty;

}

Was the post helpful? Do let us know. In the next post, we will learn how to read Excel 95 format (BIFF5 format) using the free, open source NPOI library. Don't forget to read and share the post that I publish. Have a great day!