In this article I’m going to
explain how to export data from DataTable to Excel in ASP.NET using C#.
If you want to export GridView to Excel, word and PDF document please refer these articles
Export GridView data to excel or word document using ASP.NET and c#
Export GridView data to PDF document in ASP.NET using iTextSharp
Sometime we need to export data from
DataTable to excel. Here I’ll show you how to export data to Excel by using
RkLib ExportData.
RKLib ExportData is a class library used to export data to excel. So here we have to
create table and fetch records to DataTable and we can export to excel.
First we have to download RKLib ExportData is a class library. Download link is given below.
Then open your project and include the dll class library
Browse RKLib class library then click ok.
Next we have to create a table like this
Table Design:
|
Column Name
|
Data Type
|
|
empid
|
varchar(50)
|
|
name
|
varchar(100)
|
|
designation
|
varchar(100)
|
|
city
|
varchar(50)
|
|
country
|
varchar(50)
|
Here we have to create page like this.
Designer Source Code:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD
XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ImageButton ID="imgbtnExcel" runat="server" ImageUrl="~/Images/excel_icon.png"
OnClick="imgbtnExcel_Click" />
<asp:GridView ID="gvDetails" runat="server" AutoGenerateColumns="false" ShowFooter="true"
Width="100%">
<AlternatingRowStyle BackColor="#AED6FF" />
<Columns>
<asp:BoundField DataField="empid" HeaderText="Employee-ID" />
<asp:BoundField DataField="name" HeaderText="Name" />
<asp:BoundField DataField="designation" HeaderText="Designation" />
<asp:BoundField DataField="city" HeaderText="City" />
<asp:BoundField DataField="country" HeaderText="Country" />
</Columns>
<HeaderStyle BackColor="#0063A6" ForeColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>
Note:
You have to use following Namespace
using RKLib.ExportData;
Code Behind:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using RKLib.ExportData;
public partial class _Default : System.Web.UI.Page
{
SqlConnection conn = new SqlConnection("Data Source=SPIDER;Initial
Catalog=Demo; Integrated Security=True");
DataSet ds = new DataSet();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
protected void BindGrid()
{
conn.Open();
string cmdstr = "Select * from EmployeeDetails";
SqlCommand cmd = new SqlCommand(cmdstr, conn);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(ds);
cmd.ExecuteNonQuery();
gvDetails.DataSource = ds;
gvDetails.DataBind();
conn.Close();
}
protected void imgbtnExcel_Click(object sender, ImageClickEventArgs e)
{
conn.Open();
string cmdstr = "Select * from EmployeeDetails";
SqlCommand cmd = new SqlCommand(cmdstr, conn);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(ds);
DataTable dtcaste = ds.Tables[0];
int[] iColumns = { 0, 1, 2, 3, 4 };
string[] sHeaders =
{
"Employee-ID"
,"Name"
,"Designation"
,"City"
,"Country"
};
// Export the details of specified columns with specified headers to CSV
RKLib.ExportData.Export objExport
= new RKLib.ExportData.Export();
objExport.ExportDetails(dtcaste, iColumns, sHeaders, Export.ExportFormat.CSV, "file.csv");
}
}