在启用分页的情况下将GridView导出到Excel-ExportGridViewtoExcelwithpagingenabled

I've enabled exporting to Excel from a GridView like this :

我已经启用了从GridView导出到Excel,如下所示:

 private void ExportGridToExcel()
{
    Response.Clear();
    Response.Buffer = true;
    Response.ClearContent();
    Response.ClearHeaders();
    Response.Charset = "";
    string FileName = "BU Results" + DateTime.Now + ".xls";
    StringWriter strwritter = new StringWriter();
    HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);

    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    Response.COntentType= "application/vnd.ms-excel";
    Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);
    GridView1.GridLines = GridLines.Both;
    GridView1.HeaderStyle.Font.Bold = true;
    GridView1.RenderControl(htmltextwrtter);
    Response.Write(strwritter.ToString());
    Response.End();

}


protected void Button3_Click(object sender, EventArgs e)
{
    ExportGridToExcel();

}

This works fine- the only issue is that when I have paging enabled it exports only one page at a time and includes the page hyperlinks at the bottom. So I tried fixing it by adding this code to my ExportGridToExcel :

这很好 - 唯一的问题是,当我启用分页时,它一次只导出一个页面,并在底部包含页面超链接。所以我尝试通过将此代码添加到我的ExportGridToExcel来修复它:

GridView1.AllowPaging = false;
GridView1.DataBind();

This does get rid of the paging in the export, however I've also got some filters applied and those are then removed in the Export so it exports the whole GridView Table rather than the filtered Data.

这确实摆脱了导出中的分页,但是我也应用了一些过滤器,然后在导出中删除它们,因此它导出整个GridView表而不是过滤后的数据。

This is how the GridView can be filtered :

这就是GridView的过滤方式:

protected void Button1_Click(object sender, EventArgs e)
{
    ViewState.Add("test", true);

    if (DropDownList1.SelectedValue.ToString() == "Name")
    {

        ObjectDataSource1.FilterExpression = "Name LIKE '%" + TextBox1.Text + "%' ";

    }
    else if (DropDownList1.SelectedValue.ToString() == "Department")
    {

        ObjectDataSource1.FilterExpression = "Department LIKE '%" + TextBox1.Text + "%' ";

    }
  }

Any suggestions?

1 个解决方案

 public static void ExportGrid(TBL_CONDORDataTable dt, string filename)
    {
        try
        {
            HttpResponse respOnse= HttpContext.Current.Response;

            // first let's clean up the response.object
            response.Clear();
            response.Charset = "";

            // set the response mime type for excel
            response.COntentType= "application/vnd.ms-excel";
            response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");

            // create a string writer
            using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {
                    // instantiate a datagrid
                    DataGrid dg = new DataGrid();
                    dg.DataSource = dt;
                    dg.DataBind();
                    dg.RenderControl(htw);
                    response.Write(sw.ToString());
                    response.End();
                }
            }
        }
        catch (Exception ex)
        { string msg = ex.Message.ToString(); }
    }
}