Friday, December 30, 2011

export gridview to excel in asp.net

Title:How to Export data from grid view to excel in c#

Description:
As per previous articles we have gone through some examples like Export grid view data to word document,Export grid view to PDF,Import excel data to Asp.net Grid view ,Export data to excel in asp.net.Here i would like to share  how to export grid view data to Excel sheet in asp.net using c#.net.It can be done in two steps.First is render to HTML from grid view then the present HTML to Excel.

Example:
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GdvMainmasters" Runat="server" DataSourceID="OrdrDb">
<Columns>
<asp:BoundField DataField="OrderID" HeaderText="OrderID" 
SortExpression="OrderID" />
<asp:BoundField DataField="OrderName" HeaderText="OrderName" 
SortExpression="OrderName" />
<asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />
<asp:BoundField DataField="Address" HeaderText="Address" 
SortExpression="Address" />
<asp:BoundField DataField="Amount" HeaderText="Amount" 
SortExpression="Amount" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="OrdrDb" runat="server" 
ConnectionString="<%$ ConnectionStrings:TestConnectionString %>" 
SelectCommand="SELECT * FROM [Orders]"></asp:SqlDataSource>
<asp:Button ID="btnexportOrderstoWord" runat="server" Text="ExportExcel" 
onclick="btnexportOrderstoWord_Click" /></div>
</asp:GridView>
<asp:Lable id="LblMsg" runat="server"/>
<asp:Button id="BtnSendtoExcel" Text="Export" runat="server" OnClick="BtnSendtoExcel_Click"/>
</div>
</form>
</body>
</html>

CodeBehind:
protected void BtnSendtoExcel_Click(object sender, EventArgs e)
{
try
{
Excelbtn = "ABC";
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",attachment;filename=OrderDetails.xls");
Response.ContentType = "application/ms-excel";
StringWriter swr = new StringWriter();
HtmlTextWriter hwr = new HtmlTextWriter(sw);
GdvMainmasters.AllowPaging = false;
GdvMainmasters.AutoGenerateSelectButton = false;
GdvMainmasters.Enabled = false;
GdvMainmasters.DataBind();
GdvMainmasters.HeaderRow.Cells[0].BorderStyle = BorderStyle.Inset;
GdvMainmasters.HeaderRow.Cells[1].BorderStyle = BorderStyle.Inset;
GdvMainmasters.HeaderRow.Cells[2].BorderStyle = BorderStyle.Inset;
GdvMainmasters.HeaderRow.Cells[3].BorderStyle = BorderStyle.Inset;
GdvMainmasters.HeaderRow.Cells[3].BorderStyle = BorderStyle.Inset;
for (int j = 0; j < GdvMainmasters.Rows.Count; j++)
{
GridViewRow row = GdvMainmasters.Rows[j];
GdvMainmasters.RenderControl(hwr);
Response.Write(swr.ToString());
Response.Flush();
Response.End();
}
catch (Exception e3)
{
LblMsg.Text = e3.Message;
}
} 


Note:While run the above code we may get the some errors RegisterForEventValidation can only be called during Render.If you get an error then need to override the control.


3 comments:

webmonkeymon said...

Thank you very much sir!
I did get this to work. I typically am working in VB now so had to struggle a bit to fix a few minor issues with syntax. Case sensitive Capital letters! ahh!

all very minor. without to much got it to work.

not sure i understand why their are two buttons?

Big thank you.

webmonkeymon said...

Thank you Sir!!

it was a big help!!

Unknown said...

i have 93 column how to define that field

Bel