Additional Helper Methods to the ExcelDataReader Package

• Download example - 1.3 MB

Introduction

ExcelDataReaderHelper is a small helper class that provides some extra helper methods for the ExcelDataReader package, similar to LinqToExcel making it easy to map rows of worksheet to a given generic type, using a simple convention to map column data to properties. And just as easy be able to retrieve a range of cells as a jagged array of objects or a given generic type.

The ExcelDataReader is a great package with many advantages for reading data from Excel. It gives the ability to read Excel files in both binary (.xls) and openXML (.xlsx) format, all without needing the JET/ACE driver or Excel installed. This makes it a lot easier to use in general and also more suitable for server use. Server administrators have a grudge against installing dependencies like Excel or extra drivers on their servers. With the ExcelDataReader package, the software is self reliant, which is a property that defines good software.

Although the ExcelDataReader gives great power, it also is known to be somewhat harder to use. There is no easy method for retrieving cell data or mapping rows directly to objects. As I was working on a project that needed just this, it seemed like a good idea to capture this functionality in a nice small helper.

Background

The basic idea of the ExcelDataReaderHelper is to expose a few very useful and easy to use methods for any given Excel file or stream. For this to work correctly, it won't be a surprise it has a dependency on ExcelDataReader. Since this dependency is transparently handled by Nuget, this shouldn't be a problem.

I added (or at least tried to add) proper documentation comments to the ExcelDataReaderHelper package so code insight is able to help you out whenever possible.

Using the Code

Below are some of the basic properties and functions available. For a better understanding of how they work, the example project shows best how to use them.

/// 
/// Getting the number of worksheets
///
ExcelDataReaderHelper.WorksheetCount

///  
/// Getting worksheet names
///
ExcelDataReaderHelper.WorksheetNames

///
/// Getting untyped cells as jagged array (object[][])
///
ExcelDataReaderHelper.GetRangeCells(...);

///
/// Getting typed cells of T as jagged array (T[][])
///
ExcelDataReaderHelper.GetRangeCells<T>(...);

///
/// Getting objects of T for each row with column values mapped to properties (T[])
///
ExcelDataReaderHelper.GetRange<T>(...);

Acknowledgement

• Created on Mint 17 using MonoDevelop 5.9.4 and Mono 4.0.2.

History

• 10^th August, 2015: Initial version
• 11^th August, 2015: Link to source on GitHub