c# - 将启用分页的 GridView 导出到 Excel

Question

我已经启用了从 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();

}

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

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

这确实消除了导出中的分页，但是我还应用了一些过滤器，然后在导出中删除了这些过滤器，因此它导出了整个 GridView 表而不是过滤后的数据.

这是过滤 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 + "%' ";

    }
  }

有什么建议吗？

Answer 1

如何在 ObjectData Source 上运行另一个查询并将该结果集发送到另一个方法以导出数据。

下面的方法接受数据表，将其绑定(bind)到数据网格对象并将其导出。

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