Click here to Skip to main content
Click here to Skip to main content

Tagged as

Export to Excel from DataGrid (Using an Microsoft Access database)

, 15 Sep 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
How to export to Excel from DataGrid using an Microsoft Access database.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

lperumb
Software Developer Wipro Technologies
India India
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 15 Sep 2011
Article Copyright 2011 by lperumb
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid