By Corey Adler
This post is the fifth in a series discussing EPPlus, an open-source tool designed to make it easy for your code to interact with Excel. The first post dealt with the ExcelPackage and ExcelWorkbook classes. In the second post I discussed the ExcelWorksheet class. The third one discussed the ExcelRange class—responsible for most of your typical Excel interactions. In the most recent post I discussed various page manipulations, such as Styles and Formulas. Now, finally, after discussing all of those prerequisites it is now time to discuss doing an Export to Excel function. My intent here is to utilize most of the methods that were discussed in my previous posts, and combine them together to show you how they all interact to create a wonderful and professional looking Excel sheet.
Before we get started on making an export, we will need actual records to export, as well as an application that we can call the EPPlus methods from. For my example I am using SQL Server 2012 to hold the data, Visual Studio 2013 to create a solution with both a WinForms and MVC project, and Entity Framework 6.1 (using Reverse Engineer Code-First, which I’ll talk about at a different time) for my ORM. I only have one table—PersonData—with 5 fields, shown in the picture below. The Birthday and TotalSales fields will get their own special treatment later on, which is why I have included them in the random seeming table.
DATABASE IMAGE HERE
THE DATA IMAGE HERE
Both the WinForms and the MVC application contain the exact same thing: A simple page containing all of the records found in the database, as well as a button to export the records to Excel. The reason I have both a Desktop and Web application setup here is because there is a slight difference in exporting to a Desktop application vs a Web application, which I will discuss later on.
Now onto the actual export function. Let’s take this one step at a time, using what’s been done in previous posts.
The method call, shown above, is a very easy of generating a basic Excel file, which is shown below. Assuming that you’ve read the previous posts in this series, these lines should look familiar to you. The ExcelPackage gets either a passed in Stream or creates a MemoryStream, the Workbook is setup with some basic information on the file, and a Worksheet is generated by calling the Add() function. Right after that, notice the use of ExcelRange’s LoadFromCollection function to load all of the data grabbed from the database into the Excel file at location [1,1] (and remember that the Boolean flag at the end is to show the headers!).
You should instantly see some of the drawbacks with the use of LoadFromCollection. The columns are too narrow, TotalSales has no space in between the words, the TotalSales column is not registering it as being money, there’s no styling with the headers, and the Birthday column is completely out of whack with reality. So let’s remedy these issues, adding in number formatting, column formatting, and adjusting birthdays.
Above are the new lines that have been added to the method. You’ll notice that I’ve removed the LoadFromCollection method call here. Since that call relies heavily on using reflection to get the values, and doesn’t automatically recognize when a column header is actually supposed to be 2 words, I switched to doing it manually. Even though it’s manual, you may notice that it’s still not that many lines of code to see a much nicer product, as shown below. I’ve also added a variable called recordCount that is the number of records found in the personDataRecords collection. This will come into play again a little later. I’ve also included the number formatting for the Total Sales column (using Excel’s number formatting rules) and the use of AutoFitColumns to increase its column size to a minimum of 15 (as opposed to 10 for the other columns). You might ask why the loop is currently using index + 2. That’s because, while Excel and EPPlus use one-based arrays, C# (and the personDataRecords array) are zero-based. Also, the fact that the header row exists require that the value be changed at a row 2 more than the index.
Ah, that’s a little better. The headers have been cleaned up, and the number formatting has taken hold. You may also notice that the Birthday issue was solved. It turns out that the problem with using DateTime objects is not a C# or an EPPlus problem. It turns out it’s an Excel problem. Per a blog post that I found when researching this issue (found at http://bit.ly/1vEw273): “Excel treats all dates as serial numbers. It then uses formatting to present the serial number as a date. Normally, when you enter a date Excel recognizes it as a date, and formats it automatically. However, Excel actually stores the serial number the date represents. Clearly, this didn’t happen in this example, so you’ll have to format it manually”. In order to compensate for this I decided to use the ToShortDateString method found on the DateTime property.
Now we have a spreadsheet with proper headers, all the data, and it all fits on the page. There’s really only one thing left to do: Style it. So let’s put in some styling, shall we? Let’s put in 3 different styles here: 1) Obviously let’s style the headers so that they look more professional (and so that we can see right away that those are headers. 2) Let’s highlight and bold in red the salesman with the lowest sales (to identify a weak spot). 3) Let’s highlight a “Top Performers” group— those who had over $50,000 in total sales. We’ll highlight them green, and style it in Italics. So here’s the extra code that we’ll need:
Let’s take a look at how this code is accomplishing the stated objectives, shown in the picture below. First off, notice the use of the ExcelRange class from A1:E1 (for the headers). This code sets the background color (after setting the fill pattern type first!), the font, font size, font color, horizontal alignment, as well as a bottom border to really set off the header row from the remaining rows. You can see just how much cleaner and better the headers look from it (and how easy it is to accomplish something like that). Second are the usages of style that are in the loop under different ‘if’ blocks. Setting the color and font style are easily done to accomplish the goal. The final thing added to the code simply makes it easier to distinguish one line from another by adding a thin border on the bottom.
So now we are getting the data, we’ve made sure the columns look good, that the numbers look good, and that we’ve styled the whole sheet to our liking. The last thing that I’ll introduce is the simple use of Formulas to round it all off. I’ll include the code for 3 different formulas: 1) Total from the Total Sales column. 2) Total Sales of all Top Performers. 3) Percentage of Total from Top Performers. Here are the few lines of code that need to be added (after the loop, of course!).
As you can see from the code it is easy to implement formulas, as long as one is aware of a few potential slip ups: First, since it’s an Excel formula, there’s no way to debug it in C#. Second, make sure that you have the cell properly formatted if it uses numbers. And third, to always make sure that you have the parentheses in the string correctly matching. I’ve even included the use of FormulaR1C1 here as well, so that you can see its proper usage. In English that line says the following: The cell’s formula is the value 1 row up (same column) divided by the value 2 rows up (again, same column). Although you might think that I should multiply it by 100 to get the percentage, keep in mind that the number format (that of the percentage) automatically assumes that it will be coming in as a decimal, and will do the multiplication for you. The result speaks for itself:
So, there you have it! A properly formatted, nicely styled, formula filled Excel export using many of the methods and fields that we’ve discussed in previous posts. Now it’s time to save it.
It should be fairly obvious from the above pictures precisely what the difference between saving the file in the desktop application vs. the MVC application. In the MVC application, one should use the Response object to send what will end up being a MemoryStream object to the user. Attaching the stream to the Response will automatically generate a dialog box asking the user where to save the file. In contrast, the desktop application requires the user to first enter the information which it then uses to create the FileStream object that gets passed into the ExcelService. There are other ways of saving it in a desktop application, but this is the one that I’ve tended to use.
I’ve included a zip file of the solution (34 MB) and the Excel files (1, 2, 3, 4) for you to download and have fun with. My next post will deal with using EPPlus for importing records from Excel to your database. Until then, I wish you good coding.