Introduction:
Here I will explain how to export gridview to Word or Excel document using asp.net in c#.
Here don’t forgot to set the connection string in web.config file here I am getting database connection from web.config file for that reason you need to set the connectionstring in web.config file like this
Now if you’re getting any error message like
Here I will explain how to export gridview to Word or Excel document using asp.net in c#.
Description:
I have one gridview that has filled with user details now I need to export gridview data to word or excel document based on selection. To implement this functionality first we need to design aspx page like this
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td align="right">
<asp:ImageButton ID="btnExcel" runat="server" ImageUrl="~/ExcelImage.jpg"
onclick="btnExcel_Click" />
<asp:ImageButton ID="btnWord" runat="server" ImageUrl="~/WordImage.jpg"
onclick="btnWord_Click" />
</td>
</tr>
<tr>
<td>
<asp:GridView runat="server" ID="gvdetails" DataSourceID="dsdetails" AllowPaging="true" AllowSorting="true" AutoGenerateColumns="false">
<RowStyle BackColor="#EFF3FB" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="UserId" HeaderText="UserId" />
<asp:BoundField DataField="UserName" HeaderText="UserName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" />
<asp:BoundField DataField="Location" HeaderText="Location" />
</Columns>
</asp:GridView>
</td>
</tr>
</table>
<asp:SqlDataSource ID="dsdetails" runat="server" ConnectionString="<%$ConnectionStrings:dbconnection %>"
SelectCommand="select * from UserInformation"/>
</div>
</form>
</body>
</html>
|
<connectionStrings>
<add name="dbconnection" connectionString="Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"/>
</connectionStrings>
|
After set the connection string in web.config file now we are able to get the data from database and we can bind that data to gridview by using sqldatasource. Now if you run application gridview appears like this
 |
Now in code behind add this reference
using System.IO;
|
After that write the following code in code behind
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
/// <summary>
/// This event is used to export gridview data to word document
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnWord_Click(object sender, ImageClickEventArgs e)
{
gvdetails.AllowPaging = false;
gvdetails.DataBind();
Response.ClearContent();
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.doc"));
Response.Charset = "";
Response.ContentType = "application/ms-word";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gvdetails.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
/// <summary>
/// This Event is used to export gridview data to Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExcel_Click(object sender, ImageClickEventArgs e)
{
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.xls"));
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gvdetails.AllowPaging = false;
//Change the Header Row back to white color
gvdetails.HeaderRow.Style.Add("background-color", "#FFFFFF");
//Applying stlye to gridview header cells
for (int i = 0; i < gvdetails.HeaderRow.Cells.Count; i++)
{
gvdetails.HeaderRow.Cells[i].Style.Add("background-color", "#507CD1");
}
int j = 1;
//This loop is used to apply stlye to cells based on particular row
foreach (GridViewRow gvrow in gvdetails.Rows)
{
gvrow.BackColor = Color.White;
if (j <= gvdetails.Rows.Count)
{
if (j % 2 != 0)
{
for (int k = 0; k < gvrow.Cells.Count; k++)
{
gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");
}
}
}
j++;
}
gvdetails.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
| |
Demo for Word document
 |
If you observe above code I added one function that is VerifyRenderingInServerForm this function is used to avoid the error like “control must be placed in inside of form tag”. If we set VerifyRenderingInServerForm function then compiler will think that controls rendered before exporting and our functionality will work perfectly.
Here I used basic code to export gridview data to word document and for excel code is different but we can use the same code (Export to Word) for excel also to import gridview data just by replacing Customers.doc to Customers.xls and application/ms-word to application/ms-excel but here we have problem that is row background color is applied throughout excel for that reason I made some small code modification and applied color only to the particular columns based on rows.
Demo for Excel document
 |
Download sample code attached
Now if you’re getting any error message like
Control 'gvdetails' of type 'GridView' must be placed inside a form tag with runat=server
 |
Check this post to solve this problem
Otherwise if you’re getting any error message like
RegisterForEventValidation can only be called during Render();
 |
Check this post to solve your problem
|
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. |
|||
Subscribe by RSS
Subscribe by Email
|
|||
80 comments :
Hi suresh,how to use xlsx extension(office 2007) i mean it works fine in xls(office 2003) but it did't open in excel 2007.
hi,
In my code i gave default name as Customers.xls.Here you need to change this name to Customers.xlsx now it will work for xslx extension
hi i am want employee details with photo but display in gridview how to develop please help me..
gird view include image. but not open image in excel please help me..
hi
I have export gridview data into word, PDF and excel format, but i am usin checkbox in gridview and i want to export only selected data.. can anyone help me out
i tried this when i click on export excel icon it asks for save location and saves the default.aspx file instead of exzcel file
Hi suresh i used your code to print as Excel
i wanted to exclude first two columns while Generating Excel sheet how to do that
I have export gridview data into word, PDF and excel format, but i am usin checkbox in gridview and i want to export only selected data.. can anyone help me out
what is the vb.net equivalent of the VerifyRenderingInServerForm function? I added the following and it still gave the same error...
Public Overrides Sub VerifyRenderingInServerForm(ByVal cont As Control)
End Sub
Hi Suresh ,
I applied your code but am not getting any data in Excel sheet and the exceution point is not entering into the j loop for fetching the row information
Hi Suresh,
Please reply to my query .
In vb.net equivalent of the VerifyRenderingInServerForm function will be like this
Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
'Verifies that the control is rendered
End Sub
@Bansi
Check below post to export selected data from gridview using checkbox
http://www.aspdotnet-suresh.com/2011/12/export-selected-gridview-rows-to-excel.html
@@ Comment 10
i think that problem because of your not getting any data from database every time gridview rows are returning zero because of that your code not entering into loop Please check your code whether your getting data or not
hi your code works perfect for me but the problem is if i have 4 pages of data in gridview it only exports current page only i want all the data from the gridview how to do that
A simple excel export
http://csharp.net-informations.com/excel/csharp-excel-export.htm
anm.
how to export data to excel from radgridview in vb.net
PLs reply
how to export grid data in excel
Hi Suresh,
Are you familiar with web design in Microsoft Web Developer?
I have a question, I want to show the gridview if i press a button. I have tried it with javascript button syntax and also with asp button syntax. But I can't show it with my button.
Do you have some references or solution regarding this?
THank you for helping me.
Kind regards,
Hi suresh,
I want to save data in excel which shows data validations if i enter wrong details....eg: instead of date if i enter text it should show an error.... how can i add this feature.
thanks,
shaiz
Wow.. nice code.. I appreciate.. Thank You..
===
web design
Hey there mr Dasari!!
Excuse a new programmers stupid question: Do you have Word/Excell installed on the server?
hi suresh..
i've encountered following err msg while i'm running this xl export..
Error Msg: "System.InvalidOperationException: RegisterForEventValidation can only be called during Render();"... plz help me...
This code is so useful to me
our site is good
Does it works in IE7 browser??
its not working in IE7. I searched many sites in google but didn't find
please give me code that works in IE7, waiting for reply.
Thanks in Advance
it's working in all browsers... Please check your code i hope you did mistake....
Hi Suresh
It working in all browsers even in Internet Explorer but in Internet Explorer version 7
(IE7) its not working dude.
waiting.....
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class test1 : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(connstring);
DataTable dt = new DataTable();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlDataAdapter da = new SqlDataAdapter("select * from Subjects",con);
//DataTable dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
ExportTableData(dt);
}
public void ExportTableData(DataTable dtdata)
{
string attach = "attachment;filename=example.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attach);
Response.ContentType = "application/ms-excel";
string sep = "";
if (dtdata != null)
{
foreach (DataColumn dc in dtdata.Columns)
{
Response.Write(sep + dc.ColumnName);
sep = " ";
}
Response.Write(System.Environment.NewLine);
foreach (DataRow dr in dtdata.Rows)
{
sep = "";
for (int i = 0; i < dtdata.Columns.Count; i++)
{
//System.Diagnostics.Debug.WriteLine(dr.ToString());
Response.Write(sep + dr.ToString() );
sep = " ";
}
Response.Write(System.Environment.NewLine);
}
Response.End();
}
}
}
I am Not Getting Row Data in my excel sheet. am getting row data as System.data.datarow instead tabale row data. so give me a solution how to get rows data in excel along with Column
MY Excel Sheet:
SubjectId SubName Status
System.Data.DataRow System.Data.DataRow System.Data.DataRow
System.Data.DataRow System.Data.DataRow System.Data.DataRow
System.Data.DataRow System.Data.DataRow System.Data.DataRow
System.Data.DataRow System.Data.DataRow System.Data.DataRow
System.Data.DataRow System.Data.DataRow System.Data.DataRow
System.Data.DataRow System.Data.DataRow System.Data.DataRow
System.Data.DataRow System.Data.DataRow System.Data.DataRow
System.Data.DataRow System.Data.DataRow System.Data.DataRow
System.Data.DataRow System.Data.DataRow System.Data.DataRow
System.Data.DataRow System.Data.DataRow System.Data.DataRow
Sys.WebForms.PageRequestManagerParserErrorException The message received from the server could not be parsed.
Error is coming while exoprting to excel
do you have any solution
Hi Suresh,
There is image(logo)right above gridview in the center.Everything is fine.Image also get imported to excel.But not able to position the image in excel file.It should in center,but it appear in leftmost park.I am using the same method u hv written above.
What is the solution for it ?
Thanks.
Hi suresh,
Seriously thanks a lot for the code but i have problem.
i have office 10 in my system so while opening the file im getting the error as "file format is not supporting".. in code also i changed the name as.xlsx...
plz help me to open the excel file.
thanxxxx a lot sir.........
Hi Suresh i'm wondered about ur all article great and helpful in my career thank u ji!!
hi Suresh,
I want to export the records to excel sheet while paging. means what ever i currently viewing the records only those should be exported to excel. How can i achieve this?
My code is like this:
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=xyz.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.xlsx";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
gvExport.RenderControl(htmlWrite); //throwing error
Response.Write(stringWrite.ToString());
Response.End();
Thanks in advance.
People who are getting Errors Like Now Line or some thing more
gvdetails.DataBind();
Insted of the above line use the method like Use Below coding
BindGrid()
{
// Binding procedure goes here
}
hi,
your code is fine. when i click on word or excel file is saved but both show only div tag
div
/div
__
Plz reply me...
Hi,
I have a particular scenario that requires export to excel.
I have a 4 System.Web.UI.WebControls.Table controls on a web form which are already formatted. I need to export all 4 of these tables to a different worksheet in an excel workbook and the formatting must be aplied to the export as well.
How can i do this?
how to export data to excel from radgridview in vb.net
hi suresh i got some problem java script Microsoft JScript runtime error: 'Content1_Click' is undefined
alal the process work well. tahnks alot
hi suresh i got tihis problem
Microsoft JScript runtime error: 'ImageButton1_Click' is undefined
when i used the button
this is my aspx code
asp:ImageButton ID="ImageButton1" runat="server"
ImageUrl="~/Admin/word-logo.png" OnClientClick="ImageButton1_Click" Height="38px" Width="45px"
hi...
i want to transfer data from modalpopup to excel ...can u help me pls...and modal popup data is not in grid ...i m genrating report like form fill up form...
Hi Suresh ,
I am a Software Engineer i enjoy all your posting it's really helpfull especially this one was too cool... thanks keep on your service we need them
You have shared with the world. Thanks for the help and post.
Hi Suresh
Thanks a lot for the code.
It worked great for me until i was using grid view binding on design level.
But later my requirements got changed ; i have to show my table data according to the search results. So, i had to remove binding from design-level to program-level. But now it is not working as the same it was erlier.
i.e. It prompts for Excel saving dialog box but inside excel sheet the output is not shown but only div tags are there
Why is this happening?
or
is there any other way to do so?
Plz help
I am using asp.net, visual studio 10, framework 4.0
and usign linq inside
my gridview has marathi labels and it is not showing properly in excel.How to solve this problem.please help me out.
Thank you.
Hi,Suresh
thanks for your help,
i m using ajex calender in my grid view, that show is error with word and exel conversion.
plz try to give the solution.
Extender controls must be registered using RegisterExtenderControl() before calling RegisterScriptDescriptors()
thanks,
Ravi Kant Maurya
EmailID- zxcmaurya@gmail.com
Control 'ctl00_ContentPlaceHolder1_dtgMyTeam' of type 'GridView' must be placed inside a form tag with runat=server.
I got this error at
dtgMyTeam.RenderControl(htw);
while my grid view is inside Form Tag
I need a code for selected gridview data into CSV and store that csv file into a particular folder automatically
hi suresh,
i am used your export to excel coding
but i am using this code is template columns in grid view i am getting empty excel, could you plz help me...
Hi Suresh,
Your post was so useful for me.Now could you please tell me in code
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.xls"));
Response.ContentType = "application/ms-excel";
can i put date with file name..like-Customers_13/02/2013_21/05/2013
Please...Please
Thanks
Ankush
Hi Suresh.
When i export the gridview data to excel, i am getting "div" "/div" as output.
How to resolve this ?
Hi i want to expport image from aspx page in word document i had tried folloeing code but it is not
working, lpease guide me..
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=Worddocument.doc");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.word";
System.IO.StringWriter _objstringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter _objhtmlWrite = new HtmlTextWriter(_objstringWrite);
Image1.RenderControl(_objhtmlWrite);
Label1.RenderControl(_objhtmlWrite);
Response.Write(_objstringWrite.ToString());
Response.End();
Same Error (hi i am want employee details with photo but display in gridview how to develop please help me..)
After image Button Click it Displaying Grid in web page not in Excel or Grid
i Getting "NORECORDS" WHAT I DO PLS HELP ME
hi suresh
nice code. But i am getting some problem. i used this code. excel sheet is also downloading. but excel file is not opening. its showing error like verify extension or file corrupted. Please help me how to over come from this
HI Suresh will the data bought from XML to Gridview. And can I convert it to word or not?
Thanks.It works fine for me.
very nice sir
sir what is "content-disposition" pls give comment under the code it is very useful to beginners like me thank you so much sir
Hai suresh your code works fine if gridview with datasource in .apsx page is used when i use code behind to bind data then the exported excel file shows only
div
div
please guide em to proceed
Hi Suresh,
I have tried for Report viewer to export the data to word,excel and pdf using VS 2010. As i am new to VS 2010. i didnt get the idea. For time being i was used your query which works fine and finished the work.
Thanks a lot suresh.
Can You Explain Same With 3 tier architecture in asp.net ???????????
if yes, then plz send me code on my mail id
EMAIL:- anku089@gmail.com
how to show hindi data in sql sever and it is also display in browser
Is it possible to export data from the gridview of a user control to excel ???? I am doing this but getting error "VerifyRenderingInServerForm(System.Web.UI.Control)': no suitable method found to override"
......................Please help me
for sharepoint developers:
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
string attachment = "attachment; filename=AoscReport.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();
gvAoscReport.Parent.Controls.Add(frm);
frm.Attributes["runat"] = "server";
frm.Controls.Add(gvAoscReport);
frm.RenderControl(htw);
gvAoscReport.DataBind();
//GridView1.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
Hello Sir,your code is working fine,the data's are successfully exporting from gridview to excel sheet but i'm getting the format error like "THE FILE YOUR TRYING TO OPEN,'SPORTSTRAINING.XLS" IS IN A DIFFERENT FORMATTHAN SPECIFIEDBY THE FILE EXTENSION
and exporting to excel is working fine but word is not at all printing.... document is opening but it is only printing two div's like open div and closing div
Hi suresh,how to use xlsx extension(office 2007) i mean it works fine in xls(office 2003) but it did't open in excel 2007.If i change the file name to xlsx , it wont work at all..give me a solution plsssss
Hi, Suresh, I need to save excel file in .xlsx, it didn't work in it. Thank you.
hii suresh,
i want to add first some text and header to word file and then grid view data then save.....
is there any help...????
i used this function VerifyRenderingInServerForm but not working
Hi Suresh,
I have used this functionality to export the grid view data to excel. But, I can see the whole page is getting rendered. Can you please let me know, how only the grid view can be exported without all the other controls?
Note: Only a member of this blog may post a comment.