Our task in this case study is to use XSLT to transform data from an XML-based student gradebook and convert it to an OpenOffice.org spreadsheet. This is the actual markup that I use for the classes that I teach, and it is the actual transformation that I use.

The source XML document’s root element is a <gradebook> element. It contains a <task-list>, which gives information about each <task> that has been assigned to a student. Each task has an id attribute, date the assignment was due, a max (maximum) possible score, a type (lab, quiz, midterm, etc.), a weight telling what percentage of the final score this task is worth, and a recorded attribute that tells whether the scores for this task have been recorded or not. For example, I always have a midterm exam which I enter in the task list at the beginning of the semester; I just don’t set its recorded flag until the midterm has been given.

Following the task list is a series of <student> elements, each of which has an id attribute (the social security number preceded by a letter S. The <student> element contains the student’s last and first names, email address, extra info, and a series of <result> elements.

Each <result> element has a score attribute and a ref attribute. This last attribute is a reference to a task id from the task list. If I have some comments about the student’s work, that becomes the text content of the <result> element. Example 5.19, “Sample Gradebook Data” shows part of a gradebook. No real students or social security numbers were harmed in creating this data.

<gradebook>
    <task-list>
        <task date="2003-02-07" id="P01" max="100" recorded="yes"
            type="lab" weight="12">Program 1 (Functions)</task>
        <task date="2003-02-21" id="P02" max="100" recorded="yes"
            type="lab" weight="12">Program 2 (Window and Document)</task>
        <task date="2003-03-07" id="P03" max="100" recorded="yes"
            type="lab" weight="12">Program 3 (Forms)</task>
         <task date="2003-04-04" id="M01" max="100" recorded="yes"
            type="midterm" weight="26">Midterm</task>
        <task date="2003-05-09" id="P04" max="100" recorded="yes"
            type="lab" weight="12">Program 4 (DHTML)</task>
        <task date="2003-05-24" id="F01" max="100" recorded="yes"
            type="final" weight="26">Final</task>
    </task-list>
    <student id="S111-22-5416">
        <last>Albanes</last>
        <first>Ysidro</first>
        <email>albanes.ysidro@example.com</email>
        <info/>
        <result ref="P01" score="95"/>
        <result ref="P02" score="100">Good work!</result>
        <result ref="P03" score="100"/>
        <result ref="M01" score="89"/>
        <result ref="P04" score="95"/>
        <result ref="F01" score="94"/>
    </student>
    <student id="S111-22-3520">
        <last>Berlant</last>
        <first>Troy</first>
        <email>berlant.troy@example.com</email>
        <info/>
        <result ref="P01" score="100"/>
        <result ref="P02" score="100"/>
        <result ref="P03" score="90"/>
        <result ref="M01" score="72"/>
        <result ref="P04" score=""/>
        <result ref="F01" score="96"/>
    </student>
    <!-- etc. -->
</gradebook>

The resulting spreadsheet should look like Figure 5.4, “Result of Gradebook Transformation”. The first row has the headings, with tasks labelled by their task ID. The second row shows the weights for each task as decimals (a weight of 12 becomes 0.12), and the remaining rows show the results for each student.

Figure 5.4. Result of Gradebook Transformation

The XSLT file is fairly long, so we will look at it in parts. The first part establishes namespaces, includes a file with some standard font declarations, as described in the section called “Font Declarations”, and sets the course name in a variable. This variable exists solely to make it easy to modify the file for other courses.

<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:office="http://openoffice.org/2000/office"
    xmlns:style="http://openoffice.org/2000/style"
    xmlns:text="http://openoffice.org/2000/text"
    xmlns:table="http://openoffice.org/2000/table"
    xmlns:draw="http://openoffice.org/2000/drawing"
    xmlns:fo="http://www.w3.org/1999/XSL/Format"
    xmlns:xlink="http://www.w3.org/1999/xlink"
    xmlns:number="http://openoffice.org/2000/datastyle"
    xmlns:svg="http://www.w3.org/2000/svg"
    xmlns:chart="http://openoffice.org/2000/chart"
    xmlns:dr3d="http://openoffice.org/2000/dr3d"
    xmlns:math="http://www.w3.org/1998/Math/MathML"
    xmlns:form="http://openoffice.org/2000/form"
    xmlns:script="http://openoffice.org/2000/script"
>
<xsl:output method="xml"
    doctype-public="-//OpenOffice.org//DTD OfficeDocument 1.0//EN"
    doctype-system="office.dtd"/>

<xsl:include href="fontdecls.xslt"/>

<xsl:variable name="courseName">CIT 041J</xsl:variable>

The next line builds a “lookup table” of all the <student> nodes in the document, indexed by the value of their id attribute. We will use this index, which has the name student-index, to access a student’s information from another node’s context.

<xsl:key name="student-index" match="student" use="@id"/>

Here’s the first template: what to output when we encounter the root node of the document. It will create all the styles and all the heading rows, leaving First, fonts and the styles. We gave the cell styles meaningful names rather than using the OpenOffice.org naming convention (ce1, ce12, etc), because it helped us keep track of which cells had which style. OpenOffice.org doesn’t care what you name your styles, as long as they are all referenced correctly.

Note the order in which the styles occur: column styles, row styles, number styles, and then cell styles.

<office:document-content office:class="spreadsheet" office:version="1.0">
    
<office:script/>
    
<xsl:call-template name="insert-font-decls"/>

<office:automatic-styles>

    <!-- Column for last and first name -->
    <style:style style:name="co1" style:family="table-column">
        <style:properties fo:break-before="auto"
            style:column-width="2.5cm"/>
    </style:style>

    <!-- column for final grade and percentage -->
    <style:style style:name="co2" style:family="table-column">
        <style:properties fo:break-before="auto"
            style:column-width="2cm"/>
    </style:style>

    <!-- All other columns -->
    <style:style style:name="co3" style:family="table-column">
        <style:properties fo:break-before="auto"
            style:column-width="1.25cm"/>
    </style:style>
        
    <!-- Let all the rows have optimal height -->
    <style:style style:name="ro1" style:family="table-row">
        <style:properties fo:break-before="auto"
            style:use-optimal-row-height="true"/>
    </style:style>
    
    <!-- The table references a master-page which doesn't exist,
        but that doesn't bother OpenOffice.org -->
    <style:style style:name="ta1" style:family="table" 
        style:master-page-name="TAB_Sheet1">
        <style:properties table:display="true"/>
    </style:style>

    <!-- Number style for a percentage -->
    <number:percentage-style style:name="N01" style:family="data-style">
        <number:number number:decimal-places="2"
            number:min-integer-digits="1"/>
        <number:text>%</number:text>
    </number:percentage-style>
    
    <!-- individual cell styles -->
    
    <!-- style for final grade letter -->
    <style:style style:name="centered" style:family="table-cell"
        style:parent-style-name="Default">
        <style:properties fo:text-align="center"/>
    </style:style>
    
    <!-- style for the total grade percent -->
    <style:style style:name="percent" style:family="table-cell"
        style:data-style-name="N01"/>
    
    <!-- style for heading cells -->
    <style:style style:name="heading" style:family="table-cell"
        style:parent-style-name="Default">
        <style:properties fo:text-align="center"
            fo:font-weight="bold"/>
    </style:style>
    
    <!-- style for raw data cells (just use OpenOffice.org defaults) -->
    <style:style style:name="normal" style:family="table-cell"
        style:parent-style-name="Default"/>
</office:automatic-styles>

We now proceed to the body of the document, starting with the column specifications.

<!-- calculate number of raw data columns -->
<xsl:variable name="numTasks"
    select="count(gradebook/task-list/task[@recorded='yes'])"/>

<!-- start the spreadsheet -->
<table:table table:name="{$courseName} Final" table:style-name="ta1">  

    <!-- last name, first name, and ID are repeated when printing -->
    <table:table-header-columns>  
        <table:table-column table:style-name="co1"
            table:default-cell-style-name="normal"/>
        <table:table-column table:style-name="co1"
            table:default-cell-style-name="normal"/>
        <table:table-column table:style-name="co1"
            table:default-cell-style-name="normal"/>
    </table:table-header-columns>

    <!-- final grade -->
    <table:table-column table:style-name="co2"
        table:default-cell-style-name="centered" />

    <!-- percentage -->
    <table:table-column table:style-name="co2"
        table:default-cell-style-name="percent" />

    <!-- everyone else -->
    <table:table-column table:style-name="co3"
        table:default-cell-style-name="normal"
        table:number-columns-repeated="{$numTasks}"> 
    </table:table-column>

	We want the value of the courseName variable to become the value of the table:table-name attribute. The $ gets the value of the variable; the braces tell XSLT to evaluate the contents as an XPath expressions rather than the literal string "$courseName"
	The first three columns should appear on every page if the printout is more than one page long, so their specifications are enclosed in a <table:table-header-columns> element.
	All the raw data columns have the same format, so we use the table:number-columns-repeated attribute., setting its value to the value of ($) the numTasks variable.

Having specified the columns, we continue to the rows, which contain the actual table data. The first row contains headings, all of which have the heading style (bold and centered).

<table:table-header-rows> 
    <table:table-row table:style-name="ro1">
        <table:table-cell table:style-name="heading">
            <text:p>Last Name</text:p>
        </table:table-cell>

        <table:table-cell table:style-name="heading">
            <text:p>First Name</text:p>
        </table:table-cell>

        <table:table-cell table:style-name="heading">
            <text:p>ID</text:p>
        </table:table-cell>

        <table:table-cell table:style-name="heading">
            <text:p>Grade</text:p>
        </table:table-cell>

        <table:table-cell table:style-name="heading">
            <text:p>Total %</text:p>
        </table:table-cell>

        <!-- emit heading cell for each recorded task -->
        <xsl:for-each 
            select="gradebook/task-list/task[@recorded='yes']">
            <xsl:sort select="@date" order="descending"/> 
                <table:table-cell table:style-name="heading">
                    <text:p><xsl:value-of select="@id"/></text:p>
                </table:table-cell>
        </xsl:for-each>
    </table:table-row>
</table:table-header-rows>

	The first row should appear on every page if the printout is more than one page long, so its specifications is enclosed in a <table:table-header-rows> element.
	<xsl:for-each> is XSLT’s only iterative structure; it selects all the specified nodes and then applies all the processing contained within it.
	The <xsl:sort> modifies the <xsl:for-each> by specifying the order in which the selected nodes are to be processed. Rather than processing tasks in document order, we process them in descending order by their date attribute.

The next row contains the weights for each task. The first five cells will be unoccupied, and we use another <xsl:for-each> to place the weights into the remaining cells. Note that the calculation of the factor variable uses div for division. That is because the forward slash symbol is already used to separate steps in an XPath expressions, and using it for division as well would complicate life for everyone.

<table:table-row table:style-name="ro1">
    <!-- five empty cells -->
    <table:table-cell table:number-columns-repeated="5"/>
    <xsl:for-each select="gradebook/task-list/task[@recorded='yes']">
        <xsl:sort select="@date" order="descending"/>

        <xsl:variable name="factor" select="@weight div @max"/>

        <table:table-cell table:value-type="float"
            table:value="{$factor}">
            <text:p><xsl:value-of select="$factor"/></text:p>
        </table:table-cell>
    </xsl:for-each>
</table:table-row>

The only remaining job is to create the rows for each student; we will handle this by applying a template to all the <student> elements.

        <!-- Create one row for each student -->
        <xsl:apply-templates select="gradebook/student">
            <xsl:sort select="last"/> 
            <xsl:sort select="first"/>
            <xsl:with-param name="numCols" select="$numTasks+5"/>  
        </xsl:apply-templates>
    </table:table>
</office:body>
</office:document-content>
</xsl:template>

	In this case, we are using <xsl:sort> to modify the order in which the templates are applied to the selected nodes. The first <xsl:sort> specifies the primary sort key; the second <xsl:srot> specifies the secondary key. You may have as many keys as you need.
	When we create the formula for calculating the student’s grade, we have to know the ending column number for the row. This is the number of tasks plus five, so we send that to the template as a parameter named numCols.

Now we provide the template that handles the processing of the <student> element selected in the preceding code.

<xsl:template match="student">
    <xsl:param name="numCols"/> 
    
    <!-- generate the column letter for the last column -->
    <xsl:variable name="lastCol">
        <xsl:call-template name="generate-col">  
            <xsl:with-param name="n" select="$numCols"/>
        </xsl:call-template>
    </xsl:variable>

Any template which accepts parameters via <xsl:with-param> must declare the parameter name.

The code for generating the letter corresponding to the last column number is non-trivial; rather than placing it here inside the template, we have put it in a template named generate-col (which does not correspond to any element name in our document). You can think of these named templates as subroutines rather than as templates that match document elements. That is why we call it via <xsl:call-template> instead of <xsl:apply-templates>. We are going call the template with a parameter n (<xsl:with-param>); the parameter will be the number of columns. to this template; its value will be the same as numCols. In the case of six tasks, the lastCol variable will end up with the value K.

The code for the first name, last name, and student ID cells is quite straightforward; it uses the substring() function to eliminate the letter S from the student ID.

<!-- cells for last name, first name, and student ID -->
<table:table-cell>
    <text:p><xsl:value-of select="last"/></text:p>
</table:table-cell>
<table:table-cell>
    <text:p><xsl:value-of select="first"/></text:p>
</table:table-cell>
<table:table-cell>
    <text:p><xsl:value-of select="substring(@id,2)"/></text:p>
</table:table-cell>

The next columns are the final letter grade and the total percentage. Extra whitespace has been inserted into the following listing to make things easier to read.

<!-- formula for final grade letter -->
<table:table-cell
    table:formula=
    "=MID(&quot;FFFFFFDCBAA&quot;; INT([.E{position()+2}]*10)+1; 1)"/>

<!-- formula for final grade as a percentage -->
<table:table-cell
    table:number-matrix-columns-spanned="1"
    table:number-matrix-rows-spanned="1"
    table:formula=
        "=SUM([.F{position()+2}:.{$lastCol}{position()+2}] *
        [.$F$2:.${$lastCol}$2])/100"
    table:value-type="float"/>

The letter grade formula works by looking at the column to its right (the percentage), multiplying it by 10 to give a number between 0 and 10, then grabbing the corresponding letter from the string FFFFFFDCBAA. It uses the position() function to figure out which row number is being output. Here’s how that function works: <xsl:apply-templates> has selected a set of student nodes and <xsl:sort> has sorted them. Each one in turn is being processed by the current <xsl:template>. The first one to be processed has a position() equal to 1, the second has a position of 2, etc. Since there are two rows of headings in the spreadsheet, the row number being output is the position() plus two. Thus, for the first student, the formula works out to =MID("FFFFFFDCBAA";INT([.E3]*10)+1;1), as displayed in OpenOffice.org.

The next cell also uses the position() function and the lastCol variable to create the correct formula. Because the cell contains an array formula, it needs the table:number-matrix-columns-spanned and table:number-matrix-rows-spanned attributes to specify the dimensions of the resulting array. If the template is processing the first student and there are six tasks, then the XSLT creates the formula {=SUM([.F3:.K3]*[.$F$2:.$K$2])/100}, as displayed in OpenOffice.org.

The following is the remainder of the template for handling a student. The lines have been numbered for reference.

 1  <!-- save the student's id -->
 2  <xsl:variable name="id" select="@id"/>
 3
 4  <!-- insert a cell for each recorded score -->
 5  <xsl:for-each select="/gradebook/task-list/task[@recorded='yes']">
 6      <xsl:sort select="@date" order="descending"/>
 7      <xsl:variable name="taskID" select="@id"/>
 8      <xsl:call-template name="insert_score">
 9          <xsl:with-param name="n"
10              select="key('student-index', $id)/result[@ref=$taskID]/@score"/>
11      </xsl:call-template>
12  </xsl:for-each>
13  </table:table-row>
14 </xsl:template>

That takes care of the major templates. Now we write the named templates (subroutines) that we referred to earlier. First is the insert-score template, which creates a table cell whose value is the passed parameter n. If a null value has been passed (in case someone does not have a score for a task), then we insert an empty cell.

<xsl:template name="insert_score">
    <xsl:param name="n"/>
    
    <xsl:choose>
    <xsl:when test="$n != ''">
        <table:table-cell table:value-type="float" table:value="{$n}">
            <text:p><xsl:value-of select="$n"/></text:p>
        </table:table-cell>
    </xsl:when>
    <xsl:otherwise>
        <table:table-cell><text:p/></table:table-cell>
    </xsl:otherwise>
    </xsl:choose>
</xsl:template>

Finally, we have the template to figure out the column letter corresponding to the last column for each row. The pseudo-code is as follows:

<xsl:template name="generate-col">
    <xsl:param name="n"/>
    <xsl:variable name="letters">ABCDEFGHIJKLMNOPQRSTUVWXYZ</xsl:variable>
    <xsl:choose>
        <xsl:when test="$n &lt;= 26">
            <xsl:value-of select="substring($letters, $n, 1)"/>
        </xsl:when>
        <xsl:otherwise>
            <xsl:value-of select="concat(
                substring($letters, floor(($n - 1) div 26) + 1, 1),
                substring($letters, (($n - 1) mod 26) + 1, 1))"/>
        </xsl:otherwise>
    </xsl:choose>
</xsl:template>

The actual code for two-letter columns has to subtract one from the column number before dividing (so that the math works out right), and must add one to the result, because characters are numbered starting at one.