如何将XML文件转换成Excel电子数据表

列表B---一个XML电子数据表
<?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>ed woychowsky</Author>
 <LastAuthor>Edmond Woychowsky</LastAuthor>
 <Created>2007-01-26T16:54:15Z</Created>
 <LastSaved>2007-01-27T05:18:54Z</LastSaved>
 <Company>None</Company>
 <Version>10.3501</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
 <DownloadComponents/>
 <LocationOfComponents HRef="file:///D:\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
 <WindowHeight>8700</WindowHeight>
 <WindowWidth>11355</WindowWidth>
 <WindowTopX>480</WindowTopX>
 <WindowTopY>120</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="Sheet1">
 <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1"
  x:FullRows="1">
  <Row>
   <Cell><Data ss:Type="String">cell a1</Data></Cell>
   <Cell><Data ss:Type="String">cell b2</Data></Cell>
  </Row>
  <Row>
   <Cell><Data ss:Type="String">cell a2</Data></Cell>
   <Cell><Data ss:Type="String">cell b3</Data></Cell>
  </Row>
 </Table>
 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
  <Print>
   <ValidPrinterInfo/>
   <HorizontalResolution>600</HorizontalResolution>
   <VerticalResolution>0</VerticalResolution>
  </Print>
  <Selected/>
  <Panes>
   <Pane>
    <Number>3</Number>
    <ActiveRow>2</ActiveRow>
   </Pane>
  </Panes>
  <ProtectObjects>False</ProtectObjects>
  <ProtectScenarios>False</ProtectScenarios>
 </WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
  <ProtectObjects>False</ProtectObjects>
  <ProtectScenarios>False</ProtectScenarios>
 </WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
  <ProtectObjects>False</ProtectObjects>
  <ProtectScenarios>False</ProtectScenarios>
 </WorksheetOptions>
</Worksheet>
</Workbook>

剖析XML电子数据表

和文档一样古怪，它实际具有一种奇怪的结构。例如，可以将其分解为如下XML元素层次：

Workbook工作簿

  DocumentProperties文档属性

  ExcelWorkbook Excel工作簿

  Styles 格式

    Style格式

  Worksheet工作表

    Tables表格

      Row行

        Cell单元

          Data数据

    WorksheetOptions工作表选项

      Print打印

        ValidPrinterInfo 有效打印机信息

        HorizontalResolution 水平解决方案

        VerticalResolution 垂直解决方案

    Selected 被选中

    Panes 面

      Pane 面

        Number 数字

        ActiveRow 激活行

保护关

远非如此强大的分解，不是吗？实际上，从本观点来看，可以非常容易地创立建一个XSL 1.0格式表格，将XML文档从列表A转换成我的同父异母哥哥感到轻松的东西。事实上，有注解格式的表格能够在列表C中找到，结果显示在图C和列表C中。

<?xml version="1.0" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 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">
<!-- : /: Edmond Woychowsky: July 25, 2005: The purpose of this template is to create an Excel/XML spreadsheet from a
simple xml document.
-->本模板的目的是从一个简单的XML文档创建一个Excel/ XML电子数据表
<xsl:template match="/">
<Workbook>
<xsl:call-template name="DocumentProperties"/>
<xsl:call-template name="OfficeDocumentSettings"/>
<xsl:call-template name="ExcelWorkbook"/>
<xsl:call-template name="Styles"/>
<xsl:apply-templates select="/*" mode="worksheet"/>
</Workbook>
</xsl:template>
<!-- : *工作表：本模板建立电子数据表的单独工作表，一般的表格。
.
-->
<xsl:template match="*" mode="worksheet">
<xsl:variable name="position" select="position()"/>
<Worksheet ss:Name="{concat('Sheet', $position)}">
<Table ss:ExpandedColumnCount="{count(./*[1]/*)}" ss:ExpandedRowCount="{count(./*) + 2}" x:FullColumns="1" x:FullRows="1">
<xsl:apply-templates select="*" mode="row"/>
</Table>
<xsl:call-template name="WorksheetOptions"/>
</Worksheet>
</xsl:template>
<!-- : * row: This template builds the worksheet's rows.
-->行：本模板建立工作表的行
<xsl:template match="*" mode="row">
<Row>
<xsl:apply-templates select="*" mode="cell"/>
</Row>
</xsl:template>
<!-- : * cells: This template builds the row's cells.
-->单元：本模板建立行的单元。
<xsl:template match="*" mode="cell">
<xsl:variable name="type">
<xsl:choose>
<xsl:when test="number(.) = .">Number</xsl:when>
<xsl:otherwise>String</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<Cell>
<Data ss:Type="{$type}">
<xsl:value-of select="."/>
</Data>
</Cell>
</xsl:template>
<!-- : * column: This template describes a worksheet's individual columns.
-->栏：本模板描述一个工作表的单独栏。
<xsl:template match="*" mode="column">
<xsl:variable name="name" select="name(.)"/>
<xsl:variable name="length">
<xsl:call-template name="length">
<xsl:with-param name="nodeset" select="//parent::*/parent::*/*/*[name(.) = $name]"/>
</xsl:call-template>
</xsl:variable>
<xsl:variable name="width">
<xsl:choose>
<xsl:when test="($length * 5.75) &lt; 56.25">56.25</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$length * 5.75"/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:variable name="style">
<xsl:choose>
<xsl:when test="parent::*/parent::*/*/*[name(.) = $name] = number(parent::*/parent::*/*[1]/*[name(.) = $name])">
<xsl:choose>
<xsl:when test="string-length(parent::*/parent::*/*/*[name(.) = $name][contains(.,'.')]) = 0">s23</xsl:when>
<xsl:otherwise>s24</xsl:otherwise>
</xsl:choose>
</xsl:when>
<xsl:otherwise>s22</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<Column ss:StyleID="{$style}" ss:AutoFitWidth="0" ss:Width="{$width}"/>
</xsl:template>
<!-- : DocumentProperties: This template describes the document to Excel.
-->文档属性：本模板描述Excel文档。
<xsl:template name="DocumentProperties">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>ewoychowsky</Author>
<Company>EAW</Company>
<Version>10.4219</Version>
</DocumentProperties>
</xsl:template>
<!-- : OfficeDocumentSettings: This template describes the Office document to Excel.
-->办公文档设置：本模板描述Excel的办公文档。
<xsl:template name="OfficeDocumentSettings">
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<DownloadComponents/>
<LocationOfComponents HRef="file:///\\phlfsnt01\DOWNLOAD\OfficeXPSrc\"/>
</OfficeDocumentSettings>
</xsl:template>
<!-- : ExcelWorkbook: This template describes the characteristics of the wookbook to Excel.
-->Excel工作簿：本模板描述Excel的工作簿特性。
<xsl:template name="ExcelWorkbook">
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>9210</WindowHeight>
<WindowWidth>15195</WindowWidth>
<WindowTopX>0</WindowTopX>
<WindowTopY>60</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
</xsl:template>
<!-- : Styles: This template describes the display styles to Excel.
-->格式：本模板描述Excel显示格式
<xsl:template name="Styles">
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
</xsl:template>
<!-- : WorksheetOptions: This template describes the worksheet options to Excel.
-->工作表选项：本模板描述Excel工作表选项。
<xsl:template name="WorksheetOptions">
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>1200</HorizontalResolution>
<VerticalResolution>1200</VerticalResolution>
</Print>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</xsl:template>
<!-- : length: Determine either the length of the node name or the longest node(s), which ever is longer.
-->长度：决定节点名称的长度或最长的节点，哪个更长。
<xsl:template name="length">
<xsl:param name="nodeset"/>
<xsl:variable name="longest">
<xsl:call-template name="longest">
<xsl:with-param name="nodeset" select="$nodeset"/>
</xsl:call-template>
</xsl:variable>
<xsl:choose>
<xsl:when test="string-length(name($nodeset[1])) &gt; string-length($longest)">
<xsl:value-of select="string-length(name($nodeset[1]))"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="string-length($longest)"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<!-- : longest: This recursive template transverses a nodeset to find the nodes with the longest
string-length. Please note that the result of this template may itself be a nodeset.
-->最长：本回归模板横向节点设置来找到最长字串长度的节点。请注意本模板的结果不包含本身的节点设置。
<xsl:template name="longest">
<xsl:param name="nodeset"/>
<xsl:param name="length" select="0"/>
<xsl:choose>
<xsl:when test="count($nodeset[string-length(.) &gt; $length]) &gt; 0">
<xsl:call-template name="longest">
<xsl:with-param name="nodeset" select="$nodeset[string-length(.) &gt; $length]"/>
<xsl:with-param name="length" select="string-length($nodeset[string-length(.) &gt; $length][1])"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$nodeset"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>

列表D—作为XML的结果
<?xml version="1.0" encoding="UTF-8"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author xmlns="urn:schemas-microsoft-com:office:office">ewoychowsky</Author>
<Company xmlns="urn:schemas-microsoft-com:office:office">EAW</Company>
<Version xmlns="urn:schemas-microsoft-com:office:office">10.4219</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<DownloadComponents xmlns="urn:schemas-microsoft-com:office:office" />
<LocationOfComponents xmlns="urn:schemas-microsoft-com:office:office" HRef="file:///\\phlfsnt01\DOWNLOAD\OfficeXPSrc\" />
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight xmlns="urn:schemas-microsoft-com:office:excel">9210</WindowHeight>
<WindowWidth xmlns="urn:schemas-microsoft-com:office:excel">15195</WindowWidth>
<WindowTopX xmlns="urn:schemas-microsoft-com:office:excel">0</WindowTopX>
<WindowTopY xmlns="urn:schemas-microsoft-com:office:excel">60</WindowTopY>
<ProtectStructure xmlns="urn:schemas-microsoft-com:office:excel">False</ProtectStructure>
<ProtectWindows xmlns="urn:schemas-microsoft-com:office:excel">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="Sheet1">
<Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="6" x:FullColumns="1" x:FullRows="1">
<Row>
<Cell>
<Data ss:Type="String">Column 1 Row 1</Data>
</Cell>
<Cell>
<Data ss:Type="String">Column 2 Row 1</Data>
</Cell>
<Cell>
<Data ss:Type="String">Column 3 Row 1</Data>
</Cell>
</Row>
<Row>
<Cell>
<Data ss:Type="String">Column 1 Row 2</Data>
</Cell>
<Cell>
<Data ss:Type="String">Column 2 Row 2</Data>
</Cell>
<Cell>
<Data ss:Type="String">Column 3 Row 2</Data>
</Cell>
</Row>
<Row>
<Cell>
<Data ss:Type="String">Column 1 Row 3</Data>
</Cell>
<Cell>
<Data ss:Type="String">Column 2 Row 3</Data>
</Cell>
<Cell>
<Data ss:Type="String">Column 3 Row 3</Data>
</Cell>
</Row>
<Row>
<Cell>
<Data ss:Type="String">Column 1 Row 4</Data>
</Cell>
<Cell>
<Data ss:Type="String">Column 2 Row 4</Data>
</Cell>
<Cell>
<Data ss:Type="String">Column 3 Row 4</Data>
</Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Print xmlns="urn:schemas-microsoft-com:office:excel">
<ValidPrinterInfo xmlns="urn:schemas-microsoft-com:office:excel" />
<HorizontalResolution xmlns="urn:schemas-microsoft-com:office:excel">1200</HorizontalResolution>
<VerticalResolution xmlns="urn:schemas-microsoft-com:office:excel">1200</VerticalResolution>
</Print>
<ProtectObjects xmlns="urn:schemas-microsoft-com:office:excel">False</ProtectObjects>
<ProtectScenarios xmlns="urn:schemas-microsoft-com:office:excel">False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>

找到轻松感觉

可能例子非常古怪，并且这些呈现在我最年长的同父异母哥哥面前的XML文档和游戏关一样奇怪，这一努力实际上可以应用到现实世界。假设你从上司那里被指派一项工作，如果你公司的用户群需要察看数据库表中的内容，那么你将在Excel中看到。毕竟，他们对于Excel的感觉最轻松，现在希望你也如此。

责任编辑：德东

查看原文国际来源