In this example i'm explaining how to Create Or Export GridView to Excel In Asp.Net 2.0,3.5 using C# and VB.NET. Place one button on the page for exporting data to ms excel file.
I have used Northwind database to populate GridView.
I have also explained how we can Create PDF From Gridview in one of my previous articles.
But when we try to execute this code on we get this httpexception error.
to get past this error we can write this method in code behind.
or we can add a html form and render it after adding gridview in it, i'll be using this.
If paging is enabled or Gridview contains controls like linkbutton, DropDownLists or checkboxes etc then we get this error.
we can fix this error by setting event validation property to false in page directive.
Hyperlinks or other controls in gridview are not desireable in excel sheet, we should display their display text instead, for this write a method to remove controls and display their respective text property as mentioned below.
HTML SOURCE
C# CODE
VB.NET
This is how exported excel sheet will look like.
Hope this helps.
I have also explained how we can Create PDF From Gridview in one of my previous articles.
Write following code in Click Event of button
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=GridViewToExcel.xls");
Response.ContentType = "application/excel";
StringWriter sWriter = new StringWriter();
HtmlTextWriter hTextWriter = new HtmlTextWriter(sWriter);
GridView1.RenderControl(hTextWriter);
Response.Write(sWriter.ToString());
Response.End();
to get past this error we can write this method in code behind.
public override void VerifyRenderingInServerForm(Control control)
{
}
or we can add a html form and render it after adding gridview in it, i'll be using this.
we can fix this error by setting event validation property to false in page directive.
<%@ Page Language="C#" AutoEventWireup="true" EnableEventValidation="false" CodeFile="Default.aspx.cs" Inherits="_Default" %>
Hyperlinks or other controls in gridview are not desireable in excel sheet, we should display their display text instead, for this write a method to remove controls and display their respective text property as mentioned below.
private void ChangeControlsToValue(Control gridView)
{
Literal literal = new Literal();
for (int i = 0; i < gridView.Controls.Count; i++)
{
if (gridView.Controls[i].GetType() == typeof(LinkButton))
{
literal.Text = (gridView.Controls[i] as LinkButton).Text;
gridView.Controls.Remove(gridView.Controls[i]);
gridView.Controls.AddAt(i,literal);
}
else if (gridView.Controls[i].GetType() == typeof(DropDownList))
{
literal.Text = (gridView.Controls[i] as DropDownList).SelectedItem.Text;
gridView.Controls.Remove(gridView.Controls[i]);
gridView.Controls.AddAt(i,literal);
}
else if (gridView.Controls[i].GetType() == typeof(CheckBox))
{
literal.Text = (gridView.Controls[i] as CheckBox).Checked ? "True" : "False";
gridView.Controls.Remove(gridView.Controls[i]);
gridView.Controls.AddAt(i,literal);
}
if (gridView.Controls[i].HasControls())
{
ChangeControlsToValue(gridView.Controls[i]);
}
}
}
HTML SOURCE
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" DataSourceID="sqlDataSourceGridView" AutoGenerateColumns="False" CssClass="GridViewStyle" GridLines="None" Width="650px" ShowHeader="False"> <Columns> <asp:TemplateField HeaderText="Customer ID" ItemStyle-Width="75px"> <ItemTemplate> <asp:LinkButton ID="lButton" runat="server" Text='<%#Eval("CustomerID") %>' PostBackUrl="~/Default.aspx"> </asp:LinkButton> </ItemTemplate> <ItemStyle Width="75px"></ItemStyle> </asp:TemplateField> <asp:BoundField DataField="CompanyName" HeaderText="Company" ItemStyle-Width="200px" > <ItemStyle Width="200px"></ItemStyle> </asp:BoundField> <asp:BoundField DataField="ContactName" HeaderText="Name" ItemStyle-Width="125px"> <ItemStyle Width="125px"></ItemStyle> </asp:BoundField> <asp:BoundField DataField="City" HeaderText="city" ItemStyle-Width="125px" > <ItemStyle Width="125px"></ItemStyle> </asp:BoundField> <asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="125px" > <ItemStyle Width="125px"></ItemStyle> </asp:BoundField> </Columns> <RowStyle CssClass="RowStyle" /> <PagerStyle CssClass="PagerStyle" /> <SelectedRowStyle CssClass="SelectedRowStyle" /> <HeaderStyle CssClass="HeaderStyle" /> <AlternatingRowStyle CssClass="AltRowStyle" /> </asp:GridView> <asp:SqlDataSource ID="sqlDataSourceGridView" runat="server" ConnectionString="<%$ ConnectionStrings:northWindConnectionString %>" SelectCommand="SELECT [CustomerID], [CompanyName], [ContactName], [City], [Country] FROM [Customers]"> </asp:SqlDataSource> <table align="left" class="style1"> <tr> <td class="style2"> <asp:RadioButtonList ID="RadioButtonList1" runat="server" AutoPostBack="True" RepeatDirection="Horizontal" RepeatLayout="Flow"> <asp:ListItem Value="0">All Pages</asp:ListItem> </asp:RadioButtonList> </td> <td> <asp:Button ID="btnExportToExcel" runat="server" Text="Export To Excel" Width="215px" onclick="btnExportToExcel_Click"/> </td> </tr> </table>
C# CODE
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
if (RadioButtonList1.SelectedIndex == 0)
{
GridView1.ShowHeader = true;
GridView1.GridLines = GridLines.Both;
GridView1.AllowPaging = false;
GridView1.DataBind();
}
else
{
GridView1.ShowHeader = true;
GridView1.GridLines = GridLines.Both;
GridView1.PagerSettings.Visible = false;
GridView1.DataBind();
}
ChangeControlsToValue(GridView1);
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=GridViewToExcel.xls");
Response.ContentType = "application/excel";
StringWriter sWriter = new StringWriter();
HtmlTextWriter hTextWriter = new HtmlTextWriter(sWriter);
HtmlForm hForm = new HtmlForm();
GridView1.Parent.Controls.Add(hForm);
hForm.Attributes["runat"] = "server";
hForm.Controls.Add(GridView1);
hForm.RenderControl(hTextWriter);
// Write below code to add cell border to empty cells in Excel file
// If we don't add this line then empty cells will be shown as blank white space
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"> <head><meta http-equiv="Content-Type" content="text/html;charset=windows-1252"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>ExportToExcel</x:Name><x:WorksheetOptions><x:Panes></x:Panes></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head> <body>");
sBuilder.Append(sWriter + "</body></html>");
Response.Write(sBuilder.ToString());
Response.End();
}
private void ChangeControlsToValue(Control gridView)
{
Literal literal = new Literal();
for (int i = 0; i < gridView.Controls.Count; i++)
{
if (gridView.Controls[i].GetType() == typeof(LinkButton))
{
literal.Text = (gridView.Controls[i] as LinkButton).Text;
gridView.Controls.Remove(gridView.Controls[i]);
gridView.Controls.AddAt(i,literal);
}
else if (gridView.Controls[i].GetType() == typeof(DropDownList))
{
literal.Text = (gridView.Controls[i] as DropDownList).SelectedItem.Text;
gridView.Controls.Remove(gridView.Controls[i]);
gridView.Controls.AddAt(i,literal);
}
else if (gridView.Controls[i].GetType() == typeof(CheckBox))
{
literal.Text = (gridView.Controls[i] as CheckBox).Checked ? "True" : "False";
gridView.Controls.Remove(gridView.Controls[i]);
gridView.Controls.AddAt(i,literal);
}
if (gridView.Controls[i].HasControls())
{
ChangeControlsToValue(gridView.Controls[i]);
}
}
}
VB.NET
Protected Sub btnExportToExcel_Click(sender As Object, e As EventArgs)
If RadioButtonList1.SelectedIndex = 0 Then
GridView1.ShowHeader = True
GridView1.GridLines = GridLines.Both
GridView1.AllowPaging = False
GridView1.DataBind()
Else
GridView1.ShowHeader = True
GridView1.GridLines = GridLines.Both
GridView1.PagerSettings.Visible = False
GridView1.DataBind()
End If
ChangeControlsToValue(GridView1)
Response.ClearContent()
Response.AddHeader("content-disposition", "attachment; filename=GridViewToExcel.xls")
Response.ContentType = "application/excel"
Dim sWriter As New StringWriter()
Dim hTextWriter As New HtmlTextWriter(sWriter)
Dim hForm As New HtmlForm()
GridView1.Parent.Controls.Add(hForm)
hForm.Attributes("runat") = "server"
hForm.Controls.Add(GridView1)
hForm.RenderControl(hTextWriter)
Dim sBuilder As New StringBuilder()
sBuilder.Append("<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"> <head><meta http-equiv="Content-Type" content="text/html;charset=windows-1252"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>ExportToExcel</x:Name><x:WorksheetOptions><x:Panes></x:Panes></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head> <body>")
sBuilder.Append(sWriter & "</body></html>")
Response.Write(sBuilder.ToString())
Response.[End]()
End Sub
Private Sub ChangeControlsToValue(gridView As Control)
Dim literal As New Literal()
For i As Integer = 0 To gridView.Controls.Count - 1
If gridView.Controls(i).[GetType]() = GetType(LinkButton) Then
literal.Text = TryCast(gridView.Controls(i), LinkButton).Text
gridView.Controls.Remove(gridView.Controls(i))
gridView.Controls.AddAt(i, literal)
ElseIf gridView.Controls(i).[GetType]() = GetType(DropDownList) Then
literal.Text = TryCast(gridView.Controls(i), DropDownList).SelectedItem.Text
gridView.Controls.Remove(gridView.Controls(i))
gridView.Controls.AddAt(i, literal)
ElseIf gridView.Controls(i).[GetType]() = GetType(CheckBox) Then
literal.Text = If(TryCast(gridView.Controls(i), CheckBox).Checked, "True", "False")
gridView.Controls.Remove(gridView.Controls(i))
gridView.Controls.AddAt(i, literal)
End If
If gridView.Controls(i).HasControls() Then
ChangeControlsToValue(gridView.Controls(i))
End If
Next
End Sub
22:12
|
Category:
|
2
comments
Comments ( 2 )
I have three gridview with different no of columns like 8,6 and 2 !! so when i export them to excel the column width is not showing proper format !! plz help
C# GridView .NET component