Export to Excel

Ø       Rendering the Gridview Control to Excel

Ø       Rendering the underlying DataTable to Excel

Rendering the Gridview Control to Excel

This is one of the most commonly done approach where we set MIME type and use Gridview’s RenderControl() method, similar to we do for a Datagrid control.

        string attachment = "attachment; filename=Employee.xls";

        Response.ClearContent();

        Response.AddHeader("content-disposition", attachment);

        Response.ContentType = "application/ms-excel";

        StringWriter stw = new StringWriter();

        HtmlTextWriter htextw = new HtmlTextWriter(stw);

        gvEmployee.RenderControl(htextw);

        Response.Write(stw.ToString());

        Response.End();

When we execute the above code, it will give the following error.

Control 'gvEmployee' of type 'GridView' must be placed inside a form tag with runat=server

We can resolve the error by 2 ways,

1.      Adding the Gridview to a HtmlForm object programmatically

2.      Overriding VerifyRenderingInServerForm Event in the page.

Adding the Gridview to a HtmlForm object programmatically

So, instead of rendering the gridview add the gridview to an HtmlForm object and render the form.

        HtmlForm form = new HtmlForm();

        string attachment = "attachment; filename=Employee.xls";

        Response.ClearContent();

        Response.AddHeader("content-disposition", attachment);

        Response.ContentType = "application/ms-excel";

        StringWriter stw = new StringWriter();

        HtmlTextWriter htextw = new HtmlTextWriter(stw);

        form.Controls.Add(gvEmployee);

        this.Controls.Add(form);

        form.RenderControl(htextw);

        Response.Write(stw.ToString());

        Response.End();

This will solve the error.

Overriding VerifyRenderingInServerForm Event in the page

Adding this event in the codebehind confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time.

protected void Button1_Click(object sender, EventArgs e)

{

string attachment = "attachment; filename=Employee.xls";

        Response.ClearContent();

        Response.AddHeader("content-disposition", attachment);

        Response.ContentType = "application/ms-excel";

        StringWriter stw = new StringWriter();

        HtmlTextWriter htextw = new HtmlTextWriter(stw);

        gvEmployee.RenderControl(htextw);

        Response.Write(stw.ToString());

        Response.End();

}

public override void VerifyRenderingInServerForm(Control control)

    {

    }

Rendering the underlying DataTable to Excel

This is one of the easiest ways of exporting the data to excel.  The content of the DataTable should be written to the response by setting ContentType attribute to "application/vnd.ms-excel" and by setting its header.

protected void Button3_Click(object sender, EventArgs e)

    {

        DataTable dt = GetData();

        string attachment = "attachment; filename=Employee.xls";

        Response.ClearContent();

        Response.AddHeader("content-disposition", attachment);

        Response.ContentType = "application/vnd.ms-excel";

        string tab = "";

        foreach (DataColumn dc in dt.Columns)

        {

            Response.Write(tab + dc.ColumnName);

            tab = "\t";

        }

        Response.Write("\n");

        int i;

        foreach (DataRow dr in dt.Rows)

        {

            tab = "";

            for (i = 0; i < dt.Columns.Count; i++)

            {

                Response.Write(tab + dr[i].ToString());

                tab = "\t";

            }

            Response.Write("\n");

        }

        Response.End();

    }

Execute the page and see export to excel in action.

Download the code attached with this article. No need to change any settings as it uses the Sql Express database in App_Data folder.