(转)Asp.Net(C#) XML+Xslt转Excel的解决方案

1. 新建一个Excel文档，并填写表头与两行左右的内容，然后另存为XML表格 格式 并修改成Xslt模板;
2. 将要导入的数据生成XML格式文档;
3. 通过Xslt模板将数据生成，并设定Response.ContentType = "application/vnd.ms-excel";
4. 刷新输出页保存文件即为Excel格式的文档

ExportCards.xsl模板代码

XML/HTML代码

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:fn="http://www.w3.org/2005/xpath-functions" xmlns:xdt="http://www.w3.org/2005/xpath-datatypes" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>
<xsl:template match="BitLab.Xml">

<xsl:param name="Collection" select="Cards/Card" />

<?mso-application progid="Excel.Sheet"?>
<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">
  <Created>1996-12-17T01:32:42Z</Created>
  <LastSaved>2008-07-04T02:10:38Z</LastSaved>
  <Version>11.6360</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>
  <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">
   <Font ss:FontName="宋体" x:CharSet="134"/>
  </Style>
  <Style ss:ID="s29">
   <Font ss:FontName="宋体" x:CharSet="134"/>
   <NumberFormat ss:Format=""￥"#,##0.00;"￥"\-#,##0.00"/>
  </Style>
  <Style ss:ID="s30">
   <Font ss:FontName="Verdana" x:Family="Swiss"/>
  </Style>
  <Style ss:ID="s31">
   <Font ss:FontName="Verdana" x:Family="Swiss"/>
   <NumberFormat ss:Format=""￥"#,##0.00;"￥"\-#,##0.00"/>
  </Style>
  <Style ss:ID="s32">
   <Font ss:FontName="Verdana" x:Family="Swiss"/>
   <NumberFormat ss:Format="General Date"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Cards">
  <Table ss:ExpandedColumnCount="7" ss:ExpandedRowCount="{1+count($Collection)}" x:FullColumns="1"
   x:FullRows="1" ss:StyleID="s30" ss:DefaultColumnWidth="54">
   <Column ss:StyleID="s30" ss:Width="45.75" ss:Span="1"/>
   <Column ss:Index="3" ss:StyleID="s30" ss:Width="129.75" ss:Span="1"/>
   <Column ss:Index="5" ss:StyleID="s31" ss:Width="54.75"/>
   <Column ss:Index="7" ss:StyleID="s30" ss:Width="127.5"/>
   <Row>
    <Cell ss:StyleID="s28"><Data ss:Type="String">标识列</Data></Cell>
    <Cell ss:StyleID="s28"><Data ss:Type="String">批次号</Data></Cell>
    <Cell ss:StyleID="s28"><Data ss:Type="String">卡序列号</Data></Cell>
    <Cell ss:StyleID="s28"><Data ss:Type="String">卡密码</Data></Cell>
    <Cell ss:StyleID="s29"><Data ss:Type="String">面额</Data></Cell>
    <Cell ss:StyleID="s28"><Data ss:Type="String">状态</Data></Cell>
    <Cell ss:StyleID="s28"><Data ss:Type="String">创建时间</Data></Cell>
   </Row>

<xsl:for-each select="$Collection">

   <Row>
    <Cell><Data ss:Type="Number"> <xsl:value-of select="@ID" /> </Data></Cell>
    <Cell><Data ss:Type="Number"> <xsl:value-of select="@BatchID" /> </Data></Cell>
    <Cell ss:StyleID="s30"><Data ss:Type="String"> <xsl:value-of select="@SN" /> </Data></Cell>
    <Cell ss:StyleID="s30"><Data ss:Type="String"> <xsl:value-of select="@Password" /> </Data></Cell>
    <Cell ss:StyleID="s31"><Data ss:Type="Number"> <xsl:value-of select="@Par" /> </Data></Cell>
    <Cell ss:StyleID="s30"><Data ss:Type="String"> <xsl:value-of select="@State" /> </Data></Cell>
    <Cell ss:StyleID="s32"><Data ss:Type="String"> <xsl:value-of select="@CreateDate" /> </Data></Cell>
   </Row>

</xsl:for-each>

 </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

 </xsl:template>
</xsl:stylesheet>

导出代码：

C#代码

public partial class ExportCardForm : Czintel.Moodou.UI.ManagePage
{
    protected void Page_Load(object sender, EventArgs e)
    {
        this.Components.Remove(ServerContext.User);

        Response.ContentType = "application/vnd.ms-excel";
        this.StyleSheet = "ExportCards.xsl";
        ComponentCollection<Card> cards = Cards.LoadCards();
        this.Components.Add(cards);

    }

}

具体转换过程在基类里，这儿就不写了！