Easy Excel Interaction with EPPlus (Part 7): EPPlus 4.0 Beta Features

Tuesday, September 23, 2014

Easy Excel Interaction with EPPlus (Part 7): EPPlus 4.0 Beta Features

By Corey Adler
This is the seventh post in my series about EPPlus, an open-source tool designed to make it easier for C# code to interact with Excel spreadsheets. Up until this point my posts have dealt with learning how to create import and export functions easily using this tool. During the course of writing those posts I noticed that EPPlus had started Beta testing on version 4.0. I’ve decided to take a look, and see exactly what’s changed. This post, the first of two on the beta, will discuss some of those changes, and how they might affect your import/export processes that were talked about in previous posts. In the next post, I will discuss the newly implemented Formula Parser engine, which allows you to create your own, custom formulas in C# for use in Excel.

1) LoadFromCollection

In the 5th post in this series, discussing the actual Export, I used ExcelRange’s LoadFromCollection function to load all of the data in the first example. One of the primary drawbacks to this method, I noted, was the following:

"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."

Now, after looking over some of the new code found in the ExcelRange class, I can tell you, in the words of Oscar Rogers:

The code for LoadFromCollection now currently, assuming you want to print the headers, takes a look for one of two different Attribute types—Description and DisplayName (in that order!). If either of them exists on the property itself it will then grab the string value that was passed into the attribute and use that as the column header value. If it does not find either of those, it then tries and replace underscores in the property name with spaces and use that as the column header value. If no underscores are present, it just uses the property name instead.

2) Delete Row

Back in the 2nd post, discussing the ExcelWorksheet class, I talked about the 2 overloads of the DeleteRow method, as shown here:

A couple of tweaks have been made to this function. First of all, there is a new overload for this function that takes only the row number and deletes that one row—instead of having to call the other overload and just use ‘1’ for the amount of rows to delete. The second change is to the second overload from the above picture. This overload has now been pretty much deprecated. It will now always shift the rows up, even if you send in ‘false’! The XML documentation for that field explains that the field is no longer being used, but just be careful if you do try to use it. It essentially only calls the first overload. I assume that it’s only there for backwards-compatibility, but it’s still pretty much useless now.

3) Date1904

One of the other things included in the beta is the ability in the ExcelWorkbook class to switch storing dates to that based off of an epoch date of January 1, 1904. Confused? So was I, so I did some research on this. It turns out that Excel for Windows and Excel for Mac store dates differently. You might remember from the Export post the problem with the Birthday column from the first example. To better refresh your memory, here’s a picture—with some emphasis added.

See those numbers? Those are generated by calculating the number of days between that date and Excel’s epoch date. That’s what’s stored in the background XML page for any date entered into a spreadsheet. For Excel for Windows, that epoch date is January 1, 1900. For Excel for Mac, however, that epoch date is January 2, 1904. So why are they different? It turns out (per article at: http://support.microsoft.com/kb/180162) that older Macintosh computers did not support dates before January 1, 1904 to "prevent problems related to the fact that 1900 was not a leap year". Any change to this Boolean property will trigger a change in every single worksheet in the workbook to change the dates to be based on the corresponding epoch date. Just something to be aware of, in case you know that you’ll have to deal with Mac users using the spreadsheet.

4) Other Changes

There are a number of other, minor changes included in the Beta. I’ll list some of those.

Changing the Value property of an ExcelRange object automatically erases any formulas located at that range (or individual cell).

EPPlus now checks to make sure that every worksheet name is unique

ExcelPackage, on Dispose, now closes and disposes the Stream object attached to the ExcelPackage, so that you no longer have to outside of it.

ExcelWorkbook now implements IDisposable—so be sure to slap it in a using statement!

InsertColumn and DeleteColumn now exist on the ExcelWorksheet class. Each of these have 2 overloads, matching the overloads for InsertRow and DeleteRow (with the exception of the no longer used DeleteRow overload with the shift Boolean.

These are the main things that I found from looking into the Beta code that might help/change the way that you implement your Import and Export functions in your code. When 4.0 gets fully released, I will go back to see if there are any other additions that I may have missed. Next time I will discuss the brand new custom Formula Parser, and how you can create Excel functions for your exported spreadsheet from C# code. Until then, I wish you good coding.