Click here to Skip to main content
14,640,360 members
Articles » Languages » C# » General
Tip/Trick
Posted 10 Aug 2015

Stats

11.4K views
292 downloads
4 bookmarked

Additional Helper Methods to the ExcelDataReader Package

Rate this:
4.72 (7 votes)
Please Sign up or sign in to vote.
4.72 (7 votes)
10 Aug 2015CPOL
ExcelDataReaderHelper: Functionality similar to LinqToExcel using the ExcelDataReader package

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.

Package source on GitHub.

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

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

E.F. Nijboer
Software Developer (Senior)
Netherlands Netherlands
Currently working as a Software Developer on several projects.

Comments and Discussions

 
QuestionVersion of ExcelDataReader used Pin
Duho Wise14-Feb-18 12:46
MemberDuho Wise14-Feb-18 12:46 
GeneralMy vote of 4 Pin
Abhishek Kumar Goswami18-Aug-15 3:21
professionalAbhishek Kumar Goswami18-Aug-15 3:21 
Good job but i would suggest please include bit description here with some core logic of code snippets , would be really helpful beginers.
GeneralRe: My vote of 4 Pin
E.F. Nijboer9-Sep-15 7:58
MemberE.F. Nijboer9-Sep-15 7:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.