Exporting Excel 2010 to XML

After many expletives and banging of keys, I finally found that it is really easy to export an excel sheet to XML – that is as long as you remember to;

 

1-      Turn the developer tab on in the ribbon

2-      Create a valid XML file to map to (the special word is valid, it might be valid to us mere mortals but Excel is an odd beast)

3-      Map it up

4-      Export – no really that’s it

 

 

1- Turn developer bar on

File -> Options -> Customize Ribbon -> From the Main tabs list on the right tick the developer tickbox -> OK

 

Once this is turned on go to the developer tab and click the Source button.

A new panel will appear (empty probably) this is where the XML file will be once it has been mapped.

 

 

2 – Create an XML file

 

<services>

    <Service>

        <code></code>

        <description></description>

        <comment></comment>

    </Service>

    <Service>

        <code></code>

        <description></description>

        <comment></comment>

    </Service>

</services>

 

The important bit to note is the 2^nd instance of the service node, if you only have a single instance, excel only allows you to export the top row of your data….. hmm a bug maybe?

Save your  XML mapping file somewhere easy to find (don’t delete this as you will need this if you ever need to export the data again, it’s not stored as part of your workbook)

 

 

3- Map it up

 

Back in Excel, Click the XML Maps button from the XML panel and select an XML file you just created, if asked click yes on the schema message box (saves time unless you want to make your one).

Once the mapping structure is in right click on any of the nodes and click Map Element, now select the range of data from the excel sheet you want to populate your XML file.

If your nodes and columns are ordered the same, you can right click on the top level mapping node and map the whole XML file to all of your columns in one go.

 

 

4- Export

 

Now click the Verify Map for Export, if all went well Excel will now be able to export your data using your XML mapping file via the Export button in the developer tab.

 

 

goodluck