Post
Ask Question

Export Gridview Data to Excel in ASP.NET

Let us create a web application. Open Visual Studio. Add an empty web application. Add a webpage. Name it as per your convenience. I have named it Index.aspx.

Write the following HTML mark up in the aspx page.

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Index.aspx.cs" Inherits="ConvertDatatoExcel.Index" %>  
  2.    
  3. <!DOCTYPE html>  
  4.    
  5. <html xmlns="http://www.w3.org/1999/xhtml">  
  6. <head runat="server">  
  7.     <title></title>  
  8. </head>  
  9. <body>  
  10.     <form id="form1" runat="server">  
  11.         <div>  
  12.             <asp:GridView ID="Grddata" runat="server">  
  13.             </asp:GridView>  
  14.    
  15.             <br />  
  16.             <asp:Button ID="Btngenerateexcel" runat="server" Text="Generate Excel" OnClick="Btngenerateexcel_Click" />  
  17.         </div>  
  18.     </form>  
  19. </body>  
  20. </html>  
We have a gridview and a button to export the gridview data in excel. Let us now bind this gridview with some data. But first we need to add reference of ClosedXML.dll in the application. We will be using this library to perform the operations. Copy the ClosedXML.dll in the bin folder.

folder

Add its reference in the application.

reference

Now we have added the necessary reference in the application. Let us have a look at the code in the .cs file.

Go to the Index.aspx.cs file and write the following code.
  1. using System;  
  2. using System.Data;  
  3. using System.Web.UI;  
  4. using ClosedXML.Excel;  
  5. using System.IO;  
  6.    
  7.    
  8. namespace ConvertDatatoExcel  
  9. {  
  10.     public partial class Index : System.Web.UI.Page  
  11.     {  
  12.         protected void Page_Load(object sender, EventArgs e)  
  13.         {  
  14.             if (!Page.IsPostBack)  
  15.             {  
  16.                 Grddata.DataSource = BindGrid();  
  17.                 Grddata.DataBind();  
  18.             }  
  19.         }  
  20.    
  21.         protected DataTable BindGrid()  
  22.         {  
  23.             DataTable dt = null;  
  24.    
  25.              dt = new DataTable();  
  26.                 dt.Columns.Add("StudentID",typeof(int));  
  27.                 dt.Columns.Add("Name", typeof(string));  
  28.                 dt.Columns.Add("Marks", typeof(int));  
  29.    
  30.                 dt.Rows.Add(1, "Nitin Tyagi", 400);  
  31.                 dt.Rows.Add(2, "John Smith", 300);  
  32.                 dt.Rows.Add(3, "James Smith", 200);  
  33.                 dt.Rows.Add(4, "John Doe", 400);  
  34.               
  35.          return dt;  
  36.               
  37.           }  
  38.    
  39.         protected void Btngenerateexcel_Click(object sender, EventArgs e)  
  40.         {  
  41.             DataTable dt = null;  
  42.             try  
  43.             {  
  44.                  dt = BindGrid();  
  45.                  
  46.                      using (XLWorkbook wb = new XLWorkbook())  
  47.                      {  
  48.                          wb.Worksheets.Add(dt, "Students");  
  49.    
  50.                          Response.Clear();  
  51.                          Response.Buffer = true;  
  52.                          Response.Charset = "";  
  53.                          Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";  
  54.                          Response.AddHeader("content-disposition", "attachment;filename=Download.xlsx");  
  55.                          using (MemoryStream MyMemoryStream = new MemoryStream())  
  56.                          {  
  57.                              wb.SaveAs(MyMemoryStream);  
  58.                              MyMemoryStream.WriteTo(Response.OutputStream);  
  59.                              Response.Flush();  
  60.                              Response.End();  
  61.                          }  
  62.                        
  63.                  }  
  64.    
  65.             }  
  66.             catch (Exception Ex)  
  67.             {  
  68.             }  
  69.             finally  
  70.             {  
  71.                 dt = null;  
  72.             }  
  73.         }  
  74.     } 
We will run the application now. In case you get the below error while clicking on Export button ... 

error

... Then in that case we need to add DocumentFormat.openXML package to our application. To do this go to Visual Studio->Tools->Library Package manager-> Package Manager Console.

Tools

Type the following command and hit enter.

Install-Package DocumentFormat.OpenXml

Tools

The necessary packages will be added in the application and the application will run without error now. Browse the webpage.

webpage
Click on Generate Excel button. We get the following output in Excel file.

Excel

We have successfully exported gridview to Excel in Asp.Net.

 
Next Recommended Reading Export GridView Data to Excel, Word, Pdf, Text and Csv Format and Print in Asp.Net