excel - XSL 将 XML 转换为 Excel

Question

这里是新人，请耐心等待。我有一个基本的 XSL 文件，可以读取我的 xml 数据。我正在尝试将 xml 放入 Excel 中。这是我的问题。对于一个小的 XML 文件，似乎很容易转换它，但是对于这个具有多个节点(我认为它们被调用)的 XML 文件，当我调用数据时，它是不对的。我只想显示 XML 检查部分的信息，然后以显示我想要的 6 或 7 列的方式在 Excel 中显示它，然后显示数据。这是我到目前为止所拥有的:

XML:

<bdiData>
  <documentControlInfo>
    <documentInfo>
      <docDescription>Checks for Company X</docDescription>
      <docID>
        <ID>123456789</ID>
      </docID>
      <docModifier>My Company</docModifier>
      <docCreateDate>2010-08-23</docCreateDate>
      <docCreateTime>07:08:54-0700</docCreateTime>
      <standardVersion>1.0</standardVersion>
      <testIndicator>0</testIndicator>
      <resendIndicator>0</resendIndicator>
    </documentInfo>
    <sourceInfo>
      <sourceName>My Banking Name</sourceName>
      <sourceID>
        <idOther>ShortBankName</idOther>
      </sourceID>
    </sourceInfo>
    <destinationInfo>
      <destinationName>My Company</destinationName>
      <destinationID>
        <idOther>MYCO</idOther>
      </destinationID>
    </destinationInfo>
  </documentControlInfo>
  <checkItemCollection>
    <collectionInfo>
      <description>Items</description>
      <ID>654811650</ID>
      <Classification>
        <classification>Items</classification>
      </Classification>
    </collectionInfo>
    <checkItemBatch>
      <checkItemBatchInfo>
        <description>Paid Checks</description>
        <ID>1239668334710</ID>
        <Classification>
          <classification>Paid Checks</classification>
        </Classification>
      </checkItemBatchInfo>
      <checkItem>
        <checkItemType>check</checkItemType>
        <checkAmount>2960</checkAmount>
        <postingInfo>
          <date>2009-06-12</date>
          <RT>87654321</RT>
          <accountNumber>123465798</accountNumber>
          <seqNum>007725552898</seqNum>
          <trancode>001152</trancode>
          <amount>2960</amount>
          <serialNumber>55225410</serialNumber>
        </postingInfo>

XSL 文件:

<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="*[position() = 2]/*/checkItem/postingInfo/*">

            <Cell>
              <Data ss:Type="String">
                <xsl:value-of select="local-name()"/>
              </Data>
            </Cell>
          </xsl:for-each>
        </Row>
        <xsl:apply-templates/>
      </Table>
    </Worksheet>
  </xsl:template>


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


  <xsl:template match="/*/checkItem/postingInfo/*">
    <Cell>
      <Data ss:Type="String">
        <xsl:value-of select="."/>
      </Data>
    </Cell>
  </xsl:template>


</xsl:stylesheet>

有谁知道如何才能获取 XML 文件的检查部分并以简单的方式对其进行格式化？

谢谢

加布里埃尔VA

Answer 1

我认为你需要这个样式表:

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:x="urn:schemas-microsoft-com:office:excel">
    <xsl:template match="/">
        <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
        <Workbook>
            <xsl:apply-templates/>
        </Workbook>
    </xsl:template>
    <xsl:template match="/*">
        <Worksheet ss:Name="{*/*/*[local-name()='docDescription']}">
            <Table x:FullColumns="1" x:FullRows="1">
                <Row>
                    <xsl:for-each select="*/*/*[local-name()='checkItem'][1]//*[not(*)]">
                        <Cell>
                            <Data ss:Type="String">
                                <xsl:value-of select="local-name()"/>
                            </Data>
                        </Cell>
                    </xsl:for-each>
                </Row>
                <xsl:apply-templates select="*/*/*[local-name()='checkItem']"/>
            </Table>
        </Worksheet>
    </xsl:template>
    <xsl:template match="*[local-name()='checkItem']" priority="1">
        <Row>
            <xsl:apply-templates select=".//*[not(*)]"/>
        </Row>
    </xsl:template>
    <xsl:template match="*[not(*)]">
        <Cell>
            <Data ss:Type="String">
                <xsl:value-of select="."/>
            </Data>
        </Cell>
    </xsl:template>
</xsl:stylesheet>

使用此输入(格式正确):

<bdiData>
    <documentControlInfo>
        <documentInfo>
            <docDescription>Checks for Company X</docDescription>
            <docID>
                <ID>123456789</ID>
            </docID>
            <docModifier>My Company</docModifier>
            <docCreateDate>2010-08-23</docCreateDate>
            <docCreateTime>07:08:54-0700</docCreateTime>
            <standardVersion>1.0</standardVersion>
            <testIndicator>0</testIndicator>
            <resendIndicator>0</resendIndicator>
        </documentInfo>
        <sourceInfo>
            <sourceName>My Banking Name</sourceName>
            <sourceID>
                <idOther>ShortBankName</idOther>
            </sourceID>
        </sourceInfo>
        <destinationInfo>
            <destinationName>My Company</destinationName>
            <destinationID>
                <idOther>MYCO</idOther>
            </destinationID>
        </destinationInfo>
    </documentControlInfo>
    <checkItemCollection>
        <collectionInfo>
            <description>Items</description>
            <ID>654811650</ID>
            <Classification>
                <classification>Items</classification>
            </Classification>
        </collectionInfo>
        <checkItemBatch>
            <checkItemBatchInfo>
                <description>Paid Checks</description>
                <ID>1239668334710</ID>
                <Classification>
                    <classification>Paid Checks</classification>
                </Classification>
            </checkItemBatchInfo>
            <checkItem>
                <checkItemType>check</checkItemType>
                <checkAmount>2960</checkAmount>
                <postingInfo>
                    <date>2009-06-12</date>
                    <RT>87654321</RT>
                    <accountNumber>123465798</accountNumber>
                    <seqNum>007725552898</seqNum>
                    <trancode>001152</trancode>
                    <amount>2960</amount>
                    <serialNumber>55225410</serialNumber>
                </postingInfo>
            </checkItem>
        </checkItemBatch>
    </checkItemCollection>
</bdiData>

输出:

<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel">
    <Worksheet ss:Name="Checks for Company X">
        <Table x:FullColumns="1" x:FullRows="1">
            <Row>
                <Cell>
                    <Data ss:Type="String">checkItemType</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">checkAmount</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">date</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">RT</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">accountNumber</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">seqNum</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">trancode</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">amount</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">serialNumber</Data>
                </Cell>
            </Row>
            <Row>
                <Cell>
                    <Data ss:Type="String">check</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">2960</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">2009-06-12</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">87654321</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">123465798</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">007725552898</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">001152</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">2960</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">55225410</Data>
                </Cell>
            </Row>
        </Table>
    </Worksheet>
</Workbook>

Excel 可以正确打开它。

注意:这些 fn:local-name() 之所以存在，是因为您的输入样本不可靠。