Home  »  Articles  »  C#.Net  »

Export GridView To Excel using C#.Net

 Posted By : Manoranjan SahooPosted Date : 15/11/2010 08:40:16 AM Category : C#.Net Points : 8 (Rs 4.00)

Here I provide the simple code for export gridview to excel in c#.net. Here i considered that, there is one gridview and two button.One button to fetch the data and show on gridview and other is to export that data into excel. To export follow the below steps.

Step 1 :

Create one .aspx file. Add one gridview and two button to this.

Step2 :

Now Name one of those button as btnExportToExcel. then write the following code to the click event of that button. Make changes the sql query as per your need.

Code :

protected void btnExportToExcel_Click(object sender, EventArgs e)
{
    try
    {
        string lblSQLQry="select * from  tablename where <condition>";
        string fileName="FileName";//you can also pass a unique file name here
        ExportToExcelwithDatareader1(lblSQLQry, fileName);
    }
    catch (SqlException ex){}
    catch (SystemException ex){}
}

Step3 :

Below the above click event method write the following method. Make changes the connection details as per your need.

Code :

public void ExportToExcelwithDatareader1(string strqry, string strfileName)
{
    try
    {
        SqlDataReader dr = null;
        StringBuilder sb = new StringBuilder();
        SqlCommand cmdexreader = new  SqlCommand();
        SqlConnection sqlconn="Your SQL Server Connection String";
        sqlconn.Open();
        cmdexreader.CommandText = strqry;
        cmdexreader.CommandTimeout = 0;
        cmdexreader.Connection =sqlconn;
        dr = cmdexreader.ExecuteReader(CommandBehavior.CloseConnection);
        string headername="Report";
        sb.Append("<TABLE border='1'><Center><thead style='font-size:16;font-family:Verdana;color:green'><font color='green' size='5'><b>" + headername + "</font></thead></Center>");
        for (int count = 1; count < dr.FieldCount - 3; count++)
        {
            string thname = "";
            thname = dr.GetName(count).ToString();
            sb.Append("<TH width='71' style='background-color:orange;position:fixed;color:White;font-family:Verdana;white-space: pre-wrap ;font-size:14'><b>" + thname + "</b></TH>");
        }
        while (dr.Read())
        {
            sb.Append("<TR>");
            for (int col = 1; col < dr.FieldCount - 3; col++)
            {
                sb.Append("<TD align='Center'><font color='black'>" + dr.GetValue(col).ToString() + "</font></TD>");
            }
            sb.Append("</TR>");
        }
        dr.Dispose();
        sb.Append("</TABLE>");
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=" + strfileName + ".xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.xls";
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        stringWrite.WriteLine(sb.ToString());
        System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
        Response.Write(stringWrite.ToString());
        Response.Flush();
        Response.End();
    }
    catch (Exception ex){}
}

Step 4 :

That’s it. Now you can run the file to see the output.

Thank You.

Responses
No responses found for this thread. Be the first to respond this thread.
Post Reply
Login to post Response.