none
How to inject an external Xml data file into a SpreadsheetML package RRS feed

  • Question

  • I am trying to dynamically create a SpreadheetML package by combining a workbook template (created manually in Excel) with data retrieved from a database.

    The template workbook will 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, calculated columns, formulae, charts etc that are present in the template.

    I have had some success using the OpenXml Format SDK, but the project is getting more complex that I would have hoped, mainly because the data injection process needs to handle many tasks (resizing the table, inserting new worksheet rows, maintaining shared strings, maintaining the calculation chain etc). I am wondering if there is a simpler (and more robust) approach ...

    Given that Excel is capable of binding its Xml Tables to external data sources, 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 stored inside the workbook package? If so, how do I insert the Xml file (using the SDK) and how do I define an Xml Table in the template that will automatically bind to the injected data when the workbook is opened?

    Many thanks for your suggestions.

    Tuesday, October 6, 2009 2:28 PM

All replies

  • i am facing same issue. Any suggesitons??

    Friday, November 6, 2009 10:51 AM
  • @Abhicha,

    After a little research I discovered that SpreadsheetML doesn't support embeded data in the same way that WordprocessingML does. Although it is quite simple to store Xml data within the package, there does not appear to be any way of binding it to a worksheet. What a pity!

    Anyway, I have been working on a solution for the last month and it is almost complete. It is an extension library that sits on top of OpenXml SDK 2.0. Although my library does not directly solve the original problem (nothing will solve that) it makes it very easy to inject database data (or any other data) directly into a worksheet table. The library automatically takes care of all the housekeeping, including:

    - adjusting row and cell references (and other dependent references) when worksheet rows and columns are inserted and deleted
    - storing string data in the shared string table (completely transparently)
    - automatic format conversion between DateTime, TimeSpan and all .Net numeric types and their SpreadsheetML equivalents
    - automatic management of the calculation chain
    - automatic management of hyperlinks (just store a System.Uri value to a cell and it will be rendered as an active hyperlink)
    - many other behind-the-scenes details that are essential to injecting data into a worksheet
    - integral support for all the standard styles and themes that come with Excel

    When time permits, I also intend adding easy-to-use support for other Excel features like charts and pivot tables etc.

    I have a couple of more days of testing and some documentation to complete and then I will post the first version of my library as an open source project on CodePlex. If you are interested, reply again here and I will post a link.

    All the best,
    Tim
    Friday, November 6, 2009 2:48 PM
  • For those who may be interested, I have now completed the initial preview release of the above-mentioned project.

    It is available on CodePlex, complete with API Reference and 40-page user/developer guide, at http://extrememl.codeplex.com/

    Your feedback, positive or negative, would be appreciated.

    Thanks,
    Tim

    Saturday, November 14, 2009 3:36 PM
  • Here's a good article to answer the question, only diference is data source is Word table not database.

    http://blogs.msdn.com/brian_jones/archive/2009/04/01/importing-a-table-from-wordprocessingml-to-spreadsheetml.aspx


    Z.J.
    Tuesday, November 17, 2009 3:51 AM