Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Export gridview data to Excel/word document in asp.net C#

Apr 2, 2011
Introduction:

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>
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

<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 RSS subscribe by email Subscribe by Email

80 comments :

Anonymous said...

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.

Suresh Dasari said...

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

Anonymous said...

hi i am want employee details with photo but display in gridview how to develop please help me..

Anonymous said...

gird view include image. but not open image in excel please help me..

raj said...

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

Anonymous said...

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

krishna reddy said...

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

Bansi said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

Hi Suresh,

Please reply to my query .

Anonymous said...
This comment has been removed by a blog administrator.
Suresh Dasari said...

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

Suresh Dasari said...

@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

Suresh Dasari said...

@@ 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

krishna reddy said...

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

Anonymous said...

A simple excel export

http://csharp.net-informations.com/excel/csharp-excel-export.htm

anm.

Anonymous said...

how to export data to excel from radgridview in vb.net

Anonymous said...

PLs reply

poonamtemgire said...

how to export grid data in excel

Anonymous said...

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,

shaiz said...

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

Christina said...

Wow.. nice code.. I appreciate.. Thank You..
===
web design

Anonymous said...

Hey there mr Dasari!!
Excuse a new programmers stupid question: Do you have Word/Excell installed on the server?

Anonymous said...

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...

sriraj said...

This code is so useful to me

Anonymous said...

our site is good

Anonymous said...

Does it works in IE7 browser??

Anonymous said...

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

Suresh Dasari said...

it's working in all browsers... Please check your code i hope you did mistake....

Anonymous said...

Hi Suresh

It working in all browsers even in Internet Explorer but in Internet Explorer version 7
(IE7) its not working dude.

waiting.....

jyo said...
This comment has been removed by the author.
jyo said...

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

Anonymous said...

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

Anonymous said...

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.

Unknown said...

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.

Anonymous said...

thanxxxx a lot sir.........

pragadesh said...

Hi Suresh i'm wondered about ur all article great and helpful in my career thank u ji!!

crazy kittu said...

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.

Vara said...

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
}

Govind said...

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...

Anonymous said...

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?

shanti said...

how to export data to excel from radgridview in vb.net

Unknown said...

hi suresh i got some problem java script Microsoft JScript runtime error: 'Content1_Click' is undefined
alal the process work well. tahnks alot

Anonymous said...

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"

Anonymous said...

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...

Praveen Rao Chavan said...

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

Imi Chitterman said...

You have shared with the world. Thanks for the help and post.

TheHashBubbles said...

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

TheHashBubbles said...

I am using asp.net, visual studio 10, framework 4.0
and usign linq inside

Anonymous said...

my gridview has marathi labels and it is not showing properly in excel.How to solve this problem.please help me out.
Thank you.

Anonymous said...

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

aman chaudhary said...

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

Unknown said...
This comment has been removed by the author.
Anonymous said...

I need a code for selected gridview data into CSV and store that csv file into a particular folder automatically

Anonymous said...

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...

Anonymous said...

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

Unknown said...

Hi Suresh.
When i export the gridview data to excel, i am getting "div" "/div" as output.
How to resolve this ?

Anonymous said...

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();

Unknown said...

Same Error (hi i am want employee details with photo but display in gridview how to develop please help me..)

Unknown said...
This comment has been removed by the author.
Unknown said...

After image Button Click it Displaying Grid in web page not in Excel or Grid

Anonymous said...

i Getting "NORECORDS" WHAT I DO PLS HELP ME

Unknown said...

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

Unknown said...

HI Suresh will the data bought from XML to Gridview. And can I convert it to word or not?

Anonymous said...

Thanks.It works fine for me.

prasad said...

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

Baiju EP said...

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

Anonymous said...

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.

Unknown said...

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

Unknown said...

how to show hindi data in sql sever and it is also display in browser

Unknown said...

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

sarath said...

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();
}

Unknown said...

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

Unknown said...

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

Anonymous said...

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

Anonymous said...

Hi, Suresh, I need to save excel file in .xlsx, it didn't work in it. Thank you.

Unknown said...

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...????

Unknown said...

i used this function VerifyRenderingInServerForm but not working

Krishna said...

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?

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.