Intro to Excel XML Part 2: Displaying your data

Here's some more information for those of you new to XML or at least Office XML. I mentioned in the previous post that it is possible to leverage Excel's XML file format to display your data in a rich way. There are actually two options here, but today I'll just touch on one of them. I'll show how you can use an XSLT to transform your XML into the SpreadsheetML format, which allows Excel to display your data in a rich way. Another option is to use Excel's built in XML mapping technology which means you can avoid building the XSLT and instead just use Excel's UI to map your data.

Building an XSLT (works in Office XP & 2003)

In the previous post, I showed how you could create a simple XML file to represent some names and phone numbers in an Excel spreadsheet. I also mentioned that if you had this data in your own XML schema, you could create a simple XSLT to transform it into SpreadsheetML. One of my coworkers sent me an example XSLT and XML file to show this, so I figured I'd post it for you all to see. Thanks Ali.

Part 1: Your own XML file.

Let's start with an XML file that represents your data. Here's the one we'll use for this example:

<?xml version="1.0" encoding="UTF-8" ?>
<p:PhoneBook xmlns:p="urn:somePhoneBook">
<p:Entry>
<p:FirstName>Brian</p:FirstName>
<p:LastName>Jones</p:LastName>
<p:Number>(425) 123-4567</p:Number>
</p:Entry>
</p:PhoneBook>

Notice that this file is in a generic namespace "urn:somePhoneBook", and has no Office information in it. We need to teach Excel how it should display this content.

Part 2: Create an XSLT

In the previous post, we created a really simple spreadsheet file where the header row was bold and the data was in plain text below it. Let's create an XSLT that transforms the custom XML from part 1 to look the same as the XML we created in the previous post.

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="https://www.w3.org/1999/XSL/Transform" xmlns:p="urn:somePhoneBook"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

<!-- match the root and produce the requisite root Excel Xml stuff -->
<xsl:template match="/">
<ss:Workbook>
<ss:Styles>
<ss:Style ss:ID="1">
<ss:Font ss:Bold="1"/>
</ss:Style>
</ss:Styles>
<ss:Worksheet ss:Name="Sheet1">
<xsl:apply-templates />
</ss:Worksheet>
</ss:Workbook>
</xsl:template>

<!-- match the PhoneBook element and produce the table and header row -->
<xsl:template match="p:PhoneBook">
<ss:Table>
<ss:Row ss:StyleID="1">
<ss:Cell>
<ss:Data ss:Type="String">First Name</ss:Data>
</ss:Cell>
<ss:Cell>
<ss:Data ss:Type="String">Last Name</ss:Data>
</ss:Cell>
<ss:Cell>
<ss:Data ss:Type="String">Phone Number</ss:Data>
</ss:Cell>
</ss:Row>
<xsl:apply-templates />
</ss:Table>
</xsl:template>

<!-- match an entry and produce a whole row of data from it -->
<xsl:template match="p:Entry">
<ss:Row>
<ss:Cell>
<ss:Data ss:Type="String">
<xsl:value-of select="p:FirstName" />
</ss:Data>
</ss:Cell>
<ss:Cell>
<ss:Data ss:Type="String">
<xsl:value-of select="p:LastName" />
</ss:Data>
</ss:Cell>
<ss:Cell>
<ss:Data ss:Type="String">
<xsl:value-of select="p:Number" />
</ss:Data>
</ss:Cell>
</ss:Row>
</xsl:template>

</xsl:stylesheet>

For those of you not familiar with XSLT, let me quickly describe what's going on. The purpose of an XSLT file is to take an input XML file (which we had in part 1), and transform it into another type of XML file. In this case we want to take the Phonebook XML from Part 1 and transform it into SpreadsheetML for Excel to read. The first part of the XSLT where you see <xsl:template match="/"> is finding the root of your XML file, and it will generate the root element for the SpreadsheetML file. After that first part of the XSLT has run, there is a root element generated for the Workbook, as well as an element for the Worksheet and a Style declaration.

The next part of the XSLT <xsl:template match="p:PhoneBook"> says that for the Phonebook element in the original file, a SpreadsheetML tag called "Table" should be created, as well as a header row that is formatted according to StyleID 1.

The last part of the XSLT says that for each "Entry" tag, a SpreadsheetML row element should be created. In the row there will be three Cells, with the values pulled from the FirstName tag, the LastName tag, and the Number tag.

Part 3: Putting this all together

OK, so you have a really simple XML file from part 1 and an XSLT file from part 2. Go ahead and create a file on your desktop called "test.xml" and paste the content from part 1 into it. Create a file called "view.xslt" on your desktop too and paste the contents from part 2 into it. Now we just need to get Excel to open the file and apply the XSLT.

You'll need to put a Processing Instruction (PI) in the XML file that tells Excel to apply the transform. Here's what your XML should look like if you add the PI:

<?xml version="1.0" encoding="UTF-8" ?>
<?xml-stylesheet type="text/xsl" href="view.xslt"?>
<p:PhoneBook xmlns:p="urn:somePhoneBook">
<p:Entry>
<p:FirstName>Brian</p:FirstName>
<p:LastName>Jones</p:LastName>
<p:Number>(425) 123-4567</p:Number>
</p:Entry>
</p:PhoneBook>

Note that this will only work if you have the XML file and the XSLT in the same place (like on the desktop). Once you've updated your XML file, open it in Excel. You will probably get a dialog asking you if you want to apply the stylesheet. Select that you want to open the file with the stylesheet and hit OK.

You should now have your data in Excel with the formatted header row.

Part 4: Other things to try

Well, now you've learned how to create a view for data in Excel. Go ahead and play around with the source file and the xslt to see what else you can do. For example, you can add a lot more Entry elements to your source file, and the XSLT will still apply properly to bring them all in. You can also modify the XSLT to apply formatting or other types of display properties to the resulting transform. Try the following XML and see how it works:

<?xml version="1.0" encoding="UTF-8" ?>
<?xml-stylesheet type="text/xsl" href="view.xslt"?>
<p:PhoneBook xmlns:p="urn:somePhoneBook">
<p:Entry>
<p:FirstName>Brian</p:FirstName>
<p:LastName>Jones</p:LastName>
<p:Number>(425) 123-4567</p:Number>
</p:Entry>
<p:Entry>
<p:FirstName>Chad</p:FirstName>
<p:LastName>Rothschiller</p:LastName>
<p:Number>(425) 123-4567</p:Number>
</p:Entry>
<p:Entry>
<p:FirstName>Scott</p:FirstName>
<p:LastName>Walker</p:LastName>
<p:Number>(425) 123-4567</p:Number>
</p:Entry>
<p:Entry>
<p:FirstName>Shawn</p:FirstName>
<p:LastName>Villaron</p:LastName>
<p:Number>(425) 123-4567</p:Number>
</p:Entry>
</p:PhoneBook>

That should still open fine, just with more data. This is an easy way to bring in huge amounts of data if you want to use Excel to do some calculations, sorting, charting, etc.

Some of you may have already worked with Excel 2003's support for custom defined schemas. If you have, all this work with XSLTs probably seems like a bunch of extra work. For those of you who haven't done that yet, I'll touch on it in another post. To summarize though, it makes it possible to basically do this exact same thing without having to write any XSLT files. You just use some basic tools to tell Excel where you want your data to go and how you want it to look and Excel will take care of everything else. Even better, you have the ability to make changes and write back to your original XML file.