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.

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

Now the Developer is visible

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

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

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

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.

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.

8. Let me populate the sheet with some data.

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.

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

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.

Excel enables you to import your own custom XML Schema or public XML standards such as RosettaNet and bind the elements to a particular cell. Excel 2007 take one step further with the ability to detect detail line items and apply table formating as well as filtering when you drop the detail line onto the spreadsheet as below.

However there is a rule apply here, you must define the minOccurs and maxOccurs attribute in the element level (example here would be LineItem)

if you left out the minOccurs and maxOccurs attribute, Excel will not able to determine the element is a detail line.