Easy Excel Interaction with EPPlus (Part 3): The ExcelRange Class

Monday, July 14, 2014 @ 03:05
By Corey Adler

This post is the 3rd in a series of posts designed to show how easy it is to use the EPPlus library (found at NuGet or at http://epplus.codeplex.com/) to have your code communicate with Excel, specifically in regards to importing or exporting data. In the first 2 posts I’ve discussed the basic setup of an Excel file using this helpful tool, including the ExcelPackage, ExcelWorkbook, and ExcelWorksheet. This post will be discussing the most important part of any dealings with Excel: The cells themselves, accessed via the ExcelRange class. We will take a look at the main constructors, as well as some of the functions that you will be most likely to use.

In the picture above you will see the 3 main ways to utilize the ExcelRange class. The first one takes a direct address, exactly how you would actually reference a range in Excel (like “A1:B4”). The second one is used when you know that you only want to access one cell, seeing as how you must give it only 1 row and 1 column value. The third one helps you grab a range of cells without having to formulate it in the traditional Excel format. You just specify a start point and an end point and it will return all of the cells in that range. No matter how you tend to process Excel, there is a flavor made just right for you. In addition, the ExcelRange class, like many of EPPlus’ main classes, implements IDisposable. So once you’ve finished using the range—assuming you’ve place your call in a using statement—it will be removed from memory, making sure that your experience is smooth and quick.

Now that we’ve gotten acquainted with the ExcelRange class, here are some of the more important functions that we will be discussing.

The first one is the Value property. This should be straight forward enough: What should be placed in the cells in the specified range? Once this is set, you can step away and not worry about it. Unlike Microsoft’s Excel API, there’s no need to differentiate between “Value” and “Value2”. In EPPlus, there’s just one “Value” field to use. The Copy function copies not only the value but any associated formula or style from one cell range to another.  Keep in mind, though: You would be best served by sending a single cell into that function call. The Copy function uses that ExcelRange as the starting cell to receive the copied information.

The final set of functions that I’d like to discuss in this post involves the different Load functions, a few of which are shown in the picture above. Each of these different functions has multiple overloads, but I’ve decided to only showcase one from each function. Each of these different functions will load the data from a specific data source into the Excel file, starting at the beginning of the ExcelRange specified. These functions can be immensely helpful when creating Excel files, as they do a lot of the work for you. All you need to do afterwards is to style it. LoadFromCollection takes a collection of objects and uses reflection to populate each cell (including the headers, if so desired), LoadFromDataTable is handy with grabbing data straight from the database into the cells, and LoadFromText will take the contents of a CSV file and push them into the file. While each of these methods can be very useful, it’s important to note that no styling or number formatting takes place during these calls. You’re on your own for that, which is why these methods can be impractical if you have a column where some of the values might be numbers and some not. But I’ll save that discussion for the next blog post, which will be on styling, formats, and formulas in EPPlus. Until then, I wish you good coding.