Easy Excel Interaction with EPPlus (Part 8): Common Mistakes

Monday, November 03, 2014 @ 08:00

By Corey Adler

This is the 8th and final post in a series on EPPlus, an open-source tool designed to make it easier for C# code to interact with Excel spreadsheets. My previous posts discussed a number of the parts and pieces of EPPlus, as well as how to bring them all together to import and export Excel files in your code. This post will show you some of the common mistakes that can be made while using EPPlus—mistakes so common that I unknowingly made them myself over the course of writing this series of posts! Before I begin discussing this, I would like to openly acknowledge and thank Paragon’s Director of Technology, John Ours, for finding these tips and tricks.

The biggest thing that needs to be understood when dealing with problems in spreadsheets created by EPPlus is the fact that these errors do not always get picked up on during runtime execution of the code. EPPlus is not able, currently, to figure out some of the bounds that would be automatically picked up on while running Excel itself. To that end, here are a couple of the things that you should be aware of:

  1. The name of a Worksheet can only be 25 characters long: That’s right. There is actually a limit on the name of the worksheet. This is something checked by Excel, but EPPlus still allows you to save the worksheet with a sheet name that’s too long. This is something that doesn’t seem that difficult to change, but until they do, be sure to keep your eye out for this issue.
  2. The name of a Worksheet can’t have any slashes in it: Both forward and back slashes are not allowed anywhere there. A lot of other special characters are allowed (like brackets), and are converted like they would be in a typical XML document, but slashes are not allowed due to the fact that the worksheet name is part of a path inside one of the xml documents that is part of the Excel file.

One of the other things to be careful about is that, oftentimes, formatting done by EPPlus will be recognized by Excel as a custom format. This can be especially aggravating when you’re trying to put specific dates into your spreadsheet. Imagine that you’ve finally put dates into your spreadsheet, only to see them be sorted as though they were strings, instead of dates. So how do you tell Excel that the information contained here should be formatted as a date? By implementing the code shown below for proper date formatting (kudos to John Ours for this code).

Excel Date Formatting

As I mentioned in the Export post, Excel actually saves dates as being a number, representing the number of days from Excel’s epoch date to the date inputted. What the ExcelDateValue method above does is to format the date in C# in the exact same way, using a TimeSpan object to create the value that Excel would have normally produced on its own. After that, the only things left to do is to tell EPPlus the correct format—using .NET’s baked in DateTimeFormatInfo class—and to actually place the date (as generated by ExcelDateValue) in the spreadsheet itself. After you’ve done these simple steps, all of your dates should be treated by Excel as actual dates instead of as a custom format.

In your own future dealings with EPPlus I’m sure that you will find other mistakes and issues similar to the ones mentioned above. Should that situation arise, let me tell you of what I’ve found to be the best way to troubleshoot EPPlus. Newer Excel files can be edited in Windows Explorer to become .zip files, and can easily be unzipped to show you the underlying XML used to create the spreadsheet in Excel. By looking at the XML, you might see something wrong with how EPPlus generated the Excel file, and then can work from there to try and make the generated XML correct—you might even be able to adjust the EPPlus source code itself, if need be. As an extension of this method, one other way to see what might be going wrong is to open up Excel, manually create the spreadsheet that you were expecting, and then convert it to a .zip file and compare the XML generated by that file to the XML generated by the EPPlus spreadsheet. By comparing and diffing those files, you’ll be sure to see exactly where the problems lie, and can work on fixing it.

In conclusion, I hope that you’ve found this series of posts on EPPlus to be useful. I’ve found it to be a very handy tool in my coding arsenal—especially with how many times clients tend to want Excel-based reports of their data. I encourage you to delve further into EPPlus, especially into some of the more visual features it has, like pie charts and diagrams, which were not the topic of this series. If you run into any issues with EPPlus, be sure to visit the website at http://epplus.codeplex.com or the EPPlus tag on StackOverflow. I’ll be sure to see you there.

Until next time, I wish you good coding.