利用xlst导出多表头的简便方法

时间:2021-04-30 19:47:33

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

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

利用xlst导出多表头的简便方法

然后我们将此文件保存为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="_ &quot;¥&quot;* #,##0_ ;_ &quot;¥&quot;* \-#,##0_ ;_ &quot;¥&quot;* &quot;-&quot;_ ;_ @_ "/>
</Style>
<Style ss:ID="s4" ss:Name="百分比">
<NumberFormat ss:Format="0%"/>
</Style>
<Style ss:ID="s3" ss:Name="千位分隔[0]">
<NumberFormat ss:Format="_ * #,##0_ ;_ * \-#,##0_ ;_ * &quot;-&quot;_ ;_ @_ "/>
</Style>
<Style ss:ID="s2" ss:Name="货币">
<NumberFormat ss:Format="_ &quot;¥&quot;* #,##0.00_ ;_ &quot;¥&quot;* \-#,##0.00_ ;_ &quot;¥&quot;* &quot;-&quot;??_ ;_ @_ "/>
</Style>
<Style ss:ID="s1" ss:Name="千位分隔">
<NumberFormat ss:Format="_ * #,##0.00_ ;_ * \-#,##0.00_ ;_ * &quot;-&quot;??_ ;_ @_ "/>
</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");进行对比,大家就知道绑定的原理了

导出结果,如下图

利用xlst导出多表头的简便方法

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