嗨波

  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理
利用xlst导出多表头的简便方法

　　大家都知道在ASP.NET中进行表格导出有很多种办法，aspose,npoi,cvs等等，今天就来介绍xlst,导出多表头。与以往不一样的是我们利用模板，只需要在模板中定义好表格样式，然后绑定数据就可以了。话不多说，直接上步骤

      首先做模板，我们在excel把想导出的模板格式进行排版，举例(有的用户很可能看到过这个例子，我也是在园里看过来的，但是经过改良，有些不一样，先把原有地址贴过来：http://www.cnblogs.com/bluecountry/archive/2012/08/03/2622282.html)

      

     然后我们将此文件保存为xml格式，最后用VS，也可以使用其它文档编辑工具打开，这时你会看到里面xml代码，然后编辑XML

     在代码顶端加入xlst的头信息

     <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

     <xsl:template>

     .....

     在文件末尾加上尾部信息，去掉多余的代码

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

     最后将文件格式改为xlst这样，xlst文件就做好了，放到asp.net网站内，然后新建一个aspx页面，放置按钮用来触发导出excel事件，从后台从数据库获取数据，话不多说，直接上代码

     首先是后台

    

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Web;
  5 using System.Web.UI;
  6 using System.Web.UI.WebControls;
  7 using System.Xml.Xsl;
  8 using System.Data.SqlClient;
  9 using System.Data;
 10 using System.Configuration;
 11 using System.IO;
 12 using System.Xml;
 13 
 14 namespace 导出测试
 15 {
 16     public partial class TJTest : System.Web.UI.Page
 17     {
 18         public readonly static string connString = ConfigurationManager.ConnectionStrings["DBBulkTest"].ToString();
 19         public SqlConnection connection = new SqlConnection(connString);
 20         public SqlConnection Connection
 21         {
 22             get
 23             {
 24                 if (connection == null)
 25                 {
 26                     connection = new SqlConnection(connString);
 27                 }
 28                 if (connection.State != ConnectionState.Open)
 29                 {
 30                     connection.Open();
 31                 }
 32                 return connection;
 33             }
 34         }
 35         protected void Page_Load(object sender, EventArgs e)
 36         {
 37 
 38         }
 39 
 40         protected void Button1_Click(object sender, EventArgs e)
 41         {
 42             //首先从数据库获取数据
 43             DataSet ds = new DataSet("ds");
 44             SqlDataAdapter da = new SqlDataAdapter("select ID,Area,jchs,jcje,syhs,syje,bghs,mark from tongji", Connection);
 45             SqlDataAdapter da1 = new SqlDataAdapter("select sum(jchs) jchs,sum(jcje) jcje,sum(syhs) syhs,sum(syje) syje,sum(bghs) bghs from tongji", Connection);
 46             da.Fill(ds, "tongji");
 47             da1.Fill(ds, "huizong");
 48 
 49             MemoryStream dataStream = new MemoryStream();
 50             ds.WriteXml(dataStream);//将数据读到内存流中
 51             dataStream.Position = 0;
 52             XmlReader reader = XmlReader.Create(dataStream);//创建xml读取器
 53             MemoryStream outStream = new MemoryStream();//声明内存流，用来存放转化后要输出的内容
 54 
 55             XslCompiledTransform xTrans = new XslCompiledTransform();
 56             xTrans.Load(Server.MapPath("~/wxjj.xlst")); //加载xlst模板文件
 57             xTrans.Transform(reader, null, outStream);//进行转化，并将得得到的内容放到outStream中
 58 
 59             outStream.Position = 0;
 60             StreamReader srHeader = null; //用来存放样式
 61             StreamReader srContent = null;//将内存流转为输出流
 62              try
 63             {
 64                 srContent = new StreamReader(outStream, System.Text.Encoding.UTF8);
 65                 //srHeader = new StreamReader(Server.MapPath("~/XlstHeader.txt"), System.Text.Encoding.UTF8);
 66                 this.Response.Clear();
 67                 string fileName = this.Server.UrlEncode("测试导出文件") + DateTime.Now.Date.ToString("yyyyMMdd") + ".xls";
 68                 this.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
 69                 this.Response.ContentType = "application/vnd.ms-excel";
 70                 this.Response.ContentEncoding = System.Text.Encoding.UTF8;
 71                 this.Response.Charset = "gb2312";
 72                 //this.Response.Write(srHeader.ReadToEnd() + srContent.ReadToEnd() + "</Workbook>");
 73                 this.Response.Write(srContent.ReadToEnd());
 74                 this.Response.End();
 75             }
 76              catch (Exception ex)
 77              {
 78                  throw ex;
 79              }
 80              finally
 81              {
 82                  if (srContent != null)
 83                  {
 84                      try
 85                      {
 86                          srContent.Close();
 87                      }
 88                      catch { }
 89                  }
 90                  if (srHeader != null)
 91                  {
 92                      try
 93                      {
 94                          srHeader.Close();
 95                      }
 96                      catch { }
 97                  }
 98              }
 99         }
100     }
101 }
后台代码

    这里注意我在声明dataset与datatalbe的时候都命了名字，这在xlst模板绑定的时候会用到.接下来来看看我们的xlst文件代码

  1 <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  2 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  3   <xsl:template match="ds">
  4     <?mso-application progid="Excel.Sheet"?>
  5     <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  6               xmlns:o="urn:schemas-microsoft-com:office:office"
  7               xmlns:x="urn:schemas-microsoft-com:office:excel"
  8               xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
  9               xmlns:html="http://www.w3.org/TR/REC-html40">
 10       <Styles>
 11         <Style ss:ID="s5" ss:Name="货币[0]">
 12           <NumberFormat ss:Format="_ &quot;￥&quot;* #,##0_ ;_ &quot;￥&quot;* \-#,##0_ ;_ &quot;￥&quot;* &quot;-&quot;_ ;_ @_ "/>
 13         </Style>
 14         <Style ss:ID="s4" ss:Name="百分比">
 15           <NumberFormat ss:Format="0%"/>
 16         </Style>
 17         <Style ss:ID="s3" ss:Name="千位分隔[0]">
 18           <NumberFormat ss:Format="_ * #,##0_ ;_ * \-#,##0_ ;_ * &quot;-&quot;_ ;_ @_ "/>
 19         </Style>
 20         <Style ss:ID="s2" ss:Name="货币">
 21           <NumberFormat ss:Format="_ &quot;￥&quot;* #,##0.00_ ;_ &quot;￥&quot;* \-#,##0.00_ ;_ &quot;￥&quot;* &quot;-&quot;??_ ;_ @_ "/>
 22         </Style>
 23         <Style ss:ID="s1" ss:Name="千位分隔">
 24           <NumberFormat ss:Format="_ * #,##0.00_ ;_ * \-#,##0.00_ ;_ * &quot;-&quot;??_ ;_ @_ "/>
 25         </Style>
 26         <Style ss:ID="Default" ss:Name="Normal">
 27           <Alignment/>
 28           <Borders/>
 29           <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
 30           <Interior/>
 31           <NumberFormat/>
 32           <Protection/>
 33         </Style>
 34         <Style ss:ID="s6"/>
 35         <Style ss:ID="s7">
 36           <Alignment/>
 37           <NumberFormat/>
 38         </Style>
 39         <Style ss:ID="s8">
 40           <Alignment ss:Horizontal="Center"/>
 41           <NumberFormat/>
 42         </Style>
 43         <Style ss:ID="s9">
 44           <Alignment ss:Horizontal="Center"/>
 45           <NumberFormat/>
 46         </Style>
 47         <Style ss:ID="s10">
 48           <Alignment ss:Horizontal="Center"/>
 49           <NumberFormat/>
 50         </Style>
 51         <Style ss:ID="s11">
 52           <Alignment ss:Horizontal="Center"/>
 53           <NumberFormat/>
 54         </Style>
 55         <Style ss:ID="s12">
 56           <Alignment ss:Horizontal="Center"/>
 57           <NumberFormat/>
 58         </Style>
 59         <Style ss:ID="s13">
 60           <Alignment ss:Horizontal="Center"/>
 61           <NumberFormat/>
 62         </Style>
 63         <Style ss:ID="s14">
 64           <Alignment ss:Horizontal="Center"/>
 65           <NumberFormat/>
 66         </Style>
 67         <Style ss:ID="s15">
 68           <Alignment ss:Horizontal="Center"/>
 69           <NumberFormat/>
 70         </Style>
 71       </Styles>
 72       <Worksheet ss:Name="Sheet1">
 73         <Table ss:ExpandedColumnCount="10" ss:ExpandedRowCount="34" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
 74           <Column ss:Index="2" ss:StyleID="s6" ss:AutoFitWidth="0" ss:Width="71.25"/>
 75           <Row>
 76             <Cell ss:StyleID="s13" ss:MergeDown="1">
 77               <Data ss:Type="String">序号</Data>
 78             </Cell>
 79             <Cell ss:StyleID="s14" ss:MergeDown="1">
 80               <Data ss:Type="String">小区名称</Data>
 81             </Cell>
 82             <Cell ss:StyleID="s9" ss:MergeAcross="1">
 83               <Data ss:Type="String">缴   存</Data>
 84             </Cell>
 85             <Cell ss:StyleID="s10" ss:MergeAcross="1">
 86               <Data ss:Type="String">使   用</Data>
 87             </Cell>
 88             <Cell ss:StyleID="s8">
 89               <Data ss:Type="String">变更</Data>
 90             </Cell>
 91             <Cell ss:StyleID="s15" ss:MergeAcross="2" ss:MergeDown="1">
 92               <Data ss:Type="String">备        注</Data>
 93             </Cell>
 94           </Row>
 95           <Row>
 96             <Cell ss:Index="3" ss:StyleID="s8">
 97               <Data ss:Type="String">户数</Data>
 98             </Cell>
 99             <Cell ss:StyleID="s8">
100               <Data ss:Type="String">金额</Data>
101             </Cell>
102             <Cell ss:StyleID="s8">
103               <Data ss:Type="String">户数</Data>
104             </Cell>
105             <Cell ss:StyleID="s8">
106               <Data ss:Type="String">金额</Data>
107             </Cell>
108             <Cell ss:StyleID="s8">
109               <Data ss:Type="String">户数</Data>
110             </Cell>
111           </Row>
112           <xsl:for-each select="tongji">
113             <Row>
114               <Cell ss:StyleID="s8">
115                 <Data ss:Type="String">
116                   <xsl:value-of select="ID"/>
117                 </Data>
118               </Cell>
119               <Cell ss:StyleID="s8">
120                 <Data ss:Type="String">
121                   <xsl:value-of select="Area"/>
122                 </Data>
123               </Cell>
124               <Cell ss:StyleID="s8">
125                 <Data ss:Type="String">
126                   <xsl:value-of select="jchs"/>
127                 </Data>
128               </Cell>
129               <Cell ss:StyleID="s8">
130                 <Data ss:Type="String">
131                   <xsl:value-of select="jcje"/>
132                 </Data>
133               </Cell>
134               <Cell ss:StyleID="s8">
135                 <Data ss:Type="String">
136                   <xsl:value-of select="syhs"/>
137                 </Data>
138               </Cell>
139               <Cell ss:StyleID="s8">
140                 <Data ss:Type="String">
141                   <xsl:value-of select="syje"/>
142                 </Data>
143               </Cell>
144               <Cell ss:StyleID="s8">
145                 <Data ss:Type="String">
146                   <xsl:value-of select="bghs"/>
147                 </Data>
148               </Cell>
149               <Cell ss:StyleID="s11" ss:MergeAcross="2">
150                 <Data ss:Type="String">
151                   <xsl:value-of select="mark"/>
152                 </Data>
153               </Cell>
154             </Row>
155           </xsl:for-each>
156           <xsl:for-each select="huizong">
157             <Row>
158               <Cell ss:StyleID="s8">
159                 <Data ss:Type="String">总计：</Data>
160               </Cell>
161               <Cell ss:StyleID="s8"/>
162               <Cell ss:StyleID="s8">
163                 <Data ss:Type="String">
164                   <xsl:value-of select="jchs"/>
165                 </Data>
166               </Cell>
167               <Cell ss:StyleID="s8">
168                 <Data ss:Type="String">
169                   <xsl:value-of select="jcje"/>
170                 </Data>
171               </Cell>
172               <Cell ss:StyleID="s8">
173                 <Data ss:Type="String">
174                   <xsl:value-of select="syhs"/>
175                 </Data>
176               </Cell>
177               <Cell ss:StyleID="s8">
178                 <Data ss:Type="String">
179                   <xsl:value-of select="syje"/>
180                 </Data>
181               </Cell>
182               <Cell ss:StyleID="s8">
183                 <Data ss:Type="String">
184                   <xsl:value-of select="bghs"/>
185                 </Data>
186               </Cell>
187               <Cell ss:StyleID="s12" ss:MergeAcross="2"/>
188             </Row>
189           </xsl:for-each>
190           <Row ss:StyleID="s7">
191             <Cell ss:StyleID="s7">
192               <Data ss:Type="String">负责人：</Data>
193             </Cell>
194             <Cell ss:Index="4" ss:StyleID="s7">
195               <Data ss:Type="String">复核人：</Data>
196             </Cell>
197             <Cell ss:Index="7" ss:StyleID="s7">
198               <Data ss:Type="String">填表：</Data>
199             </Cell>
200           </Row>
201         </Table>
202         <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
203           <PageSetup>
204             <Header x:Margin="0.511111111111111"/>
205             <Footer x:Margin="0.511111111111111"/>
206           </PageSetup>
207           <Selected/>
208           <TopRowVisible>0</TopRowVisible>
209           <LeftColumnVisible>0</LeftColumnVisible>
210           <PageBreakZoom>100</PageBreakZoom>
211           <Panes>
212             <Pane>
213               <Number>3</Number>
214               <ActiveRow>2</ActiveRow>
215               <ActiveCol>6</ActiveCol>
216               <RangeSelection>R3C7</RangeSelection>
217             </Pane>
218           </Panes>
219           <ProtectObjects>False</ProtectObjects>
220           <ProtectScenarios>False</ProtectScenarios>
221         </WorksheetOptions>
222       </Worksheet>
223       <Worksheet ss:Name="Sheet2">
224         <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>
225         <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
226           <PageSetup>
227             <Header x:Margin="0.511111111111111"/>
228             <Footer x:Margin="0.511111111111111"/>
229           </PageSetup>
230           <TopRowVisible>0</TopRowVisible>
231           <LeftColumnVisible>0</LeftColumnVisible>
232           <PageBreakZoom>100</PageBreakZoom>
233           <ProtectObjects>False</ProtectObjects>
234           <ProtectScenarios>False</ProtectScenarios>
235         </WorksheetOptions>
236       </Worksheet>
237       <Worksheet ss:Name="Sheet3">
238         <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>
239         <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
240           <PageSetup>
241             <Header x:Margin="0.511111111111111"/>
242             <Footer x:Margin="0.511111111111111"/>
243           </PageSetup>
244           <TopRowVisible>0</TopRowVisible>
245           <LeftColumnVisible>0</LeftColumnVisible>
246           <PageBreakZoom>100</PageBreakZoom>
247           <ProtectObjects>False</ProtectObjects>
248           <ProtectScenarios>False</ProtectScenarios>
249         </WorksheetOptions>
250       </Worksheet>
251     </Workbook>
252   </xsl:template>
253 </xsl:stylesheet>
xlst 模板代码

   在上面的xlst文件中，注意到<xsl:template match="ds">，<xsl:for-each select="tongji">，<xsl:for-each select="huizong">这几句话，再与后台代码中的

    DataSet ds = new DataSet("ds");da.Fill(ds, "tongji");da1.Fill(ds, "huizong");进行对比，大家就知道绑定的原理了

    导出结果，如下图

   

    这里我没有给出数据表信息，主要目的是想让大家知道如何配置xslt以及后台如何使用模板

     

   

posted on 2015-07-14 14:34  嗨波  阅读(535)  评论(0)  编辑  收藏  举报