一、Gridview中的内容导出到Excel

  在日常工作中,经常要将gridview中的内容导出到excel报表中去,在asp.net 2.0中,同样可以很方便地实现将整个gridview中的内容导出到excel报表中去,下面介绍其具体做法:

  首先,建立基本的页面default.aspx

<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
<br/>
<asp:Button ID="BtnExport" runat="server" OnClick="BtnExport_Click"
Text="Export to Excel" />
</form>

 

  在default.aspx.cs中,写入如下代码:

protected void Page_Load(object sender, EventArgs e)
{
 if (!Page.IsPostBack)
 {
  BindData();
 }
}
private void BindData()
{
 string query = "SELECT * FROM customers";
 SqlConnection myConnection = new SqlConnection(ConnectionString);
 SqlDataAdapter ad = new SqlDataAdapter(query, myConnection);
 DataSet ds = new DataSet();
 ad.Fill(ds, "customers");
 GridView1.DataSource = ds;
 GridView1.DataBind();
}

public override void VerifyRenderingInServerForm(Control control)
{
 // Confirms that an HtmlForm control is rendered for
}

protected void Button1_Click(object sender, EventArgs e)
{
 Response.Clear();
 Response.AddHeader("content-disposition","attachment;filename=FileName.xls");
 Response.Charset = "gb2312";
 Response.ContentType = "application/vnd.xls";
 System.IO.StringWriter stringWrite = new System.IO.StringWriter();
 System.Web.UI.HtmlTextWriter htmlWrite =new HtmlTextWriter(stringWrite);

 GridView1.AllowPaging = false;
 BindData();
 GridView1.RenderControl(htmlWrite);

 Response.Write(stringWrite.ToString());
 Response.End();
 GridView1.AllowPaging = true;
 BindData();
}
protected void paging(object sender,GridViewPageEventArgs e)
{
 GridView1.PageIndex = e.NewPageIndex;
 BindData();
} 


  在上面的代码中,我们首先将gridview绑定到指定的数据源中,然后在button1的按钮(用来做导出到EXCEL的)的事件中,写入相关的代码。这里使用Response.AddHeader("content-disposition","attachment;filename= exporttoexcel.xls");中的filename来指定将要导出的excel的文件名,这里是exporttoexcel.xls。要注意的是,由于gridview的内容可能是分页显示的,因此,这里在每次导出excel时,先将gridview的allowpaging属性设置为 false,然后通过页面流的方式导出当前页的gridview到excel中,最后再重新设置其allowpaging属性。另外要注意的是,要写一个空的VerifyRenderingInServerForm方法(必须写),以确认在运行时为指定的ASP.NET 服务器控件呈现HtmlForm 控件。

 

-----------------------------------------------

以上方法是当你的 GridView写在aspx页面上,但是当GridView是写在ascx页面上时,在使用GridView1.RenderControl()就会有问题,因为无法重写VerifyRenderingInServerForm方法,所以在ascx页面,我们可以在服务器端写一个方法来自己拼接一个GridView的html代码,伪代码如下:

上面的代码做一些改动如下:

private void GetGVHtml()

{

         //循环遍历GridView,拼接为一个html代码

}

protected void Button1_Click(object sender, EventArgs e)
{
 Response.Clear();
 Response.AddHeader("content-disposition","attachment;filename=FileName.xls");
 Response.Charset = "gb2312";
 Response.ContentType = "application/vnd.xls";
 //System.IO.StringWriter stringWrite = new System.IO.StringWriter();
 //System.Web.UI.HtmlTextWriter htmlWrite =new HtmlTextWriter(stringWrite);

 GridView1.AllowPaging = false;
 BindData();
 //GridView1.RenderControl(htmlWrite);

 Response.Write(GetGVHtml());
 Response.End();
 GridView1.AllowPaging = true;
 BindData();
}

//下面是一个客户端获得GridView的html的方法:

<script language=javascript>

function GetGvHtml(gvid,hfid)
{
    
         var ReportContent = "";

    ReportContent += "<html>";
        ReportContent += "<head>";    
            ReportContent += "<style type='text/css'>";

                ReportContent += "body { font-family: Arial 12px; margin: 0px; width: 100%; }";
                ReportContent += ".print-header { width: 100%; border: solid 1px Black; text-align:center; vertical-align: middle; margin: 5px 0px 5px 0px; line-height: normal; font-size:14px; background-color: #cbcbcb; }";
                ReportContent += ".tablestyle { font-family: Arial; font-size: small; border: solid 1px #8C8C8C; font-size: 12px;}";
                ReportContent += ".headerstyle th { border-color: #ABABAB; border-style: solid solid solid none; border-width: 1px 1px 1px medium; text-align:left; font-weight: normal; vertical-align: bottom; background-color: #ececec; padding: 2px 0px 0px 4px; font-family: Arial; font-size: 12px; vertical-align: top;}";
                ReportContent += ".rowstyle td { border-color: #ABABAB; border-style: solid solid solid none; border-width: 1px 1px 1px medium; text-align:left; padding: 2px 0px 0px 4px; font-family: Arial; font-size: 12px; vertical-align: top; }";
                ReportContent += ".altrowstyle td { border-color: #ABABAB; border-style: solid solid solid none; border-width: 1px 1px 1px medium; text-align:left; padding: 2px 0px 0px 4px; font-family: Arial; font-size: 12px; vertical-align: top; }";
                ReportContent += ".PrintVisibleCol { display: block; }";
                ReportContent += ".PrintInvisibleCol { display: none; }";
                // Added H1 and H2 for printing the checklist data item grading page.
                ReportContent += "H1 { text-align:left; font-size: 14px; font-weight:normal; font-weight: bold;}";
                ReportContent += "H2 { text-align:left; font-size: 14px; font-weight:normal; font-weight: bold;}";
            
            ReportContent += "</style>";            
        ReportContent += "</head>";

        ReportContent += "<body>";
            ReportContent += "<table cellspacing='0' cellpadding='0' width='35%' class='print-grid-content'>";

                    if (document.getElementById(document.getElementById(gvid).value) != null) {
                        var GridContent = document.getElementById(document.getElementById('gvid').value).innerHTML;
                        GridContent = GridContent.replace(/<[\/]{0,1}(a|A)[^><]*>/g, "");
                        GridContent = GridContent.replace(/<[\/]{0,1}(img|IMG)[^><]*>/g, "");
                        GridContent = GridContent.replace(/<[\/]{0,1}(input|INPUT)[^><]*>/g, "");
                        GridContent = GridContent.replace(/<[\/]{0,1}(select|SELECT)[^><]*>/g, "");                        
                    }
                        ReportContent += GridContent;
                        
                    ReportContent += "</td>";
                ReportContent += "</tr>";
            ReportContent += "</table>";            
        ReportContent += "</body>";
    ReportContent += "</html>";
    document.getElementById(document.getElementById(hfid).value).value = ReportContent;
}

</script>

<asp:HiddenField ID="hfGVHtml" runat="server" />
<input type="hidden" value='<%=gvGroupList.ClientID%>' id="gvid"/>
<input type="hidden" value='<%=hfGVHtml.ClientID%>' id="hfid"/>

<asp:LinkButton ID="lbtnExcelExport" OnClientClick="javascript:GetGvHtml('gvid','hfid');" class="iconExport" runat="server" 
                                        onclick="lbtnExcelExport_Click" >导出Excel</asp:LinkButton>

 

 

服务器端:

protected void lbtnExcelExport_Click(object sender, EventArgs e)
        {
                Response.Clear();
                Response.AddHeader("content-disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls");
                Response.Charset = "gb2312";
                Response.ContentType = "application/vnd.xls";
                Response.Write(hfGVHtml.Value.ToString());
                Response.End();
        }