By Corey Adler
This post is the 4th 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 this edition, I will be discussing some tips and tricks in regards to the Style, Numberformat, and Formula properties—which you will be using quite often. These are often essential when dealing with Excel, and I hope to show you how easy it can be to implement these key features.
The Style property allows you to adjust the look of anything within the cell, including, but not limited to: Font, fill, alignment, and border. Each of these features has their own separate class which includes a bounty of other ways to style your Excel sheet. Some of the examples of these are listed above, including changing background colors, font sizes, and fill types. Each of the various functions and properties are simple to use, and do exactly what they look like they should do. One tip that needs to be pointed out with Style is that, should you decide to do a fill, you must specify the pattern type BEFORE you do anything else. If you do not do so, a friendly exception will pop up telling you of your mistake.
The Numberformat property, while technically a part of Styles, is mentioned separately here because of its own complexities in implementation. There is only use for the Numberformat property—which is to set its Format property in order to format the contents of the cell. The formatting is based on Excel’s formatting styles, rather than those typically found in C# (like the different formats that can be done using the ToString() method). You can set it to be a regular number, a currency, or a percentage with little difficulty. One thing that cannot emphasize enough: Make sure you’re implementing this across the board with all of the cells that contains a number, and make sure that it’s absolutely correct. While the export will work and exit out without exception, you will notice an error when you try to open the file saying that your file has unreadable content. If you do see that message when you open up the file, start looking closer at the formatting of numbers and dates, because EPPlus is telling you that something is wrong.
For my final topic, I would like to discuss implementing formulas, using the Formula property. The Formula property allows you to set a specific range of cells (most likely only one cell) to some calculation of other cells present. You may set it to any typical Excel function, and it will work exactly as you intend it. 3 examples are shown in the picture above, including the use of the SUM and SUBTOTAL functions. This can be very handy to have, especially when you need to report on some financial data, and want to have the final numbers there for you right after you export, without having to add the formulas in yourself. You may also notice the property on the last line of the picture, that of FormulaR1C1. There is, as you can see, one fundamental difference between the two: Instead of using the regular Excel row and column notation, it uses notation based on specific rows and columns (like “R4C1” which would be the same as “A4”), and will even calculate those dynamically for you. So that call is stating that the value located in that range should be the value of whatever is found 2 columns to its left multiplied by the value found 1 column to its left.
There a few tips I have for you while using this property. First, understand that there is no way for this formula to give you any sort of error while the export is being run. So be sure to double check those formulas, and make sure that you have no typos! Second, you’ll oftentimes find yourself doing those formulas in dynamic locations, based on the fact that you may not have a fixed amount of rows or columns. I always find it extremely helpful to just always keep a running tab on how many rows and columns I have on my sheet, in case I realize that I have a need for a dynamic formula later on (like by totaling expenses). Third, it might behoove you to put in a comment over each formula that you write explaining what exactly that formula is for. The next programmer who looks at your code may not instinctually know that that’s the location for Total Gross Sales. Confusion can often lead to preventable mistakes.
Now that all of the basics have been covered by these last posts, it’s time to start implementing your first Export function with EPPlus! The next post will cover that, showing different tips and tricks for returning a properly done, good looking Excel file from your application—both on the web and on your desktop. Until then, I wish you good coding.