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

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.

 

Here's how to read Excel 97-2003 document (XLS) using NPOI libraries (www.kunal-chowdhury.com)

 

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 might be interested to read:



 

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!

 

 



If you have come this far, it means that you liked what you are reading. Why not reach little more and connect with me directly on Twitter, Facebook, Google+ and LinkedIn. I would love to hear your thoughts and opinions on my articles directly. Also, don't forget to share your views and/or feedback in the comment section below.

2 comments

 
© 2008-2017 Kunal-Chowdhury.com - Microsoft Technology Blog for developers and consumers | Designed by Kunal Chowdhury
Back to top