|
|
Exporting GridView to Excel
AzamSharp
Published Date: 7/26/2005 11:03:47 PM
Views: 40892
Abstract:
In one of my previous article Exporting GridView to Text Files I talked about how you can export GridView to text files. The problem with using text files is that you cannot control the format and it gets really messy if you have more columns. In this article I will explain how you can export your GridView control to Excel files. If you are working with Datagrid than you can also check out my article Exporting Datagrid to Excel, Text and Word Files.
Introduction:
In one of my previous article Exporting GridView to Text Files I talked about how you can export GridView to text files. The problem with using text files is that you cannot control the format and it gets really messy if you have more columns. In this article I will explain how you can export your GridView control to Excel files. If you are working with Datagrid than you can also check out my article Exporting Datagrid to Excel, Text and Word Files.
Exporting GridView to Excel Files:
The approach is pretty much the same as it was in Datagrid. The following code executes on a button click event.
| Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
Response.Charset = "";
// If you want the option to open the Excel file without saving than
// comment out the line below
// Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
gvMaster.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End(); |
The code is almost identical to what we used when exporting Datagrid (see here). If you run this code it will not work the reason is that we need to tell ASP.NET that there is a control in our form that will be rendered at runtime. For this you can just override the Page.VerifyRenderingInServerForm method.
| public override void VerifyRenderingInServerForm(Control control)
{
// Confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time.
} |
Now if you try to export your GridView to excel it will work fine.
I hope you like the article, happy coding !
Enter Comment/Feedback
Comments/Feedbacks
|
|
|
|
Subject: Excellent!
Name: Tony
Date: 2/7/2007 7:27:13 PM
Comment: can you traslate the code to VB?
Thanks |
|
|
|
|
|
Subject: Online Convertor
Name: AzamSharp
Date: 2/7/2007 8:22:52 PM
Comment: Hi Tony,
You can use an online convertor to translate code from C# to VB.NET.
Check out the following link:
http://www.kamalpatel.net/ConvertCSharp2VB.aspx
|
|
|
|
|
|
Subject: Export Gridview to excel
Name: vincent
Date: 2/8/2007 8:36:12 AM
Comment: Thank you for your code. It works perfect when the columns in the gridview is less than 50 columns. Do you have any suggestion to avoid error when columns > 50.
Thank you for your help.
Vincent. |
|
|
|
|
|
Subject: Carriage Return
Name: Eric
Date: 2/8/2007 10:32:45 AM
Comment: I have some text with carriage returns in it. WHen I import those, the Excel table creates a new row for each carriage return. How do I keep them in the same row? |
|
|
|
|
|
Subject: Nothing happens
Name: Wilson
Date: 2/12/2007 10:46:28 PM
Comment: It cant seems to work for me.. I have an .aspx page with gridview. I copied your button click event handler as shown above and substituted the 'gvMaster' with the ID of my gridview.
After I clicked the button, nothing happens.
Must I override 'Page.VerifyRenderingInServerForm' method? If yes, how could I do that?
I tested the .aspx page using localhost.
Can someone help me out on this?
Thank You so much. |
|
|
|
|
|
Subject: Export Gridview to excel
Name: Pradeep
Date: 3/17/2007 3:11:53 AM
Comment: Thank you for your code. It works perfect when the gridview doesn't have paging.It gives an error like
"RegisterForEventValidation can only be called during Render();" can u help me out. |
|
|
|
|
|
Subject: Exporting Gridview to Excel
Name: Kaushalendra Pandey
Date: 4/9/2007 5:15:35 AM
Comment: I have seen your article of exporting the gridview to excel but if there is a link button or a push button then there occurs error which says that "put the control in runat="server" " etc. etc.
Please tell me what to do for that. |
|
|
|
|
|
Subject: Export Gridview to excel
Name: Anita
Date: 4/9/2007 11:21:58 AM
Comment: U are a Genius |
|
|
|
|
|
Subject: thanks
Name: srinivas
Date: 4/17/2007 2:49:21 AM
Comment: ya it works thankz |
|
|
|
|
|
Subject: Error while executing
Name: Surekha
Date: 4/27/2007 1:59:38 PM
Comment: Hello,
I used the same code as suggested by you in vb.net, but may be because of paging, I don't know, it gives an error like
"RegisterForEventValidation can only be called during Render();" can u help me out.
Thanks,
Surekha |
|
|
|
|
|
Subject: Export Error
Name: Andrea
Date: 8/2/2007 3:21:41 AM
Comment: If AllowPaging or AllowSorting properties of your Gridview are set to True, you'll give an error like:
"RegisterForEventValidation can only be called during Render();".
To avoid this, simply write:
gvMaster.AllowPaging = False gvMaster.AllowSorting = False
gvMaster.DataBind()
to the top of your exporting routine.
|
|
|
|
|
|
Subject: export columns
Name: sajid
Date: 8/13/2007 2:56:59 AM
Comment: Howdy,
My gridview is databound to a view of a table, but the gridview only shows 7 out of 21 columns.
when I export to excel It seems to export all columns.
Is there any way of only exporting the 7 columns I need. |
|
|
|
|
|
Subject: Why is google docs not importing my spreadsheet?
Name: Mahernoz
Date: 9/11/2007 4:03:17 AM
Comment: Hi,
Thanks for the excellent article.
Now,i want to manually import my "generated" excel sheet to Googledocs, so that I can share this "google spreadsheet" with other
people.
I am doing this... File->New after that i try "Import".
I am getting the error: The Uploaded file could not be imported.
I have also tried to upload it from my computer directly by clicking
on "Upload" by it gives me the following message:
"We're sorry, but we were unable to upload this document" and some
steps such as Copy-Paste to do the same.
I don't know, why i am not able to import the document directly. Do i
need to make any changes in the encoding?
I am using UTF 8 encoding while exporting my gridview.
Please advice.
Regards,
Mahernoz |
|
|
|
|
|
Subject: Why we need to override VerifyRenderingInServerForm
Name: Pandian
Date: 11/24/2007 2:16:09 AM
Comment: Please let me know why we need to override this VerifyRenderingInServerForm(). |
|
|
|
|
|
Subject: RE: Why need for VerifyRenderingInServerForm
Name: AzamSharp
Date: 11/27/2007 12:28:39 PM
Comment: Hi Pandian,
This tells ASP.NET that we are responsible for rendering the ASP.NET controls on the form so don't bother to verify it for us. |
|
|
|
|
|
Subject: Azam is the man
Name: Nadeem
Date: 12/26/2007 12:32:59 AM
Comment: Thanks azam you are the man!!!!
! |
|
|
|
|
|
Subject: RE: Azam is the man
Name: AzamSharp
Date: 12/29/2007 6:25:18 PM
Comment: Thanks Nadeem. I am glad you enjoyed the article! |
|
|
|
|
|
Subject: Need advise.
Name: dinu selva
Date: 1/24/2008 4:31:30 AM
Comment: Hi Azam,
ya this code executes excellent. but after save the excel(i export gridview hav 3 records) file. and try to open the error message say "Unable to read"
whats the problem here?
protected void Button1_Click(object sender, EventArgs e)
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
VerifyRenderingInServerForm(Employee_Grid);
(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
return;
} |
|
|
|
|
|
Subject: Append logo To Excel
Name: Suryakant Ker
Date: 2/19/2008 10:39:34 PM
Comment: I want to add logo to exported excel sheet. Please Help.. |
|
|
|
|
|
Subject: RE: Append logo to excel
Name: AzamSharp
Date: 2/20/2008 8:49:27 PM
Comment: Hi Suryakant Ker,
Try to append the URL to the image file. When you open the excel file the image will be downloaded in excel. I have not tried it but it should work! |
|
|
|
|
|
Subject: Leading 0's removed
Name: Zeeshan
Date: 2/26/2008 1:45:53 AM
Comment: Hello,
The export works perfectly, but i am facing a small problem.
My gridview fields contain Telephone Numbers,
eg. 0216675749
After exporting to xls, i get
216675749
Please advise.
|
|
|
|
|
|
Subject: RE: Leading 0's Removed
Name: AzamSharp
Date: 2/26/2008 7:37:26 PM
Comment: Hi Zeeshan,
Please check out the following article:
http://www.gridviewguy.com/ArticleDetails.aspx?articleID=197_Extensive_Study_of_GridView_Export_to_Excel |
|
|
|
|
|
Subject: Thanks
Name: Zeeshan
Date: 2/27/2008 2:23:59 AM
Comment: Thanks... btw ...Good article. |
|
|
|
|
|
Subject: Excellent
Name: Mangai
Date: 3/25/2008 2:16:55 AM
Comment: Excellent article. If explanantion for code is done it would have been really useful |
|
|
|
|
|
Subject: error
Name: sarla
Date: 3/29/2008 12:14:09 AM
Comment: using this code error:-
RegisterForEventValidation can only be called during Render();
pls Provide the solution |
|
|
|
|
|
Subject: RE: Error
Name: AzamSharp
Date: 3/31/2008 9:40:19 AM
Comment: Hi Sarla,
You need to override VerifyRenderingInForm method. I have explained in my other GridView to Excel articles. |
|
|
|
|
|
Subject: GridviewExport to Excel
Name: Babangida
Date: 4/24/2008 2:37:44 AM
Comment: am getting the following warning when i try to open the file in EXCEL 2007. How can i avoid it:
The File you are trying to open. "testing.xls" is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. DO you want to opne the file now?
|
|
|
|
|
|
Subject: hara...superb topic
Name: subhas
Date: 7/3/2008 1:29:06 AM
Comment: no doubt it help me..Excellent..
hara..ha ra sankar...
ki hobeeeeeeeeeeeeeeeeeeee |
|
|
|
|
|
Subject: if Gridview including Chinese
Name: Ji cheng
Date: 8/4/2008 8:03:16 PM
Comment: if gridview including Chinese, it will doesn't work |
|
|
|
|
|
Subject: How to Export Gridview Caption
Name: Maria Hobbs
Date: 8/6/2008 8:36:29 AM
Comment: Great article. However, I need to export the gridview caption and this code doesn't provide for that. How can I grab that item on the export?
Thanks in advance for your response.
Maria |
|
|
|
|
|
Subject: Windows Application
Name: Agnes Loyola
Date: 8/6/2008 10:47:44 PM
Comment: How to Export the Gridview to Excel in windows application with Open,Save and Cancel option? |
|
|
|
|
|
Subject: Add logo and title
Name: Cheryll
Date: 8/11/2008 10:29:18 PM
Comment: hi azamSharp!. This code is really a great help to me. How about if I wanted to add logo and additional column for title above on the exported excel |
|
|
|
|
|
Subject: ExportDataToExcel
Name: RaviKumar
Date: 11/25/2008 1:27:46 AM
Comment: hi..this code is pretty good and its working propely..but i didnt understand how it works...can u please explain..what is happening in each step....thanku in advance |
|
|
|
|
|
Subject: re: ExportToExcel
Name: AzamSharp
Date: 11/26/2008 10:09:06 AM
Comment: Hi RaviKumar,
Debug the application and find out! |
|
|
|
|
|
Subject: i want to export dynamically added gridview rows export to excel
Name: devi
Date: 12/3/2008 4:01:55 AM
Comment: hi,
gridview to excel is working fine,but we need dynamically added gridview rows to excel sheet .
if any one konws the solution plz help me.let me know the solution.
Thnx in advance |
|
|
|
|
|
Subject: Exporttoxcel
Name: happygirl
Date: 2/22/2010 7:34:02 PM
Comment: Worked PERFECT. Thank you very much! |
|
|
|