Wednesday, September 08, 2010

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 2nd 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

12 comments:

infocyde said...

Thanks for posting this, just what I needed.

stooni said...

Thanks for this Post,

Anonymous said...

The example is fine if you want to export all line items into xml, however if you want to create an xml file with non repeated header items I assume that's not what excel is able to (tried a lot but no success).

MSD_SI said...

Is there a way to only export a single row of data instead of the entire XML data table? I'm trying to populate a fillable PDF form (which I know how to do), but I'd like to only export only single rows of data vs the entire table.

Adrian Hayes said...

Thanks! Cursing and lamentations eased by your post.

Anonymous said...

Hello,
Can one excel document be exported into multiple XML document?

In other terms, can each line of the excel document generate a seperate xml document VS each excel line generate xml code into one big xml document.

Thank you

Jucarii said...

can such an export be made dynamically ? ( not via Developer -> Export )

Jucarii said...

can such an export be made dynamically ? ( not via Developer -> Export )

Derek Keogh said...

Thanks, easy to understand and it works!

Derek Keogh said...

Thanks, this is simple to understand and it works.

Derek Keogh said...

Thanks, easy to understand and it works!

Anonymous said...

Thanks! This helped me a lot!