ASP.NET Export GridView to Excel

Post Views: 580

In this post, I’ll share a simple class which you can use to export your ASP.NET GridView to Excel Sheet file. You have to pass your ASP.NET GridView control name (Id of GridView) and file name with which you wish to export the excel sheet file.

ASP.NET Export GridView to Excel class :


public void GridViewToExcelSheet(GridView dataGrid, String fileName)
{
try
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename="+fileName+System.DateTime.Now.ToString()+".xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
using (StringWriter stringWriter = new StringWriter())
{
HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);
dataGrid.RenderControl(htmlTextWriter);
Response.Output.Write(stringWriter.ToString());
Response.Flush();
Response.End();
}
}
catch (Exception ex)
{
String error = ex.Message.ToString();
}
}

public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that all the controls is rendered */
}

My GridView :

ASP.NET Export GridView to Excel – GridView with Data.

Exported Excel Sheet of GridView :

ASP.NET Export GridView to Excel – Exported Excel Sheet

Usage :

Let’s see how we can use the above class in our .aspx.cs file.

My Design page – .ASPX Page :


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridViewToExcel.aspx.cs" Inherits="GridViewToExcel" %>

<!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 runat="server" ID="myDataGrid" BackColor="White" BorderColor="#999999"
BorderStyle="None" BorderWidth="1px" CellPadding="5" GridLines="Vertical">
<AlternatingRowStyle BackColor="#DCDCDC" />
<FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
<HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
<RowStyle BackColor="#EEEEEE" ForeColor="Black" />
<SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#0000A9" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#000065" />
</asp:GridView>

<br />
<br />
<asp:Button runat="server" Text="Export" ID="btnExport" OnClick="btnExport_Click" />
</div>
</form>
</body>
</html>

Code file – .ASPX.CS page (File name : GridViewToExcel.aspx.cs)


using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Diagnostics;
using System.Threading;
using System.Drawing;

public partial class GridViewToExcel : System.Web.UI.Page
{
static String connectionString = "Data Source=192.168.0.192;Initial Catalog=ParallelCodes;User ID=sa;Password=789";
SqlConnection con;
SqlCommand cmd;
static DataTable dt;
SqlDataAdapter sqlAdapter;

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
FillGrid();
}

public void FillGrid()
{
con = new SqlConnection(connectionString);
cmd = new SqlCommand("Select Id,ProName as [Product], ProDesc as [Description] from Producttbl", con);
sqlAdapter = new SqlDataAdapter(cmd);
dt = new DataTable();
sqlAdapter.Fill(dt);
myDataGrid.DataSource = dt;
myDataGrid.DataBind();
con.Close();
}
public void GridViewToExcelSheet(GridView dataGrid, String fileName)
{
try
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename="+fileName+System.DateTime.Now.ToString()+".xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
using (StringWriter stringWriter = new StringWriter())
{
HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);
dataGrid.RenderControl(htmlTextWriter);
Response.Output.Write(stringWriter.ToString());
Response.Flush();
Response.End();
}
}
catch (Exception ex)
{
String error = ex.Message.ToString();
}
}

public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that all the controls is rendered */
}

protected void btnExport_Click(object sender, EventArgs e)
{
GridViewToExcelSheet(myDataGrid, "myGridExcelFile");
}

}

Output :

ASP.NET Export GridView to Excel – Exported Excel Sheet

Also see :

Gridview with updatepanel in Asp.net C#

Gridview with delete button in asp.net

Gridview checkbox in Asp.net c#

GridView with Buttons ASP.NET C#

STYLING THE GRIDVIEW IN ASP.NET

How to fill data in gridview in asp net

ASP.NET GridView Export to CSV file