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.
• 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!