Clever Geek Handbook

Programmatically Entering Xml Data into an Excel 2007 Workbook

I need to create an Excel 2007 template workbook and develop tools to populate it programmatically using a dataset. The template workbook should include all formatting and template content that will appear in the final result, but will not insert data rows.

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

I experimented with the new OpenXml Document Formats SDK 2.0 (using C #) and found that it offers a lot of flexibility when reading and working with Excel workbooks. However, the more I delve into this, the more I find that it requires a deep knowledge of the inner workings of Excel. For example, the fact that tables, worksheets, and computation chains are maintained separately within a package tests the limits of my understanding. I am concerned that my "solution" might appear unexpected behavior in different data scenarios.

So, I'm wondering if there is a more robust approach ...

Given that Excel is capable of communicating with external data sources, including Xml data, is it possible that my dataset can be saved as an Xml file and inserted into a workbook package file? In other words, is it possible for an "external" data source to be inside a workbook package? If so, how do I go about defining a placeholder table in the template that automatically binds to the data entered when the workbook is opened?

Thanks a lot for your suggestions.

+2


source to share


3 answers


This suggests a slightly different approach, but I found Apache POI ( http://poi.apache.org/ ) to be great for generating Excel tutorials. I have yet to find an Excel function that I wanted to be unsupported. It also doesn't require very deep knowledge of Excel and comes with a good library of examples.



I also had to convert XML based data to Excel. I wrote some code that used POI in Scala to take advantage of my wonderful XML processing library.

+1


source


Have you tried to do this with the "Get External Data" which is built into excel? Data tab of the ribbon, then in the From other sources dropdown list, you can get Excel from your XML information. It may take some experimentation to get the formatting done.



0


source


You can try creating a custom button (on the ribbon bar?) To read the xml inside, and fill the sheet for you.

VSTO is your friend. :)

0


source






More articles:


All Articles