Export GridView to Excel with paging enabled

Question

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

 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 :

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.

This is how the GridView can be filtered :

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?

The main idea is that these filters need to be reapplied in the ExportGridToExcel method before the data bind is called. Your code example does not show the filters (i assume select parameters) being applied. If you do not want to get the data again you can cache the selected data and bind to that in the export. — Bolo

ElimGarak ElimGarak · Accepted Answer · 2015-11-19T23:58:28

How about running another query on the ObjectData Source and sending that result set to another method to export the data.

The method below accepts a data table, binds it to a data grid object and exports it.

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

Export GridView to Excel with paging enabled

1 Answers