Here's how to read Excel 95 or older document (XLS) using NPOI libraries

Here's how to read Excel 95 or older document (XLS) using NPOI libraries


In the last two articles, we have learned how to use the NPOI library to read Excel 2007 (.xlsx) and Excel 97-2003 (.xls) file format. We have also discussed more about the APIs and shared the code snippet for you to understand it easily.

 

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

 

Here's how to read Excel 95 or older 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 may like to read:



 

Reading Excel 95 and older document format using NPOI

In previous posts we have seen that, we need to use the NPOI.XSSF.Extractor.XSSFExcelExtractor class to read 'Excel 2007' format and we need to use the NPOI.HSSF.ExcelExtractor class to read 'Excel 97-2003' file format. But for 'Excel 95 and older' file formats, the API class is different. In this case, we will have to use NPOI.HSSF.Extractor.OldExcelExtractor class from the NPOI library. The exposed property 'Text' provides you the document content that includes all the sheets.

 

The API 'OldExcelExtractor' takes an input of type System.IO.FileStream. To read the content of the Excel 95 or older workbooks (Excel 5.0 or older), first create the instance of the FileStream passing the file path as input. Then create the instance of OldExcelExtractor and pass the file stream to it. 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 may encounter while accessing/reading the file.

 

/// <summary>Gets the text from 95 format excel file.</summary>
/// <param name="filePath">The file path of the Excel sheet.</param>
/// <returns>The text contents of the Excel sheets</returns>
private static string GetTextFromExcel95Format(string filePath)
{
    FileStream fileStream = null;
    OldExcelExtractor excelExtractor = null;
 
    try
    {
        using (fileStream = new FileStream(filePath, FileMode.Open))
        {
            excelExtractor = new OldExcelExtractor(fileStream);
            return excelExtractor.Text;
        }
    }
    catch
    {
        // handle the exception
    }
    finally
    {
        if (excelExtractor != null)
        {
            excelExtractor.Close();
            excelExtractor = null;
        }
 
        if (fileStream != null)
        {
            fileStream.Close();
            fileStream.Dispose();
            fileStream = null;
        }
    }
 
    return string.Empty;
}

 

I hope that the post was as useful as the previous posts. Also, I hope that the code shared above was clear and easy to understand. If you are facing any further issue, do let me know below. Don't forget to check my other posts published in this blog. Have a great day! Cheers!!!

 

 



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

  1. I think you will find none of that finally block is needed. The oldExtractor.Close just closes the same stream as the automatically called Dispose of fileStream (via the using block). All of it is redundant, and best removed for readability. It is even more stable without it, as you have only one place then to catch errors, other than those that might occur in the finally block.

    ReplyDelete
    Replies
    1. True! But added to catch and close the instance, which fails for some reason. I feaced that, and this resolved that issue.

      Delete

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