January 3, 2015 by Nitesh

Export Data From GridView to Excel 2007/2010 in ASP.Net

Friends,

In this post, we will see how can we export data from GridView to Excel 2007/2010 format in ASP.Net in less than 5 minutes. Without much delay, let’s get started –

  • Add EPPlus package using Nuget.export-gridview-excel-1
  • Include OfficeOpenXml and OfficeOpenXml.Table namespaces at the top.
  • In your event handler, write the below code.
  • You’re done.

VB.Net Code:

       Response.Clear()
       Response.Charset = ""
       Response.ContentEncoding = System.Text.Encoding.UTF8
       Response.Cache.SetCacheability(HttpCacheability.NoCache)
       Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
       Response.AddHeader("content-disposition", "attachment;filename=GridData.xlsx")

       Dim dt As DataTable = CType(gridView.DataSource, DataTable)
       Using pck As New ExcelPackage()

          Dim wsDt As ExcelWorksheet = pck.Workbook.Worksheets.Add("Sheet1")
          wsDt.Cells("A1").LoadFromDataTable(dt, True, TableStyles.None)
          wsDt.Cells(wsDt.Dimension.Address).AutoFitColumns()

          Response.BinaryWrite(pck.GetAsByteArray())
       End Using

       Response.Flush()
       Response.End()

C# Code:

       Response.Clear();
       Response.Charset = "";
       Response.ContentEncoding = System.Text.Encoding.UTF8;
       Response.Cache.SetCacheability(HttpCacheability.NoCache);
       Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
       Response.AddHeader("content-disposition", "attachment;filename=GridData.xlsx");

       DataTable dt= gridView.DataSource as DataTable;
       using(ExcelPackage pck As New ExcelPackage())
       {
         ExcelWorksheet wsDt = pck.Workbook.Worksheets.Add("Sheet1");
         wsDt.Cells("A1").LoadFromDataTable(dt, True, TableStyles.None);
         wsDt.Cells(wsDt.Dimension.Address).AutoFitColumns();

         Response.BinaryWrite(pck.GetAsByteArray());
       }

       Response.Flush();
       Response.End();

Explanation:

In the above code, what we are doing is first of all, clearing the Response and setting the ContentType and Header for the Response object. This is to be noted that “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet” is the type to be set for Excel 2007. Once these are set, we are getting the Data Source of the GridView in a DataTable. Then, we use an object of ExcelPackage class to create a worksheet and load the DataTable into the worksheet from Cell A1. Calling the AutoFitColumns()  ensures that the Excel file written is auto set to fit the column contents. Once the data is written and the excel file is created in memory, we write the same back to the browser. This allows users to either open the Excel file or save it their hard disks.

Hope you like this post! Keep learning & sharing! Cheers!

#ASP.Net#C##GridView#VB.Net
  • Tejonidhi

    Thaks a lot !!! it is working 🙂

  • shahid majeed

    I have tried your code, but its generate the html of the page where the gridview is used.
    Its not the actual xls file. I get the warning message from excel before opening the file.

    here is my code
    protected void btnDownload_Click(object sender, ImageClickEventArgs e)
    {
    try
    {
    if (GridView1.Rows.Count > 0)
    exportToExcel.makeExcel(GridView1, “roomList.xls”);
    }
    catch (Exception exc)
    {
    }
    }

    using System;
    using System.IO;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using OfficeOpenXml;
    using log4net;
    using OfficeOpenXml.Table;

    public class exportToExcel
    {
    private static readonly ILog log = LogManager.GetLogger(“Conference”);
    public exportToExcel()
    {

    }
    public static void makeExcel(GridView gv, string fileName)
    {
    try
    {
    System.Web.HttpContext.Current.Response.Clear();
    System.Web.HttpContext.Current.Response.Charset = “”;
    System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
    System.Web.HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
    System.Web.HttpContext.Current.Response.ContentType = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”;
    System.Web.HttpContext.Current.Response.AddHeader(“content-disposition”, “attachment;filename=”+fileName);

    DataTable dt= gv.DataSource as DataTable;
    using(ExcelPackage pck = new ExcelPackage())
    {
    ExcelWorksheet wsDt = pck.Workbook.Worksheets.Add(“Sheet1”);
    wsDt.Cells[“A1”].LoadFromDataTable(dt, true, TableStyles.None);
    wsDt.Cells[wsDt.Dimension.Address].AutoFitColumns();
    System.Web.HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray());
    }
    System.Web.HttpContext.Current.Response.Flush();
    System.Web.HttpContext.Current.Response.End();
    }
    catch (Exception exc)
    {
    log.Error(“Exception inexportToExcel makeExcel: “+exc.Message);
    log.Error(“Exception inexportToExcel makeExcel: ” + exc.StackTrace);
    throw new Exception(exc.Message);
    }
    }
    }

Support us!

If you like this site please help and make click on any of these buttons!

Powered by WordPress Popup