Export Gridview Data to Excel in ASP.NET

Nitin
8y
5.9k
0
0

Blog

ConvertDatatoExcel.zip

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.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Index.aspx.cs" Inherits="ConvertDatatoExcel.Index" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="Grddata" runat="server">
</asp:GridView>
<br />
<asp:Button ID="Btngenerateexcel" runat="server" Text="Generate Excel" OnClick="Btngenerateexcel_Click" />
</div>
</form>
</body>
</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.

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