c# GridView导出Excel （包括GridView在ascx上的情况如何导出）

一、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();
        }

posted on 2010-08-06 16:57 ^_^ fighting 阅读(960) 评论(1) 编辑收藏举报

刷新页面返回顶部

导航

c# GridView导出Excel （包括GridView在ascx上的情况如何导出）