Shout out to EPPlus

I just have a quick shout-out to someone who doesn't know they helped me out with a project. I needed a way to generate an Excel spreadsheet from inside a c# application. My client for the project wanted something more detailed and more usable than a .csv export file, because the single page of data was, to use the database lingo, not normalized. I had data repeated all over the place, and it was hard to read.

After a quick web search, I found EPPlus, which did exactly what I needed. I wanted to both write the spreadsheet to a file, and to build it in memory and attach it to an email which my software sends in the background without opening up an email client. The first I figured would be easy, but the second required writing to a stream, and EPPlus was already set up to allow for both files and in-memory streams. A few hours of data layout on multiple pages, and voila! I have formatting, sorting, column sizing, and as many pages as I need. Alas, I don't know how to build a pivot table worth beans, but EPPlus does that, too, according to the docs.

Their sample code project was really helpful to look up things like formatting and sizing of cells. The whole update to my code got me thinking of how you can do really cool stuff by intersecting multiple areas of expertise. Mix computer based testing with Excel and email, and you can end up with something pretty slick. It's the sort of programmer candy contract coders thrive on.

C# Version Numer Tip

Have you ever tried to store data in the application properties in C#? It has some really slick abilities to store settings for you. I use it for window sizes and things like that. All you do is add data to your settings (usually found in Properties/Settings.settings in your project), then reference them. You start off by getting your application set up to use settings with this "using" statement:

using System.Configuration;

You read and write them like this:

internalValue = Properties.Settings.Default.MyNamedSetting;
// modify your internal value.

Properties.Settings.Default.MyNamedSetting = internalValue;

The tricky part is that every time you update your application version number, these settings are left behind and it creates a new set for you. How do you get around such a problem? By using a couple of the more obscure features of Properties.Settings.Default.

First, you need a way to detect when it's a new version. You can do that by creating a boolean settings entry named something like FirstRun with a default value of true. You can tell if it's your very first time to run the application at a particular version number because it will have the default value of true. Then you can set the value of FirstRun to false. By using the special built-in Upgrade() function, and saving the results, you can import your previous data just like this:

if(Properties.Settings.Default.FirstRun)
{
    Properties.Settings.Default.Upgrade();
    Properties.Settings.Default.FirstRun = false;
    Properties.Settings.Default.Save();
}

There. Now you have a system that automatically detects version bumps, and saves all your special settings from one version to the next.