Export to Excel using Asp.Net DataTable and DataGrid – C#, Vb.Net

  ASP.NET   Tuesday, June 9, 2015   3334 | No Comments Yet!!! | No Download | No Demo | Pawan kumar
 Tags: Export to Excel using Asp.Net DataTable and DataGrid – C#  Vb.Net 


1). HTML

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html>
<head>
    <title>Export To Excel using Asp.Net</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <input type="button" id="btExportToExcel" value="Export To Excel" 
                onserverclick="btExportToExcel_Click" runat="server" />
        </div>
    </form>
</body>
</html

2). Code Behind

using System;
using System.Data;                      // FOR DATABASE
using System.Data.SqlClient;            // FOR SQL CONNECTION.
using System.Web.UI.WebControls;        // FOR DATAGRID.

public partial class _Default : System.Web.UI.Page 
{
    protected void btExportToExcel_Click(object sender, EventArgs e)
    {
        using (SqlConnection con = new SqlConnection("Data Source=dna;Persist Security Info=False;" +
                "Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=demo;Connect Timeout=30;"))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = "SELECT BookID [Book ID], BookName [Name of the Book], " +
                    "Price [Price ($)] FROM dbo.Books";
                con.Open();

                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;

                DataTable dt = new DataTable();
                sda.Fill(dt);

                Decimal dTotalPrice = 0;
                for (int i = 0; i <= dt.Rows.Count - 1; i++) {
                    dTotalPrice += dt.Rows[i].Field<Decimal>(2);
                }

                // NOW ASSIGN DATA TO A DATAGRID.
                DataGrid dg = new DataGrid();
                dg.DataSource = dt; 
                dg.DataBind();

                // THE EXCEL FILE.
                string sFileName = "BooksList-" + System.DateTime.Now.Date + ".xls"; 
                sFileName = sFileName.Replace("/", "");

                // SEND OUTPUT TO THE CLIENT MACHINE USING "RESPONSE OBJECT".
                Response.ClearContent();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", "attachment; filename=" + sFileName);
                Response.ContentType = "application/vnd.ms-excel";
                EnableViewState = false;

                System.IO.StringWriter objSW = new System.IO.StringWriter();
                System.Web.UI.HtmlTextWriter objHTW = new System.Web.UI.HtmlTextWriter(objSW);

                dg.HeaderStyle.Font.Bold = true;     // SET HEADER AS BOLD.
                dg.RenderControl(objHTW);

                // STYLE THE SHEET AND WRITE DATA TO IT.
                Response.Write("<style> TABLE { border:dotted 1px #999; } " + 
                        "TD { border:dotted 1px #D5D5D5; text-align:center } </style>");
                Response.Write(objSW.ToString());

                // ADD A ROW AT THE END OF THE SHEET SHOWING A RUNNING TOTAL OF PRICE.
                Response.Write("<table><tr><td><b>Total: </b></td><td></td><td><b>" +
                            dTotalPrice.ToString("N2") + "</b></td></tr></table>");

                Response.End();
                dg = null;
            }
        }        
    }
}

 


Post Comments

if you have some suggestion, question, request on Export to Excel using Asp.Net DataTable and DataGrid – C#, Vb.Net.

Please use the comment box to express your views.

 

Comments: