Hi,
I try below code for expert to excel data from the gride view and it's working find without any error. let me know in case any error on that .
I hope this will help to you.
Code:
Image: Page Design
Image : Sample in action
Image: Export to Excel button is clicked
Image: GridView contents exported to Excel
ExcelExport.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExportExcel.aspx.cs" Inherits="DeleteConfirm" %>
<!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>Contacts Listing</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<strong><span style="font-size: small; font-family: Arial; text-decoration: underline">
Contacts Listing
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Export To Excel" /></span></strong><br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ContactID"
DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." style="font-size: small; font-family: Arial" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Vertical">
<Columns>
<asp:BoundField DataField="ContactID" HeaderText="ContactID" ReadOnly="True" SortExpression="ContactID" Visible="False" />
<asp:BoundField DataField="FName" HeaderText="First Name" SortExpression="FName" />
<asp:BoundField DataField="LName" HeaderText="Last Name" SortExpression="LName" />
<asp:BoundField DataField="ContactPhone" HeaderText="Phone" SortExpression="ContactPhone" />
<asp:TemplateField HeaderText="Favorites">
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" />
</ItemTemplate></asp:TemplateField>
</Columns>
<FooterStyle BackColor="#CCCC99" />
<RowStyle BackColor="#F7F7DE" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ContactsConnectionString1 %>"
DeleteCommand="DELETE FROM [ContactPhone] WHERE [ContactID] = @ContactID" InsertCommand="INSERT INTO [ContactPhone] ([FName], [LName], [ContactPhone]) VALUES (@FName, @LName, @ContactPhone)"
ProviderName="<%$ ConnectionStrings:ContactsConnectionString1.ProviderName %>"
SelectCommand="SELECT [ContactID], [FName], [LName], [ContactPhone] FROM [ContactPhone]"
UpdateCommand="UPDATE [ContactPhone] SET [FName] = @FName, [LName] = @LName, [ContactPhone] = @ContactPhone WHERE [ContactID] = @ContactID">
<InsertParameters>
<asp:Parameter Name="FName" Type="String" />
<asp:Parameter Name="LName" Type="String" />
<asp:Parameter Name="ContactPhone" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="FName" Type="String" />
<asp:Parameter Name="LName" Type="String" />
<asp:Parameter Name="ContactPhone" Type="String" />
<asp:Parameter Name="ContactID" Type="Int32" />
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="ContactID" Type="Int32" />
</DeleteParameters>
</asp:SqlDataSource>
<br />
</div>
</form>
</body>
</html>
ExcelExport.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using System.IO;
public partial class DeleteConfirm : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
//Export the GridView to Excel
PrepareGridViewForExport(GridView1);
ExportGridView();
}
private void ExportGridView()
{
string attachment = "attachment; filename=Contacts.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
// Create a form to contain the grid
HtmlForm frm = new HtmlForm();
GridView1.Parent.Controls.Add(frm);
frm.Attributes["runat"] = "server";
frm.Controls.Add(GridView1);
frm.RenderControl(htw);
//GridView1.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
}
private void PrepareGridViewForExport(Control gv)
{
LinkButton lb = new LinkButton();
Literal l = new Literal();
string name = String.Empty;
for (int i = 0; i < gv.Controls.Count; i++)
{
if (gv.Controls[i].GetType() == typeof(LinkButton))
{
l.Text = (gv.Controls[i] as LinkButton).Text;
gv.Controls.Remove(gv.Controls[i]);
gv.Controls.AddAt(i, l);
}
else if (gv.Controls[i].GetType() == typeof(DropDownList))
{
l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;
gv.Controls.Remove(gv.Controls[i]);
gv.Controls.AddAt(i, l);
}
else if (gv.Controls[i].GetType() == typeof(CheckBox))
{
l.Text = (gv.Controls[i] as CheckBox).Checked ? "True" : "False";
gv.Controls.Remove(gv.Controls[i]);
gv.Controls.AddAt(i, l);
}
if (gv.Controls[i].HasControls())
{
PrepareGridViewForExport(gv.Controls[i]);
}
}
}
}
shailesh kavathiya
