CODEWITHSUNDEEP
c#asp.netgridview
MariaL
MariaL

Reputation: 1242

Export GridView to Excel with paging enabled

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?

Upvotes: 2

Views: 1572

Answers (1)

Elim Garak
Elim Garak

Reputation: 1827

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

Upvotes: 0

Related Questions