How to bind and Export GridView data to Ms Excel file using asp.net C#,VB.Net
Introduction: In previous articles i explained How to bind and Export GridView data to Ms Word file and and Bind and Export GridView data to PDF file in asp.net and Bind and Export GridView data to CSV file in asp.net and How to Get city, state and country based on zip code using Google map API and How to enable JavaScript in asp.net and JavaScript validation in asp.net website and Ajax AutoCompleteExtender control example without using web service and Get Title,Description and Keywords Meta tags from URL and How to use RangeValidator validation control with example.
In this article I will explain with example How to Bind GridView and Export Gridview data to Ms Excel file using asp.net with C# and VB.Net.
Implementation: Let's create an asp.net website demo to see it in action.
Note: EMP_ID column is set to Primary key and Identity specification is set to yes with Identity increment and Identity seed equal to 1. Insert some data in this table that you want to show in the Gridview.
Note: Replace the Data Source and Initial Catalog(i.e. Database Name) as per your application.
First include the following namespaces
In this article I will explain with example How to Bind GridView and Export Gridview data to Ms Excel file using asp.net with C# and VB.Net.
click on image to enlarge |
- Create a Database e.g. "MyDataBase" and a table under that DataBase in Sql Server and name it "EMPLOYEE" as shown in figure:
- Now in web.config file add the connection string under <configuration> tag
<connectionStrings>
<add name="conStr" connectionString="Data Source=LocalServer;Initial
Catalog=MyDataBase;Integrated Security=True"/>
</connectionStrings> Note: Replace the Data Source and Initial Catalog(i.e. Database Name) as per your application.
- Source Code:
- In the design page (.aspx) place a GridView control to bind data and a Button control to Export the GridView data to MS Excel file.
<fieldset style="width:360px;">
<legend>Bind
and Export GridView data to Ms Excel in asp.net</legend>
<table>
<tr>
<td>
<asp:GridView ID="grEmp" runat="server" AllowPaging="True" AutoGenerateColumns="False"
GridLines="None" Width="100%" CellPadding="4" ForeColor="#333333">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:BoundField DataField="EMP_NAME" HeaderText="Emp
Name" />
<asp:BoundField DataField="DEPT" HeaderText="Department" />
<asp:BoundField DataField="SALARY" HeaderText="salary" />
<asp:BoundField DataField="EMAIL_ID" HeaderText="Email
Id" />
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
</td>
</tr>
<tr>
<td>
<asp:Button ID="btnExportToExcel" runat="server" Text="Export
To MS Excel File" OnClick="btnExportToExcel_Click" />
</td>
</tr>
</table>
</fieldset>
C#.Net Code to Bind and Export
GridView data to Ms Excel file
First include the following
namespaces
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Net;
using System.Net.Mail;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Text;
Then write the code as:
SqlConnection
con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindEmpGrid();
}
}
public override void VerifyRenderingInServerForm(Control
control)
{
//It solves the error "Control 'GridView1' of type
'GridView' must be placed inside a form tag with runat=server."
}
protected void BindEmpGrid()
{
SqlCommand
cmd = new SqlCommand("select * from EMPLOYEE",
con);
DataTable dt = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
grEmp.DataSource = dt;
grEmp.DataBind();
}
protected void
btnExportToExcel_Click(object sender, EventArgs e)
{
try
{
Response.ClearContent();
string attachment = "attachment;
filename=MyExelFile.xls";
Response.ClearContent();
Response.AddHeader("content-disposition",
attachment);
Response.ContentType = "application/ms-excel";
StringWriter
strWrite = new StringWriter();
HtmlTextWriter
htmWrite = new HtmlTextWriter(strWrite);
HtmlForm
htmfrm = new HtmlForm();
grEmp.Parent.Controls.Add(htmfrm);
htmfrm.Attributes["runat"] =
"server";
htmfrm.Controls.Add(grEmp);
htmfrm.RenderControl(htmWrite);
Response.Write(strWrite.ToString());
Response.Flush();
Response.End();
}
catch (Exception ex){}
}
VB.Net Code to Bind and Export
GridView data to Ms Excel file
Design the form as shown above but just replace the line <asp:Button ID="btnExportToExcel" runat="server" Text="Export To MS Excel
File" OnClick="btnExportToExcel_Click" /> with the <asp:Button ID="btnExportToExcel" runat="server" Text="Export To MS Excel
File" />
Then in the code behind file(.aspx.vb) wrtie the code as:
First include the following namespaces
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Net
Imports System.Net.Mail
Imports System.Web.UI.HtmlControls
Imports System.IO
Imports System.Text
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
BindEmpGrid()
End If
End Sub
Public Overrides Sub VerifyRenderingInServerForm(control As Control)
'It solves the error "Control 'GridView1' of type
'GridView' must be placed inside a form tag with runat=server."
End Sub
Protected Sub BindEmpGrid()
Dim cmd As New SqlCommand("select * from EMPLOYEE",
con)
Dim dt As New DataTable()
Dim adp As New SqlDataAdapter(cmd)
adp.Fill(dt)
grEmp.DataSource = dt
grEmp.DataBind()
End Sub
Protected Sub
btnExportTExcel_Click(sender As Object, e As EventArgs) Handles btnExportTExcel.Click
Try
Response.ClearContent()
Dim attachment As String = "attachment;
filename=MyExelFile.xls"
Response.ClearContent()
Response.AddHeader("content-disposition",
attachment)
Response.ContentType = "application/ms-excel"
Dim strWrite As New
StringWriter()
Dim htmWrite As New HtmlTextWriter(strWrite)
Dim htmfrm As New HtmlForm()
grEmp.Parent.Controls.Add(htmfrm)
htmfrm.Attributes("runat") =
"server"
htmfrm.Controls.Add(grEmp)
htmfrm.RenderControl(htmWrite)
Response.Write(strWrite.ToString())
Response.Flush()
Response.[End]()
Catch ex As Exception
End Try
- Notice that I have added an overriding function VerifyRenderingInServerForm in the code behind. This is to resolve the error “Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server” that may occur while exporting GridView data to MS Excel file or MS Word file or PDF or CSV (Comma separated value) file.
Note: To view complete article on
why this error occur and how to resolve that error, read my article “How to Solve Error Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server”
- After exporting, gridview data to Ms Excel file, will look as shown in figure:
Click on image to enlarge |
Now over to you:
"If you like my work; you can appreciate by leaving your comments,
hitting Facebook like button, following on Google+, Twitter, Linked in and
Pinterest, stumbling my posts on stumble upon and subscribing for receiving
free updates directly to your inbox . Stay tuned and stay connected for more
technical updates."
No comments:
Post a Comment