gridview多重表头+排序+导出EXCEL
第一次发现GridView还可以修改表头,本来还在愁怎么样做成这种格式的表呢,没想到做成了,代码如下
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System.Data.SqlClient;
using System.Drawing;public partial class V01004 : System.Web.UI.Page
{
public static string ls_yesr, is_userid;protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
year();
query();}
}
private void year()
{
string ls_con = "";
ls_con = System.Configuration.ConfigurationManager.AppSettings["k3db1"];
SqlConnection myConnection = new SqlConnection(ls_con);
string ls_sql = "select year(fdate) year from icstockbill group by year(fdate) order by year(fdate) desc ";SqlDataAdapter myCommand = new SqlDataAdapter(ls_sql, myConnection);
DataSet ds = new DataSet();
myCommand.Fill(ds);
dd_year.DataSource = ds.Tables[0].DefaultView;
dd_year.DataBind();
}
private void query()
{
visionox.sa sa1 = new visionox.sa();
ls_yesr = dd_year.SelectedItem.Text.ToString();
GridView1.DataSource = sa1.uf_sademonth("k3db1", ls_yesr).Tables[0].DefaultView;
GridView1.DataBind();}
//排序
protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
// 从事件参数获取排序数据列
string sortExpression = e.SortExpression.ToString();
// 假定为排序方向为“顺序”
string sortDirection = "ASC";
// “ASC”与事件参数获取到的排序方向进行比较,进行GridView排序方向参数的修改
if (sortExpression == this.GridView1.Attributes["SortExpression"])
{
//获得下一次的排序状态
sortDirection = (this.GridView1.Attributes["SortDirection"].ToString() == sortDirection ? "DESC" : "ASC");
}
// 重新设定GridView排序数据列及排序方向
this.GridView1.Attributes["SortExpression"] = sortExpression;
this.GridView1.Attributes["SortDirection"] = sortDirection;
SortGridView(sortExpression, sortDirection);
}
private void SortGridView(string sortExpression, string direction)
{
visionox.sa sa1 = new visionox.sa();
ls_yesr = dd_year.SelectedItem.Text.ToString();
DataTable dt = sa1.uf_sademonth("db1", ls_yesr).Tables[0];
DataView dv = new DataView(dt);
dv.Sort = sortExpression + " " + direction;
//将DataView绑定到GridView上
GridView1.DataSource = dv;
GridView1.DataBind();
}
protected void dd_year_SelectedIndexChanged(object sender, EventArgs e)
{
query();
}
protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
{
switch (e.Row.RowType)
{
case DataControlRowType.Header:
//第一行表头
TableCellCollection tcHeader = e.Row.Cells;
//tcHeader.Clear();
tcHeader.Add(new TableHeaderCell());tcHeader[0].Attributes.Add("colspan", "4");
tcHeader[0].Attributes.Add("bgcolor", "white");
tcHeader[0].Text = "";
tcHeader.Add(new TableHeaderCell());
tcHeader[1].Attributes.Add("colspan", "2"); //跨Column
tcHeader[1].Text = "1月";
tcHeader.Add(new TableHeaderCell());
tcHeader[2].Attributes.Add("colspan", "2"); //跨Column
tcHeader[2].Text = "2月";
tcHeader.Add(new TableHeaderCell());
tcHeader[3].Attributes.Add("colspan", "2"); //跨Column
tcHeader[3].Text = "3月";
tcHeader.Add(new TableHeaderCell());
tcHeader[4].Attributes.Add("colspan", "2"); //跨Column
tcHeader[4].Text = "4月";
tcHeader.Add(new TableHeaderCell());
tcHeader[5].Attributes.Add("colspan", "2"); //跨Column
tcHeader[5].Text = "5月";
tcHeader.Add(new TableHeaderCell());
tcHeader[6].Attributes.Add("colspan", "2"); //跨Column
tcHeader[6].Text = "6月";
tcHeader.Add(new TableHeaderCell());
tcHeader[7].Attributes.Add("colspan", "2"); //跨Column
tcHeader[7].Text = "7月";
tcHeader.Add(new TableHeaderCell());
tcHeader[8].Attributes.Add("colspan", "2"); //跨Column
tcHeader[8].Text = "8月";
tcHeader.Add(new TableHeaderCell());
tcHeader[9].Attributes.Add("colspan", "2"); //跨Column
tcHeader[9].Text = "9月";
tcHeader.Add(new TableHeaderCell());
tcHeader[10].Attributes.Add("colspan", "2"); //跨Column
tcHeader[10].Text = "10月";
tcHeader.Add(new TableHeaderCell());
tcHeader[11].Attributes.Add("colspan", "2"); //跨Column
tcHeader[11].Text = "11月";
tcHeader.Add(new TableHeaderCell());
tcHeader[12].Attributes.Add("colspan", "2"); //跨Column
tcHeader[12].Text = "12月</th></tr><tr>";
// 第二行表头
tcHeader.Add(new TableHeaderCell());
tcHeader[13].Attributes.Add("bgcolor", "Khaki");
tcHeader[13].Attributes.Add("sortexpression", "id");
tcHeader[13].Text = "ID";
tcHeader.Add(new TableHeaderCell());
tcHeader[14].Attributes.Add("bgcolor", "Khaki");
tcHeader[14].Attributes.Add("sortexpression", "fnumber");
tcHeader[14].Text = "产品代码";
tcHeader.Add(new TableHeaderCell());
tcHeader[15].Attributes.Add("bgcolor", "Khaki");
tcHeader[15].Attributes.Add("sortexpression", "fname");
tcHeader[15].Text = "产品名称";
tcHeader.Add(new TableHeaderCell());
tcHeader[16].Attributes.Add("bgcolor", "Khaki");
tcHeader[16].Attributes.Add("sortexpression", "fmodel");
tcHeader[16].Text = "产品规格";
tcHeader.Add(new TableHeaderCell());
tcHeader[17].Attributes.Add("bgcolor", "DarkSeaGreen");
tcHeader[17].Text = "数量";
tcHeader.Add(new TableHeaderCell());
tcHeader[18].Attributes.Add("bgcolor", "LightSteelBlue");
tcHeader[18].Text = "金额";
tcHeader.Add(new TableHeaderCell());
tcHeader[19].Attributes.Add("bgcolor", "DarkSeaGreen");
tcHeader[19].Text = "数量";
tcHeader.Add(new TableHeaderCell());
tcHeader[20].Attributes.Add("bgcolor", "LightSteelBlue");
tcHeader[20].Text = "金额";
tcHeader.Add(new TableHeaderCell());
tcHeader[21].Attributes.Add("bgcolor", "DarkSeaGreen");
tcHeader[21].Text = "数量";
tcHeader.Add(new TableHeaderCell());
tcHeader[22].Attributes.Add("bgcolor", "LightSteelBlue");
tcHeader[22].Text = "金额";
tcHeader[23].Attributes.Add("bgcolor", "DarkSeaGreen");
tcHeader[23].Text = "数量";
tcHeader.Add(new TableHeaderCell());
tcHeader[24].Attributes.Add("bgcolor", "LightSteelBlue");
tcHeader[24].Text = "金额";
tcHeader[25].Attributes.Add("bgcolor", "DarkSeaGreen");
tcHeader[25].Text = "数量";
tcHeader.Add(new TableHeaderCell());
tcHeader[26].Attributes.Add("bgcolor", "LightSteelBlue");
tcHeader[26].Text = "金额";
tcHeader[27].Attributes.Add("bgcolor", "DarkSeaGreen");
tcHeader[27].Text = "数量";
tcHeader.Add(new TableHeaderCell());
tcHeader[28].Attributes.Add("bgcolor", "LightSteelBlue");
tcHeader[28].Text = "金额";
tcHeader[29].Attributes.Add("bgcolor", "DarkSeaGreen");
tcHeader[29].Text = "数量";
tcHeader.Add(new TableHeaderCell());
tcHeader[30].Attributes.Add("bgcolor", "LightSteelBlue");
tcHeader[30].Text = "金额";
tcHeader[31].Attributes.Add("bgcolor", "DarkSeaGreen");
tcHeader[31].Text = "数量";
tcHeader.Add(new TableHeaderCell());
tcHeader[32].Attributes.Add("bgcolor", "LightSteelBlue");
tcHeader[32].Text = "金额";
tcHeader[33].Attributes.Add("bgcolor", "DarkSeaGreen");
tcHeader[33].Text = "数量";
tcHeader.Add(new TableHeaderCell());
tcHeader[34].Attributes.Add("bgcolor", "LightSteelBlue");
tcHeader[34].Text = "金额";
tcHeader[35].Attributes.Add("bgcolor", "DarkSeaGreen");
tcHeader[35].Text = "数量";
tcHeader.Add(new TableHeaderCell());
tcHeader[36].Attributes.Add("bgcolor", "LightSteelBlue");
tcHeader[36].Text = "金额";
tcHeader[37].Attributes.Add("bgcolor", "DarkSeaGreen");
tcHeader[37].Text = "数量";
tcHeader.Add(new TableHeaderCell());
tcHeader[38].Attributes.Add("bgcolor", "LightSteelBlue");
tcHeader[38].Text = "金额";
tcHeader[39].Attributes.Add("bgcolor", "DarkSeaGreen");
tcHeader[39].Text = "数量";
tcHeader.Add(new TableHeaderCell());
tcHeader[40].Attributes.Add("bgcolor", "LightSteelBlue");
tcHeader[40].Text = "金额";
LinkButton linkBtn = new LinkButton();
linkBtn.ID = "LinkButton1";
linkBtn.Text = "产品代码";
linkBtn.CommandName = "sort";
linkBtn.CommandArgument = "fnumber";
linkBtn.Command += new CommandEventHandler(linkBtn_Command);
tcHeader[14].Controls.Add(linkBtn);
LinkButton linkBtn1 = new LinkButton();
linkBtn1.ID = "LinkButton2";
linkBtn1.Text = "产品规格";
linkBtn1.CommandName = "sort";
linkBtn1.CommandArgument = "fmodel";
linkBtn1.Command += new CommandEventHandler(linkBtn_Command);
tcHeader[16].Controls.Add(linkBtn1);break;
}
}
protected void linkBtn_Command(object sender, CommandEventArgs e)
{
}//导出EXCEL
protected void Button1_Click1(object sender, EventArgs e)
{
Response.Clear();
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
Response.AddHeader("content-disposition", "attachment;filename=instimestock.xls");
Response.Charset = "UTF-8";//设置字符集
curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;//设置编码集
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
//写到Excel的数据不用分页
BindData();
GridView1.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());//向客户端写数据
Response.End();
}
private void BindData()
{
query();
}
public override void VerifyRenderingInServerForm(Control control)//这个方法不写的话可能报错类型“GridView”的控件“GridView1”必须放在具有 runat=server 的窗体标记内。
{
// Confirms that an HtmlForm control is rendered for
}
}