Patrick\’s Bytes

14, January 2008

Excel mapping to XML Schema

Filed under: Excel,OpenXML — patrickyong @ 7:23 am

While OpenXML’s WordProcessingML has the capability to attach a custom XML instance and data bind with Content Controls, Excel achieve similar goal (similar only, not exactly) by attaching a XML schema (from xl\xmlMaps.xml file) and have xpath mapping to cells. Please refer to my previous post, I can even map a sequence of XML elements as a table in Excel. This post is more like a beginner’s guide to how all this work.

1. Enable Developer tab in the Ribbon UX. Click on the Office button and open Excel options.

image

2. Click on the Popular tab, and check "Show

image

Now the Developer is visible

image

3. Now click on the Source button to open up the XML Source task pane on the right.

image

4. Click on the XML Maps button on the bottom to open up XML Maps window

image

5. The click on Add button and attach the XML Schema (.xsd) file you want.

image

6. Click OK at XML Maps window and you will see the XML Source task pane XML tree got populated with the elements of your XML schema.

image

7.  To map the elements onto the Excel sheet, I just drag and drop the elements onto the cell. Should I want to add the item list as a table, I drag the item over.

image

8. Let me populate the sheet with some data.

image

9. Now to export the data I key in just now as a XML instance of Claims, click on the Export button on the Developer tab.

image

10. Give a proper name to the XML file in the Save dialog, then open it with IE.

image 

Wala!! You can see data in the XML file is same as the one in Excel. So you can export this XML instance to any Line of Business application for data processing.

Advertisements

4 Comments »

  1. hi patrick,

    That is a very good example you have given.

    here i have a question related to mapping to List to lists mapping to xml.
    let take a practical e.g. we have a purchase order with multiple line item.

    name: VENDOR1
    date: 1/2/2009

    itemA
    quantity :: 5
    rate :: 10.00
    amount :: 50.00

    itemB
    quantity :: 5
    rate :: 10.00
    amount :: 50.00

    how are we going to do the mapping to it.

    this is the xml code that i have written.

    VENDOR1
    1/2/2009

    A
    5
    10.00
    50.00

    B
    5
    10.00
    50.00

    hope you can guide me.will be of great help.

    regards,
    abhilash…

    Comment by Abhilash — 25, February 2009 @ 2:17 pm | Reply

  2. Hey Patrick,

    Thanks for the example above. I have a quick question though: When I export from Excel to XML, the date format (mm/dd/yyyy) converts to a serial date format (ex: 40449). I suppose the difference between your example and my file is that I applied the mapping to an existing Excel file rather than starting from scratch.

    How did you maintain the date format into XML?

    Thanks,

    K.C.

    Comment by K.C. — 11, November 2010 @ 1:05 am | Reply

  3. How to Export Schema from Excel 2007?

    Comment by Rakesh — 10, October 2012 @ 4:28 pm | Reply

  4. Great Thanks for update this information!!

    Comment by Mubarak — 19, July 2013 @ 4:56 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: