Excel From C#: What I did.

Picking up where last week’s introduction left off, I’m going to explain how I added Excel export support to one of my products.

As a little background, in case you missed the introduction, I had a couple of feature requests from customers which involved exporting to Excel. The most important was a simple data table export, the files were probably going to be imported into other systems, so no formatting was necessary, and compatibility was the priority. The second was less important, but it was going to be viewed rather than processed, and relied on the formatting of the output.

A little investigation had left me with a short list of options:-

  • Automation via Interop
  • Crystal Reports
  • Excel Compatible HTML
  • CSV
  • Learning the Excel File format
  • Buying a component

Automation and Crystal Reports were both viable, but I ruled them out quickly because of the Excel dependency and the additional distributables respectively. Obviously, learning the file format wasn’t going to be a sensible use of my time, and CSV files definitely weren’t going to be suitable for the formatted export.

This left me with Excel compatible HTML and a third party component.

The HTML was a perfect choice for the formatted export, as the files could be opened either in Excel or in a browser. Also, it wasn’t a major operation; I just wrote my own code to generate data in the desired format, and then wrote some code to write an html file with the suitable style and table structures.

For the simple table exports, I found the perfect component in Val Mazur’s xPort Tools. At just under $70, the price was great, the support from Val was friendly, prompt and effective, and I managed to get it to do everything I needed in a very short period of time. Once the component was added to my application, I just had to feed it the DataTable or DataSet I wanted to export and it produced the Excel file I needed.

I’m very happy with the resulting features, everything that was requested is in there, and the invested time and money were well within my initial targets. Mission accomplished.

Now, I’ve got to be honest here, this was a more organic decision than these two posts might suggest. I probably wasn’t objective and sensible enough throughout the decision making process to be able to say with 100% certainty that I made the perfect choice. In the spirit of openness, here’s a little public post-mortem on my decision making process.

Some Obvious Flaws

  • Offering to do the heavily formatted export in the first place was probably a mistake. I didn’t really take enough time to analyse the amount of work involved, or how significant a feature it would be for the software. I just thought it looked like it would be fun, and figured that as I needed to do Excel exports anyway, it would be a good idea. This was also a clear cut case of trying to please a customer by giving them what they ask for, which is rarely what they really want or need.

    Had I stuck to exporting some simple tables to an Excel compatible format, I could probably have made a lot of customers happy with a simple CSV export.

  • I didn’t really give the CSV option enough consideration throughout. Even after I chose to go ahead with both features, I could still have made a lot of people happy with a CSV file for the basic table exports. Had I been working on a larger team, and had the cost of the component been less negligible, I might have struggled to justify the decision. I was a little blinded by the potential cross-over between the two features, and I didn’t backtrack and re-evaluate enough when circumstances changed.

    Overall though, exporting to native Excel files is a useful ability, and I’m happy with the decision to use the external component.

  • Writing the code for the HTML export probably wasn’t the best use of my time. I rushed into this before I realised that the Excel export files probably weren’t destined for Excel, and therefore HTML wouldn’t be a good solution for the table exports. Had I given it more thought, I might have decided that an Excel export component with formatting options would have been worth the extra investment. Or, I might have gone back to the customer to find out if the export could be implemented as a report, or even if what they actually needed was something else altogether.

Conclusion

Clearly, I’m happy with the outcome. The new features worked as I’d hoped, and everything came together on schedule. Beyond that, I enjoyed working on the features I developed, and I think the balance between what I built and what I bought ended up about right.

I’m not so happy with the decision making process, this could easily have gone really badly, and had it been a larger project I could have wasted a lot of time and money. Obviously, this was never going to be a big project, so I’m not going to be too hard on myself for the way I went about it, but I think I can still learn some useful lessons from the experience.

If there’s any interest, I’m willing to write more about the Excel HTML export, explaining how to do it, anything to watch out for, or even providing some source code.