Friday, June 1, 2012

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.




Search Flipkart Products:
Flipkart.com

1 comment:

Bhaskara said...

great post
http://csharpektroncmssql.blogspot.com/2011/12/export-gridview-to-excel-in-aspnet.html