Wednesday, 4 October 2017

XSLT to convert Excel (xls) to html

Issue

Require html from an xls (Excel) spreadsheet

Resolution

Quick and dirty XSLT transformation

Xquery

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
     xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" 
    xmlns="http://www.w3.org/1999/xhtml"
 exclude-result-prefixes="ss"
         version="2.0">
 <xsl:output method="html"/>

  <xsl:template match="/">
    <html>
       <head>
        <style>
         <xsl:apply-templates select="ss:Workbook/ss:Styles/ss:Style"/>
        </style>
       </head>
     <body>
      <div id="layout2">
       <xsl:apply-templates select="ss:Workbook/ss:Worksheet/ss:Table"/>
      </div>
     </body>
    </html>
  </xsl:template>
 
  <xsl:template match="ss:Style">
   <xsl:text>#</xsl:text>
   <xsl:value-of select="@ss:ID"/>
   <xsl:text> {</xsl:text>
   <xsl:if test="ss:Alignment/@ss:Horizontal">
   <xsl:text>text-align: </xsl:text>
   <xsl:value-of select="ss:Alignment/@ss:Horizontal"/>
   <xsl:text>;</xsl:text>
  </xsl:if>
  <xsl:if test="ss:Alignment/@ss:Vertical">
   <xsl:text>vertical-align: </xsl:text>
   <xsl:value-of select="ss:Alignment/@ss:Vertical"/>
   <xsl:text>;</xsl:text>
  </xsl:if>
  <xsl:if test="ss:Alignment/@ss:Vertical">
   <xsl:text>vertical-align: </xsl:text>
   <xsl:value-of select="ss:Alignment/@ss:Vertical"/>
   <xsl:text>;</xsl:text>
  </xsl:if>
  <xsl:if test="ss:Font/@ss:Bold=1">
   <xsl:text>font-weight: bold;</xsl:text>
  </xsl:if>
  <xsl:if test="ss:Font/@ss:Color">
   <xsl:text>color: </xsl:text>
   <xsl:value-of select="ss:Font/@ss:Color"/>
   <xsl:text>;</xsl:text>
  </xsl:if>
  <xsl:if test="ss:Font/@ss:StrikeThrough=1">
   <xsl:text>text-decoration: line-through;</xsl:text>
  </xsl:if>
  <xsl:if test="ss:Font/@ss:Underline='Single'">
   <xsl:text>text-decoration: underline;</xsl:text>
  </xsl:if>
  <xsl:if test="ss:Interior/@ss:Color">
   <xsl:text>background-color: </xsl:text>
   <xsl:value-of select="ss:Interior/@ss:Color"/>
   <xsl:text>;</xsl:text>
  </xsl:if>
  <xsl:for-each select="ss:Borders/ss:Border">
   <xsl:if test="@ss:Color">
    <xsl:text>border-</xsl:text>
    <xsl:value-of select="@ss:Position"/>
    <xsl:text>-color:</xsl:text>
    <xsl:value-of select="@ss:Color"/>
    <xsl:text>;</xsl:text>
   </xsl:if>
   <xsl:if test="@ss:Weight">
      <xsl:text> border-</xsl:text>
      <xsl:value-of select="@ss:Position"/>
      <xsl:text>-width:</xsl:text>
      <xsl:value-of select="@ss:Weight"/>
      <xsl:text>px;</xsl:text>
   </xsl:if>
   <xsl:if test="@ss:LineStyle">
    <xsl:text>border-</xsl:text><xsl:value-of select="@ss:Position"/><xsl:text>-style:</xsl:text>
    <xsl:value-of select="@ss:LineStyle"/>
    <xsl:text>;</xsl:text>
   </xsl:if>
  </xsl:for-each>
  <xsl:text>}</xsl:text>
  <xsl:text>.scrolltable {overflow: scroll;}</xsl:text>
 </xsl:template> 
 
 <xsl:template match="ss:Table">
  <div class="scrolltable">
   <table>
    <xsl:apply-templates select="ss:Row"/>
   </table>
  </div>
 </xsl:template>
 
 <xsl:template match="ss:Row">
  <tr>
   <xsl:apply-templates select="ss:Cell"/>
  </tr>
 </xsl:template>
 
 <xsl:template match="ss:Row[@ss:Hidden = '1']" priority="10"/> 
 
 <xsl:template match="ss:Cell">
  <td>
   <xsl:attribute name="id">
    <xsl:value-of select="@ss:StyleID"/>
   </xsl:attribute>
   <xsl:if test="@ss:MergeAcross">
    <xsl:attribute name="colspan">
     <xsl:value-of select="@ss:MergeAcross+1"/>
    </xsl:attribute>
   </xsl:if>
   <xsl:value-of select="ss:Data"/>
  </td>
 </xsl:template>
 
</xsl:stylesheet>