Saturday, 18 March 2017

XML Templating

Working with XML to do complicated things quickly is great, but creating the XML files can be a pain if you don't work with excel.


So here is how to do that much easier, first of all you need to create an xml file, and they you need to create at least two sets of code so that excel can see that the structure is consistent, once that is done you can import the file to excel to add the data.

So step one create an XML file.
Here is my example file,

<?xml version="1.0" encoding="utf-8"?>
<Computers>
    <Servers>
<Price>350</Price>
<Brand>Hewlet-Packard</Brand>
<Model>ML350</Model>
<Color>Silver</Color>
    </Servers>
    <Servers>
<Price>300</Price>
<Brand>Dell inc</Brand>
<Model>PowerEdge R730</Model>
<Color>Carbon Black</Color>
    </Servers>
    <Servers>
<Price>400</Price>
<Brand>IBM</Brand>
<Model>IBM Lenovo x3650</Model>
<Color>Carbon Black</Color>
    </Servers>
</Computers>

Once you create and save the template, using whatever editor you like, personally it was notepad for me, you'll need to open the data.xml in excel.
Be sure to change the extension type to XML or you won't find it.
Another option is to right-click on the file and choice open with excel.

Once you open it your going to get asked a few questions, first one being, how do you want to open this file, the answer you want is As an XML table.

Next up it will tell you is that the XML file source doesn't refer to a schema so it will create one based on the example in the file.


This is all fine and the file once open will have whatever data you entered into your example XML, however, only columns with data will show in excel so don't freak out if the other coding elements don't show as they are still there.


Last but not least when you save the file make sure you use the XML extension as excel by default will try to save it as an excel file.


And that's it, you can now create very complex data sheets using excel in minutes, without the effort you would have in coding them.

No comments: