Category >
ASP.NET
|| Published on :
Tuesday, June 9, 2015 || Views:
5517
||
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;
}
}
}
}