Export GridView to Access Using ASP.Net C#

Background

There is often a need in a project's reporting module to export a GridView to Access. So by considering that requirement I decided to write this article especially focusing on beginners and those who want to learn how to export a GridView to Access Using ASP.NET C# and using the itextsharp DLL.

 Prerequisites

• To export the Grid view, we need to use a reference of itextsharp.dll.
• Download the itextsharp.dll from the internet.

The itextsharp.dll is the free DLL available for download that provides some methods to export a Grid view into an Access file. After adding the reference, use the following reference of itextsharp.dll:

using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html.simpleparser;

Now before creating the application, let us create a table named employee in a database from where we show the records in a Grid view, the table has the following fields (shown in the following image):

 

 

I hope you have created the same type of table.

Now create the project as:

1. "Start" - "All Programs" - "Microsoft Visual Studio 2010".
2. "File" - "New Project" - "C#" - "Empty Project" (to avoid adding a master page).
3. Provide the Project name such as ExportGridToAccess or another as you wish and specify the location.
4. Then right-click on Solution Explorer - "Add New Item" - Default.aspx page.
5. one Buttons, one label and a grid view.

Now let us create a function to bind the records to the Grid view from the database. If you are a beginner and don't know in detail how to bind a Grid view from a database then refer to my following article.

• Inserting Form Data Into DataBase and Display In ASP.Net GridView

Now, for this article create the following function in the default.aspx.cs page to bind the Grid view:

private void Bindgrid()
   {
       connection();
       query = "select *from Employee";//not recommended this i have written just for example,write stored procedure for security
       com = new SqlCommand(query, con);
       SqlDataReader dr = com.ExecuteReader();
       GridView1.DataSource = dr;
       GridView1.DataBind();
       con.Close();

   }

Now, call the above function on page load as:

protected void Page_Load(object sender, EventArgs e)
   {
       if (!IsPostBack)
       {
           Bindgrid();

       }
   }

Now run the application and then we can see the following records in the Grid view:

 

 

 

We now have records to export into Access. Let us start coding for our actual requirements. Add the VerifyRenderingInServerForm event after the page load that is required when exporting a Grid view to Excel, Word and PDF to avoid the run time error "GridView' must be placed inside a form tag with runat=server."

public override void VerifyRenderingInServerForm(Control control)
   {
       //required to avoid the runtime error "
       //Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."
   }

Now before creating the function to Export the grid view to Access add the reference of itextsharp.dll by right-clicking the Solution Explorer. After adding the reference the Solution Explorer will look as in the following:

 

 

Now create the following function to export the Grid view to Access:

 

private void ExportGridToAccess()
    {

        Response.ContentType = "application/ms-access";
        Response.AddHeader("content-disposition", "attachment; filename=Vithal_Wadje.mdb");
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        GridView1.RenderControl(hw);
        StringReader sr = new StringReader(sw.ToString());
        Document AccessDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
        HTMLWorker htmlparser = new HTMLWorker(AccessDoc);
        PdfWriter.GetInstance(AccessDoc, Response.OutputStream);
        AccessDoc.Open();
        htmlparser.Parse(sr);
        AccessDoc.Close();
        Response.Write(AccessDoc);
        Response.End();
        GridView1.AllowPaging = true;
        GridView1.DataBind();
    }

 Now double-click on the Export button and call the preceding function on "onclick" as in the following:

protected void Button1_Click(object sender, EventArgs e)
   {
       ExportGridToAccess();
   }

 Now the entire code of the Default.aspx.cs page will be as follows:

using System;
using System.Web.UI;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Web;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html.simpleparser;

public partial class _Default : System.Web.UI.Page
{
    private SqlConnection con;
    private SqlCommand com;
    private string constr, query;
    private void connection()
    {
        constr = ConfigurationManager.ConnectionStrings["getconn"].ToString();
        con = new SqlConnection(constr);
        con.Open();

    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bindgrid();

        }
    }

    public override void VerifyRenderingInServerForm(Control control)
    {
        //required to avoid the runtime error "
        //Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."
    }

    private void Bindgrid()
    {
        connection();
        query = "select *from Employee";//not recommended this i have written just for example,write stored procedure for security
        com = new SqlCommand(query, con);
        SqlDataReader dr = com.ExecuteReader();
        GridView1.DataSource = dr;
        GridView1.DataBind();
        con.Close();

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        ExportGridToAccess();
    }
    private void ExportGridToAccess()
    {

        Response.ContentType = "application/ms-access";
        Response.AddHeader("content-disposition", "attachment; filename=Vithal_Wadje.mdb");
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        GridView1.RenderControl(hw);
        StringReader sr = new StringReader(sw.ToString());
        Document AccessDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
        HTMLWorker htmlparser = new HTMLWorker(AccessDoc);
        PdfWriter.GetInstance(AccessDoc, Response.OutputStream);
        AccessDoc.Open();
        htmlparser.Parse(sr);
        AccessDoc.Close();
        Response.Write(AccessDoc);
        Response.End();
        GridView1.AllowPaging = true;
        GridView1.DataBind();
    }
}

 Now run the application and click on the Export Button. The following popup is shown:

 

 

 Now click and save the preceding file and open it with Excel by establishing the connection or you can open this file with the tool mdb file viewer. 

 

 Notes

• Download the Zip file from the attachment for the full source code of the application.
• Change the connection string in the web.config file to specify your server location.

Summary

I hope this article is useful for all readers, if you have any suggestion then please contact me including beginners also.