Reading Excel with EPPlus (.NET library)

EPPlus

Excel documents are popular in all forms of business as they can be used to manage and calculate just about every kind of business process. They are especially useful for data manipulation where the business domain expert can supply data in a state useful to the relevant developer - who we hope is an expert!

Manually capturing data is often cheaper than having a developer build import classes however it does remove a lot of the human error risk. Once an import procedure is well defined and tested it can be re-used so it depends on the budget and how complex the spreadsheets are.

My ‘ExcelDocumentDemo‘ project is a simple console application that imports excel documents and maps them to sample class called ExcelDataModel

EPPlus (Excel 2007/2010+)

I am a massive fan of EPPlus as its very easy to manipulate .xlsx documents.

This is a great article on codeproject.com by Debopam Pal explaining how to get up and running with EPP.

My class ‘ReadXLSX’ is based on such articles and simply maps the excel data to a List of ‘ExcelDataModel’

Microsoft.Office.Interop.Excel (legacy office 97/2003)

My class ‘ReadXLS’ is for legacy offce 97/2003 support AND OR stubborn users that remain fond of these older .xls excel documents, these should be migrated over to the newer .xlsx format.

Although older .xls files are widely depricated its not a bad idea to know how to at very least read from them. My class ReadXLS maps the excel data to a List of ‘ExcelDataModel’

Example Results

Both .xlsx and .xls sample files are included in my solution:

This is the spreadsheet data viewed in excel

Excel Document

Once mapped this is the data as seen from Visual Studio

Excel Data

References