Programmatically injecting Xml data into an Excel 2007 workbook

Question

I need to create an Excel 2007 template workbook and devise the means to programmatically populate it with a dataset. The template workbook should include all of the formatting and boilerplate content that will appear in the final result, but will not inlcude any data rows.

The data injection process must be able to dynamically insert data into the workbook, hopefully without any knowledge of the styling, conditional columns, calculations, charts etc that are present in the template.

I have been experimenting with the new OpenXml Document Formats SDK 2.0 (using C#) and found that it offers a lot of flexibility in reading and manipulating Excel workbooks. However, the more I delve into it, the more I discover that it requires an intimate knowledge of the inner workings of Excel. For example, the fact that tables, worksheets and the calculation chain are all maintained separately inside the package is testing the limits of my understanding. I am concerned that my "solution" might exhibit unexpected behavior in different data scenarios.

So, I am wondering whether there is a more robust approach ...

Given that Excel is capable of binding to external data sources, including Xml data, is it possible that my dataset could be saved as an Xml file and injected into the workbook package file? In other words, is it feasible for an "external" data source to be inside the workbook package? If so, how would I go about defining a placeholder table in the template that would automatically bind to the injected data when the workbook is opened?

Many thanks for your suggestions.

Answer 1

Have you tried doing this with the 'Get External Data' that is built into excel? Data tab of ribbon then in the From Other Sources drop down you can have Excel retrieve information from you xml file. Some experimentation might be necessary to get the formatting down.

Answer 2

You could try creating a custom button (on the ribbon bar ?) to read the xml in, and populate the sheet for you.

VSTO is your friend there. :)

Answer 3

This involves a bit of a different approach, but I've found Apache POI (http://poi.apache.org/) to be great for generating Excel workbooks. I have yet to find an Excel feature I wanted that wasn't supported. It also does not require a very deep knowledge of Excel and comes with a nice library of examples.

I, too, had to convert XML-based data to Excel. I wrote the code that used POI in Scala to take advantage of its wonderful XML-processing library.