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;