Export Grid View to Excel

Posted by Venu510 under ASP.NET on 7/16/2011 | Points: 10 | Views : 11677 | Status : [Member] | Replies : 6
Hi Friends!

Could you please send the code in vb.net to export Gridview data to excel.
I gone thorough google but everyone giving the same which is not satisfying me.In those codes when i export the data form grid to excel, in excel only the gridview data is displaying in table structure and remaining page is displaying as like word document.I need the code to export excel and after open the excel file it should look like same as excel with out blank like word document.


Regards
----------
Venu




Responses

Posted by: A4u_6178 on: 7/16/2011 [Member] Starter | Points: 25

Up
0
Down
Hello,
Refer the link..
http://www.dotnetfunda.com/articles/article10.aspx
the cide is in c# u can convert that code into vb.net refer below link to do so..
http://www.developerfusion.com/tools/convert/csharp-to-vb/

Thanks & Regards,

Venu510, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dora743 on: 7/16/2011 [Member] Starter | Points: 25

Up
0
Down
Sample code and also you can apply colors to excel rows

protected void btnExportExcel_Click(object sender, EventArgs e)

{

Response.Clear();
Response.Buffer = true;

Response.AddHeader("content-disposition",
"attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);

grdExport.AllowPaging = false;
oMailing.GetData(out ODs);
grdExport.DataSource = ODs;
grdExport.DataBind();

//Change the Header Row back to white color
grdExport.HeaderRow.Style.Add("background-color", "#FFFFFF");

//Apply style to Individual Cells
grdExport.HeaderRow.Cells[0].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[1].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[2].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[3].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[4].Style.Add("background-color", "green");

for (int i = 0; i < grdExport.Rows.Count; i++)
{
GridViewRow row = grdExport.Rows[i];

//Change Color back to white
row.BackColor = System.Drawing.Color.White;

//Apply text style to each Row
row.Attributes.Add("class", "textmode");

//Apply style to Individual Cells of Alternating Row
if (i % 2 != 0)
{
row.Cells[0].Style.Add("background-color", "#C2D69B");
row.Cells[1].Style.Add("background-color", "#C2D69B");
row.Cells[2].Style.Add("background-color", "#C2D69B");
row.Cells[3].Style.Add("background-color", "#C2D69B");
row.Cells[4].Style.Add("background-color", "#C2D69B");
}
}
grdExport.RenderControl(hw);

//style to format numbers to string
string style = @"<style> .textmode { mso-number-format:\@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}


Dorababu

Venu510, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Venu510 on: 7/16/2011 [Member] Starter | Points: 25

Up
0
Down
Thank you friends

Regards
----
Venu

Venu510, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dora743 on: 7/16/2011 [Member] Starter | Points: 25

Up
0
Down
Does the problem resolved for you if so mark the one which helps you as Answer

Dorababu

Venu510, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Saranpselvam on: 5/10/2013 [Member] Starter | Points: 25

Up
0
Down
Just paste this code in a button click Event. it will work
{
GridView1.AllowPaging = false;
GridView1.AllowSorting = false;
GridView1.DataSource = DT // your data table
GridView1.DataBind();
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=My Report.Xls");
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
string style = "<style> td{mso-number-format:\\@}</style>";
Response.Write(style);
Response.Write("<Center > <font size=2 face=Verdana><b>My Report </b></font> </Center> ");
Response.Write("<table><tr></tr></table>");
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
GridView1.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.Flush();
Response.End();
}

/// after this in your page you have to add the following method

public override void VerifyRenderingInServerForm(Control control)
{

// Confirms that an HtmlForm control is rendered for the

}


Thanks
saranpselvam@gmail.com

Venu510, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Satyapriyanayak on: 5/10/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Default.aspx code


<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" 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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>

</div>
<asp:GridView ID="g1" runat="server">
</asp:GridView>
<br />
<br />
<asp:Label ID="Label1" runat="server" Text="Export data to Excel"
BackColor="Yellow" Font-Bold="True" ForeColor="#FF3300"></asp:Label><br />

<asp:ImageButton ID="ImageButton1" runat="server" ImageUrl= "~/images/Excel2.JPG" />

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

Default.aspx.vb code

Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

Partial Class _Default
Inherits System.Web.UI.Page
Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()
Dim con As New SqlConnection(strConnString)
Dim str As String
Dim com As SqlCommand
Dim sqlda As SqlDataAdapter
Dim ds As DataSet
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
bindgrid()
g1.Visible = False
End Sub
Sub bindgrid()
con.Open()
str = "select * from SampleCustomer"
com = New SqlCommand(str, con)
sqlda = New SqlDataAdapter(com)
ds = New DataSet
sqlda.Fill(ds, "SampleCustomer")
g1.DataSource = ds
g1.DataMember = "SampleCustomer"
g1.DataBind()
con.Close()
End Sub
Private Sub ExportToExcel(ByVal strFileName As String, ByVal dg As GridView)
Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Dim oStringWriter As New System.IO.StringWriter
Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)

g1.RenderControl(oHtmlTextWriter)

Response.Write(oStringWriter.ToString())
Response.[End]()

End Sub

Protected Sub ImageButton1_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ImageButton1.Click
g1.Visible = True
ExportToExcel("Report.xls", g1)
End Sub
Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

End Sub
End Class


If this post helps you mark it as answer
Thanks

Venu510, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response