Practical example: test Excel column names

Turn 1 into A, 26 into Z and 27 into AA. A function like this is useful when processing code builds cell addresses for an Excel file. The conversion looks simple, but a few transitions make it easy to get wrong.

This example comes from our XSpec training. The material includes several implementations, a table of 16,384 expected values and a test generator. The idea is practical: a replacement function should produce the same column names as the reference.

Pay attention to the transitions

Excel column names are not quite ordinary base-26 numbers. There is no zero digit. Z is followed by AA, and ZZ by AAA. The following algorithm therefore subtracts one before division and remainder calculation.

The public function accepts column numbers from 1 through 16,384, the Excel worksheet limit. The last column is XFD. A general-purpose letter-numbering function could accept a larger range, but that is not the task here.

examples/04-columns/column-name.xsl

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="3.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  xmlns:f="urn:example:columns" xmlns:e="urn:example:errors"
  exclude-result-prefixes="#all">
  <!-- This public function deliberately enforces the Excel column range. -->
  <xsl:function name="f:column-name" as="xs:string">
    <xsl:param name="number" as="xs:integer"/>
    <xsl:sequence select="
      if ($number lt 1 or $number gt 16384)
      then error(xs:QName('e:OUT_OF_RANGE'),
        'The column number must be between 1 and 16384.')
      else f:letters($number)"/>
  </xsl:function>
  <xsl:function name="f:letters" as="xs:string">
    <xsl:param name="number" as="xs:integer"/>
    <xsl:sequence select="
      if ($number eq 0) then ''
      else concat(f:letters(($number - 1) idiv 26),
        codepoints-to-string(65 + (($number - 1) mod 26)))"/>
  </xsl:function>
</xsl:stylesheet>

The public function checks the input range first. The recursive helper then calculates the name. Zero, negative numbers and values above the limit should produce an error.

Two particularly useful tests sit at the transition between two and three letters:

examples/04-columns/column-name.xspec

<x:scenario xmlns:x="http://www.jenitennison.com/xslt/xspec" xmlns:xs="http://www.w3.org/2001/XMLSchema" 
  xmlns:f="urn:example:columns" xmlns:e="urn:example:errors" label="Column 702 is ZZ"> 
    <x:call function="f:column-name"><x:param select="702"/></x:call>
    <x:expect label="Return ZZ" select="'ZZ'" result-type="xs:string"/>
  </x:scenario>

examples/04-columns/column-name.xspec

<x:scenario xmlns:x="http://www.jenitennison.com/xslt/xspec" xmlns:xs="http://www.w3.org/2001/XMLSchema" 
  xmlns:f="urn:example:columns" xmlns:e="urn:example:errors" label="Column 703 is AAA"> 
    <x:call function="f:column-name"><x:param select="703"/></x:call>
    <x:expect label="Return AAA" select="'AAA'" result-type="xs:string"/>
  </x:scenario>

The small hand-written suite contains fifteen valid inputs and three invalid ones: 0, -1 and 16,385. Short boundary tests are convenient during development because a failure immediately points to the troublesome transition. Names such as "old" or "performance" in the historical files are not evidence that an implementation is correct or faster; those claims need a comparison.

Check all 16,384 values

Here the allowed range is small enough to test every value. Expected names come from the supplied colnames.xml, not from the replacement function. Otherwise the same bug could appear on both sides of the comparison and go unnoticed.

All 16,384 pairs have also been compared with an independent Python calculation. No mismatches were found, and the numbering covers the complete range without duplicates. That checks the reference data. Running the new XSLT function through XSpec is a separate task and remains outstanding.

The package converts the reference table into a simple fixture. The exhaustive check processes the whole table and returns any numbers and names that differ. Its XSpec wrapper stays short:

examples/04-columns/exhaustive.xspec

<?xml version="1.0" encoding="UTF-8"?> 
<x:description xmlns:x="http://www.jenitennison.com/xslt/xspec"
  stylesheet="exhaustive-check.xsl">
  <x:scenario label="Compare all 16384 values with the independent fixture">
    <x:context href="column-cases.xml"/>
    <x:expect label="Check every value without a mismatch">
      <verification checked="16384" mismatches="0"/>
    </x:expect>
  </x:scenario>
</x:description>

A single XSpec expectation checks the summary: 16,384 values processed and no mismatches. This does not create a separate report entry for every column number. If the check fails, the output contains the mismatching cases.

Generate a separate scenario for each value

Sometimes one report entry per input is exactly what is needed. The package includes a generator for that:

examples/04-columns/generate-tests.xsl

<?xml version="1.0" encoding="UTF-8"?> 
<xsl:stylesheet version="3.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  xmlns:x="http://www.jenitennison.com/xslt/xspec"
  xmlns:f="urn:example:columns" exclude-result-prefixes="xs">
  <xsl:output method="xml" indent="yes"/>
  <xsl:param name="from" as="xs:integer" select="1"/>
  <xsl:param name="to" as="xs:integer" select="16384"/>
  <xsl:template match="/">
    <!-- The output belongs in this example's generated/ directory. -->
    <x:description stylesheet="../column-name.xsl">
      <xsl:namespace name="f" select="'urn:example:columns'"/>
      <xsl:for-each select="cases/case[xs:integer(@number) ge $from
                                     and xs:integer(@number) le $to]">
        <x:scenario label="Generated test for column {@number}">
          <x:call function="f:column-name"><x:param select="{@number}"/></x:call>
          <x:expect label="Return {@expected}" select="'{@expected}'"/>
        </x:scenario>
      </xsl:for-each>
    </x:description>
  </xsl:template>
</xsl:stylesheet>

Here's how the tests are generated for the entire area:

python scripts/generate-individual-tests.py --start 1 --end 16384

The new file is located in examples/04-columns/generated/individual.xspec. It must then be run using XSpec. To test it, you can narrow down the range using --start and --end, for example, to the numbers around 702 and 703.

The generator selects actual column numbers. That is easier to follow than a series of position() filters whose positions can change after each filter.

Separate scenarios produce a detailed report, but add generation, compilation and reporting work. Keep the compact exhaustive check for regular runs and the generated scenarios for detailed inspection. The roughly fifteen-minute duration in the 2013 slides was a measurement from that setup, not a runtime estimate for the new environment.

<< back

next >>