Mit Excel-XML-Tabellen arbeiten

(Auszug aus "XSLT Kochbuch" von Sal Mangano)

Problem

Sie möchten Daten aus Excel nach XML exportieren, aber nicht im nativen Microsoft-eigenen Format.

Lösung

XSLT 1.0

Wenn Sie eine Excel-Tabelle haben, die wie folgt aussieht:

Datum Preis Menge
20010817 61.88 260163
20010820 62.7 241859
20010821 60.78 233989
20010822 60.66 387444

dann sieht für Excel (XP bzw. 2003) das XML-Format so aus:

<?xml version="1.0"?>
<Workbook 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:html="http://www.w3.org/TR/REC-html40">
  <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
    <Author>Salvatore R. Mangano</Author>
    <LastAuthor>Salvatore R. Mangano</LastAuthor>
    <Created>2002-08-18T00:43:49Z</Created>
    <LastSaved>2002-08-18T02:19:21Z</LastSaved>
    <Company>Descriptix</Company>
    <Version>10.3501</Version>
  </DocumentProperties>
  <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
    <DownloadComponents/>
    <LocationOfComponents HRef="/"/>
  </OfficeDocumentSettings>
  <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
    <WindowHeight>9915</WindowHeight>
    <WindowWidth>10140</WindowWidth>
    <WindowTopX>240</WindowTopX>
    <WindowTopY>255</WindowTopY>
    <ProtectStructure>False</ProtectStructure>
    <ProtectWindows>False</ProtectWindows>
  </ExcelWorkbook>
  <Styles>
    <Style ss:ID="Default" ss:Name="Normal">
      <Alignment ss:Vertical="Bottom"/>
      <Borders/>
      <Font/>
      <Interior/>
      <NumberFormat/>
      <Protection/>
    </Style>
  </Styles>
  <Worksheet ss:Name="msft">
    <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="5" x:FullColumns="1" x:FullRows="1">
      <Row>
        <Cell>
          <Data ss:Type="String">Datum</Data>
        </Cell>
        <Cell>
          <Data ss:Type="String">Preis</Data>
        </Cell>
        <Cell>
          <Data ss:Type="String">Menge</Data>
        </Cell>
      </Row>
      <Row>
        <Cell>
          <Data ss:Type="Number">20010817</Data>
        </Cell>
        <Cell>
          <Data ss:Type="Number">61.88</Data>
        </Cell>
        <Cell>
          <Data ss:Type="Number">260163</Data>
        </Cell>
      </Row>
      <Row>
        <Cell>
          <Data ss:Type="Number">20010820</Data>
        </Cell>
        <Cell>
          <Data ss:Type="Number">62.7</Data>
        </Cell>
        <Cell>
          <Data ss:Type="Number">241859</Data>
        </Cell>
      </Row>
      <Row>
        <Cell>
          <Data ss:Type="Number">20010821</Data>
        </Cell>
        <Cell>
          <Data ss:Type="Number">60.78</Data>
        </Cell>
        <Cell>
          <Data ss:Type="Number">233989</Data>
        </Cell>
      </Row>
      <Row>
        <Cell>
          <Data ss:Type="Number">20010822</Data>
        </Cell>
        <Cell>
          <Data ss:Type="Number">60.66</Data>
        </Cell>
        <Cell>
          <Data ss:Type="Number">387444</Data>
        </Cell>
      </Row>
    </Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
      <Selected/>
      <Panes>
        <Pane>
          <Number>3</Number>
          <ActiveRow>11</ActiveRow>
          <ActiveCol>5</ActiveCol>
        </Pane>
      </Panes>
      <ProtectObjects>False</ProtectObjects>
      <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
  </Worksheet>
</Workbook>

Das ist vermutlich nicht das, was Ihnen vorschwebte!

In diesem Beispiel wird eine Excel-XML-Datei bequem in eine einfachere XML-Datei umgewandelt. Viele mit Excel erzeugte Tabellen besitzen eine Struktur, in der die erste Zeile Spaltennamen enthält und die folgenden Zeilen die Daten für diese Spalten enthalten.

Eine nahe liegende Abbildung würde die Spaltennamen in Elementnamen konvertieren und die restlichen Zellen in Elementinhalte. Dann fehlten nur noch die Information bezüglich der Namen im obersten Element und das Element, das jeweils eine Zeile enthält. Dieses Stylesheet erhält die Namen als Parameter mit einigen offensichtlichen Vorgabewerten. Es konvertiert einen Teil der nützlichen Metadaten in Kommentare und schmeißt das Excel-spezifische Zeug weg. Dieser Abschnitt bietet einige weitere Parameter, die die Umwandlung verallgemeinern, z.B. welche Zeile die Spaltennamen enthält, wo die Daten anfangen und was bei leeren Zellen passieren soll:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
  <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
  <!-- Der Name des obersten Elements -->
  <xsl:param name="topLevelName" select=" 'Table' "/>
  <!-- Der Name jeder Zeile -->
  <xsl:param name="rowName" select=" 'Row' "/>
  <!-- Der zu verwendende Namensraum -->
  <xsl:param name="namespace"/>
  <!-- Das zu verwendende Namensraumpräfix -->
  <xsl:param name="namespacePrefix"/>
  <!-- Das zu verwendende Zeichen, falls Spaltennamen Leerstellen enthalten -->
  <xsl:param name="wsSub" select="'_'"/>
  <!-- Bestimmt, welche Zeile die Spaltennamen enthält -->
  <xsl:param name="colNamesRow" select="1"/>
  <!-- Bestimmt, in welcher Zeile die Daten anfangen -->
  <xsl:param name="dataRowStart" select="2"/>
  <!-- Bei false werden Zellen ohne Inhalt oder solche, die nur Leerraum enthalten, übersprungen -->
  <xsl:param name="includeEmpty" select="true( )"/>
  <!-- Bei false werden keine Metadaten über Autor und Erzeugung in einen Kommentar gesetzt -->
  <xsl:param name="includeComment" select="true( )"/>
  <!-- Normalisiere den namespacePrefix -->
  <xsl:variable name="nsp">
    <xsl:if test="$namespace">
      <!-- Verwende Präfix nur, wenn Namensraum angegeben ist -->
      <xsl:choose>
        <xsl:when test="contains($namespacePrefix,':')">
          <xsl:value-of select="concat(translate(substring-before($namespacePrefix,':'),' ',''),':')"/>
        </xsl:when>
        <xsl:when test="translate($namespacePrefix,' ','')">
          <xsl:value-of select="concat(translate($namespacePrefix,' ',''),':')"/>
        </xsl:when>
        <xsl:otherwise/>
      </xsl:choose>
    </xsl:if>
  </xsl:variable>
  <!-- Hole die Namen aller Spalten mit ersetztem Leerraum -->
  <xsl:variable name="COLS" select="/*/*/*/ss:Row[$colNamesRow]/ss:Cell"/>
  <xsl:template match="o:DocumentProperties">
    <xsl:if test="$includeComment">
      <xsl:text>&#xa;</xsl:text>
      <xsl:comment>
       <xsl:text>&#xa;</xsl:text>
        <xsl:if test="normalize-space(o:Company)">
          <xsl:text>Company: </xsl:text>
          <xsl:value-of select="o:Company"/>
          <xsl:text>&#xa;</xsl:text>
        </xsl:if>
        <xsl:text>Author: </xsl:text>
        <xsl:value-of select="o:Author"/>
        <xsl:text>&#xa;</xsl:text>
        <xsl:text>Created on: </xsl:text>
        <xsl:value-of select="translate(o:Created,'TZ',' ')"/>
        <xsl:text>&#xa;</xsl:text>
        <xsl:text>Last Author: </xsl:text>
        <xsl:value-of select="o:LastAuthor"/>
        <xsl:text>&#xa;</xsl:text>
        <xsl:text>Saved on:</xsl:text>
        <xsl:value-of select="translate(o:LastSaved,'TZ',' ')"/>
        <xsl:text>&#xa;</xsl:text>
      </xsl:comment>
    </xsl:if>
  </xsl:template>
  <xsl:template match="ss:Table">
    <xsl:element name="{concat($nsp,translate($topLevelName,'&#x20;&#x9;&#xa;',$wsSub))}" namespace="{$namespace}">
      <xsl:apply-templates select="ss:Row[position( ) &gt;= $dataRowStart]"/>
    </xsl:element>
  </xsl:template>
  <xsl:template match="ss:Row">
    <xsl:element name="{concat($nsp,translate($rowName,'&#x20;&#x9;&#xa;',$wsSub))}" namespace="{$namespace}">
      <xsl:for-each select="ss:Cell">
        <xsl:variable name="pos" select="position( )"/>
        <!-- Hole korrekten Spaltennamen, auch wenn es in der Originaltabelle leere Spalten gibt -->
        <xsl:variable name="colName">
          <xsl:choose>
            <xsl:when test="@ss:Index and $COLS[@ss:Index = current( )/@ss:Index]">
              <xsl:value-of select="$COLS[@ss:Index = current( )/@ss:Index]/ss:Data"/>
            </xsl:when>
            <xsl:when test="@ss:Index">
              <xsl:value-of select="$COLS[number(current( )/@ss:Index)]/ss:Data"/>
            </xsl:when>
            <xsl:otherwise>
              <xsl:value-of select="$COLS[$pos]/ss:Data"/>
            </xsl:otherwise>
          </xsl:choose>
        </xsl:variable>
        <xsl:if test="$includeEmpty or translate(ss:Data,'&#x20;&#x9;&#xa;','')">
          <xsl:element name="{concat($nsp,translate($colName,'&#x20;&#x9;&#xa;',$wsSub))}" namespace="{$namespace}">
            <xsl:value-of select="ss:Data"/>
          </xsl:element>
        </xsl:if>
      </xsl:for-each>
    </xsl:element>
  </xsl:template>
  <xsl:template match="text( )"/>
</xsl:stylesheet>

Das Ergebnis der Transformation (mit den vorgegebenen Parameterwerten) ist die folgende, wesentlich direktere XML-Darstellung:

<Table>
  <Row>
    <Datum>20010817</Datum>
    <Preis>61.88</Preis>
    <Menge>260163</Menge>
  </Row>
  <Row>
    <Datum>20010820</Datum>
    <Preis>62.7</Preis>
    <Menge>241859</Menge>
  </Row>
  <Row>
    <Datum>20010821</Datum>
    <Preis>60.78</Preis>
    <Menge>233989</Menge>
  </Row>
  <Row>
    <Datum>20010822</Datum>
    <Preis>60.66</Preis>
    <Menge>387444</Menge>
  </Row>
</Table>

XSLT 2.0

Die wesentlichen Verbesserungen beim Einsatz von XSLT 2.0 bestehen darin, Hilfsfunktionen zum Entfernen redundanten Codes einführen zu können, und in der prägnanteren XPath 2.0-Syntax.

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:fn="http://www.w3.org/2005/02/xpath-functions" 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:ckbk="http://www.oreilly.com/xsltckbk">
  <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
  <!-- Der Name des obersten Elements -->
  <xsl:param name="topLevelName" select=" 'Table' " as="xs:string"/>
  <!-- Der Name jeder Zeile -->
  <xsl:param name="rowName" select=" 'Row' " as="xs:string"/>
  <!-- Der zu verwendende Namensraum -->
  <xsl:param name="namespace" select=" '' " as="xs:string"/>
  <!-- Das zu verwendende Namensraumpräfix -->
  <xsl:param name="namespacePrefix" select=" '' " as="xs:string" />
  <!-- Das zu verwendende Zeichen, falls Spaltennamen Leerstellen enthalten -->
  <xsl:param name="wsSub" select="'_'" as="xs:string"/>
  <!-- Bestimmt, welche Zeile die Spaltennamen enthält -->
  <xsl:param name="colNamesRow" select="1" as="xs:integer"/>
  <!-- Bestimmt, in welcher Zeile die Daten anfangen -->
  <xsl:param name="dataRowStart" select="2" as="xs:integer"/>
  <!-- Bei false werden Zellen ohne Inhalt oder solche, die nur Leerraum enthalten, übersprungen -->
  <xsl:param name="includeEmpty" select="true( )" as="xs:boolean"/>
  <!-- Bei false werden keine Metadaten über Autor und Erzeugung in einen Kommentar gesetzt -->
  <xsl:param name="includeComment" select="true( )" as="xs:boolean"/>
  <!-- Normalisiere den namespacePrefix -->
  <xsl:variable name="nsp" as="xs:string" select="if (contains($namespacePrefix,':')) then concat(translate(substring-before($namespacePrefix,':'),' ',''),':') else if (matches($namespacePrefix,'\W')) then concat(translate($namespacePrefix,' ',''),':') else '' "/>
  <!-- Hole die Namen aller Spalten -->
  <xsl:variable name="COLS" select="/*/*/*/ss:Row[$colNamesRow]/ss:Cell"/>
  <xsl:template match="o:DocumentProperties">
    <xsl:if test="$includeComment">
      <xsl:text>&#xa;</xsl:text>
      <xsl:comment select="concat('&#xa;', ckbk:comment(o:Company), ckbk:comment(o:Author), ckbk:comment(o:Created,'Created on'), ckbk:comment(o:LastAuthor,'Last Author'), ckbk:comment(o:LastSaved,'Saved on'))"/>
    </xsl:if>
    <xsl:text>&#xa;</xsl:text>
  </xsl:template>
  <xsl:template match="ss:Table">
    <xsl:element name="{ckbk:makeName($nsp,$topLevelName,$wsSub)}" namespace="{$namespace}">
      <xsl:apply-templates select="ss:Row[position( ) ge $dataRowStart]"/>
    </xsl:element>
  </xsl:template>
  <xsl:template match="ss:Row">
    <xsl:element name="{ckbk:makeName($nsp,$rowName,$wsSub)}" namespace="{$namespace}">
      <xsl:for-each select="ss:Cell">
        <xsl:variable name="pos" select="position( )"/>
        <!-- Hole korrekten Spaltennamen, auch wenn es in der Originaltabelle leere Spalten gibt -->
        <xsl:variable name="colName" as="xs:string" select="if (@ss:Index and $COLS[@ss:Index = current( )/@ss:Index]) then $COLS[@ss:Index = current( )/@ss:Index]/ss:Datae else if (@ss:Index) then $COLS[number(current( )/@ss:Index)]/ss:Data else $COLS[$pos]/ss:Data"/>
        <xsl:if test="$includeEmpty or translate(ss:Data,'&#x20;&#x9;&#xA;','')">
          <xsl:element name="{ckbk:makeName($nsp,$colName,$wsSub)}" namespace="{$namespace}">
            <xsl:value-of select="ss:Data"/>
          </xsl:element>
        </xsl:if>
      </xsl:for-each>
    </xsl:element>
  </xsl:template>
  <xsl:template match="text( )"/>
  <xsl:function name="ckbk:makeName" as="xs:string">
    <xsl:param name="nsp" as="xs:string"/>
    <xsl:param name="name" as="xs:string"/>
    <xsl:param name="wsSub" as="xs:string"/>
    <xsl:sequence select="concat($nsp,translate($name,'&#x20;&#x9;&#xa;',$wsSub))"/>
  </xsl:function>
  <xsl:function name="ckbk:comment" as="xs:string">
    <xsl:param name="elem"/>
    <xsl:sequence select="ckbk:comment($elem, local-name($elem))"/>
  </xsl:function>
  <xsl:function name="ckbk:comment" as="xs:string">
    <xsl:param name="elem"/>
    <xsl:param name="label" as="xs:string"/>
    <xsl:sequence select="if (normalize-space($elem)) then concat($label,': ',$elem,'&#xa;') else '' "/>
  </xsl:function>
</xsl:stylesheet>

Diskussion

Fast hätte ich dieses Rezept im Buch weggelassen, weil es mir am Anfang zu trivial erschien. Aber dann habe ich erkannt, dass eine robuste Lösung mit vielen Sonderfällen umgehen können muss, was viele Implementierungen (inklusive meiner ersten) nicht konnten. So gibt es in Tabellen oftmals leere Spalten, die zur räumlichen Trennung verwendet werden. Sie müssen wissen, wie Sie mit ihnen umgehen, indem Sie das @ss:Index-Attribut untersuchen. In der ersten Version dieses Buchs waren außerdem noch viele Wahlmöglichkeiten fest kodiert, die in dieser Version als Parameter auftauchen.

Mindestens eine offensichtliche Erweiterung könnte an diesem Stylesheet noch vorgenommen werden: die Behandlung mehrerer ss:Worksheet-Elemente. Diese Behandlung könnte dadurch erfolgen, dass die Arbeitsblatt-Nummer als Parameter angegeben wird:

<xsl:param name="WSNum" select="1"/>
<xsl:variable name="COLS" select="/*/ss:Worksheet[$WSNum]/*/ss:Row[$colNamesRow]/ss:Cell"/>
<xsl:template match="ss:Workbook">
  <xsl:element name="{concat($nsp,translate($topLevelName,'&#x20;&#x9;&#xA;',$wsSub))}" namespace="{$namespace}">
    <xsl:apply-templates select="ss:Worksheet[number($WSNum)]/ss:Table"/>
  </xsl:element>
</xsl:template>

Eine anspruchsvollere Lösung behandelt jedes Worksheet in einem Dokument mit mehreren Worksheets als separates Element im resultierenden Dokument. Diese Vorgehensweise bedeutet, dass die Spaltennamen nicht mehr als globale Variablen behandelt werden können:

<xsl:template match="ss:Workbook">
  <xsl:element name="{concat($nsp,translate($topLevelName,'&#x20;&#x9;&#xA;',$wsSub))}" namespace="{$namespace}">
    <xsl:choose>
      <xsl:when test="number($WSNum) &gt; 0">
        <xsl:apply-templates select="ss:Worksheet[number($WSNum)]/ss:Table">
          <xsl:with-param name="COLS" select="ss:Worksheet[number($WSNum)]/*/ss:Row[$colNamesRow]/ss:Cell"/>
        </xsl:apply-templates>
      </xsl:when>
      <xsl:otherwise>
        <xsl:for-each select="ss:Worksheet">
          <xsl:element name="{concat($nsp,translate(@ss:Name,'&#x20;&#x9;&#xA;',$wsSub))}" namespace="{$namespace}">
            <xsl:apply-templates select="ss:Table">
              <xsl:with-param name="COLS" select="*/ss:Row[$colNamesRow]/ss:Cell"/>
            </xsl:apply-templates>
          </xsl:element>
        </xsl:for-each>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:element>
</xsl:template>
<xsl:template match="ss:Table">
  <xsl:param name="COLS"/>
    <xsl:apply-templates select="ss:Row[position( ) &gt;= $dataRowStart]">
      <xsl:with-param name="COLS" select="$COLS"/>
    </xsl:apply-templates>
</xsl:template>
<xsl:template match="ss:Row">
  <xsl:param name="COLS"/>
  <!-- Der Rest stimmt mit dem Original überein... -->
</xsl:template>

Das einzige Problem bei dieser Lösung ist, dass sie die Annahme macht, dass die Spaltennamen in allen Arbeitsblättern immer in der gleichen Zeile stehen.

  

zum Seitenanfang

<< zurück vor >>

 

 

 

Tipp der data2type-Redaktion:
Zum Thema XSLT bieten wir auch folgende Schulungen zur Vertiefung und professionellen Fortbildung an:

Copyright © 2006 O'Reilly Verlag GmbH & Co. KG
Für Ihren privaten Gebrauch dürfen Sie die Online-Version ausdrucken.
Ansonsten unterliegt dieses Kapitel aus dem Buch "XSLT Kochbuch" denselben Bestimmungen, wie die gebundene Ausgabe: Das Werk einschließlich aller seiner Teile ist urheberrechtlich geschützt. Alle Rechte vorbehalten einschließlich der Vervielfältigung, Übersetzung, Mikroverfilmung sowie Einspeicherung und Verarbeitung in elektronischen Systemen.

O'Reilly Verlag GmbH & Co. KG, Balthasarstraße 81, 50670 Köln, kommentar(at)oreilly.de