Asp.net export GridView to Excel spreadsheet

In this port “Asp.net export GridView to Excel spreadsheet”, we shall see how to export content from a GridView to an Excel spreadsheet.

Exporting content to Excel is a common feature, often seen in reporting modules, there are many ways of exporting details to work, here we shall see a simple technique, of reading  the content in a GridView and exporting it to Excel by setting the Content-Type. 

This method of exporting is very handy since it allows to do any kind of formatting to the Excel spreadsheet using the HTML tags.

Add a Button to the Asp.Net page and in the click event of the Button add the following code to export the content.

protected void Page_Load(object sender, EventArgs e)

{

    SqlConnection objConn;

    SqlCommand objCmd;

    DataSet dsDocData;

    SqlDataAdapter objDA;

    //

    string strConn = ConfigurationManager.ConnectionStrings["EmployeesConnectionString"].ToString();

    objConn = new SqlConnection(strConn);

    objCmd = new SqlCommand("SELECT TOP 10 * FROM Employee", objConn);

    objDA = new SqlDataAdapter(objCmd);

    dsDocData = new DataSet();

    objDA.Fill(dsDocData, "dtDocData");

    //

    grdEmployees.DataSource = dsDocData.Tables["dtDocData"].DefaultView;

    grdEmployees.DataBind();

}

//

protected void cmdExportGridViewToExcel_Click(object sender, EventArgs e)

{

    GenerateExcel(grdEmployees);

}

//

public static void GenerateExcel(GridView grdSource)

{

   

    StringBuilder sbDocBody = new StringBuilder(); ;

    try

    {

        // Declare Styles

        sbDocBody.Append("<style>");

        sbDocBody.Append(".Header {  background-color:Navy; color:#ffffff; font-weight:bold;font-family:Verdana; font-size:12px;}");

        sbDocBody.Append(".SectionHeader { background-color:#8080aa; color:#ffffff; font-family:Verdana; font-size:12px;font-weight:bold;}");

        sbDocBody.Append(".Content { background-color:#ccccff; color:#000000; font-family:Verdana; font-size:12px;text-align:left}");

        sbDocBody.Append(".Label { background-color:#ccccee; color:#000000; font-family:Verdana; font-size:12px; text-align:right;}");

        sbDocBody.Append("</style>");

        //

        StringBuilder sbContent = new StringBuilder(); ;

        sbDocBody.Append("<br><table align=\"center\" cellpadding=1 cellspacing=0 style=\"background-color:#000000;\">");

        sbDocBody.Append("<tr><td width=\"500\">");

        sbDocBody.Append("<table width=\"100%\" cellpadding=1 cellspacing=2 style=\"background-color:#ffffff;\">");

        // Populate the Employee Details

       

        //

        if (grdSource.Rows.Count > 0)

        {

            sbDocBody.Append("<tr><td>");

            // Inner Table for Employee Details

            sbDocBody.Append("<table width=\"600\" cellpadding=\"0\" cellspacing=\"2\"><tr><td>");

            //

            // Add Column Headers

            sbDocBody.Append("<tr><td width=\"25\"> </td></tr>");

            sbDocBody.Append("<tr>");

            sbDocBody.Append("<td> </td>");

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

            {

                sbDocBody.Append("<td class=\"Header\" width=\"120\">" + grdSource.Columns[i].HeaderText + "</td>");

            }

            sbDocBody.Append("</tr>");

            //

            // Add Data Rows

            for (int i = 0; i < grdSource.Rows.Count; i++)

            {

                sbDocBody.Append("<tr>");

                sbDocBody.Append("<td> </td>");

                for (int j = 0; j < grdSource.Columns.Count; j++)

                {

                    if (grdSource.Rows[i].Cells[j].Controls.Count == 0)

                    {

                        sbDocBody.Append("<td class=\"Content\">" + grdSource.Rows[i].Cells[j].Text + "</td>");

                    }

                    else

                    {

                        if (grdSource.Rows[i].Cells[j].Controls[1].ToString() == "System.Web.UI.WebControls.LinkButton")

                        {

                            LinkButton lnkBtn = (LinkButton)grdSource.Rows[i].Cells[j].Controls[1];

                            sbDocBody.Append("<td class=\"Content\">" + lnkBtn.Text + "</td>");

                        }

                        else if (grdSource.Rows[i].Cells[j].Controls[1].ToString() == "System.Web.UI.WebControls.Label")

                        {

                            Label lblData = (Label)grdSource.Rows[i].Cells[j].Controls[1];

                            sbDocBody.Append("<td class=\"Content\">" + lblData.Text + "</td>");

                        }

                    }

                }

                sbDocBody.Append("</tr>");

            }

            sbDocBody.Append("</table>");

            sbDocBody.Append("</td></tr></table>");

            sbDocBody.Append("</td></tr></table>");

        }

        //

        HttpContext.Current.Response.Clear();

        HttpContext.Current.Response.Buffer = true;

        //

        HttpContext.Current.Response.AppendHeader("Content-Type", "application/ms-excel");

        HttpContext.Current.Response.AppendHeader("Content-disposition", "attachment; filename=EmployeeDetails.xls");

        HttpContext.Current.Response.Write(sbDocBody.ToString());

        HttpContext.Current.Response.End();

    }

    catch (Exception ex)

    {

        // Ignore this error as this is caused due to termination of the Response Stream.

    }

}

The Exported Excel spreadsheet looks like this.

That’s it we have seen a simple technique to export content of a GridView into an Excel Spreadsheet.

Related Post
Asp.net export to Word document from SQL Server Table
Asp.net export to Excel spreadsheet from SQL Server Table
Asp.net export GridView to Excel spreadsheet
Asp.net export GridView to Word Document

Asp.net export DataTable to Word Document

Asp.net export DataTable to Excel Spreadsheet