Intro to Excel XML Part 2: Displaying your data

Článek
06/30/2005

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">

<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>

<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>

<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:

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.

Comments

Anonymous
July 06, 2005
とりあえずここを読んでおけばよさそうな感じです。Brian Jones: Office XML Formats
現時点で以下の記事があがっています。
Excel：Introduction to Excel...
Anonymous
July 18, 2005
If you read Part 1 of the Word XML Introduction, you saw the basics behind a Word document, as well as...
Anonymous
October 04, 2005
Hi Brian,

This was very useful, especially for complex formatting. Thanks.

Sathi
Anonymous
October 04, 2005
No problem! :-)

Let me know if there are other topics you'd like to see covered.

-Brian
Anonymous
February 23, 2006
Good article!

Question: How to handle the excels macro security with xslt files. Excel will give a error message on the example above when the highest security level is set.

Tnx for the help

/C-W
Anonymous
February 24, 2006
I'm not sure why that is /C-W

Where is the XSLT located? The only times we block an XSLT in that security mode should be if it's not in a trusted location, or if it has any script in it. If it has script, than the XSLT also needs to be signed.

I could be wrong though (it's been a few years since we designed all that), so if you're still having problems let me know.

-Brian
Anonymous
March 02, 2006
Hi again!

Tnx for the answer.

The xslt file is the one in your example above so it should be fine. I have tried different locations for it, when it could be a problem with my domain connected workstation.
Anyway, I have move the transformation into my app to make this feature transparent to my users.

I have some other questions. Is there any documentation for this excel/office feature?

Is there a way to make the columns in the excelML file auto sized?

Tnx
Anonymous
April 18, 2006
Would you be able to cover different data types which are supported by ExcelML? It seems that you are using only type String, I wonder what other types supported to create a strong typed ExcelML file.

Thank you.
Anonymous
May 08, 2006
Brian,

Just wanted to say thank you for the MS Excel XML info. In one place I finally found clear, concise, understandable explanations with simple but informative examples.

When you publish it in book form, let me know.

Thanks,
Paul
Anonymous
May 08, 2006
Glad to help Paul. I want to make some posts on the new SpreadsheetML that's coming with Office 2007 too...

-Brian
Anonymous
March 12, 2007
If you read Part 1 of the Word XML Introduction, you saw the basics behind a Word document, as well as

Sdílet prostřednictvím