How To Export an Excel 2010 Worksheet to XML

In order export an Excel 2010 worksheet to XML you have to add XML Mappings to the file. This process is a bit different from previous Excel versions so here is a step by step on how to achieve this.

For this blog I have an Excel 2010 file containing some news articles that I would like in XML format.

Step 1

Create an .xsd file for the XML Mappings

<xs:element name="NewsItemsTable">
<xs:complexType>
<xs:sequence>
<xs:element ref="NewsItemRow" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="NewsItemRow">
<xs:complexType>
<xs:sequence>
<xs:element name="newstitle" type="xs:string"/>
<xs:element name="newsdate" type="xs:dateTime"/>
<xs:element name="newssummary" type="xs:string"/>
<xs:element name="newstext" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

NOTE:

The .xsd file contains the mappings for our XML. For this tutorial I have added fields for news articles, which match the Excel file I will be using. Note the different types of elements we will be using.

Step 2

Add the developer toolbar to the ribbon

Select File menu, then Options.

On the “Customize Ribbon” tab, check “Developer” tab in the right column.

Step 3

Add the .xsd file as an XML Source

Click the Developer toolbar and select “Source” to display the “XML Source” pane.

Click “XML Maps” to add the .xsd file.

Select the root for the mappings as “NewsItemsTable”

Confirm the XML Map was imported correctly

NOTE:

Be sure your XML Mappings Options are marked to “Automatically Merge Elements When Mapping” and “My Data Has Headings”

Step 4

Map the columns to elements

Select the columns you would like to map in the Excel file

In the “XML Source” pane, right click the “NewsItemsTable” node and select “Map element”

Confirm the columns were mapped correctly

Step 5

Export to XML

On the “Developer toolbar”, click “Export”

You will now have an XML file containing the Excel data with the mappings you defined.