Is curious that we need to explain how to export an XML file from Excel, something that should be easy and painless… but reality again surprises us and im here explaining something that is very useful in development world and someone in Microsoft thinks that the best way to implement this function in excel is doing a lot of work, aparently, unneeded.
Sometimes, we will not use a Database to store information of our product, because sometimes the data is static, nothing enters and nothing leaves, so, why use conectors and queries if we just put everything in a XML file and later parse it to obtain the information we need?, the answer is obvious, but generate that XML file could be really painfull, even more, if all the static data is stored in a precious Excel Workbook.
If you search (like i do), you will notice that Microsoft and a lot of people suggest us to download some addin of Microsoft Excel 2003 and blablabla, but nothing about Office 2010, and, if you’re using a 64bit Windows, the super method of the addin will be useless, because the macro is not implemented for 64 bit operating systems.
Then, after all the introduction, let’s do the famous XML file from a Sheet (word-game here).
1. The Data. We will use this simple information:
2. Create a Template of the XML which you will use into a text file, save it with the .xml extension:
<people> <Person> <Name></Name> <Age></Age> <Phone></Phone> </Person> <Person> <Name></Name> <Age></Age> <Phone></Phone> </Person> </people>
Notice that we need to add two registers inside the main node of the XML, cause if we don’t do it, the generated XML only will have the first row of data.
5. From the right panel, click on XML maps button, and Add in the windows that just show up. Select the .xml file that we created in the second step. If a warning shows up, just discard it. Click on Ok button to get back to the Worksheet and the Panel.
6. Now we will map the information for every column that we want to be in our final XML, so, in the right panel, right click an element, and select map element. Select the entire column (including the header) of the corresponding element. It adds a filter on it and that’s all, the element now will have data when we export the XML. Repeat for all the elements of the XML. If you don’t map data to the element, it just will be blank when you export.
And that’s all, now, you can use your favorite way to parse this XML in your application.