Chapter 5. Spreadsheets

Surprisingly, we have already covered a great deal of the information about spreadsheets. Spreadsheets share a great deal of their markup with tables that you find in text documents. This shouldn’t come as a surprise—a spreadsheet is just a two-dimensional table. It can have many blank rows and columns and can do calculations on the cell entries, but a spreadsheet is still just a table at heart.

However, there are things that make a spreadsheet, well, spreadsheetish. Most important, the <office:body> has an <office:spreadsheet> element as its child (rather than <office:text> for a word processing document). Other elements and attributes specific to spreadsheets are in the styles.xml file, but most are in content.xml.

The styles.xml file stores information that sets from the sheet tab of the Format Page dialog, shown in Figure 5.1, “Spreadsheet Page Options”. Specifically, this information is in the <style:page-layout-properties> element that is inside the first <style:page-layout> element within the <office:automatic-styles>.

Example 5.1, “Page Options” shows this markup.

The <office:automatic-styles> element contains

The other major style information in a spreadsheet deals with formatting numbers so that they show up as percentages, scientific notation, etc. A number style must convey two pieces of information: what kind of a number it is (number, currency, percent, date, etc.) and how the number is to be displayed. This is mirrored in the XML as a <number:entity-style> element, where entity can be number, currency, percent, date, etc.

This element has a required style:name attribute that gives the style a unique identifier, and a style:family attribute with a value of data-style. The contents of this element will tell how to display the number, percent, currency, date, etc.

Let’s start with the “pure numeric” styles: numbers, percents, scientific notation, and fractions.

A plain number is contained in a <number:number-style> element with a style:name attribute. Contained within this element is the description of how to display the number. In this case, we need only a simple <number:number> element that has these attributes

Example 5.3, “Number Style for format #,##0.00” shows a number style for displaying two places to the right of the decimal, one leading zero, and a grouping separator.

If you want negative numbers to be red, then things become radically different. Rather than having one style, OpenDocument requires two styles, with the negative being the default and a “conditional style” for positive values. Here is the XML for a number with two digits to the right of the decimal, one leading zero, a thousands separator, and negative numbers in red:

1 This is the format to be used for positive numbers. The style:volatile="true" tells the application to retain this style, even if it is never used.
2 This is the main style for negative numbers. They should be displayed in red …
3 … starting with a minus sign …
4 … followed by the number with two decimal places, at least one leading zero, and a thousands separator.
5 However, in the event that the value of the cell is greater than or equal to (&gt;=) zero, use the positive number style (N112P0).

Currency styles are similar to number styles. Specifying a currency always creates two styles: one for negative values, and one for positive values. Example 5.8, “Currency in Format -$#,##0.00” shows the XML for a currency format of US Dollars with two digits after the decimal point, a minimum of one digit before the decimal, and a thousands separator.

1 The formatting for positive values appears first, contained in a <number:currency-style> element.
2 The <number:currency-symbol> ’s content is the dollar sign. The number:language and number:country allow a program to distinguish the US dollar from the New Zealand dollar or Mexican new peso symbol, which look the same but have different countries and/or languages.
3 The number portion of the display uses the same <number:number> element that we have already described[6].
4 For negative values, the minus sign precedes the currency symbol.
5 As in Example 5.4, “Number Style for format -#,##0.00 with Negative Values in Red”, a <style:map> is used to choose whether to use the negative number format or the positive number format.

The appearance of <number:text> elements mirrors the order in which the text appears. Example 5.9, “Currency Format for Greek Drachma” shows the negative number portion of the XML for the Greek drachma. In this format, the value is shown in red, the minus sign appears first, then the number, then a blank and the letters “Δρχ.” (We are showing only the negative number specification.)

OpenDocument applications support a large number of different formats for dates and times. Rather than explain each one in detail, it’s easier to simply compose the style you want out of parts.

For dates, the enclosing element is a <number:date-style> element, with the usual style:name attribute. The number:automatic-order attribute is used to automatically order data to match the default order for the language and country of the data. You may also set the number:format-source to fixed, to let the application determine the value of “short” and “long” representations of months, days, etc. If the value is language, then those values are taken from the language and country set in the style.

Within the <number:date-style> element are the following elements, with their significant attributes:

Example 5.10, “Date Styles” shows three date styles. The first will display the fourth day of the seventh month of 2005 as Monday, July 4, 2005; the second will display it as 07/04/05, and the third as 3rd Quarter 05.

Time values are represented by the <number:time-style> element. Its sub-elements are

Example 5.11, “Time Style” shows the style required to display a time in the format 09:02:34 AM

Let us now turn our attention to the table content, which is contained in content.xml, inside the <office:body> element. Each sheet is stored as a separate <table:table>. Its table:name attribute is the name that will appear on the spreadsheet tab, and the table:style-name attribute refers to a table style as described in the section called “Styles for the Sheet as a Whole”.

Cells that contain numbers also contain a <text:p> that shows the display form of the value. The actual value is stored in the <table:table-cell> element with two attributes: office:value-type and office:value. These are related as described in Table 5.1, “office:value-type and office:value”.

Formula cells contain a table:formula attribute. Within the table:formula attribute, references to individual cells or cell ranges are enclosed in square brackets. Relative cell names are expressed in the form sheetname.cellname. Thus, a reference to cell A3 in the current spreadsheet will appear as [.A3], and a reference to cell G17 in a spreadsheet named Sheet2 will appear as [Sheet2.G17]. The range of cells from G3 to K7 in the current spreadsheet appear as [.G3:.K7].

Absolute cell names simply have the preceding $ on them, much as you would enter them in Thus, an absolute reference to cell C4 in the current spreadsheet would be written as [.$C$4].

Depending upon the return type of the formula, the table cell will contain appropriate office:value and office:value-type attributes. Example 5.14, “Return Types from Formulas” shows the result of three formulas; the first returns a simple number, the second returns a string showing roman numerals, and the third produces a time value from the contents of three cells.

According to the specification, an OpenDocument-compatible application should depend only upon the formula to generate its display. A program could generate a spreadsheet that would display identically to the preceding example when opened in, using only the information shown in Example 5.15, “Minimal Formulas”.

If you are using an array formula which would be represented in within curly braces, such as {=B6:C6*B7:C7}, you must specify the number of rows and columns that the result will occupy. The preceding formula is marked up as follows:

    table:number-matrix-rows-spanned="1" table:formula="oooc:=[.B6:.C6]*[.B7:.C7]"
    office:value-type="float" office:value="27">

We will use this information about spreadsheets to write a Python program that does currency conversion. All cells that are stored in one currency (such as U.S. dollars) will be converted to the equivalent values in a different currency (such as Korean Won) and saved to a new spreadsheet.

To find and change the appropriate <number:currency-style> elements, the program must know the values of number:country and number:language for the source and destination currencies. To find and change the appropriate <table:table-cell> elements, the program must know the three-letter abbreviation found in table:currency for the source and destination currencies.

Finally, we will need to provide format strings for positive and negative values in the destination currency, the currency symbol for the destination currency, and a conversion factor for multiplying the value of the numbers in the spreadsheet. We will store all this information in an ad-hoc XML file of the form shown in Example 5.16, “Money Conversion Parameters”, which converts U.S. dollars to Korean Won. [This is file currencyparam.xml in directory ch05 in the downloadable example files.]

The symbols in the format string have the following meanings:

All the other characters in the format string are taken as text. This allows you to place blanks and other characters in a format.

Take a deep breath and hold on tight; this is the largest function in the program.

def fixCurrency( filename ):

    #   Read the styles.xml file as a string file
    #   and create a disk file for output
    dataSource = StringIO ( filename )) 1
    tempFileName = "/tmp/workfile"
    dataSink = open(tempFileName, "w")

    #   Parse the document
    document = xml.dom.minidom.parse( dataSource )

    #   Create document fragments from the format strings 2
    posXML = od_number.ODNumber( document, positiveFormatString, 
        language, country, currencySymbol )
    posXML.createCurrencyStyle( )
    negXML = od_number.ODNumber( document, negativeFormatString,
        language, country, currencySymbol )
    negXML.createCurrencyStyle( )

    #   Fix number style elements
    currencyElements = document.getElementsByTagName("number:currency-symbol")
    for element in currencyElements:
        if (element.getAttribute( "number:language" ) == oldLanguage and 3
            element.getAttribute( "number:country" ) == oldCountry):
            element.setAttribute( "number:language", language )
            element.setAttribute( "number:country", country ) 

            parent = element.parentNode 4
            children = parent.childNodes
            i = len(children)-1
            while (i >= 0):
                if (children[i].nodeName == "number:number" or 
                children[i].nodeName == "number:text" or 
                children[i].nodeName == "number:currency-symbol" or 
                children[i].nodeType == xml.dom.Node.TEXT_NODE):
                    parent.removeChild( children[i] )
                i = i - 1

            #   select the appropriate number format markup
            if ((parent.getAttribute("style:name"))[-2:] == "P0"): 5
                fragment = posXML.getFragment()
                fragment = negXML.getFragment()
            #   and insert it into the <number:currency-style> element
            for child in fragment.childNodes: 6
                parent.appendChild( child.cloneNode(True) )

    #   Fix table cells (which only exist in content.xml) 7
    rowElements = document.getElementsByTagName("table:table-row")
    for row in rowElements:
        cell = getChildElement( row, "table:table-cell" ) 8
        while (cell != None):
            if (cell.getAttribute("table:currency") ==  oldAbbrev ):
                # change the currency abbreviation
                cell.setAttribute("table:currency", abbreviation )

                # and the number in the cell, if there is one
                valueStr = cell.getAttribute("office:value")
                if (valueStr != ""):
                    result = float( valueStr ) * factor
                    cell.setAttribute("office:value",  '%f' % result)
                #   remove any children of this cell
                children = cell.childNodes 9
                i = len(children)-1
                while (i >= 0):
                    cell.removeChild( children[i] )
                    i = i - 1
            # move to the next cell in the row      
            cell = getSiblingElement( cell, "table:table-cell" )

    #   Serialize the document tree to the output file
    xml.dom.ext.Print( document, dataSink )

    #   Add the temporary file to the new .zip file, giving it
    #   the same name as the input file.
    outFile.write( tempFileName, filename )
1 The input file is a member of a .zip file; we can’t pass the .zip file itself on to the parser. Nor can we open a file descriptor for a member of the .zip archive, so we are forced to read in the input file into a string, and use the StringIO constructor to make it look like a file.

On the other hand, we can’t easily write a string to a member of the output file, so we create a temporary file on disk. (The filename is a Unix filename; change it as appropriate for your system.)

2 We will convert the format strings to document fragments so that we can just copy the XML from the fragments into the DOM tree that we are modifying. This is nontrivial code, so it’s separated out into another module altogether.
3 We don’t want to indiscriminately modify all the <number:currency-symbol> elements; you may have multiple currencies in your document, and you want to change only the ones specified in your parameters.
4 Before we put in the new format markup, we have to get rid of the old markup. We don’t eliminate all the old stuff; we want to keep any <style:properties> (for red text) and <style:map> elements (which select positive or negative formats).

When removing the children, we have to go in reverse order; if we had started by removing child number zero, then child number one would move into its place and we would miss it on the next loop iteration.

5 This code presumes that you are using a file that has been created with; currency formats for positive values always end with the characters P0.
6 This code uses the cloneNode() function to make sure that all of the fragment nodes’ descendants get copied into the document being modified.
7 Rather than retrieve all the <table:table-cell> elements at once, which could strain memory with a large document, we get cells one row at a time.
8 We can’t just go to the first child of the table row; there may be intervening whitespace text nodes. Thus, we have our own getChildElement() function to find the node we really want. A similar getSiblingElement() function finds the next sibling while avoiding those pesky whitespace nodes.
9 Rather than try to update the value of the <text:p> inside the cell (which would force us to do all the calculation and formatting that does), we just eliminate it and let re-create it after a load and save.

Copying the manifest also creates a temporary file:

def copyManifest():
    #   read the manifest.xml file as a string
    #   and create a disk file for transfer to the .zip output
    dataSource = "META-INF/manifest.xml" )
    tempFileName = "/tmp/workfile"
    dataSink = open(tempFileName, "w")
    dataSink.write( dataSource );
    outFile.write( tempFileName, "META-INF/manifest.xml" )

Finally, the code for parsing the format string to produce an XML document fragment.

I put this into a module, even though it’s not something that would be useful for any other program. I did it this way because I didn’t know of any other way to do an “include.” Hey, this is my first Python program of any size greater than “Hello, World!” [This module is file in directory ch05 in the downloadable example files.]

import xml.dom
import xml.dom.ext
import xml.dom.minidom
import re

class ODNumber:
    def __init__(self, document, formatString, language, country,
    currencySymbol ):
        self.thousands = False;     # thousands separator?
        self.nDecimals = 0;         # number of decimal places
        self.minIntegerDigits = 0;  # min. integer digits
        self.textStr = ""           # text string being built
        self.fragment = None        # fragment being built
        self.document = document    # copy parameters to class attributes
        self.language = language = country
        self.currencySymbol = currencySymbol
        self.formatString = formatString

    def endStr( self ):  1
        if (self.textStr != ""):
            textElement = self.document.createElement( "number:text" )
            textNode = self.document.createTextNode( self.textStr )
            textElement.appendChild( textNode )
            self.fragment.appendChild( textElement )
            self.textStr = ""

    def addCurrency( self ):
        self.endStr() 2
        node = self.fragment.appendChild(
            self.document.createElement( "number:currency-symbol" ) )
        node.setAttribute( "number:language", self.language )
        node.setAttribute( "number:country", )
        node.appendChild( self.document.createTextNode( self.currencySymbol ) )

    def addNumber( self ): 3
        node = self.fragment.appendChild(
            self.document.createElement("number:number") )
        node.setAttribute( "number:min-integer-digits",
            "%d" % self.minIntegerDigits )
        if (self.nDecimals > 0):
            node.setAttribute( "number:decimal-places",
                "%d" % self.nDecimals )
        if (self.thousands):
            node.setAttribute( "number:grouping", "true" )

    def createCurrencyStyle ( self ):
        """Scan a format string, where:

        $ indicates the currency symbol
        # indicates an optional digit
        0 indicates a required digit
        , indicates the thousands separator (no matter your locale)
        . indicates the decimal point (no matter your locale)

        Creates a document fragment with appropriate

        self.fragment = self.document.createElement("number:fragment")  4

        hasDecimal = False

        numchars = re.compile( "[#,0.]" )  5

        i = 0
        while (i < len(self.formatString)):
            char = self.formatString[i]

            if (char == "$"):
                self.addCurrency( )
            elif ( numchars, char )):
                self.endStr( ) 6
                while ( numchars, char )):
                    if (char == ","):
                        self.thousands = True
                    elif (char == "0"):
                        if (hasDecimal):
                            self.nDecimals = self.nDecimals + 1
                            self.minIntegerDigits = \
                                self.minIntegerDigits + 1
                    elif (char == "."):
                        hasDecimal = True;

                    if (i == len(self.formatString) - 1):
                    i = i + 1
                    char = self.formatString[i]
                self.addNumber( )
                self.textStr = self.textStr + char 7
            i = i + 1
        self.endStr( ) 8

    def getFragment( self ): 9
        return self.fragment
1 As we parse a string, anything that isn’t part of the format string or currency symbol gets accumulated into the textStr variable. This function dumps it out to an <number:text> element.
2 If we have accumulated any text prior to the currency symbol (it could be a minus sign), output it.
3 This function is called when we reach the end of the number part of the format string.
4 We create a phony element to be the container for all the other elements that we are going to create. Why should we go to the trouble of creating our own tree structure to hold independently-created nodes when that’s part of the DOM’s job?
5 This compiles a regular expression; matching against it is easier than doing a large conditional expression to see if we have a character that’s part of the number format.
6 As soon as we find one of those crucial characters, we output any pending text, then gather the format information in the while loop.
7 If the character isn’t a currency symbol or part of a number format, it’s just generic text to be accumulated.
8 If there’s any pending text when we hit the end of the string, we need to put it into the output.
9 A utility function to return the fragment that’s been built. Truth in advertising: Since this module wasn’t designed for object-oriented purity, it is here more for appearance’s sake than anything else.

If you wish to specify a print range for the sheet (corresponding to the dialog box shown in Figure 5.5, “Spreadsheet Print Ranges” add a table:print-ranges attribute to the <table:table> element. Its value will be in a form like Sheet1.A1:Sheet1:F9.

The <table:table-column> elements that are to be repeated will be enclosed in a <table:table-header-columns> element; the <table:table-row> elements to be repeated will be enclosed in a <table:table-header-rows> element.

Example 5.17, “Structure of Print Ranges” shows the skeleton of the XML markup for the print ranges chosen in Figure 5.1, “Spreadsheet Page Options”.

Our task in this case study is to use XSLT to transform data from an XML-based student gradebook and convert it to an OpenDocument 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.18, “Sample Gradebook Data” shows part of a gradebook. No real students or social security numbers were harmed in creating this data. [This is file minigrades.xml in directory ch05 in the downloadable example files.]

The resulting spreadsheet should look like Figure 5.6, “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.

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 parameter. The parameter allows users to enter the course name from the command line. [The entire XSLT file is file gradebook_to_ods.xsl in directory ch05 in the downloadable example files.]

<?xml version="1.0"?>
<xsl:stylesheet version="1.0"



<xsl:output method="xml" indent="yes"/>

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

<xsl:param name="courseName">Course</xsl:param>

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 begin the body of the document. We gave the cell styles meaningful names rather than using the naming convention (ce1, ce12, etc), because it helped us keep track of which cells had which style. doesn’t care what you name your styles, as long as they are all referenced correctly; this should be the case with all OpenDocument compatible applications.

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

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


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

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

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

    <!-- Number style for a percentage -->
    <number:percentage-style style:name="N01">
        <number:number number:decimal-places="2"
    <!-- individual cell styles -->
    <!-- style for final grade letter -->
    <style:style style:name="centered" style:family="table-cell"
        <style:paragraph-properties fo:text-align="center"/>
    <!-- style for the total grade percent -->
    <style:style style:name="percent" style:family="table-cell"
    <!-- style for heading cells -->
    <style:style style:name="heading" style:family="table-cell"
        <style:paragraph-properties fo:text-align="center"/>
        <style:text-properties fo:font-weight="bold"/>
    <!-- style for raw data cells (just use defaults) -->
    <style:style style:name="normal" style:family="table-cell"


We now proceed to the spreadsheet content, starting with the column specifications.

<!-- calculate number of raw data columns -->
<xsl:variable name="numTasks" 1

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

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

    <!-- 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:number-columns-repeated="{$numTasks}"> 4
1 Calculate the number of columns in the spreadsheet by counting the number of tasks that have been recorded.
2 We want the value of the courseName parameter 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 expression rather than the literal string $courseName.
3 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.
4 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 (that's what $ stands for) 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> 1
    <table:table-row table:style-name="ro1">
        <table:table-cell table:style-name="heading">
            <text:p>Last Name</text:p>

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

        <table:table-cell table:style-name="heading">

        <table:table-cell table:style-name="heading">

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

        <!-- emit heading cell for each recorded task -->
        <xsl:for-each 2
            <xsl:sort select="@date" order="descending"/> 3
                <table:table-cell table:style-name="heading">
                    <text:p><xsl:value-of select="@id"/></text:p>
1 The first row should appear on every page if the printout is more than one page long, so its specification is enclosed in a <table:table-header-rows> element.
2 <xsl:for-each> is XSLT’s only iterative structure; it selects all the specified nodes and then applies all the processing contained within it.
3 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 office:value-type="float"
            <text:p><xsl:value-of select="$factor"/></text:p>

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"/> 1
                <xsl:sort select="first"/>
                <xsl:with-param name="numCols" select="$numTasks+5"/>  2
1 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:sort> specifies the secondary key. You may have as many keys as you need.
2 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"/> 1
    <!-- generate the column letter for the last column -->
    <xsl:variable name="lastCol">
        <xsl:call-template name="generate-col">  2
            <xsl:with-param name="n" select="$numCols"/>
1 Any template which accepts parameters via <xsl:with-param> must declare the parameter name.
2 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 to call the template with a parameter n (<xsl:with-param>). The parameter will be the number of columns in 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 -->
    <text:p><xsl:value-of select="last"/></text:p>
    <text:p><xsl:value-of select="first"/></text:p>
    <text:p><xsl:value-of select="substring(@id,2)"/></text:p>

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 -->
    "oooc:=MID(&quot;FFFFFFDCBAA&quot;; INT([.E{position()+2}]*10)+1; 1)"/>

<!-- formula for final grade as a percentage -->
        "oooc:=SUM([.F{position()+2}:.{$lastCol}{position()+2}] *

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 oooc:=MID("FFFFFFDCBAA";INT([.E3]*10)+1;1) (the oooc: does not show up in the application).

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 that displays as {=SUM([.F3:.K3]*[.$F$2:.$K$2])/100}.

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"/>
 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:when test="$n != ''">
        <table:table-cell office:value-type="float" office:value="{$n}">
            <text:p><xsl:value-of select="$n"/></text:p>

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:when test="$n &lt;= 26">
            <xsl:value-of select="substring($letters, $n, 1)"/>
            <xsl:value-of select="concat(
                substring($letters, floor(($n - 1) div 26) + 1, 1),
                substring($letters, (($n - 1) mod 26) + 1, 1))"/>

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.

[6] If you want to have a replacement for the decimal part of the number (as in $15.--), you add number:decimal-replacement="--" to the <number:number> element.

Copyright (c) 2005 O’Reilly & Associates, Inc. Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.2 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts. A copy of the license is included in the section entitled "GNU Free Documentation License".