Christian Nagel's Blog

Getting Data from Excel 2003

Lately I've posted about creating Word documents with XSLT. Office 2003 has another cool XML feature:

I needed data from an Excel sheet in a special format for processing. With XML schemas this can be done very easily:

• Create an XML schema
• With the Excel task pane (XML Source), the schema can be referenced by adding an XML map.
• Within the task pane the XML elements can be selected and moved to the corresponding Excel columns
• Save as XML data stores the data in an XML format that corresponds to the schema.

Now it's possible to transform the data as needed using XSLT.

Christian

Creating Word Documents with XSLT (Part 2 - Creating Tables)

With my last blog about creating Word documents with XSLT I've shown an example to do a very simple document.

Here I'm showing how to create a table using Word. The same XML file as before is used:

<?xml version="1.0" encoding="utf-8" ?>
<Courses>
 <Course Number="MS-2524">
  <Title>XML Web Services Programming</Title>
 </Course>
 <Course Number="MS-2124">
  <Title>C# Programming</Title>
 </Course>
 <Course Number="NET2">
  <Title>.NET 2.0 Early Adapter</Title>
 </Course>
</Courses>

The XSLT file to create a Word document with a table:

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml">
 <xsl:output method="xml" indent="yes" />
 <xsl:template match="/">
  <xsl:processing-instruction name="mso-application">
   <xsl:text>progid="Word.Document"</xsl:text>
  </xsl:processing-instruction>
  <w:wordDocument>
   <w:body>
    <w:tbl>
     <w:tblPr>
      <w:tblStyle w:val="TableGrid"/>
      <w:tblW w:w="0" w:type="auto"/>
      <w:tblLook w:val="01E0"/>
     </w:tblPr>
     <w:tblGrid>
      <w:gridCol w:w="4428"/>
      <w:gridCol w:w="4428"/>
     </w:tblGrid>
     <w:tr>
      <w:tc>
       <w:p>
        <w:r>
         <w:t>Number</w:t>
        </w:r>
       </w:p>
      </w:tc>
      <w:tc>
       <w:p>
        <w:r>
         <w:t>Course Title</w:t>
        </w:r>
       </w:p>
      </w:tc>
     </w:tr>
     <xsl:apply-templates select="Courses/Course" />
    </w:tbl>
   </w:body>
  </w:wordDocument>
 </xsl:template>
 <xsl:template match="Course">
  <w:tr>
   <w:tc>
    <w:p>
     <w:r>
      <w:t>
       <xsl:value-of select="@Number" />
      </w:t>
     </w:r>
    </w:p>
   </w:tc>
   <w:tc>
    <w:p>
     <w:r>
      <w:t>
       <xsl:value-of select="Title"/>
      </w:t>
     </w:r>
    </w:p>
   </w:tc>
  </w:tr>
 </xsl:template>
</xsl:stylesheet>

With WordML these are some elements that make up a table:

<tbl>	<tbl> represents a table. This is similar to HTML <TABLE>. A WordML <table> element exists, too. However, the <table> element is used for a Office Data Source Object.
<tblPr>	With the element <tblPr> table-wide properties such as the style and the width are defined.
<tblGrid>	The element <tblGrid> defines the grid layout of the table. In the example two columns with similar width are defined.
<tr>	<tr> is the row of a table
<tc>	<tc> is a column inside the row

This helps creating Word documents with WordML:

• Create a document with Microsoft Word and save it as XML file. Check the XML source.
• Using Visual Studio 2005, add the Word schemas to the XML file for Intellisense-support.
• Check the documentation of the Office schemas.

The Office schemas (including documentation) can be found on the MSDN Website.

Christian

Documentation that shows up in the XML Editor

The XML Editor of Visual Studio 2005 shows the elements that are allowed according to the XML Schema. The editor not only shows the elements and attributes that are possible where the cursor is positioned, it also shows some documentation about the elements and attributes. The documentation can be written inside the schema with the <xsd:annotation> and <xsd:documentation> tags:

  <xs:element name="Book" minOccurs="0" maxOccurs="unbounded">
     <xs:annotation>
      <xs:documentation>Represents information about a book.</xs:documentation>
     </xs:annotation>
     <xs:complexType>
      <xs:sequence>
       <xs:element name="Title" type="xs:string">
        <xs:annotation>
         <xs:documentation>Represents the title of a book.</xs:documentation>
        </xs:annotation>
       </xs:element>
       <!-- ... -->

Christian

Creating Word Documents with XSLT

Using the Office Schemas it is easy to create Microsoft Word 2003 documents.

Let's start with this XML document:

<?xml version="1.0" encoding="utf-8" ?>
<Courses>
 <Course Number="MS-2524">
  <Title>XML Web Services Programming</Title>
 </Course>
 <Course Number="MS-2124">
  <Title>C# Programming</Title>
 </Course>
 <Course Number="NET2">
  <Title>.NET 2.0 Early Adopter</Title>
 </Course>
</Courses>

The result should be a Word document.

A simple Word document containing the above data can be as simple as this:

<?xml version="1.0" encoding="utf-8"?>
<?mso-application progid="Word.Document"?>
<w:wordDocument xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml">
  <w:body>
    <w:p>
      <w:r>
        <w:t>MS-2524, XML Web Services Programming</w:t>
      </w:r>
    </w:p>
    <w:p>
      <w:r>
        <w:t>MS-2124, C# Programming</w:t>
      </w:r>
    </w:p>
    <w:p>
      <w:r>
        <w:t>NET2, .NET 2.0 Early Adopter</w:t>
      </w:r>
    </w:p>
  </w:body>
</w:wordDocument>

With XSLT the document can be created using this style sheet:

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml">
 <xsl:output method="xml" indent="yes" />
 <xsl:template match="/">
  <xsl:processing-instruction name="mso-application">
   <xsl:text>progid="Word.Document"</xsl:text>
  </xsl:processing-instruction>
  <w:wordDocument>
   <w:body>
     <xsl:apply-templates select="Courses/Course" />
   </w:body>
  </w:wordDocument>
 </xsl:template>
 <xsl:template match="Course">
    <w:p>
     <w:r>
      <w:t>
       <xsl:value-of select="@Number" />, <xsl:value-of select="Title"/>
      </w:t>
     </w:r>
    </w:p>
 </xsl:template>
</xsl:stylesheet>

Adding the processing instruction mso-application to the document allows the system to deal with the XML file as a Word document. The parser reads the progid for Word to display the Word icon, and to start Word when opening the file.

  <xsl:processing-instruction name="mso-application">
   <xsl:text>progid="Word.Document"</xsl:text>
  </xsl:processing-instruction>

The elements <w:wordDocument> and <w:body> can be compared to the HTML tags <HTML> and <BODY>. <w:t> is the tag for a text output.

The schemas for Microsoft Office 2003 can be downloaded from the MSDN Website. The download is an installation package that includes schemas for Office 2003 as well as documentation. Referencing the schemas with the XML and XSLT editors of Visual Studio 2005 gives intellisense :-)

Christian

 

XSLT: Converting multiple elements to one attribute

I've received a question how to convert multiple XML elements to a values for a single attribute. Here is the information about how this can be done with XSLT.

The XML source:

<Article>
 <Head>
  <Title>.NET Enterprise Services</Title>
  <MainCategory></MainCategory>
  <Keyword>Framework</Keyword>
  <Keyword>C#</Keyword>
  <Keyword>Enterprise Services</Keyword>
 </Head>
 <Body>
 </Body>
 <Info>
 </Info>
</Article>

From this source the elements <Keyword> should be converted to this result:

<META name="keywords" content="Framework, C#, Enterprise Services">

This is done with this XSLT transformation:

<?xml version="1.0" encoding="ISO-8859-1"?>
<!-- Demo Style sheet to convert multiple elements to a single attribute value 
-->
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">
 <xsl:output method="html" indent="yes"/>
 <xsl:template match="/">
  <HTML>
   <HEAD>
    <xsl:element name="META">
     <xsl:attribute name="name">keywords</xsl:attribute>
     <xsl:attribute name="content">
      <xsl:for-each select="Article/Head/Keyword">
       <xsl:value-of select="."/>
       <xsl:if test="position() != last()">, </xsl:if>
      </xsl:for-each>
     </xsl:attribute>
    </xsl:element>
    <TITLE>
    </TITLE>
   </HEAD>
   <BODY></BODY>
  </HTML>
 </xsl:template>
</xsl:stylesheet>

<xsl:element> and <xsl:attribute> allows dynamic creation of elements and attributes. <xsl:element name="META"> defines the element <META>. <xsl:attribute name="name">keywords</xsl:attribute> results in <META name="keywords">. The content of the attribute content is created dynamically with a selection of each <Article><Head><Keyword> element, and in between of these values a semicolon is added. <xsl:if test=position() != last()"> checks if the <Keyword> element was not the last one, as with the last one no more semicolon is used.

I really like the XSLT editor and debugger of Visual Studio 2005. This makes it easy to create XSLT files.

Update: Bryan has another option to create the Meta tags:

 <xsl:template match="/">
  <HTML>
   <HEAD> 
    <META name="keywords">
     <xsl:attribute name="Content">
      <xsl:apply-templates select="Article/Head/Keyword" />
     </xsl:attribute>
    </META>
    <TITLE>
    </TITLE>
   </HEAD>
   <BODY></BODY>
  </HTML>
 </xsl:template>
 
 <xsl:template match="Article/Head/Keyword" >
  <xsl:value-of select="."/>
  <xsl:if test="following-sibling::Keyword">,</xsl:if>
 </xsl:template>

Christian