Export DataTable to Excel in asp.net C#
In this article, we will learn how to export excel sheet from grid view using DataTable in asp.net c#, here we bind the dynamically grid view using Datatable. If you want to get textbox values and save into database then you can see how to save dynamically data in DataTable in asp.net C#.
SoureCode:-
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="exportexcelsheet.aspx.cs" Inherits="exportexcelsheet" %>
<!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="GridView1" runat="server" >
</asp:GridView>
<br />
<asp:Button ID="Button1" runat="server" Text="ExporttoExcel" OnClick="Button1_Click" />
</div>
</form>
</body>
</html>
Code behind:-
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class exportexcelsheet : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridviewData();
}
}
DataTable dt = new DataTable();
protected void BindGridviewData()
{
dt.Columns.Add("Id", typeof(Int32));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Male", typeof(string));
dt.Columns.Add("Location", typeof(string));
DataRow dtrow = dt.NewRow(); // Create New Row
dtrow["Id"] = 1; //Bind Data to Columns
dtrow["Name"] = "Neeraj Srivastava";
dtrow["Male"] = "Male";
dtrow["Location"] = "Hyderabad";
dt.Rows.Add(dtrow);
dtrow = dt.NewRow(); // Create New Row
dtrow["Id"] = 2; //Bind Data to Columns
dtrow["Name"] = "Dheeraj Srivastava";
dtrow["Male"] = "Male";
dtrow["Location"] = "Lucknow";
dt.Rows.Add(dtrow);
dtrow = dt.NewRow(); // Create New Row
dtrow["Id"] = 3; //Bind Data to Columns
dtrow["Name"] = "Lokdendra Bundela";
dtrow["Male"] = "Male";
dtrow["Location"] = "Luckbow";
dt.Rows.Add(dtrow);
GridView1.DataSource = dt;
GridView1.DataBind();
}
private void ExporttoExcel(DataTable table)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=Reports.xls");
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
HttpContext.Current.Response.Write("<font style='font-size:15.0pt; font-family:Times New Roman;'>");
HttpContext.Current.Response.Write("<BR><BR><BR>");
HttpContext.Current.Response.Write("<Table border='1' bgColor='#ffffff' " +
"borderColor='#000000' cellSpacing='0' cellPadding='0' " +
"style='font-size:12.0pt; font-family:Times New Roman; background:white;'> <TR>");
int columnscount = GridView1.Columns.Count;
HttpContext.Current.Response.Write("<Table border='1' bgColor='#FFFFFF' " +
"borderColor='#000000' cellSpacing='0' cellPadding='0' " +
"style='font-size:12.0pt; font-family:Times New Roman; background:white;'>");
HttpContext.Current.Response.Write("<TR valign='top' style='background:#D8D8D8;'>");
HttpContext.Current.Response.Write("<TD >ID </TD>");
HttpContext.Current.Response.Write("<TD>Name</TD>");
HttpContext.Current.Response.Write("<TD>Gender</TD>");
HttpContext.Current.Response.Write("<TD>Location</TD>");
HttpContext.Current.Response.Write("</TR>");
foreach (DataRow row in table.Rows)
{
//write in new row
HttpContext.Current.Response.Write("<TR>");
for (int i = 0; i < table.Columns.Count; i++)
{
HttpContext.Current.Response.Write("<Td>");
HttpContext.Current.Response.Write(row[i].ToString());
HttpContext.Current.Response.Write("</Td>");
}
HttpContext.Current.Response.Write("</TR>");
}
HttpContext.Current.Response.Write("</Table>");
HttpContext.Current.Response.Write("</font>");
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
protected void Button1_Click(object sender, EventArgs e)
{
BindGridviewData();
ExporttoExcel(dt);
}
}
Out-Put-
Download:-
Export DataTable to Excel in asp.net C#
Reviewed by NEERAJ SRIVASTAVA
on
4:32:00 PM
Rating:
Reviewed by NEERAJ SRIVASTAVA
on
4:32:00 PM
Rating:
No comments: