Export Gridview Data to Excel in ASP.NET

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.

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.

using System;
using System.Data;
using System.Web.UI;
using ClosedXML.Excel;
using System.IO;
namespace ConvertDatatoExcel
{
public partial class Index : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
Grddata.DataSource = BindGrid();
Grddata.DataBind();
}
}
protected DataTable BindGrid()
{
DataTable dt = null;
dt = new DataTable();
dt.Columns.Add("StudentID",typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Marks", typeof(int));
dt.Rows.Add(1, "Nitin Tyagi", 400);
dt.Rows.Add(2, "John Smith", 300);
dt.Rows.Add(3, "James Smith", 200);
dt.Rows.Add(4, "John Doe", 400);
return dt;
}
protected void Btngenerateexcel_Click(object sender, EventArgs e)
{
DataTable dt = null;
try
{
dt = BindGrid();
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt, "Students");
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=Download.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
catch (Exception Ex)
{
}
finally
{
dt = null;
}
}
}

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.