XSL将XML转换成Excel [英] XSL Taking XML transforming it into Excel

查看：124
本文介绍了XSL将XML转换成Excel的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

这里的新朋友和我一起忍受。我有一个基本的XSL文件，将读取我的xml数据。我试图将xml放入Excel。回答我的问题使用一个小的XML文件，它似乎很容易地转换，但是当我调用数据时，这个XML文件有几个节点（我认为它们被调用），它不对。我只想从XML的检查部分显示信息，然后以Excel的方式显示我想要的6或7列，然后显示数据。到目前为止我已经有了：



XML：

  bdiData> 
< documentControlInfo> 
< documentInfo> 
< docDescription>检查公司X< / docDescription> 
< docID> 
< ID> 123456789< / ID> 
< / docID> 
< docModifier>我的公司< / 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>我的银行名称< / sourceName> 
< sourceID> 
< idOther> ShortBankName< / idOther> 
< / sourceID> 
< / sourceInfo> 
< destinationInfo> 
< destinationName>我的公司< / destinationName> 
< destinationID> 
< idOther> MYCO< / idOther> 
< / destinationID> 
< / destinationInfo> 
< / documentControlInfo> 
< checkItemCollection> 
< collectionInfo> 
< description>项目< / description> 
< ID> 654811650< / ID> 
<分类> 
<分类>项目< / classification> 
< / Classification> 
< / collectionInfo> 
< checkItemBatch> 
< checkItemBatchInfo> 
< description>付费支票< / description> 
< ID> 1239668334710< / ID> 
<分类> 
<分类>付费检查< /分类> 
< / 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： 
 $ 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： w3.org/TR/REC-html40\"> 
< xsl：apply-templates /> 
< / Workbook> 
< / xsl：template> 
 
 
< xsl：template match =/ *> 
<工作表> 
< xsl：attribute name =ss：Name> 
< xsl：value-of select =local-name（/ * / *）/> 
< / xsl：attribute> 
<表x：FullColumns =1x：FullRows =1> 
< Row> 
 
< xsl：for-each select =* [position（）= 2] / * / checkItem / postingInfo / *> 
 
<单元格> 
<数据ss：Type =String> 
< xsl：value-of select =local-name（）/> 
< / Data> 
< / Cell> 
< / xsl：for-each> 
< / Row> 
< xsl：apply-templates /> 
< /表> 
< / Worksheet> 
< / xsl：template> 
 
 
< xsl：template match =/ * / checkItem / postingInfo / *> 
< Row> 
< xsl：apply-templates /> 
< / Row> 
< / xsl：template> 
 
 
< xsl：template match =/ * / checkItem / postingInfo / *> 
<单元格> 
<数据ss：Type =String> 
< xsl：value-of select =。/> 
< / Data> 
< / Cell> 
< / xsl：template> 
 
 
< / xsl：stylesheet>有没有人有任何想法，我可以得到JUSt的检查部分的XML文件，并拥有它的



格式化的方式？



谢谢



GabrielVA

解决方案

我想你需要这个样式表：

 < 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> 
<工作簿> 
< xsl：apply-templates /> 
< / Workbook> 
< / xsl：template> 
< xsl：template match =/ *> 
< Worksheet ss：Name ={* / * / * [local-name（）='docDescription']}> 
<表x：FullColumns =1x：FullRows =1> 
< Row> 
< xsl：for-each select =* / * / * [local-name（）='checkItem'] [1] // * [not（*）]> 
<单元格> 
<数据ss：Type =String> 
< xsl：value-of select =local-name（）/> 
< / Data> 
< / Cell> 
< / xsl：for-each> 
< / Row> 
< xsl：apply-templates select =* / * / * [local-name（）='checkItem']/> 
< /表> 
< / Worksheet> 
< / xsl：template> 
< xsl：template match =* [local-name（）='checkItem']priority =1> 
< Row> 
< xsl：apply-templates select =.//* [not（*）]/> 
< / Row> 
< / xsl：template> 
< xsl：template match =* [not（*）]> 
<单元格> 
<数据ss：Type =String> 
< xsl：value-of select =。/> 
< / Data> 
< / Cell> 
< / xsl：template> 
< / xsl：stylesheet>

使用此输入（正确形成）：

 < bdiData> 
< documentControlInfo> 
< documentInfo> 
< docDescription>检查公司X< / docDescription> 
< docID> 
< ID> 123456789< / ID> 
< / docID> 
< docModifier>我的公司< / 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>我的银行名称< / sourceName> 
< sourceID> 
< idOther> ShortBankName< / idOther> 
< / sourceID> 
< / sourceInfo> 
< destinationInfo> 
< destinationName>我的公司< / destinationName> 
< destinationID> 
< idOther> MYCO< / idOther> 
< / destinationID> 
< / destinationInfo> 
< / documentControlInfo> 
< checkItemCollection> 
< collectionInfo> 
< description>项目< / description> 
< ID> 654811650< / ID> 
<分类> 
<分类>项目< / classification> 
< / Classification> 
< / collectionInfo> 
< checkItemBatch> 
< checkItemBatchInfo> 
< description>付费支票< / description> 
< ID> 1239668334710< / ID> 
<分类> 
<分类>付费检查< /分类> 
< / 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>

输出：

 code><？mso-application progid =Excel.Sheet？> 
< Workbook xmlns =urn：schemas-microsoft-com：office：spreadsheetxmlns：ss =urn：schemas-microsoft-com：office：spreadsheetxmlns：x =urn：schemas-microsoft- COM：办公室：EXCEL> 
<工作表ss：Name =检查公司X> 
<表x：FullColumns =1x：FullRows =1> 
< Row> 
<单元格> 
<数据ss：Type =String> checkItemType< / Data> 
< / Cell> 
<单元格> 
<数据ss：Type =String> checkAmount< / Data> 
< / Cell> 
<单元格> 
<数据ss：Type =String> date< / Data> 
< / Cell> 
<单元格> 
<数据ss：Type =String> RT< / Data> 
< / Cell> 
<单元格> 
<数据ss：Type =String> accountNumber< / Data> 
< / Cell> 
<单元格> 
<数据ss：Type =String> seqNum< / Data> 
< / Cell> 
<单元格> 
<数据ss：Type =String> trancode< / Data> 
< / Cell> 
<单元格> 
<数据ss：Type =String> amount< / Data> 
< / Cell> 
<单元格> 
<数据ss：Type =String> serialNumber< / Data> 
< / Cell> 
< / Row> 
< Row> 
<单元格> 
<数据ss：Type =String>检查< / Data> 
< / Cell> 
<单元格> 
<数据ss：Type =String> 2960< / Data> 
< / Cell> 
<单元格> 
<数据ss：Type =String> 2009-06-12< / Data> 
< / Cell> 
<单元格> 
<数据ss：Type =String> 87654321< / Data> 
< / Cell> 
<单元格> 
<数据ss：Type =String> 123465798< / Data> 
< / Cell> 
<单元格> 
<数据ss：Type =String> 007725552898< / Data> 
< / Cell> 
<单元格> 
<数据ss：Type =String> 001152< / Data> 
< / Cell> 
<单元格> 
<数据ss：Type =String> 2960< / Data> 
< / Cell> 
<单元格> 
<数据ss：Type =String> 55225410< / Data> 
< / Cell> 
< / Row> 
< /表> 
< / Worksheet> 
< / Workbook>

Excel正确打开。



注意：那些 fn：local-name（）在那里，因为您的输入示例不可靠。


New guy here so bear with me. Ive got a basic XSL file that will read my xml data. Im trying to put xml into Excel. Heres my issue. With a small XML file it seems to convert it easly, BUT with this XML file that had several nodes ( I think they are called), when I call up the data, its not right. I want to only show info from the check portion of XML and then show it in Excel in a way that shows the 6 or 7 columns that I want, then show the data. Heres what I have so far:

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 File:

<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>

Does anyone have any Idea how I can get to JUSt the check portion f the XML file and have it format in an eay way??

Thanks

GabrielVA

解决方案

I think you need this stylesheet:

<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>

With this input (proper well formed):

<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>

Output:

<?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>

Wich is properly open by Excel.

Note: those fn:local-name() are there because your input sample is unreliable.

这篇关于XSL将XML转换成Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Office最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆