Post
Ask Question

Bind Gridview And Export Gridview Data To Excel Using ASP.NET C#

Introduction

 
In this article, I would like to share a utility that can be used to export Gridview data to an Excel file using ASP.NET C#. In this article, we will learn how to export Gridview data to Excel using ClosedXML.
 
Below are the steps to bind Gridview and export Gridview data to excel,
 
Step 1
 
Create a new website.
 
Step 2
 
Create a webform aspx page.
 
Step 3
 
Add reference of AjaxToolKit as attached in source code.
 
Step 4
 
Add reference of ClosedXML using NuGet package or download DLL from the internet.
 
We need to add a reference for ClosedXML as like below,
 
Bind Gridview and Export Gridview Data To Excel Using ASP.Net C#
 
Step 5
 
Design the webpage like below,
  1. <!DOCTYPE html>  
  2. <html  
  3.     xmlns="http://www.w3.org/1999/xhtml">  
  4.     <head runat="server">  
  5.         <title></title>  
  6.     </head>  
  7.     <body>  
  8.         <form id="form1" runat="server">  
  9.             <div>  
  10.                 <asp:GridView ID="grdview" runat="server" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3">  
  11.                     <FooterStyle BackColor="White" ForeColor="#000066" />  
  12.                     <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />  
  13.                     <PagerStyle BackColor="White" ForeColor="#000066" Horizontal />  
  14.                     <RowStyle ForeColor="#000066" />  
  15.                     <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />  
  16.                     <SortedAscendingCellStyle BackColor="#F1F1F1" />  
  17.                     <SortedAscendingHeaderStyle BackColor="#007DBB" />  
  18.                     <SortedDescendingCellStyle BackColor="#CAC9C9" />  
  19.                     <SortedDescendingHeaderStyle BackColor="#00547E" />  
  20.                 </asp:GridView>  
  21.                 <asp:Button ID="btnExport" runat="server" Text="Export to Excel" OnClick="btnExport_Click"/>  
  22.             </div>  
  23.         </form>  
  24.     </body>  
  25. </html>  
Step 6
 
Add below namespace,
  1. using ClosedXML.Excel;  
  2. using System.Configuration;  
  3. using System.Data.Common;  
  4. using System.Data.OleDb;  
Step 7
 
Add the below source code .aspx.cs page.
 
Create datatable with 6 columns and 8 Rows as below,
  1. static DataTable GetTable() {  
  2.     //  
  3.     // Here we create a DataTable with 6 columns.  
  4.     //  
  5.     DataTable table = new DataTable();  
  6.     table.Columns.Add("EmpID", typeof(int));  
  7.     table.Columns.Add("EmpName", typeof(string));  
  8.     table.Columns.Add("Address", typeof(string));  
  9.     table.Columns.Add("EmpCity", typeof(string));  
  10.     table.Columns.Add("EmpSex", typeof(string));  
  11.     table.Columns.Add("EmpJoiningdate", typeof(string));  
  12.     //  
  13.     // Here we added 8 DataRows.  
  14.     //  
  15.     table.Rows.Add(10, "Ajit Kumar", "XYZ", "Mumbai", "M", DateTime.Now.ToString("yyyy-MM-dd"));  
  16.     table.Rows.Add(30, "Ajay Kumar", "XYZ", "Gwalior", "M", DateTime.Now.ToString("yyyy-MM-dd"));  
  17.     table.Rows.Add(40, "Arbind Kumar", "XYZ", "Gwalior", "M", DateTime.Now.ToString("yyyy-MM-dd"));  
  18.     table.Rows.Add(50, "Anil Kumar", "XYZ", "Gwalior", "M", DateTime.Now.ToString("yyyy-MM-dd"));  
  19.     table.Rows.Add(70, "Ashok kumar", "XYZ", "Asansol", "M", DateTime.Now.ToString("yyyy-MM-dd"));  
  20.     table.Rows.Add(80, "Rina Kumar", "XYZ", "Patna", "F", DateTime.Now.ToString("yyyy-MM-dd"));  
  21.     table.Rows.Add(20, "Dhiraj Kumar", "XYZ", "Noida", "M", DateTime.Now.ToString("yyyy-MM-dd"));  
  22.     table.Rows.Add(90, "Neema Kumari", "XYZ", "Delhi", "F", DateTime.Now.ToString("yyyy-MM-dd"));  
  23.     return table;  
  24. }  
Create funcation to bind datatable to gridview,
  1. public void BindGrid() {  
  2.     DataTable Dt = GetTable();  
  3.     ViewState["Data"] = Dt;  
  4.     grdview.DataSource = Dt;  
  5.     grdview.DataBind();  
  6. }  
Call Bindgrid function on page load to display gridview with data on page load,
  1. protected void Page_Load(object sender, EventArgs e)  
  2. {  
  3.    BindGrid();  
  4. }  
Create funcation Exportgridtoexcel to export grid data into excel,
  1. private void ExportGridToExcel() {  
  2.     try {  
  3.         DataTable dt = (DataTable) ViewState["Data"]; //your datatable  
  4.         dt.TableName = "EmpDetails";  
  5.         using(XLWorkbook wb = new XLWorkbook()) {  
  6.             wb.Worksheets.Add(dt);  
  7.             Response.Clear();  
  8.             Response.Buffer = true;  
  9.             Response.Charset = "";  
  10.             Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";  
  11.             Response.AddHeader("content-disposition", "attachment;filename=EmployeeDetails.xlsx");  
  12.             using(MemoryStream MyMemoryStream = new MemoryStream()) {  
  13.                 wb.SaveAs(MyMemoryStream);  
  14.                 MyMemoryStream.WriteTo(Response.OutputStream);  
  15.                 Response.Flush();  
  16.                 dt.Dispose();  
  17.                 Response.End();  
  18.             }  
  19.         }  
  20.     } catch (Exception ex) {}  
  21. }  
Call ExportGridToExcel() function on button click,
  1. protected void btnExport_Click(object sender, EventArgs e)    
  2. {    
  3.    Export GridToExcel();    
  4. }    
Add below code to rendering form,
  1. public override void VerifyRenderingInServerForm(Control control)  
  2. {  
  3. }   
Step 8
 
Build and run the application,
 
Bind Gridview and Export Gridview Data To Excel Using ASP.Net C#
 
Step 9
 
After clicking on Export to Excel, we will have Excel file as per the following screen downloaded and sample data of Excel as shown below,
 
Bind Gridview and Export Gridview Data To Excel Using ASP.Net C#
 
Bind Gridview and Export Gridview Data To Excel Using ASP.Net C#
 

Summary

 
In this article, we learned how to bind Gridview through datatable using ASP.NET C# and Export Gridview data to Excel using ClosedXML in ASP.NET C#.


Similar Articles