Easy Excel Interaction with EPPlus (Part 1): The Basics

Monday, June 16, 2014 @ 09:49
By Corey Adler

Without a doubt one of the worst programming tasks I’ve ever had in my career was the first time I tried tackling an Export to Excel function. It seemed simple enough to me; after all it was Microsoft Excel and I was using C# (a Microsoft supported programming language). How hard could it possibly be? It turns out that it can be quite difficult. Between trying to figure out the difference between ‘Value’ and ‘Value2’ for a field, and trying to just grab the correct range of cells it was a confusing nightmare. I ended up giving up on it entirely. Fast forward a couple of years and I’m tasked to create an Export to Excel function again. Only this time it’s in a professional setting, so I can’t just give up. That’s when I found the EPPlus project (http://epplus.codeplex.com/), which makes interacting with Excel an absolute cinch using the Open Office format. So, for this series of posts, I’d like to take you on a guided tour of many of the features of EPPlus, and how it can make your import and export functions a real pleasure to implement. 
First things first: The ExcelPackage class. This is the class you will use for all things Excel. It is the end all and be all of EPPlus. There are 2 great things about the ExcelPackage class: First, it implements IDisposbale. So once you’re done with your Excel function it will happily exit from memory—assuming of course that you’ve put it in a using statement like you should. Second are some of the class constructors that you will find indubitably helpful. Here are the signatures of some of the ones that I’ve found the most useful:

The last 2 constructors allow you to simply point to a file or a stream and ExcelPackage will just immediately load up the file (or even create it if it does not exist yet). It’s really that simple. Or if you don’t want to create the file right now (like if you want to send it through a web service) you can always use the first constructor on the list and just have it hold everything else in memory for you through its Stream property. It’s very useful, and really simple.
        After you’ve set up your ExcelPackage it’s time to setup your workbook, which is a property found on the ExcelPackage class. And by “setup” I mean do nothing because ExcelPackage will automatically initialize that property when you start it up. You can just call the workbook and add stuff to it immediately without fear of getting a NullReferenceException. After that, you can start setting up your workbook’s Worksheets collection. And, yet again, that means do nothing because it will do it for you the instant you make a call to Workbook.Worksheets. Pretty convenient, huh?
       I’ll finish off this basic introduction by showing some of the workbook and worksheet fields that you might want to consider configuring for your particular function. For the workbook, you might want to setup a few of the small details that you typically see on an Excel workbook: The workbook author, title, and comments on the workbook. Setting those things up, much like the rest of EPPlus, is a snap.

Next up is setting up your basic worksheet. As I mentioned earlier, the second that you call the Worksheets property on the Workbook it will initialize the collection, making sure that you can easily call and add to it without having to worry about an exception being thrown. Here then is how you would create a worksheet in your workbook. Note that the call to Add() on the Worksheets collection will take in the name that will show on the bottom of the Excel file as the name of the worksheet itself! No need to play around with different values like in the Excel API. Just pass in a string value, and watch it populate all by itself.

Take a close look at the initialization of the worksheet variable. Notice how it’s grabbing the worksheet at position 1? That’s because, for many things in EPPlus, the arrays are not zero-based! They are actually one-based. If you try to use the zero-index you will get a lovely exception message telling you about your mistake. Afterwards you can start setting up some of the lovely properties found on the ExcelWorksheet object—which I will discuss in the next post. I’ll also show you some basic tips and tricks to do when dealing with worksheets in EPPlus, and start getting you setup for a simple export from Excel function. Until then, I wish you good coding!