使用XML数据结合XSLT导出Excel
XML数据如下：
View Code
<?xml version="1.0" encoding="utf-8"?>
<!--<?xml-stylesheet type="text/xsl" href="toexcel_test.xslt"?>-->
<DataSet>
<Data>
<Item Name="ID" Type="Hidden" Value="121">121</Item>
<Item Name="编号" Type="Text" Value="1">1</Item>
<Item Name="姓名" Type="Text" Title="基本信息" Colspan="6" Value="张三">张三</Item>
<Item Name="性别" Type="Text" Title="基本信息" Value="男">男</Item>
<Item Name="出生日期" Type="Text" Title="基本信息" Value="1987-8-22">1987-8-22</Item>
<Item Name="籍贯" Type="Text" Title="基本信息" Value="湖北武汉">湖北武汉</Item>
<Item Name="职业" Type="Text" Title="基本信息" Value="销售人员">销售人员</Item>
<Item Name="现居地" Type="Text" Title="基本信息" Value="深圳">深圳</Item>
</Data>
<Data>
<Item Name="ID" Type="Hidden" Value="122">122</Item>
<Item Name="编号" Type="Text" Value="2">2</Item>
<Item Name="姓名" Type="Text" Title="基本信息" Value="李四">李四</Item>
<Item Name="性别" Type="Text" Title="基本信息" Value="男">男</Item>
<Item Name="出生日期" Type="Text" Title="基本信息" Value="1988-9-22">1987-8-22</Item>
<Item Name="籍贯" Type="Text" Title="基本信息" Value="湖北武汉">湖北武汉</Item>
<Item Name="职业" Type="Text" Title="基本信息" Value="销售人员">销售人员</Item>
<Item Name="现居地" Type="Text" Title="基本信息" Value="深圳">深圳</Item>
</Data>
<Data>
<Item Name="ID" Type="Hidden" Value="123">123</Item>
<Item Name="编号" Type="Text" Value="3">3</Item>
<Item Name="姓名" Type="Text" Title="基本信息" Value="王五">王五</Item>
<Item Name="性别" Type="Text" Title="基本信息" Value="男">男</Item>
<Item Name="出生日期" Type="Text" Title="基本信息" Value="1989-10-22">1987-8-22</Item>
<Item Name="籍贯" Type="Text" Title="基本信息" Value="湖北武汉">湖北武汉</Item>
<Item Name="职业" Type="Text" Title="基本信息" Value="销售人员">销售人员</Item>
<Item Name="现居地" Type="Text" Title="基本信息" Value="深圳">深圳</Item>
</Data>
<Other>
<Caption>员工信息</Caption>
</Other>
</DataSet>
可以通过另存对应的Excel文件为“表格xml”，得到Excel文件对应的XML，如下图：
仔细查看得到的表格XML文件，你会发现Excel文件对应的XML描述，就像HTML+CSS一样，也可以在这个XML文件中设置Excel的各种样式。
使用XSLT结合XML数据源转换其中动态的部分可以得到需要的Excel文件。
比如一个转换XML成“Excel表格数据XML”的XSLT如下：
View Code
<?xml version="1.0" encoding="utf-8"?>
<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">
<xsl:template match="/DataSet">
<Workbook>
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Created>1996-12-17T01:32:42Z</Created>
<LastSaved>2000-11-18T06:53:49Z</LastSaved>
<Version>11.6568</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<RemovePersonalInformation/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>4530</WindowHeight>
<WindowWidth>8505</WindowWidth>
<WindowTopX>480</WindowTopX>
<WindowTopY>120</WindowTopY>
<ActiveSheet>0</ActiveSheet>
<AcceptLabelsInFormulas/>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s28">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="16" ss:Bold="1"/>
</Style>
<Style ss:ID="s30">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="s31">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Color="#000000"/>
</Style>
<Style ss:ID="s34">
<Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="s35">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="s36">
<Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
</Styles>
<Worksheet ss:Name="sheet">
<Table x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="100" ss:DefaultRowHeight="14.25">
<Row ss:Height="20.25">
<Cell ss:StyleID="s28">
<xsl:attribute name="ss:MergeAcross">
<xsl:value-of select="count(//Data[1]/Item[@Type!='Hidden'])"/>
</xsl:attribute>
<Data ss:Type="String">
<xsl:value-of select="//Caption"/>
</Data>
</Cell>
</Row>
<Row>
<xsl:for-each select="//DataSet/Data[1]/Item[@Type!='Hidden']">
<xsl:choose>
<xsl:when test="@Title!=''">
<xsl:if test="@Colspan>0">
<Cell ss:StyleID="s31">
<xsl:attribute name="ss:MergeAcross">
<xsl:value-of select="number(@Colspan)-1"/>
</xsl:attribute>
<Data ss:Type="String">
<xsl:value-of select="@Title"/>
</Data>
</Cell>
</xsl:if>
</xsl:when>
<xsl:otherwise>
<Cell ss:StyleID="s31" ss:MergeDown="1">
<Data ss:Type="String">
<xsl:value-of select="@Name"/>
</Data>
</Cell>
</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
</Row>
<Row>
<xsl:for-each select="//DataSet/Data[1]/Item[@Type!='Hidden'][@Title!='']">
<Cell ss:StyleID="s31">
<xsl:if test="position()=1">
<xsl:attribute name="ss:Index">2</xsl:attribute>
</xsl:if>
<Data ss:Type="String">
<xsl:value-of select="@Name"/>
</Data>
</Cell>
</xsl:for-each>
</Row>
<xsl:for-each select="//DataSet/Data">
<Row>
<xsl:for-each select="Item[@Type!='Hidden']">
<Cell ss:StyleID="s31">
<Data ss:Type="String">
<xsl:value-of select="substring(@Value , 1 , 80)"/>
</Data>
</Cell>
</xsl:for-each>
</Row>
</xsl:for-each>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Unsynced/>
<Print>
<ValidPrinterInfo/>
<PaperSizeIndex>9</PaperSizeIndex>
<HorizontalResolution>300</HorizontalResolution>
<VerticalResolution>300</VerticalResolution>
</Print>
<Selected/>
<FreezePanes/>
<FrozenNoSplit/>
<SplitHorizontal>4</SplitHorizontal>
<TopRowBottomPane>7</TopRowBottomPane>
<SplitVertical>1</SplitVertical>
<LeftColumnRightPane>1</LeftColumnRightPane>
<ActivePane>0</ActivePane>
<Panes>
<Pane>
<Number>3</Number>
</Pane>
<Pane>
<Number>1</Number>
</Pane>
<Pane>
<Number>2</Number>
<ActiveRow>4</ActiveRow>
</Pane>
<Pane>
<Number>0</Number>
<ActiveRow>4</ActiveRow>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
</xsl:template>
</xsl:stylesheet>
使用XslCompiledTransform.Transform 方法即可调用XSLT文件转换指定的XML。
详见MSDN：http://msdn.microsoft.com/zh-cn/library/system.xml.xsl.xslcompiledtransform.transform(v=VS.80).aspx
得到XML之后向前台Response即可实现导出效果，C#的方法如下：
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "application/ms-excel";
Response.AddHeader("Content-Disposition", "inline;filename='前台看到的文件名'");
Response.Write(xmlStr);//xmlStr为转换之后得到的XML字符串
Response.Flush();
Response.Close();
得到的Excel效果如下(第一列及前三行是固定的)：
Excel第一列及前三行是固定这一效果对应的XML是：
<SplitHorizontal>3</SplitHorizontal>
<TopRowBottomPane>3</TopRowBottomPane>
<SplitVertical>1</SplitVertical>
<LeftColumnRightPane>1</LeftColumnRightPane>
