使用 XSLT 创建 Excel (SpreadsheetML) 输出 [英] Create Excel (SpeadsheetML) output with XSLT

问题描述

我有这个 XML 文件，我想创建一个 XSL 文件以将其转换为 Excel.每行应代表一个标志.这些列将是关键属性，如颜色、ID、描述以及其他徽标的任何其他键.

I have this XML file and I want to create an XSL file to convert it to Excel. Each row should represent a logo. The columns will be the key attributes like color, id, description plus any other key for other logos.

<Top>
  <logo>
    <field key="id">172-32-1176</field>
    <field key="color">Blue</field>
    <field key="description"><p>Short Description</p></field>
    <field key="startdate">408 496-7223</field>
  </logo>
  <logo>
    <field key="id">111-111-111</field>
    <field key="color">Red</field>
  </logo>
  <!-- ... -->    
</Top>

XSL 文件是这样的:

The XSL file is something like this:

<xsl:stylesheet 
  version="1.0"
  xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  xmlns:msxsl="urn:schemas-microsoft-com:xslt"
  xmlns:user="urn:my-scripts"
  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:template match="/">
    <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"
    >
      <xsl:apply-templates/>
    </Workbook>
  </xsl:template>

  <xsl:template match="/*">  
    <Worksheet>
      <xsl:attribute name="ss:Name">
        <xsl:value-of> select="local-name(/*)"/>
      </xsl:attribute>
      <Table x:FullColumns="1" x:FullRows="1">
        <Row>
          <xsl:for-each select="*/*">
            <Cell>
              <Data ss:Type="String">
                <xsl:value-of select="@key"/>
              </Data>
            </Cell>
          </xsl:for-each>
        </Row>
        <xsl:apply-templates/>
      </Table>
    </Worksheet>
  </xsl:template>

  <xsl:template match="/*/*">
    <Row>
      <xsl:apply-templates/>
    </Row>
  </xsl:template>

  <xsl:template match="/*/*/*">
    <Cell>
      <Data ss:Type="String">
        <xsl:value-of select="."/>
      </Data>
    </Cell>
    <!-- <xsl:apply-templates/> -->
  </xsl:template>
</xsl:stylesheet>

但数据未正确放置在列下且列名重复.如何才能做到这一点?列可以按任何顺序排列，并且对于 Excel 中的第二行，列 stardate 应该为空.同理更多.

But data are not correctly placed under the columns and column names are repeating. How can this be done? The columns could be in any order and also column stardate should be empty for second row in excel. Similarly for more .

推荐答案

你们很亲近.在模板匹配方面尝试更具体 - 不要说 template match"/*/*/*" 当你可以说 template match="field".

You were very close. Try to be more specific when it comes to template matching - don't say template match"/*/*/*" when you can say template match="field".

除此之外，这是您的方法，只是略有修改:

Other than that, this is your approach, only slightly modified:

<xsl:stylesheet 
  version="1.0"
  xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  xmlns:msxsl="urn:schemas-microsoft-com:xslt"
  xmlns:user="urn:my-scripts"
  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" encoding="utf-8" indent="yes" />

  <xsl:template match="/">
    <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"
    >
      <xsl:apply-templates select="Top" />
    </Workbook>
  </xsl:template>

  <xsl:template match="Top">  
    <Worksheet ss:Name="{local-name()}">
      <Table x:FullColumns="1" x:FullRows="1">
        <Row>
          <!-- header row, made from the first logo -->
          <xsl:apply-templates select="logo[1]/field/@key" />
        </Row>
        <xsl:apply-templates select="logo" />
      </Table>
    </Worksheet>
  </xsl:template>

  <!-- a <logo> will turn into a <Row> -->
  <xsl:template match="logo">
    <Row>
      <xsl:apply-templates select="field" />
    </Row>
  </xsl:template>

  <!-- convenience: <field> and @key both turn into a <Cell> -->
  <xsl:template match="field | field/@key">
    <Cell>
      <Data ss:Type="String">
        <xsl:value-of select="."/>
      </Data>
    </Cell>
  </xsl:template>

</xsl:stylesheet>

您的重复列名"问题源于此表达式:

Your "repeating column names" problem roots in this expression:

<xsl:for-each select="*/*">

在您的上下文中，这将选择文档中的任何第三级元素(实际上是所有 中的所有 节点>s)，并用它们制作标题行.我用

In your context, this selects any third level element in the document (literally all <field> nodes in all <logo>s), and makes a header row out of them. I replaced it with

<xsl:apply-templates select="logo[1]/field/@key" />

仅从第一个 生成标题行.

which makes a header row out of the first <logo> only.

如果需要特定的列顺序(文档顺序除外)，或者不是所有 节点对于所有 的顺序都相同，事情变得更加复杂.如果需要，请告诉我.

If a certain column order is required (other than document order) or not all <field> nodes are in the same order for all <logo>s, things get more complex. Tell me if you need that.

这篇关于使用 XSLT 创建 Excel (SpreadsheetML) 输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！