(转)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代码
  1. <?xml version="1.0" encoding="utf-8"?>  
  2. <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">  
  3. <xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>  
  4. <xsl:template match="BitLab.Xml">  
  5.   
  6. <xsl:param name="Collection" select="Cards/Card" />  
  7.   
  8. <?mso-application progid="Excel.Sheet"?>  
  9. <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"  
  10.  xmlns:o="urn:schemas-microsoft-com:office:office"  
  11.  xmlns:x="urn:schemas-microsoft-com:office:excel"  
  12.  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"  
  13.  xmlns:html="http://www.w3.org/TR/REC-html40">  
  14.  <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">  
  15.   <Created>1996-12-17T01:32:42Z</Created>  
  16.   <LastSaved>2008-07-04T02:10:38Z</LastSaved>  
  17.   <Version>11.6360</Version>  
  18.  </DocumentProperties>  
  19.  <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">  
  20.   <RemovePersonalInformation/>  
  21.  </OfficeDocumentSettings>  
  22.  <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">  
  23.   <WindowHeight>4530</WindowHeight>  
  24.   <WindowWidth>8505</WindowWidth>  
  25.   <WindowTopX>480</WindowTopX>  
  26.   <WindowTopY>120</WindowTopY>  
  27.   <AcceptLabelsInFormulas/>  
  28.   <ProtectStructure>False</ProtectStructure>  
  29.   <ProtectWindows>False</ProtectWindows>  
  30.  </ExcelWorkbook>  
  31.  <Styles>  
  32.   <Style ss:ID="Default" ss:Name="Normal">  
  33.    <Alignment ss:Vertical="Bottom"/>  
  34.    <Borders/>  
  35.    <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>  
  36.    <Interior/>  
  37.    <NumberFormat/>  
  38.    <Protection/>  
  39.   </Style>  
  40.   <Style ss:ID="s28">  
  41.    <Font ss:FontName="宋体" x:CharSet="134"/>  
  42.   </Style>  
  43.   <Style ss:ID="s29">  
  44.    <Font ss:FontName="宋体" x:CharSet="134"/>  
  45.    <NumberFormat ss:Format=""￥"#,##0.00;"￥"\-#,##0.00"/>  
  46.   </Style>  
  47.   <Style ss:ID="s30">  
  48.    <Font ss:FontName="Verdana" x:Family="Swiss"/>  
  49.   </Style>  
  50.   <Style ss:ID="s31">  
  51.    <Font ss:FontName="Verdana" x:Family="Swiss"/>  
  52.    <NumberFormat ss:Format=""￥"#,##0.00;"￥"\-#,##0.00"/>  
  53.   </Style>  
  54.   <Style ss:ID="s32">  
  55.    <Font ss:FontName="Verdana" x:Family="Swiss"/>  
  56.    <NumberFormat ss:Format="General Date"/>  
  57.   </Style>  
  58.  </Styles>  
  59.  <Worksheet ss:Name="Cards">  
  60.   <Table ss:ExpandedColumnCount="7" ss:ExpandedRowCount="{1+count($Collection)}" x:FullColumns="1"  
  61.    x:FullRows="1" ss:StyleID="s30" ss:DefaultColumnWidth="54">  
  62.    <Column ss:StyleID="s30" ss:Width="45.75" ss:Span="1"/>  
  63.    <Column ss:Index="3" ss:StyleID="s30" ss:Width="129.75" ss:Span="1"/>  
  64.    <Column ss:Index="5" ss:StyleID="s31" ss:Width="54.75"/>  
  65.    <Column ss:Index="7" ss:StyleID="s30" ss:Width="127.5"/>  
  66.    <Row>  
  67.     <Cell ss:StyleID="s28"><Data ss:Type="String">标识列</Data></Cell>  
  68.     <Cell ss:StyleID="s28"><Data ss:Type="String">批次号</Data></Cell>  
  69.     <Cell ss:StyleID="s28"><Data ss:Type="String">卡序列号</Data></Cell>  
  70.     <Cell ss:StyleID="s28"><Data ss:Type="String">卡密码</Data></Cell>  
  71.     <Cell ss:StyleID="s29"><Data ss:Type="String">面额</Data></Cell>  
  72.     <Cell ss:StyleID="s28"><Data ss:Type="String">状态</Data></Cell>  
  73.     <Cell ss:StyleID="s28"><Data ss:Type="String">创建时间</Data></Cell>  
  74.    </Row>  
  75.   
  76. <xsl:for-each select="$Collection">  
  77.   
  78.    <Row>  
  79.     <Cell><Data ss:Type="Number"> <xsl:value-of select="@ID" /> </Data></Cell>  
  80.     <Cell><Data ss:Type="Number"> <xsl:value-of select="@BatchID" /> </Data></Cell>  
  81.     <Cell ss:StyleID="s30"><Data ss:Type="String"> <xsl:value-of select="@SN" /> </Data></Cell>  
  82.     <Cell ss:StyleID="s30"><Data ss:Type="String"> <xsl:value-of select="@Password" /> </Data></Cell>  
  83.     <Cell ss:StyleID="s31"><Data ss:Type="Number"> <xsl:value-of select="@Par" /> </Data></Cell>  
  84.     <Cell ss:StyleID="s30"><Data ss:Type="String"> <xsl:value-of select="@State" /> </Data></Cell>  
  85.     <Cell ss:StyleID="s32"><Data ss:Type="String"> <xsl:value-of select="@CreateDate" /> </Data></Cell>  
  86.    </Row>  
  87.   
  88. </xsl:for-each>  
  89.   
  90.  </Table>  
  91.   <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">  
  92.    <Selected/>  
  93.    <Panes>  
  94.     <Pane>  
  95.      <Number>3</Number>  
  96.     </Pane>  
  97.    </Panes>  
  98.    <ProtectObjects>False</ProtectObjects>  
  99.    <ProtectScenarios>False</ProtectScenarios>  
  100.   </WorksheetOptions>  
  101.  </Worksheet>  
  102. </Workbook>  
  103.   
  104.  </xsl:template>  
  105. </xsl:stylesheet>  



导出代码：

C#代码
  1. public partial class ExportCardForm : Czintel.Moodou.UI.ManagePage   
  2. {   
  3.     protected void Page_Load(object sender, EventArgs e)   
  4.     {   
  5.         this.Components.Remove(ServerContext.User);   
  6.   
  7.         Response.ContentType = "application/vnd.ms-excel";   
  8.         this.StyleSheet = "ExportCards.xsl";   
  9.         ComponentCollection<Card> cards = Cards.LoadCards();   
  10.         this.Components.Add(cards);   
  11.            
  12.     }   
  13.   
  14. }  


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

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/lann/p/1515257.html