In this code snippet, I made use of an Microsoft Access database to bind a DataGrid and export the data to Excel.
First, create an Access database with extension, like example.mdb, and then go to the design of the table and add the columns you wish to have and the data types. In my Access database, I have three columns EmployeeID, UserName, Title, with the table name EmployeesStandalone. Don't forget to add the Access database to the App_data folder in default.aspx.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataGrid ID="Exportexcel" runat="server">
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Export to excel"/>
</div>
</form>
</body>
</html>
In default.aspx.cs:
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
OleDbConnection MyOleDbConnection =
new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" +
Server.MapPath("~/App_Data/nwind.mdb"));
OleDbDataAdapter MyOleDbDataAdapter = new OleDbDataAdapter();
MyOleDbDataAdapter.SelectCommand =
new OleDbCommand("SELECT EmployeeID,UserName,Title FROM EmployeesStandalone",
MyOleDbConnection);
DataTable table = new DataTable();
MyOleDbConnection.Open();
try
{
MyOleDbDataAdapter.Fill(table);
}
finally
{
MyOleDbConnection.Close();
}
Exportexcel.DataSource = table.DefaultView;
Exportexcel.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=Lokesh.xls");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
Exportexcel.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}
}
Everything is self-explanatory in this. Get the response, and add the header to it specifying the file name. Create a StringWriter object and then render the entire content of the grid to the HtmlWriter object and then write it to the StringWriter object.
Hope this helps at least a few people.
