export 2010 excel worksheet to xml

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

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <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.
 
export excel 2010 to xml
 
On the “Customize Ribbon” tab, check “Developer” tab in the right column.
 
export excel 2010 to xml
 

Step 3

Add the .xsd file as an XML Source
Click the Developer toolbar and select “Source” to display the “XML Source” pane.
 
export excel 2010 to xml
 
Click “XML Maps” to add the .xsd file. 
 
export excel 2010 to xml
 
Select the root for the mappings as “NewsItemsTable”
 
export excel 2010 to xml
 
Confirm the XML Map was imported correctly
 
export excel 2010 to xml
 

NOTE:

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

Step 4

Map the columns to elements
Select the columns you would like to map in the Excel file
 
export excel 2010 to xml
 
In the “XML Source” pane, right click the “NewsItemsTable” node and select “Map element”
 
export excel 2010 to xml
 
Confirm the columns were mapped correctly
 
export excel 2010 to xml
 

Step 5

Export to XML
On the “Developer toolbar”, click “Export”
 
export excel 2010 to xml
 
You will now have an XML file containing the Excel data with the mappings you defined.
 

export excel 2010 to xml

Author

Wiz E. Wig, Mascot & Director of Magic
Wiz E. Wig

Director of Magic

Read more