大家都知道在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事件，从后台从数据库获取数据，话不多说，直接上代码
首先是后台
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml.Xsl;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.IO;
using System.Xml; namespace 导出测试
{
public partial class TJTest : System.Web.UI.Page
{
public readonly static string connString = ConfigurationManager.ConnectionStrings["DBBulkTest"].ToString();
public SqlConnection connection = new SqlConnection(connString);
public SqlConnection Connection
{
get
{
if (connection == null)
{
connection = new SqlConnection(connString);
}
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
return connection;
}
}
protected void Page_Load(object sender, EventArgs e)
{ } protected void Button1_Click(object sender, EventArgs e)
{
//首先从数据库获取数据
DataSet ds = new DataSet("ds");
SqlDataAdapter da = new SqlDataAdapter("select ID,Area,jchs,jcje,syhs,syje,bghs,mark from tongji", Connection);
SqlDataAdapter da1 = new SqlDataAdapter("select sum(jchs) jchs,sum(jcje) jcje,sum(syhs) syhs,sum(syje) syje,sum(bghs) bghs from tongji", Connection);
da.Fill(ds, "tongji");
da1.Fill(ds, "huizong"); MemoryStream dataStream = new MemoryStream();
ds.WriteXml(dataStream);//将数据读到内存流中
dataStream.Position = ;
XmlReader reader = XmlReader.Create(dataStream);//创建xml读取器
MemoryStream outStream = new MemoryStream();//声明内存流，用来存放转化后要输出的内容 XslCompiledTransform xTrans = new XslCompiledTransform();
xTrans.Load(Server.MapPath("~/wxjj.xlst")); //加载xlst模板文件
xTrans.Transform(reader, null, outStream);//进行转化，并将得得到的内容放到outStream中 outStream.Position = ;
StreamReader srHeader = null; //用来存放样式
StreamReader srContent = null;//将内存流转为输出流
try
{
srContent = new StreamReader(outStream, System.Text.Encoding.UTF8);
//srHeader = new StreamReader(Server.MapPath("~/XlstHeader.txt"), System.Text.Encoding.UTF8);
this.Response.Clear();
string fileName = this.Server.UrlEncode("测试导出文件") + DateTime.Now.Date.ToString("yyyyMMdd") + ".xls";
this.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
this.Response.ContentType = "application/vnd.ms-excel";
this.Response.ContentEncoding = System.Text.Encoding.UTF8;
this.Response.Charset = "gb2312";
//this.Response.Write(srHeader.ReadToEnd() + srContent.ReadToEnd() + "</Workbook>");
this.Response.Write(srContent.ReadToEnd());
this.Response.End();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (srContent != null)
{
try
{
srContent.Close();
}
catch { }
}
if (srHeader != null)
{
try
{
srHeader.Close();
}
catch { }
}
}
}
}
}
后台代码
这里注意我在声明dataset与datatalbe的时候都命了名字，这在xlst模板绑定的时候会用到.接下来来看看我们的xlst文件代码
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="ds">
<?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">
<Styles>
<Style ss:ID="s5" ss:Name="货币[0]">
<NumberFormat ss:Format="_ "￥"* #,##0_ ;_ "￥"* \-#,##0_ ;_ "￥"* "-"_ ;_ @_ "/>
</Style>
<Style ss:ID="s4" ss:Name="百分比">
<NumberFormat ss:Format="0%"/>
</Style>
<Style ss:ID="s3" ss:Name="千位分隔[0]">
<NumberFormat ss:Format="_ * #,##0_ ;_ * \-#,##0_ ;_ * "-"_ ;_ @_ "/>
</Style>
<Style ss:ID="s2" ss:Name="货币">
<NumberFormat ss:Format="_ "￥"* #,##0.00_ ;_ "￥"* \-#,##0.00_ ;_ "￥"* "-"??_ ;_ @_ "/>
</Style>
<Style ss:ID="s1" ss:Name="千位分隔">
<NumberFormat ss:Format="_ * #,##0.00_ ;_ * \-#,##0.00_ ;_ * "-"??_ ;_ @_ "/>
</Style>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment/>
<Borders/>
<Font ss:FontName="宋体" x:CharSet="" ss:Size=""/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s6"/>
<Style ss:ID="s7">
<Alignment/>
<NumberFormat/>
</Style>
<Style ss:ID="s8">
<Alignment ss:Horizontal="Center"/>
<NumberFormat/>
</Style>
<Style ss:ID="s9">
<Alignment ss:Horizontal="Center"/>
<NumberFormat/>
</Style>
<Style ss:ID="s10">
<Alignment ss:Horizontal="Center"/>
<NumberFormat/>
</Style>
<Style ss:ID="s11">
<Alignment ss:Horizontal="Center"/>
<NumberFormat/>
</Style>
<Style ss:ID="s12">
<Alignment ss:Horizontal="Center"/>
<NumberFormat/>
</Style>
<Style ss:ID="s13">
<Alignment ss:Horizontal="Center"/>
<NumberFormat/>
</Style>
<Style ss:ID="s14">
<Alignment ss:Horizontal="Center"/>
<NumberFormat/>
</Style>
<Style ss:ID="s15">
<Alignment ss:Horizontal="Center"/>
<NumberFormat/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="" ss:ExpandedRowCount="" x:FullColumns="" x:FullRows="" ss:DefaultColumnWidth="" ss:DefaultRowHeight="14.25">
<Column ss:Index="" ss:StyleID="s6" ss:AutoFitWidth="" ss:Width="71.25"/>
<Row>
<Cell ss:StyleID="s13" ss:MergeDown="">
<Data ss:Type="String">序号</Data>
</Cell>
<Cell ss:StyleID="s14" ss:MergeDown="">
<Data ss:Type="String">小区名称</Data>
</Cell>
<Cell ss:StyleID="s9" ss:MergeAcross="">
<Data ss:Type="String">缴 存</Data>
</Cell>
<Cell ss:StyleID="s10" ss:MergeAcross="">
<Data ss:Type="String">使 用</Data>
</Cell>
<Cell ss:StyleID="s8">
<Data ss:Type="String">变更</Data>
</Cell>
<Cell ss:StyleID="s15" ss:MergeAcross="" ss:MergeDown="">
<Data ss:Type="String">备 注</Data>
</Cell>
</Row>
<Row>
<Cell ss:Index="" ss:StyleID="s8">
<Data ss:Type="String">户数</Data>
</Cell>
<Cell ss:StyleID="s8">
<Data ss:Type="String">金额</Data>
</Cell>
<Cell ss:StyleID="s8">
<Data ss:Type="String">户数</Data>
</Cell>
<Cell ss:StyleID="s8">
<Data ss:Type="String">金额</Data>
</Cell>
<Cell ss:StyleID="s8">
<Data ss:Type="String">户数</Data>
</Cell>
</Row>
<xsl:for-each select="tongji">
<Row>
<Cell ss:StyleID="s8">
<Data ss:Type="String">
<xsl:value-of select="ID"/>
</Data>
</Cell>
<Cell ss:StyleID="s8">
<Data ss:Type="String">
<xsl:value-of select="Area"/>
</Data>
</Cell>
<Cell ss:StyleID="s8">
<Data ss:Type="String">
<xsl:value-of select="jchs"/>
</Data>
</Cell>
<Cell ss:StyleID="s8">
<Data ss:Type="String">
<xsl:value-of select="jcje"/>
</Data>
</Cell>
<Cell ss:StyleID="s8">
<Data ss:Type="String">
<xsl:value-of select="syhs"/>
</Data>
</Cell>
<Cell ss:StyleID="s8">
<Data ss:Type="String">
<xsl:value-of select="syje"/>
</Data>
</Cell>
<Cell ss:StyleID="s8">
<Data ss:Type="String">
<xsl:value-of select="bghs"/>
</Data>
</Cell>
<Cell ss:StyleID="s11" ss:MergeAcross="">
<Data ss:Type="String">
<xsl:value-of select="mark"/>
</Data>
</Cell>
</Row>
</xsl:for-each>
<xsl:for-each select="huizong">
<Row>
<Cell ss:StyleID="s8">
<Data ss:Type="String">总计：</Data>
</Cell>
<Cell ss:StyleID="s8"/>
<Cell ss:StyleID="s8">
<Data ss:Type="String">
<xsl:value-of select="jchs"/>
</Data>
</Cell>
<Cell ss:StyleID="s8">
<Data ss:Type="String">
<xsl:value-of select="jcje"/>
</Data>
</Cell>
<Cell ss:StyleID="s8">
<Data ss:Type="String">
<xsl:value-of select="syhs"/>
</Data>
</Cell>
<Cell ss:StyleID="s8">
<Data ss:Type="String">
<xsl:value-of select="syje"/>
</Data>
</Cell>
<Cell ss:StyleID="s8">
<Data ss:Type="String">
<xsl:value-of select="bghs"/>
</Data>
</Cell>
<Cell ss:StyleID="s12" ss:MergeAcross=""/>
</Row>
</xsl:for-each>
<Row ss:StyleID="s7">
<Cell ss:StyleID="s7">
<Data ss:Type="String">负责人：</Data>
</Cell>
<Cell ss:Index="" ss:StyleID="s7">
<Data ss:Type="String">复核人：</Data>
</Cell>
<Cell ss:Index="" ss:StyleID="s7">
<Data ss:Type="String">填表：</Data>
</Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.511111111111111"/>
<Footer x:Margin="0.511111111111111"/>
</PageSetup>
<Selected/>
<TopRowVisible></TopRowVisible>
<LeftColumnVisible></LeftColumnVisible>
<PageBreakZoom></PageBreakZoom>
<Panes>
<Pane>
<Number></Number>
<ActiveRow></ActiveRow>
<ActiveCol></ActiveCol>
<RangeSelection>R3C7</RangeSelection>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="" ss:ExpandedRowCount="" x:FullColumns="" x:FullRows="" ss:DefaultColumnWidth="" ss:DefaultRowHeight="14.25"/>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.511111111111111"/>
<Footer x:Margin="0.511111111111111"/>
</PageSetup>
<TopRowVisible></TopRowVisible>
<LeftColumnVisible></LeftColumnVisible>
<PageBreakZoom></PageBreakZoom>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="" ss:ExpandedRowCount="" x:FullColumns="" x:FullRows="" ss:DefaultColumnWidth="" ss:DefaultRowHeight="14.25"/>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.511111111111111"/>
<Footer x:Margin="0.511111111111111"/>
</PageSetup>
<TopRowVisible></TopRowVisible>
<LeftColumnVisible></LeftColumnVisible>
<PageBreakZoom></PageBreakZoom>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
</xsl:template>
</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以及后台如何使用模板