In a couple of instances recently I’ve had cause to take data that exists in an Excel spreadsheet and save it out as XML and, whilst it’s slightly off-topic, I thought I’d share as I find it “enjoyable” 🙂
I don’t mean as a .XLSX file but, rather, as an XML file according to my own schema.
Let’s say I sketch out some XML file format by running VS and coming up with something like;
<videos> <video title="Using Excel for XML Editing" author="Mike Taulty" duration="120"/> </videos>
I can then get Visual Studio to infer a schema from this giving me something like;
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="videos"> <xs:complexType> <xs:sequence> <xs:element name="video" minOccurs="1" maxOccurs="unbounded"> <xs:complexType> <xs:attribute name="title" type="xs:string" use="required" /> <xs:attribute name="author" type="xs:string" use="required" /> <xs:attribute name="duration" type="xs:unsignedByte" use="required" /> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>
I know that’s a very imprecise science (schema inference) but I don’t really mind here although I did tweak it just to add the minOccurs and maxOccurs facets on there.
Now, I can drop into Excel and create a new spreadsheet and on the Developer tab I can associate my schema with the sheet by clicking “Source”;
and adding an XML map;
and then dragging and dropping items from that map to the spreadsheet;
to the spreadsheet cells that I want to associate with that schema and “BOOM!” 😉 I get;
and then I can just edit my XML;
and then click Export from the Developer Tab;
and save the resulting XML. Cool! Nice and easy way to create XML. As an aside, you can also import data this way ( with the import button ) and I recently found that to be really useful because I wanted to gather all the details of the videos that we have published on the UK MSDN site into an Excel spreadsheet for discussion.
I was about to write some C# code to do this when I realised that I could just do Import from Excel, point it at the live RSS feed and it would just magically drop that into a spreadsheet for me with the XML association. Very neat.
By the way – if you’re missing the Developer tab in Excel you need to go to the Office Button, hit the “Excel Options” button;
and then go and select to show the developer tab;