SpotGrowth to Excel :: Sharp Spb

The SharpSpb project has created an encompassing data-structure for standardizing and normalizing of entomological field data gathered over decades and taking multiple forms, called the Spot-Growth Database.

The Spot-Growth Database and its associated tools organize this information as Xml, and do so according to the rules defined by the SpotGrowth schema, a file (itself Xml) which strictly defines how the SpotGrowth information is to be arranged and indentified.

Excel spreadsheets are Xml files structured according to a different schema, the Office Document Workbook schema.

If a user wanted to view their Spot-Growth data as an Excel Workbook, the most direct means of achieving this would transform the SpotGrowth data directly to Excel data. It is a matter of relabeling the values, SpotGrowth calls it Fingerprint/DateOfVisit, Excel calls it Table/Row/Cell/Value under a certain column.

Since all modern applications create and consume Xml, a dedicated language to transforming xml adhering to one schematic into xml adhereing to another schematic (in addition to other capabilities) would be immensly useful. That is what Xslt is for.

Here is the Xslt code that would transform SpotGrowth Xml into Excel Workbook Xml, ready to be loaded or opened like any Office document:

<xsl:stylesheet

  version="1.0"

  xmlns="urn:schemas-microsoft-com:office:spreadsheet"

  xmlns:o="urn:schemas-microsoft-com:office:office"

  xmlns:x="urn:schemas-microsoft-com:office:excel"

  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

  exclude-result-prefixes="ss">

  <xsl:output method="xml" indent="yes" omit-xml-declaration="no"/>

  <xsl:template match="/">

    <xsl:processing-instruction name="mso-application">

      <xsl:text>progid="Excel.Sheet"</xsl:text>

    </xsl:processing-instruction>

    <Workbook

      xmlns:html="http://www.w3.org/TR/REC-html40">

      <xsl:apply-templates/>

    </Workbook>

  </xsl:template>

  <xsl:template match="SpotGrowth">

    <!-- Create Spot Table -->

    <Worksheet>

      <xsl:attribute name="ss:Name">

        <xsl:text>Spots</xsl:text>

      </xsl:attribute>

      <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="{count(/SpotGrowth/Spot)+1}">

        <Row>

          <Cell>

            <Data ss:Type="String">SpotName</Data>

          </Cell>

          <Cell>

            <Data ss:Type="String">SpotDescription</Data>

          </Cell>

        </Row>

        <xsl:apply-templates select="Spot"/>

      </Table>

    </Worksheet>

    <!-- Create Visit Table -->

    <Worksheet>

      <xsl:attribute name="ss:Name">

        <xsl:text>Visits</xsl:text>

      </xsl:attribute>

      <Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="{count(/SpotGrowth/Spot/Visit)+1}">

        <Row>

          <Cell>

            <Data ss:Type="String">SpotName</Data>

          </Cell>

          <Cell>

            <Data ss:Type="String">VisitName</Data>

          </Cell>

          <Cell>

            <Data ss:Type="String">VisitDescription</Data>

          </Cell>

          <Cell>

            <Data ss:Type="String">VisitNumber</Data>

          </Cell>

          <Cell>

            <Data ss:Type="String">CanSimulate</Data>

          </Cell>

        </Row>

        <xsl:apply-templates select="Spot/Visit"/>

      </Table>

    </Worksheet>

    <!-- Create Visit/* Tables -->

    <xsl:call-template name="TableTemplate" >

      <xsl:with-param name ="TableName">Fingerprint</xsl:with-param>

    </xsl:call-template>

    <xsl:call-template name="TableTemplate" >

      <xsl:with-param name ="TableName">BroodStageDistribution</xsl:with-param>

    </xsl:call-template>

    <xsl:call-template name="TableTemplate" >

      <xsl:with-param name ="TableName">PineSpeciesDistribution</xsl:with-param>

    </xsl:call-template>

    <xsl:call-template name="TableTemplate" >

      <xsl:with-param name ="TableName">ForestStatus</xsl:with-param>

    </xsl:call-template>

    <xsl:call-template name="TableTemplate" >

      <xsl:with-param name ="TableName">TreeDBHDistribution</xsl:with-param>

    </xsl:call-template>

  </xsl:template>

  <!-- Visit -->

  <xsl:template match="Visit">

    <Row>

      <Cell>

        <Data ss:Type="String">

          <xsl:value-of select="SpotName"/>

        </Data>

      </Cell>

      <Cell>

        <Data ss:Type="String">

          <xsl:value-of select="VisitName"/>

        </Data>

      </Cell>

      <Cell>

        <Data ss:Type="String">

          <xsl:value-of select="VisitDescription"/>

        </Data>

      </Cell>

      <Cell>

        <Data ss:Type="String">

          <xsl:value-of select="VisitNumber"/>

        </Data>

      </Cell>

      <Cell>

        <Data ss:Type="String">

          <xsl:value-of select="CanSimulate"/>

        </Data>

      </Cell>

    </Row>

  </xsl:template>

  <!-- Spot -->

  <xsl:template match="Spot">

    <Row>

      <Cell>

        <Data ss:Type="String">

          <xsl:value-of select="SpotName"/>

        </Data>

      </Cell>

      <Cell>

        <Data ss:Type="String">

          <xsl:value-of select="SpotDescription"/>

        </Data>

      </Cell>

    </Row>

  </xsl:template>

  <xsl:template name="TableTemplate">

    <xsl:param name="TableName"/>

    <Worksheet>

      <xsl:attribute name="ss:Name">

        <xsl:value-of select="$TableName"/>

      </xsl:attribute>

      <Table ss:ExpandedColumnCount="{count(Spot[1]/Visit[1]/*[local-name()=$TableName][1]/*)}" ss:ExpandedRowCount="{count(/SpotGrowth/Spot/Visit)+1}" >

        <xsl:apply-templates select="Spot[1]/Visit[1]/*[local-name()=$TableName][1]" mode="Headers"/>

        <xsl:apply-templates select="Spot/Visit/*[local-name()=$TableName]"/>

      </Table>

    </Worksheet>

  </xsl:template>

  <xsl:template match="SpotGrowth/Spot/Visit/*">

    <Row>

      <xsl:apply-templates/>

    </Row>

  </xsl:template>

  <xsl:template match="SpotGrowth/Spot/Visit/*/*">

    <Cell>

      <Data ss:Type="String">

        <xsl:value-of select="."/>

      </Data>

    </Cell>

  </xsl:template>

  <xsl:template match="SpotGrowth/Spot/Visit/*" mode="Headers">

    <Row>

      <xsl:apply-templates mode="Headers"/>

    </Row>

  </xsl:template>

  <xsl:template match="SpotGrowth/Spot/Visit/*/*" mode="Headers">

    <Cell>

      <Data ss:Type="String">

        <xsl:value-of select="local-name()"/>

      </Data>

    </Cell>

  </xsl:template>

</xsl:stylesheet>