Reading Excel Reports with C#

In a prior post, I discussed how you can use the ExcelXmlWriter library to create dynamic and powerful Excel spreadsheets with just a few lines of C# code. In this post, we'll switch gears and take a look at how you can use C# to programmatically read data from Excel spreadsheets.

Let's assume that you're responsible for helping your team of national salesmen. Your salesmen visit a lot of clients, and they don't have the time to fill out complicated and tedious web forms to build their quotes. They need a way to sit with their clients and draft up a quote as quickly as possible. Excel is a great tool for this!

If you've already mastered building Excel reports with C#, then you'll probably have a report ready to go for your salesman that looks like this:

This is really simple. Your salesman will sit with their client and determine how many of each product they'd like to order. They can also note any special comments in the "comments" field. After a successful visit, your salesman returns with a populated quote that shows business is steady:

The salesman drop off quotes with you, and it's your job to get them imported to the system as sales orders. For this, you can use the LinqToExcel library.

The LinqToExcel library allows you to programmatically retrieve data from spreadsheets and CSV files by using C# and LINQ. In short, the LinqToExcel library will allow you to explicitly map the data rows from your spreadsheet into a collection strongly-typed C# objects. You can read data from your spreadsheet in a variety of ways, but my favorite way is to use the property-to-column mapping approach. You can do this in five easy steps:

1. Import the LinqToExcel library into your project
2. Define the ExcelQueryFactory by mapping to the spreadsheet path
3. Define the signature of your C# entity class that each data row will be mapped to
4. Set up the LinqToExcel column mappings
5. Fetch the data

Import the LinqToExcel library into your project - You can navigate directly to the LinqToExcel page and download the assembly references. However, I prefer to use Visual Studio's NuGet package manager to browse for the package and install it that way. By using the NuGet package manager, installation is as easy as the click of a button.

Define the ExcelQueryFactory by mapping to the Excel file path - After importing the LinqToExcel package, you will establish a connection to your Excel spreadsheet by providing the ExcelQueryFactory object the fully-qualified path to the spreadsheet on disc:

Defining the signature of the C# entity class - After you've initialized the ExcelQueryFactory, you can define the object to which your Excel data rows will be mapped. Each data row in the Excel spreadsheet of our example here has a product, inventory code, date available, cost per unit, quantity, and comment attributes. We can create a C# class with these attributes. Each row of the spreadsheet will be mapped into a class object of the "Product" type.

Set up the LinqToExcel column mappings - After defining your mapping entity, you can focus on setting up the mapping prerequisites for data extraction. LinqToExcel provides many options for reading data from a spreadsheet, and the approach I'm demonstrating here is the property-to-column mapping method. When using the property-to-column mapping approach, you need to explicitly tell LinqToExcel what Excel column is going to map to each class property. Note that each property of our "Product" entity class maps to a column name on our Excel spreadsheet. We define these mappings below:

Fetch the data - After defining the column mappings, you can tap into the library functionality to fetch and read data from your spreadsheet:

A link to a sample project demonstrating each of the above steps can be found on my github page. By downloading and running the simple console application, you'll see that the LinqToExcel library quickly and efficiently parses the data from the provided spreadsheet into a collection of strongly-typed objects that you can inspect during debug mode in Visual Studio:

Great, and wicked-cool! This is very handy if you're looking for ways to quickly extract data from your Excel spreadsheets. In this case, I am able to use LinqToExcel to extract the line items from my spreadsheet and prepare them for import into my sales order workflows. My salesmen are happy.

If you are going to invest any time into learning about this powerful library, here are a few tips to get you started:

• Be sure to trim any leading and trailing space from dynamically read values.
• Remember to check for null or empty values by using the C# String.IsNullOrEmpty() method.
• Be aware of the data types you are reading. Currency values, numbers, dates, strings, etc. have their own values and need to be parsed appropriately.
• All of the above, especially if you are going to use the read information in database lookups or other algorithm processing

In short, this is a very powerful library that offers extraordinary flexibility when it comes to reading data from your Excel and CSV spreadsheets. For detailed explanations on what the LinqToExcel library can (and cannot) do, be sure to check out the official LinqToExcel Project Page on github.

Again, the sample code for this post is available on my github account. Check it out and be sure to post any comments below if you find it useful in your role as an awesome C# developer! Good luck exploring all the cool features of this library!