Export user controls gridview to excel

Question

On my aspx page, I have a Usercontrol which contains a Gridview, whose data is loaded dynamically based on params passed from the aspx page to the UserControl, which I called QuotesReport1.

When the export runs, it only exports the layout into the Spreadsheet, and none of the gridview's data, for example:

<style>
    body
    {
        margin: 0px;
    }
</style>
<div>
</div>

My code for the export is:

protected void Page_Load(object sender, EventArgs e)
{
    string toDate = "";
    string fromDate = "";

    toDate = Request.QueryString.Get("toDate");
    fromDate = Request.QueryString.Get("fromDate");


    QuotesReport1.ToDate = DateTime.Parse(toDate);
    QuotesReport1.FromDate = DateTime.Parse(fromDate);
    QuotesReport1.Status = QuotesReport.quoteStatus.PENDING_REVIEW;

    string attachment = "attachment; filename=Quotes_Pending.xls";
    Response.ClearContent();
    Response.AddHeader("content-disposition", attachment);
    Response.ContentType = "application/ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);
    QuotesReport1.RenderControl(htw);
    Response.Write(sw.ToString());
    Response.End();
}
public override void VerifyRenderingInServerForm(Control control) { }

Inside the UserControl, a SQL query runs, which returns a DataTable, which is then bound to the Gridview inside the UserControl, something along the lines of:

SQLData da = new SQLData();
GridView1.DataSource = da.SGetDataTable(query);
GridView1.DataBind();

paste that code into your original question .. the formatting in the comments section makes it hard to read... — MethodMan
just a quick question, is the Grid actually displaying any data..? have you stepped thru the code on the Page_Load Event as well as putting breakpoints in the code where the UserControl is located..? — MethodMan

IamStalker IamStalker · Accepted Answer · 2012-12-26T12:59:03

The simplest of the ways i have

public class ExcelUtility
{
    public static void ToExcel(object dataSource)
    {
        GridView grid = new GridView { DataSource = dataSource };
        grid.DataBind();

        StringBuilder sb = new StringBuilder();
        foreach (TableCell cell in grid.HeaderRow.Cells)
            sb.Append(string.Format("\"{0}\",", cell.Text));
        sb.Remove(sb.Length - 1, 1);
        sb.AppendLine();

        foreach (GridViewRow row in grid.Rows)
        {
            foreach (TableCell cell in row.Cells)
                sb.Append(string.Format("\"{0}\",", cell.Text.Trim().Replace("&nbsp;", string.Empty)));
            sb.Remove(sb.Length - 1, 1);
            sb.AppendLine();
        }
        ExportToExcel(sb.ToString());
    }

    private static void ExportToExcel(string data)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=Report.csv");
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
        HttpContext.Current.Response.ContentType = "text/csv";
        HttpContext.Current.Response.Write(data);
        HttpContext.Current.Response.End();
    }
}

using:

   string result = ExcelUtility.ToExcel(_db.FindAll());
   ExcelUtility.ExportToExcel(result);

Export user controls gridview to excel

1 Answers