how to export data from gridview to Excel in asp.net using c#

Advertisement
how to export data from gridview to Excel in asp.net using c#

I will explain how to export data from gridview to Excel in asp.net using c#.

I created a database with table name EmployeeInfo as shown.

USE [chinnu]
GO

/****** Object: Table [dbo].[EmployeeInfo] Script Date: 10/29/2013 00:13:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[EmployeeInfo](
[EmpId] [nvarchar](50) NOT NULL,
[EmpName] [nvarchar](50) NOT NULL,
[EmpAddress] [nvarchar](50) NOT NULL,
[EmpDesignation] [nvarchar](50) NOT NULL
) ON [PRIMARY]

GO

In Default.aspx I am adding GridView and ImageButton as Shown.


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="file_conversion._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">
<asp:GridView ID="gridview" runat="server" AutoGenerateColumns="False" >
<Columns>
<asp:BoundField DataField="EmpId" HeaderText="EmployeeId" InsertVisible="False"
/>
<asp:BoundField DataField="EmpName" HeaderText="EmployeeName" InsertVisible="False"
/>
<asp:BoundField DataField="EmpAddress" HeaderText="EmployeeAddress" />
<asp:BoundField DataField="EmpDesignation" HeaderText="EmployeeDesignation" />
</Columns>
</asp:GridView>
<asp:ImageButton ID="ImageButton1" runat="server" ToolTip ="Click To Export Excel Format" ImageUrl="Scripts/download (1).jpg" Width="32px" Height="32px"
OnClick="btnexcel_Click" />

</form>
</body>
</html>

Add iTextSharp DLL.
Now I am writing the logic in Default.aspx.cs to export gridview data in to Excel format as shown.

using System;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html.simpleparser;
using System.Data.SqlClient;


namespace file_conversion
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridData();
}
}
private void BindGridData()
{
try
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Connstring"].ConnectionString))
{
SqlCommand command = new SqlCommand("SELECT * from EmployeeInfo", con);
SqlDataAdapter da = new SqlDataAdapter(command);
DataTable dt = new DataTable();
da.Fill(dt);
gridview.DataSource = dt;
gridview.DataBind();
}
}
catch (Exception ex)
{
}

}
protected void btnexcel_Click(object sender, ImageClickEventArgs e)
{
string attachment = "attachment; filename=ExportData.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
HtmlForm frm = new HtmlForm();
gridview.Parent.Controls.Add(frm);
frm.Attributes["runat"] = "server";
frm.Controls.Add(gridview);
frm.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}

}

}

In web.config I am adding the ConnectionString

<connectionStrings>
<add name="Connstring" connectionString="Data Source=DotNetSharePoint;Database=Chinnu;User Id=sa;Password=password123;" providerName="System.Data.SqlClient" />
</connectionStrings>


Now Press F5.

OutPut:



Advertisements
SHARE

Lakshmi Tulasi Jasti

  • Image
  • Image
  • Image
  • Image
  • Image
    Blogger Comment
    Facebook Comment

0 comments:

Post a comment