Excel from C#: An Introduction

A slight change of direction for me this week: a summary of the options available when you need to produce Microsoft Excel compatible output from C#.

I used to write Office-based VBA systems, but it’s been a few years since I really needed to work with Excel. Even so, when a customer requested some Excel export features for one of my products, I focused on compiling the appropriate data and didn’t give the actual export process a lot of thought. Obviously, I was forgetting that built-in Office functions and macros always made moving data between applications much too easy.

I realised I had to get back up to speed, and thankfully there were still plenty of options.

Automation via Interop

Using the COM automation objects to control Excel from C# may not be as straight-forward as it would be from VBA, but it is still worth considering. With Automation you can pretty much make Excel do anything a user can make it do, and I’ve always found that with a bit of trial and error I can accomplish whatever I need to.

Automating Excel is as simple as adding a reference to the COM object library, then using code like:-

Excel.Application appExcel = new Excel.ApplicationClass();
///
/// Do whatever it is you need to do eg. appExcel.Workbooks.Open(filename);
///
appExcel.Quit();

Except it’s not really as simple as that, because C# doesn’t support optional parameters, and because you often need to explicitly cast COM properties to the correct object. So that little Open example could easily become:-

(appExcel.Workbooks as Excel.Workbooks).Open(filename, null, null, null, null, etc.. etc.. );

Anyone who’s worked with the Office object libraries will know how widely used optional parameters are. They will also know that the libraries have changed a little with each version, and that you have to explicitly target the lowest version that you intend to support. Then test it with each subsequent version to make sure that none of the functionality changed.

You also add a new dependency, because the Automation code won’t work if your customer doesn’t have a compatible version of Excel installed. If they wanted the Excel export to load into Open Office, the Automation is worthless.

Despite all this, Automation is always worth considering, and there will be situations where it is the only or the best choice. However, as I was just looking to export some data in an Excel compatible format, I was sure there was a better option.

Crystal Reports

Visual Studio ships with a cut down version of Crystal Reports, and this emerged as a possible solution to my Excel requirements. You can export a Crystal report into an Excel file with a couple of lines of code, so it wasn’t going to be a difficult option.

There’s not a lot of control over export formatting in the cut down .Net version, but you can certainly get the data into a compatible file with very little fuss.

There were just a couple of minor downsides which made me keep searching. For one, I don’t already use Crystal Reports in the application. That meant a significant increase to the installation package for a relatively minor feature. For two, I’m just not a big fan of Crystal Reports, I’ve never really been able to get into it or enjoy working with it.

Excel Compatible HTML

When I remembered that all versions of Excel since 2000 have had the option to use HTML as a native file format, this quickly became my preferred option. It seemed like it would be fun trying to get it just right, and it could be viewed in a browser by any customers who don’t have Excel.

I got way too into this idea, until I remembered that customers don’t always want an Excel export just to load it into Excel or to view it. Excel can be like a codeword for “something that I can manipulate or import into all my other systems”. I realised that just being able to open the file in Excel wasn’t going to be good enough.

In a similar vein, Excel versions 2002 and higher support xml based spreadsheets via the Xmlss format, but I didn’t really look into that as an option.

Why not just use CSV?

To be honest, I don’t have a good answer for this one, I’m sure there were valid reasons that I’ve just forgotten. For now the best I can come up with is that I had my heart set on an Excel export, and, having spent a little time exploring the options, a CSV file would have felt like a cop out.

I had one export planned that would require significant formatting in the Excel file, which explains why I originally overlooked CSV, but it could still have been a valid option for the basic data outputs.

Learn the Excel file format

I spent a minute or two thinking about this, and quickly concluded it wouldn’t be a good idea. All the information is out there, I’m sure it could be fun, but I know this wouldn’t be an effective use of my time. When we talk about Build vs. Buy, there are always grey areas, where it’s difficult to decide one way or the other. This wasn’t one of them.

Buy

This seems to be a perfect scenario to use a third party component. It’s not a core feature, I don’t have any particular expertise in this area, and the component can easily be isolated in the code. If the appropriate component exists, buying could be the most time and cost effective solution.

Fortunately, there’s a lot of choice out there, although that brings a lot variation in price, capabilities and implementation. For between free and $50, I found components that wrapped the COM Automation. Still under $100, I found components to write Excel files. Then, at around $500, I found complete Excel/spreadsheet libraries, sometimes including visual components, sometimes just comprehensive import & export features.

In my case, it was easy to choose which category of product I needed. The $500 libraries went beyond my needs at the moment, and I had already eliminated Automation for various reasons. I needed to find a good, affordable, lightweight component that would just put my data into an Excel File.

Next week: What I chose and how I got on.