Export Gridview Data to Excel in ASP.NET

Introduction

This tip describes how to export data to an Excel file from a GridView. We will create a sample database and bind GridView and then export in Excel format.

Background

There are many scenarios where we would want to export data into Excel format. In this blog, we will see how we can export data from a GridView to Excel.

Coding

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.OleDb;

using System.IO;

public partial class _Default : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {        

        if (Page.IsPostBack == false)

        {

            BindGridview();

        }

    }

    public void BindGridview()

    {

        OleDbConnection con = new OleDbConnection();

        con.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data source=" + Server.MapPath("Bin/MyDatabase.mdb") + "";

        con.Open();

        OleDbCommand cmd = new OleDbCommand("SELECT * FROM MyTable", con);        

        OleDbDataAdapter da = new OleDbDataAdapter(cmd);

        DataTable dt = new DataTable();

        da.Fill(dt);

        gdvMyGridView.DataSource = dt;

        gdvMyGridView.DataBind();

        ViewState["ExportReport"] = dt;

    }

    protected void btnExport_Click(object sender, EventArgs e)

    {

        try

        {

            DataTable dt = null;

            dt = (DataTable)ViewState["ExportReport"];

            if (dt == null)

            {

                throw new Exception("No record found for export");

            }

            string Path = "D:\\ImportDailCallReport\\myexcelfile_" + DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + ".xls";

            FileInfo FI = new FileInfo(Path);

            StringWriter stringWriter = new StringWriter();

            HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWriter);

            DataGrid DataGrd = new DataGrid();

            DataGrd.DataSource = dt;

            DataGrd.DataBind();

            DataGrd.RenderControl(htmlWrite);

            string directory = Path.Substring(0, Path.LastIndexOf("\\"));

            if (!Directory.Exists(directory))

            {

                Directory.CreateDirectory(directory);

            }

            System.IO.StreamWriter vw = new System.IO.StreamWriter(Path, true);

            stringWriter.ToString().Normalize();

            vw.Write(stringWriter.ToString());

            vw.Flush();

            vw.Close();

            WriteAttachment(FI.Name, "application/vnd.ms-excel", stringWriter.ToString());

        }

        catch (Exception ex)

        {

            

        }

        finally

        {

        }

    }

    public static void WriteAttachment(string FileName, string FileType, string content)

    {

        try

        {

            HttpResponse Response = System.Web.HttpContext.Current.Response;

            Response.ClearHeaders();

            Response.AppendHeader("Content-Disposition", "attachment; filename=" + FileName);

            Response.ContentType = FileType;

            Response.Write(content);

            Response.End();

        }

        catch (Exception ex)

        {

        }

    }

}

Summary

In this tip, we have seen how to export data to an Excel file in ASP.NET. It is rather easy, but many new developers struggle with it so I wrote this to help them.