本文介绍了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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！