GridView使用自定义表头和导出到Excel

 

如上图：查看列 是静态添加上的，表头和数据则是动态添加的。

代码如下：

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class jjzbtjb_ck : System.Web.UI.Page
{
    public int sh = 0;
    string cosql = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            cosql = "SELECT 年, max(_机构) AS 供电所, MAX(供电量累计) AS 供电量, MAX(售电量累计) AS 售电量, MAX(损失电量高低压累计) AS 损失电量高低压, MAX(损失电量高压累计) AS 损失电量高压, MAX(损失电量低压累计) AS 损失电量低压, MAX(损失率高低压累计) AS 损失率高低压, MAX(损失率高压累计) AS 损失率高压, MAX(损失率低压累计) AS 损失率低压, MAX(电费金额累计) AS 电费金额, MAX(平均售电电价累计) AS 平均售电电价 FROM td_jjzbtjb GROUP BY 年";
            BindGridView(cosql);
           
            GridView1.HeaderRow.Cells.Clear();
            TableCell cell = new TableCell();
            cell.Text = "<tr><td rowspan='2'>查看</td><td rowspan='2'>年份</td><td rowspan='2'>供电所</td><td rowspan='2'>供电量(万KWh)</td><td rowspan='2'>售电量(万KWh)</td><td colspan='3'>损失电量(万KWh)</td><td colspan='3'>损失率(%)</td><td rowspan='2'>电费金额(元)</td><td rowspan='2'> 平均售电电价(元/千KWh)</td></tr><tr><td>高低压</td><td>高压</td><td>低压</td><td>高低压</td><td>高压</td> <td>低压</td> </tr>";
            GridView1.HeaderRow.Cells.Add(cell);
           
        }
    }
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "chakan")
        {
            Session["gongdiansuo"] = this.GridView1.DataKeys[Convert.ToInt32(e.CommandArgument)].Values["供电所"].ToString();
            if (Session["gongdiansuo"].ToString()!= "")
            {
                Server.Transfer("jjzbtjb_tq.aspx");
            }
            else
            {
                Response.Write("没供电所");
                Response.End();
            }
           
        }
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        //重载该方法是为避免下面的错误
        // 类型“GridView”的控件“GridView1”必须放在具有 runat=server 的窗体标记内。
        //异常详细信息: System.Web.HttpException: 类型“GridView”的控件“GridView1”必须放在具有 runat=server 的窗体标记内。

    }

//下面导出到Excel

    protected void Button1_Click(object sender, EventArgs e)
    {
       
        //string style = @"<style> .text { mso-number-format:/@; } </script> ";
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "GB2312";// 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码！！！

        Response.AppendHeader("Content-Disposition", "attachment;filename=GongDianSuoBiao.xls");
       
        Response.ContentEncoding = System.Text.Encoding.UTF7;
        Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
        this.GridView1.RenderControl(oHtmlTextWriter);
        Response.Output.Write(oStringWriter.ToString());
        Response.Flush();
        Response.End();
    }

    protected void BindGridView(string sql)
    {

        System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString.ToString());
        DataSet ds = new DataSet();
        con.Open();
        System.Data.SqlClient.SqlDataAdapter rs = new System.Data.SqlClient.SqlDataAdapter(sql, con);
        rs.Fill(ds);

        this.GridView1.DataSource = ds;
        this.GridView1.DataBind();
    }
   
}
 

说明：我之前用了SqlDataSource 绑定到GridView，然后添加的表头，可是当导出到Excel之后，表头就丢失了！动态绑定数据源没出现这种情况，所以折中了下，静态加的查看列，动态加的数据源。效果达到^_^