Easy Excel Interaction with EPPlus (Part 2): Worksheets

Monday, June 30, 2014

Easy Excel Interaction with EPPlus (Part 2): Worksheets

By Corey Adler
In my last post I introduced EPPlus, an open-source tool for having your code interact with Excel, without having to use Microsoft’s horrendous API. Not only can you find the code for it on CodePlex (http://epplus.codeplex.com), but you can also install it in your solution directly as a NuGet package. I also discussed 2 of the main classes that you will be dealing with when using EPPlus, namely the ExcelPackage and ExcelWorkbook classes. In this post I will discuss using the 3rd of the main classes found in EPPlus: The ExcelWorksheet class. I will focus primarily on those methods that you are most likely to use in each class.

The ExcelWorksheet class has 2 primary functions: First, to configure the actual sheet that you see on the screen. Second, and more importantly, it gives you access to the individual cells in the worksheet—which we will discuss when talking about the ExcelRange class later. For the first category, you’ll probably need to use the DefaultColWidth and DefaultRowHeight public properties—which should hopefully be self-explanatory. Most of the clients that I’ve had to do Excel exports for have had their own particular requirements for their worksheets—so be sure to touch base with your client about these settings. Note that these do not affect word wrapping or allow your cells to automatically expand. Those are only done at the Cell level. Although you’ll tend to access the cells through the Worksheet class, it is important to remember that the Worksheet by itself is all about formatting.

Worksheet Defaults

One other cool feature found in the ExcelWorksheet class is the ease of inserting or deleting rows from the worksheet. Both InsertRow and DeleteRow allow for adding or deleting multiple lines from the worksheet with a simple method call. The signatures are displayed below. For InsertRow, you choose which line to insert the new row—and remember that it’s one-based—and then how many rows you want to add. You also have the option to copy the style from a specific row, if you so choose. For DeleteRow, you choose which row to start deleting from and how many rows to delete. The second signature for DeleteRow can be a little misleading, however. It seems simple enough: Should it then shift the other rows up to compensate? However, EPPlus’ default behavior is to shift the rows up. Calling that second signature with a value of true is handled entirely by calling the first signature. I should also mention that for both InsertRow and DeleteRow EPPlus will automatically shift formulas and merged cells up or down as well when they are called. No muss, no fuss.

Insert Delete

The other important thing in the ExcelWorksheet class is the ability to manually access your column and row settings. Although the columns and rows each have their own separate class, I’m including them in the discussion of ExcelWorksheet because there is no collection of them contained in the Worksheet class. The only easy way to access the columns and rows is one at a time (you can access them through manipulation of the ExcelRange class, but I recommend not doing so unless it’s absolutely necessary).

Column and Row Features

Some of the common features regarding columns and rows are shown in the picture above—which shows, once again, why this library is super easy to use—as opposed to the Microsoft Excel API. Both columns and rows can easily be hidden, set to allow the text to wrap should the cell need, locked from editing, or marked as a page break—simply at the push of a Boolean flag. The other thing to note is programming the Style and NumberFormat for a specific column or row. There are many tips and tricks for getting these just right (and without errors popping up later), but I will save those for the discussion on the ExcelRange class, as they will be used more often with cells than with entire columns or rows. Although one would immediately think that this would not be so—say, if you have column full of money values that you want to have a default format for—consider the fact that the column will, inevitably, have a column header that may not fall under the same style or number formatting required for the rest of the column. It is, therefore, a better practice to do so with the Cells collection (and the ExcelRange class) on the worksheet.

One final thing to note about Columns which is not the same as for the Rows are the 3 different method signatures for AutoFit, shown in the picture below. AutoFit’s main purpose is to automatically set the width of the column to match the content found in that column. The different signatures accomplish this in different ways.

Auto Fit

The first call, which takes no parameters, will rely on the Worksheet’s value for DefaultColWidth for what the minimum width of the column should be. Do not forget to set a value first! Otherwise you will indubitably receive a friendly exception message telling you all about it. Believe me; I’ve learned that one the hard way. The 2nd signature will override the minimum width that was set with DefaultColWidth, and will use only the value you passed in for it. The 3rd signature allows you to set a maximum value on the column width, which I’ve often found necessary. Be careful when using AutoFit, though—in all cases any columns or cells that are wrapped or merged will be ignored, and the style will not take hold.

I will conclude by briefly mentioning some of the methods found on the ExcelWorksheet class that I’ve decided not to discuss here. Since my intent with this series of posts was to show an easy way to do import and export functions with Excel, I will not be discussing the ability to add drawings, charts, or pivot tables to the worksheet. I may, in the future, come back to discussing that, but that will be for another time. Next time I will be discussing the ExcelRange class—possibly the most important one that you will need to master when using EPPlus. Until then, I wish you good coding.