Matt Berseth

Those who want to just paste a function of code can enjoy this onehttp://dotnetguts.blogspot.com/2006/10/export-datagrid-data-to-excel-aspnet_15.html

Nice. Simple and elegant. Exactly what I was looking for. Good show!

Matt, you rock. I had code to do the export, but it only worked properly if I had a true dataset tied to the Gridview. Well, in an app Im currently working with, Im filling a gridview based upon data from another gridview by populating a datatable first and...well, suffice it to say that the dataset is null. My own code (and everyone elses that I looked at) produced the infamous "form with tags" error or, when overriding events (which I did not want to do either), I received the Render() error. When I stumbled upon your code, it was the perfect solution. Finally, code that actually reads from the Gridview itself and then generates the output properly for Excel. Thanks!

This doesnt work if you have grid paging turned on - it only exports the rows that are visible on the page, not the whole grid. The live demo has a paged grid, and it doesnt work.

To export the whole grid just disable Gridview paging as the first line of the Export() Sub and re-enable paging as the last line of the Export() Sub.

The control is great. I needed to modify it a little bit to allow for nested grids. If anybody else needs this, let me know and I can post it somewhere.

Matt:
Thanks for the code. I have a quick question though. Does it work for the content page that uses web Master. I tried Azams code it does not work for pages that use Master page, what I got exported was only a tag.

hey guys, I am having excel with no gridlines. anyone else having this issue?
i could use code for nested grids if William can send that along :D
thanks guys!
-Shane

Very nice code! I have translated it into VB. If anyone needs it, just let me know

Fred,

Im looing for VB version of it. Can you share it please?

Hey all,

Great little script. I added this to the vb version:

HttpContext.Current.Response.Clear()
HttpContext.Current.Response.Charset = "utf-8"
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250")

Now character accents render properly in Excel. Hope that helps.

mate you saved me! doing this inside a dotnetnuke module works great. where everything else broke this was great.

Nice script.

Could you tell me what would be needed to preserve the color of the grid rows when exported to Excel? Currently it is just black and white when opened in Excel, but on the webpage it has specific colors.

Thank you.

your grid code does exactly what I need but when I export my grid it puts a blank row at the top of the xls. do you know how I can fix this?

Do have any idea how to get the name of the textbox on excel let say on textbox is on cell[1,1] its name is txtbox1 how to get that name through c#.net?

very nice code !! Matt, youre a legend!

how can I save the xls file directly on servers hard drive without confirmation to send after by email?

This is very nice way to export, however in DNN 4.5.5 I am still getting the Control gdvBrokerReport of type GridView must be placed inside a form tag with runat=server.

What am I missing?

Thanks,
Chas

Woot, exactly what I was looking for and worked like a charm. The VB version needs the updates for row number, but excellent!

Matt, thanks bunches. This is so simple to use. I had to jump through too many hoops before to get this done. Youve made things so much easier.

PS: Anybody knows where I can something similar for exporting to text?

Sweet, this worked perfectly. What you see is what you get. I was hoping Shane would give us a few more ideas on how to customise the export with the VB code. I am not sure how to get the entire grid to export when paging is enabled.

I tried using this but when i opened the excel file, there was no content.please help..tnx

Jeremy,

Try this:

Protected Sub btnExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcel.Click
Me.btnExcel.AllowPaging = False
Me.btnExcel.DataBind()
Me.btnExcel.GridLines = GridLines.Both
GridViewExportUtil.Export("Data.xls", Me.grdView)
End Sub

freakin sweet man! exactly what i needed...

Im getting the following error when I use it in accord with DNN 4.5.5 :

Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed. Common causes for this are when the response is modified by calls to Response.Write(), response filters, HttpModules, or server trace is enabled. Detials: Error parsing near .

Let me know what I should do. This looks like it would rock.

Would you please add appropriate authorship attribution information to the code samples. This is a beautiful piece of work and needs to be appropriately acknowledged.All I had to do was paste the VB.NET (Thanks Fred) class file into my application, reference it (one line of code) in my Export button code and - Bingo! Bango! Bongo! It worked perfectly.Im new to ASP.NET and VB.NET. I used Visual Web Developer Express and your code still works.

hi
Matt Berseth
really its nice example of gridview
thanks a loat for this examples

Very Nice Code. Helped me alot. Thanks

Hi Matt-

Thanks for the great code. I am having the following problem: I have two gridviews, each on a separate page.

One of the gridviews does not have insert/edit options while the other does. When I insert the exceloutput button with on the gridview that does not allow edit/insert, it works perfectly.

When I insert the button into the page with the gridview that does insert/edit, it doesnt work. I get the following error message: Control InsertClinic of type Button must be placed inside a form tag with runat=server.
Now the weird thing, is that export excel button is in the form and that the button which then starts causing the problem is one that is in the footer of the gridview. It still works fine, except when you click the excel output button. Any idea? Thanks.

@PDA:

Thanks for the feedback.

I think you have 2 options:
1. Update the ExportGridViewUtil to exclude the footer row from the export. I think this is probably what you want to do since the footer row doesnt actually contain any data, just command buttons
2. Update the PrepareControlForExport function to handle converting an asp:Button into text, possibly by using the Text property.

Hope that helps.
Matt

From module DotNetNuke 4.5.5 I am still getting the Control dnn_ctr372_GW1 of type GridView must be placed inside a form tag with runat=server.

Please, HELP!!!

I have had many successes with your code however I have now upgraded my users to MS Office 2007, I now get the following error message, Any ideas how this can be avoided?

"The file you are trying to open, Export.xls, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from an trusted source before opening the file. Do you want to open the file now."

This worked beautifully on the first try. I have a question since I am a .NET newbie. It works perfect if I cut/paste the class definition into the file I want to call it in. If I want to use it in a number of pages how do I put the code in one place and then properly reference it?

Thanks!

Ive not tried this out yet but heres a problem Ive run into using similar approaches: If my table includes a Zipcode column, which it almost always does, and there are zipcodes with leading zeros such as 02903, they get converted into integers in Excel and the leading zeros are dropped. This issue also comes up with Social Security numbers and other IDs that may have leading zeros. Any easy solution?

Is anyone else getting:

The file you are trying to open, Customers.xls, is in a different format than specified by the file extension.

Error?

Hi Jem,

There is a Office XML and CSS alternative.

If you open the Excel file generated using this technique in a text editor like Notepad, you will notice that the values are contained in a HTML table. In Office 2000 (and above) documents can be converted from Word/Excel to HTML (File->Save As) and vice versa and because of this feature the HTML table renders nicely when opened in Excel.

The various formatting features in the Office document are defined through XML and CSS as explained in the Microsoft Office HTML and XML Reference - http://msdn2.microsoft.com/en-us/library/Aa155477(office.10).aspx . By manipulating these properties exposed through XML programmatically, we can incorporate custom formatting capability in the Office docs that we offer for downloading.

To handle number formatting, you have to use the CSS attribute called mso-number-format. A few values for this mso-number-format attribute are described here - http://www.agoric.com/sources/software/htmltoExcel

I have an article on generating Excel sheets dynamically using this Office XML and CSS technique here - http://eggheadcafe.com/tutorials/aspnet/6e1ae1a8-8285-4b2a-a89b-fafc7668a782/aspnet-download-as-wor.aspx

Based on the code there, I also cooked up a quick and dirty example on preserving number formatting here - http://www26.brinkster.com/mvark/codegallery/Export_to_excel_with_number_formatting.aspx

Hope this helps.

To have this working with a Master Page you need to override

public override void VerifyRenderingInServerForm(Control control){}

in your code.

As per a bug. Read here http://msdn2.microsoft.com/en-us/library/system.web.ui.page.verifyrenderinginserverform(vs.71).aspx

Hi Matt. Great blog. Question/Challenge for you. If the export to excel takes a while it would be nice to place a "please wait" message or an progress bar on the page. Two reasons for doing this: Keep the user from navigating away, thus loosing the response from the server that are on its way, and second letting the user know something is happening...

Any ideas on how to do this? I tried to do some javascripting showing a div, but as all the response is cleared and filled with the export nothing no javascript method for hiding that div on the current page could be run...
Would be nice if you had any ideas.

br. Børge

Matt,

What happens if were exporting the whole grid and the grid hosts more that ~65000 rows? I believe there is a certain limit on how many rows we can have on single worksheet...

PS: I am travelling right now, and there is no way for me to verify this scenario.

Well the limitation is within Excel itself (pre 2007) and is not a problem related to gridview and the export... Also the export generates plain html so calling this is an export to excel feature is not totally correct. More of a export to HTML function. The function works well because of the fact that Excel can sucessfully import HTML tables.

I was trying to implement a new feature into one of my applications about exporting gridview of a large amount of data to an excel sheet and sending it to a specific email address as an attachment. I googled as "export gridview to excel" and this blog was on the first page. And, you had the exact solution that I was absolutely looking for. Thanks a million for sharing it!

Simple, easy and to the point - Excellent code :)

I have nested header rows added thru rowdatabound event but this code only picks up the original header. Im still figuring out how to get access to those additional header rows while exporting ..

I recently published this "free" custom control [http://www.codeplex.com/ExportToExcel] that exports the grid view to excel. You do not have to write any code [zero code]. Simply drag and drop the control and start using it.

Cool, works like a charm!

I would say sincerely this article helps a lot, to considering so many rumors that wouldnt work.

I cant figure out how to hide some columns upon exporting. I have one BoundeField visible=false and TemplateField, i do not want them to be in .xls Anybody can help?
Thanks

Matt,

A good work around for handling GridView rendering problem.
It was a great help to me.

Any ideas on how to retain the same formatting and look as GridView control. Where GridView control presentation is based on stylesheet.

I tried adding background for each row, but when it is exported to excel the whole row have a background color.. any ideas how the color will only be implemented in 2 cells same with the gridview that contains 2 cells.

I added this code
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then
Dim drEmployee As DataRow = CType(CType(e.Row.DataItem, DataRowView).Row, DataRow)
If drEmployee(0).ToString = "Item" Then
e.Row.Style.Add("Background-color", "Red")
e.Row.Font.Bold = True
End If
End If
End Sub

Check this "Free to use" control that helps you with formatting.

http://www.codeplex.com/ExportToExcel

So nice dude! thanks, awsome!

Hi Matt, Is it possible to prevent the columns that are set to visible=false from getting exported ?

Simple, easy and to the point - Excellent code.

And also in my application i would like export the GridView data to PDF file.

its great help me
Advance thanks.

I want to use this code but I am using AJAX on the page, specifically the progress bar for long post backs.

I can only get this code to work if I set EnablePartialRendering="false" in the ScriptManager tag.

The Excel export works fine, but I lose the AJAX progress animations.

Is there a way to keep both??????

Happy, happy, joy, joy.
This worked the first time!
Even the paging and sorting!

it works for the single gridview control. but i have grid with in a grid.do u have any sample to reach this requirement.

Thanks,
Ram

Naresh,

In order to get it to work with Ajax you will need to setup the button that is making the call to export as a PostBackTrigger on the UpdatePanel that it is in.

This article was very helpful, thank you.

I have a little problem - I have reports with about 60,000 rows i want to export. Since it is impossible to export them to Excel, I thought to export to CSV.
Can I still use the class provided? If so, What changes should be made?

Thanks...

Hi Matt,

Thanks for the code. Ive implemented it into my application and it works fine. But I have the same concern as Axeman and Borge Hansen. How do we put a control so that it will return an Error Message to user if the row exporting is more than the max row (65536 rows).

I have tried this code, but seems like the rows.count will take too much time if we have more than 10k rows.

if (this.gvCustomer.rows.count
{
this.gvCustomers.AllowPaging = false;
this.gvCustomers.DataBind();
GridViewExportUtil.Export("Customers.xls", this.gvCustomers)
}

Please advise and thanks in advance.

--------------------------------------------------------------
Posted by: Børge Hansen | September 1, 2007 07:53 AM

Matt,

What happens if were exporting the whole grid and the grid hosts more that ~65000 rows? I believe there is a certain limit on how many rows we can have on single worksheet...

PS: I am travelling right now, and there is no way for me to verify this scenario.
-----------------------------------------------------------------
Posted by: Axeman | October 23, 2007 10:33 AM

This article was very helpful, thank you.

I have a little problem - I have reports with about 60,000 rows i want to export. Since it is impossible to export them to Excel, I thought to export to CSV.
Can I still use the class provided? If so, What changes should be made?

Thanks...
-------------------------------------------------------------------

This was great, especially after trying other versions found on the web. I am using this on an intranet.

Is the nested gridview code that William Eaton mentioned
available?

THANKS

This code is great and it so easy to integrate with my system. Thanks.

hi Matt,

Its a nice feature. Its flexible and nice to use tool.

I have a problem.

when I run this tool it works perfect in a page.

but it doesnt work if you use master pages. Then you get the following error:
(1)
Control gvUserDetails of type GridView must be placed inside a form tag with runat=server.

then I add :
gvUsers.AllowPaging = False
gvUsers.DataBind()
calling tool GridViewExportUtil.Export("Users.xls", Me.gvUsers)

then I get this error:
(2)
Sys.WebForms.PageRequestMangerParserErrorException: The mesage received from the server could not be parsed. common Causes for this error are wehn the responses is modified by calls to Resonsponse.write(), repponses filters, HttpModules, or server trace is enabled, Details: Error parsing near

I know some of you have similair problems. Was someone able to solve this problem?cheers, mesutEven adding next statement did not solve.
Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
Return
End Sub

Bruce et al,

I had the same
Sys.WebForms.PageRequestMangerParserErrorException
issue. I was using an UpdatePanel.

I moved the button outside the UpdatePanel and it worked!

I use a MasterPage as well, had to add the override method.

Awesome job Matt.

Im also looking for a way to not export columns that are marked as Visible = false. My application allows users to select columns for a report and then export it to excel. The behavior Im getting is that the columns are put in with the expected header and footer, but the fields in the data rows is blank. I had to go and mark them Visible = true before the export, but Id rather the columns not show at all. Any way to accomplish that?

Man... you save my life with this.. Thank you..

You can also download sourcecode to export GridView to Excel from this linkhttp://www.dotnetrobert.com/dotnet/ExportGridViewtoExcel/tabid/95/Default.aspx

THANK YOU VERY MUCH
I FOUND THIS SITE VERY HELPFUL

thanxxxxxxxxxxxxxxxxxxxxxxx
a 1000 000

Maybe you can try http://www.gridviewtoexcel.com

Great article ¡¡, pass information from gridview to excel sounds rare to me, under the fact that reporting can be created to DB level, but excel gives more standard I think, there are more persons that uses excel that DB ... great conversion code¡¡

Hi there

for everybody battling to get the style to export; look at this:

http://www.gridviewguy.com/ArticleDetails.aspx?articleID=197

Hope that sends you in the right direction!

Does anyone has the code to export nested gridviews?

Thanks

Thanks very much for this code Matt, and thanks to Naresh for advice on the button within an update panel.

You code works like a charm Matt. It was just plug and play. Thanks a lot.

My second column has a number in it eg 001234. how do i put a text formatting on it to avoid it formatting as 1234?

Has anyone figured out how to export ONLY the visible columns in the grid?

Can you please post the code for demo application which includes import only this page, all pages and include grid lines options.

Real Excel XML and XLSX Export in http://www.gridviewtoexcel.com

This solved some of my problems, but for some reason when I open the exported file, I only have headers, no data! I didnt make any alterations to this code itself, so why am I not getting data in my Excel sheet?

Is there a way to set pagelayout to landscape dynamically?

Reply Ifeoma:

To remove columns. Add a int16[] parameter to the export function and call a new method (displayed below) instead of PrepareControlForExport in the export function.;

code:
private static void PrepareControlForExportMaster(Control control, Int16[] ListColumnsToRemove)
{
for (int i = 0; i
{
control.Controls.RemoveAt(ListColumnsToRemove[i]);
}

GridViewExportUtil.PrepareControlForExport(control);
}

it saved my lots of time..

Thanks a lot

I would recommend this page
as wellhttp://steveorr.net/articles/ExcelExport.aspx

Hi Matt,

Great pieace of code.However can i use datagrid instead of a dataview, Any issues with this...can you plz post datagrid code for the same.i want to pass data grid instead of dataview

Thanks.
Charles

Great pieace of code.However can i use datagrid instead of a dataview, Any issues with this...can you plz post datagrid code for the same.i want to pass data grid instead of dataview

Worked great for this beginner. Makes me feel I have moved far beyond the canned, default gridview. But, when I added the code to a gridview containing this column:

It produces a spreadsheet, but it is full of the pages graphics, instead of the data table. Does not matter if Visible=False or True.
I removed the invisible field, but can that be checked for?

Very nice article and code, Matt ! Thanks!
I used it but I dont solve a problem with formatting data in the excel file.
I explain:
first column, €:
in GridView I have 1.455,00 but in the excel file i find 1455 (GridView format column: 0:###,##0.00)

third column, customer code:
in grid view I have 00100 (string) but in the excel file I have 100.
What can I do?
Thanks for helping me!
Tappat

I wish to explain better my problem. I wrote something yesterday.

Following Matts code takes data from a gridView and downloads an excel file on the client.
It runs.
The only problem I have is with the formatting data

I have a column like this in my GridView:

sales € (european currency)
1.234,00
12.345,78
689,00
- 527,89
With Matt Berseths code I obtain on the client an excel file with following result:

1.234
12.345,78
689
- 527,89

If I implemet the class code with one of following lines:

****
row.Cells(5).Text = FormatCurrency(row.Cells(5).Text, 2)
row.Cells(5).Text = FormatNumber(row.Cells(5).Text, 2)
row.Cells(5).Text = CDbl(row.Cells(5).Text)

****
it returns in debug following error: Cast not valid from string "- 527,89" to type Double.

If I implemet the class code with following line:
****
row.Cells(5).Text = Format(row.Cells(5).Text, "##,##0.00"),
****
it creates the excel file and downloads it on the browser but the result in the column cells is: ##,##0.00

How can I obtain an excel file with right data?

Regards! Tappat

Public Shared Sub Export(ByVal fileName As String, ByVal gv As GridView)
HttpContext.Current.Response.Clear()
HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", fileName))
HttpContext.Current.Response.ContentType = "application/ms-excel"
Dim sw As StringWriter = New StringWriter
Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
Dim table As Table = New Table
table.GridLines = gv.GridLines
If (Not (gv.HeaderRow) Is Nothing) Then
GridViewExport.PrepareControlForExport(gv.HeaderRow)
table.Rows.Add(gv.HeaderRow)
End If
For Each row As GridViewRow In gv.Rows
GridViewExport.PrepareControlForExport(row)
°°°°
add new line code here
°°°°
table.Rows.Add(row)
Next
If (Not (gv.FooterRow) Is Nothing) Then
GridViewExport.PrepareControlForExport(gv.FooterRow)
table.Rows.Add(gv.FooterRow)
End If
table.RenderControl(htw)
HttpContext.Current.Response.Write(sw.ToString)
HttpContext.Current.Response.End()
End Sub

i m having a button and text box in the footer of the gridview to add the data in the table....

when i try to export the data into excel file it gives me following error--Control BtnADD of type Button must be placed inside a form tag with runat=server.

Matt,

What an excellent solution. I have spent the best part of two hours researching this very problem. Until I ran into your site, the solutions I found were, at best, partial. This does everything I want.
Congratulations.
Your site is now on my Favourite links.
MarKo

Why does the generated excel file not have lines for the rows and columns?

If I want to leave a blank line between rows, how do I change the code?

I have an editable gridview but in the export to excel I dont want the edit and delete captions to appear. I turned off the literal adding code for buttons but it still prints a blank column in the excel spreadsheet. How can I tell the program not to print anything if it is a button?

This article is exactly what I have been looking for. All articles found dont deal with the paging aspect of export data and I am glad I found yours. Great Work and Thanks A million!

Hi Matt,

This code works great and my site uses it lots. However, I have 1 gridview of about 45,000 rows and about 80 columns. Im getting a outfomemory exception when the table produced from this gridview is rendered

render the table into the htmlwriter
table.RenderControl(htw)

Do you know of a way round this problem.

Thanks again

Elliott

Hi!
This is awesome!

But I need additional functionality, I want the user can select what fields to export. How can I accomplish this?

many thanks,
edcon

how can i skip the first column while exporting?

If you want gridlines in your spreadsheet, add the line (*):

Table table = new Table();

table.GridLines = GridLines.Both; //(*)

How to do the validations in gridview cells ?

I cant make the code work with nested gridviews. Does anyone know how to do it ?

hii Matt,
Its wonderful code.
But if i have template field, its not working.
Please help !!!
Thanking You
Suvasish

Thanks

Its veru Useful to me..

I tried using this but when i opened the excel file, there was no content. I also tried the suggested solution above:

Protected Sub btnExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcel.Click
Me.btnExcel.AllowPaging = False
Me.btnExcel.DataBind()
Me.btnExcel.GridLines = GridLines.Both
GridViewExportUtil.Export("Data.xls", Me.grdView)
End Sub

but I still see no content. The header line export fine though. Any other suggestion?

Thanks.

I tried in many ways to make this code work with nested gridviews. I find this code verry nice to work with and it is a pitty to not use it for nested gridviews. I didnt find the solution myself, but a friend gave me a helping hand (thank you Claudiu). So, for anyone who wants to export nested gridviews, heres the code:

using System;
using System.Data;
using System.Configuration;
using System.IO;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public class ExportToXls
{
public static void Export(string fileName, GridView gv, string title)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";

using (StringWriter sw = new StringWriter())
{
HtmlTextWriter htw = new HtmlTextWriter(sw);
try
{
// render the table into the htmlwriter
RenderGrid(gv).RenderControl(htw);
// render the htmlwriter into the response
HttpContext.Current.Response.Write("" + title + "");
HttpContext.Current.Response.Write("Report created at: " + DateTime.Now.ToString());
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
finally
{
htw.Close();
}
}
}

private static Table RenderGrid(GridView grd)
{
// Create a form to contain the grid
Table table = new Table();
table.GridLines = grd.GridLines;

// add the header row to the table
if (grd.HeaderRow != null)
{
ExportToXls.PrepareControlForExport(grd.HeaderRow);
table.Rows.Add(grd.HeaderRow);
}

// add each of the data rows to the table
foreach (GridViewRow row in grd.Rows)
{
//to allign top
row.VerticalAlign = VerticalAlign.Top;
ExportToXls.PrepareControlForExport(row);
table.Rows.Add(row);
}

// add the footer row to the table
if (grd.FooterRow != null)
{
ExportToXls.PrepareControlForExport(grd.FooterRow);
table.Rows.Add(grd.FooterRow);
}
return table;
}

private static void PrepareControlForExport(Control control)
{
for (int i = 0; i
{
Control current = control.Controls[i];
if (current is GridView)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, RenderGrid((GridView)current));
}
if (current is LinkButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
}
if (current is Button)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as Button).Text));
}

else if (current is ImageButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
}
else if (current is HyperLink)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
}
else if (current is DropDownList)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
}
else if (current is CheckBox)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "Da" : "Nu"));
}

if (current.HasControls())
{
ExportToXls.PrepareControlForExport(current);
}
}
}
}

I added in plus, a parameter wich is the title that it will apear in excel file. Happy coding.

Does anyone know how image button columns can be prevented from exporting...no columns, no headers, no images, etc. i only want those data fields. This question has been asked many times but no reply has came. We seem to be able to remove the image from the excel export if a control but i dont even want to replace that with a literal.

To remove columns from your exported file please do the following inside your function PrepareControlForExport:

Dim columnsToExclude As New SortedList
columnsToExclude.Add(0, 0) column 1
columnsToExclude.Add(1, 1) column 2
columnsToExclude.Add(2, 2) column 3

Do While (i
Dim current As Control = control.Controls(i)

If columnsToExclude.ContainsValue(i) AndAlso (current.GetType.ToString = "System.Web.UI.WebControls.DataControlFieldHeaderCell" OrElse current.GetType.ToString = "System.Web.UI.WebControls.DataControlFieldCell") Then
current.Visible = False
i += 1
Continue Do
End If

Then you just continue on with the remainder of the existing function with the line:
If (TypeOf current Is LinkButton) Then

I have created dummy grid and put my datatable in and render it successfully.
Now I need to wrap some column data what can I do

Your implementation for handling "all" rows is very clean and helpful. Ive been pulling my hair! Thank you for posting it.

I am doing something similar in C# to download a gridview into an Excel spreadsheet. I was trying to find out how it formats the excel file. I have some date fields that appear to look ok on the Excel Spreadsheet but if I try to copy them out to another spreadsheet the dates get butchered?

Thanks man. Preciate it :)

Soeren from Denmark.

Are you able to use this and save the excel file without the popup box coming up? I just want the file saved without user interaction

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

Works perfect, Thanks!!!

Your excel export works perfect! Thank you!

This is fantastic it works for me.... Your really a Ajax Guru!!! Two Thumbs up for you bro

How I can export 2 gridview to one Excel file in different sheets?

This is a good blog, and very helpful to me.
but i have to meet some problem in this method.
can u help me solve the problem?
the problem is i want to export all data from the grid view.
but my grid view have the paging function. I use this code to write the function
protected void BtnExportGrid_Click(object sender, EventArgs args)
{
this.gvCustomers.AllowPaging = false;
this.gvCustomers.DataBind();
GridViewExportUtil.Export("Customers.xls", this.gvCustomers);
}

but when i export out the data, just get the code only.

I have now upgraded my users to MS Office 2007, I now get the following error message, Any ideas how this can be avoided?

"The file you are trying to open, Export.xls, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from an trusted source before opening the file. Do you want to open the file now."

I have now upgraded my users to MS Office 2007, I now get the following error message, Any ideas how this can be avoided?

"The file you are trying to open, Export.xls, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from an trusted source before opening the file. Do you want to open the file now."

Thanks, worked like a champ

This link explains MS Office 2007 behavior

http://blogs.msdn.com/vsofficedeveloper/pages/Excel-2007-Extension-Warning.aspx

Really useful and clear, once again Matt ! Thanks and I am completing a web app here that is working real well, now with a couple of your additional examples and suggestions it is looking stellar - top notch !
All the best !

its nice code...

Matt,
This is exactly what I was looking for, I tried the same approach few days back, but that was not so customisable and also Matt, I am still searching if there are any way I can do the reverse process with these amount of functionality and customisation. Means I can import from CSV or XLS file to Grid and update in database, for new as well as for existing data. I you can guide me through this it will be very helpful for me.

Matt,
how would you export selected(via check box) rows by user to excel.
anyone?

THANK YOU!!!
this is the ONLY script that actually worked with a simple copy and paste!

Hi,

I am not sure if you have a solution to this or not. when ever we export gridview data to excel, the filename will have [1] appended to it at the end. for example if the filename that we set is "customers.xls" and when we open it, the filename will be set as "customers[1].xls". If you open it again, the filename will be set to "customers[2].xls". The sheetname will also be named in the same manner. This is causing problems if the user is trying to manipulate the exported data (for example pivoting the data etc). I have searched so many times in the net to find a solution but I am not able to find. If you have a solution to this issue, please let me know.

Thanks,
sridhar.

Simply awesome.

Thank you very much :-)

Thank you, class works great. Really helpful and useful.

Fantastic code! The only issue I ran into was while attempting to export on a content page that has a CSS linked on the master page. I get a missing file error to the CSS as others have blogged here. Other than that, great job! Thanks!

Hi Matt
I have a problem with Hyperlink column. In the excel file for the Hyperlink column value is always Blank. This column is in a template column. I do have other controls under template column and all those are working except this.
Am I missing something?
Thanks in advnce
Gagan

Hi Matt
I figured it out myself why hyperlink column was coming blank. It is because my value of Hyperlink column was between opening and closing tag in gridview.
Like
HyperLink Text
If you do like this then Text property always gives empty string and you will not get any value.

So it should be like this:

I just want to add it, as it may be helpful for someone else.

Anyhow your code is great, it helps me a lot.
Thanks
Gagan

AWESOME SCRIPT.

Its amazing that after all this time, your still getting traffic on this script!

Just seen this one!! You are a legend as always.. Thanks

Earlier have used Syncfusion for Exporting grid to excel and I was happy with it .. but I have to admit ur GridViewExportUtil is pretty cool !!

Rob Dude,

I ran across the same problem and solved it by injecting the styles from the stylesheet inline, rather than using a link to the file. The following is the code I used:

Public Enum StyleInjectionTypes
Inline
Link
End Enum

Adds the supplied stylesheet to the supplied HtmlTextWriter, using the supplied injectionType

The HtmlTextWriter to render the output to
The relative path of the file to get your styles from
A StyleInjectionTypes enumeration value, indicating how the CSS stylesheet should be rendered to the page.

Private Shared Sub AddStyleSheet(ByVal htmlTextWriter As HtmlTextWriter, ByVal relativeFilePath As String, ByVal injectionType As StyleInjectionTypes)
htmlTextWriter.RenderBeginTag(HtmlTextWriterTag.Head)
Select Case injectionType
Case StyleInjectionTypes.Inline
Dim FileStream As New FileStream(HttpContext.Current.Server.MapPath(relativeFilePath), FileMode.Open, FileAccess.Read)
Dim Reader As New StreamReader(FileStream)
htmlTextWriter.AddAttribute(HtmlTextWriterAttribute.Type, "text/css")
htmlTextWriter.RenderBeginTag(HtmlTextWriterTag.Style)
Dim InnerTextWriter As TextWriter = htmlTextWriter.InnerWriter
InnerTextWriter.Write(Reader.ReadToEnd)
htmlTextWriter.RenderEndTag()
Case StyleInjectionTypes.Link
htmlTextWriter.AddAttribute(HtmlTextWriterAttribute.Rel, "stylesheet")
htmlTextWriter.AddAttribute(HtmlTextWriterAttribute.Type, "text/css")
htmlTextWriter.AddAttribute(HtmlTextWriterAttribute.Href, relativeFilePath)
htmlTextWriter.RenderBeginTag(HtmlTextWriterTag.Link)
htmlTextWriter.RenderEndTag()
End Select
htmlTextWriter.RenderEndTag()
End Sub

The first part is just an enumeration to denote how you would like the stylesheet included in the response (linked or inline text).

The method adds the correct markup to the HtmlTextWriter for whatever type you choose, using the file you supply for the relativeFilePath parameter.

HTH,

Rob

great code thank you. i have nested header rows added thru rowdatabound that i would like to be passed to the export. is there any way to do this???

Great Code..
I faced one issue on exporting the current page, if data has values like 11-17 or 10-15; and you export them to excel they get converted to dates (Nov-17 and Oct-15).
How should i avoid that to maintain proper formatting on excel.
Thank you,

Regards,
Savitur

I want to export a nested datagird to a excel sheet. I am intrested only in datagrid and not in GridView . Plz help me with the sample code

Thanks in advance

thank you very much all the ideas that Ive found here have been helpful.

I have used your code. It works fine but when my gridview contains datetime values; then in Excel file it doesn't show that AM/PM part. How to accomplished this Sir?

Thanks
Prashant V.

Matt, Your code rocks! I am having a problem with the output that seems to have been noticed by others (tappat, JacobM and Ifeoma I.). The problem is numbers with leading zero(s) converting to integers (ex 0302 to 302) or numbers with decimal dropping the trailing zero(s) (ex 2.40 to 2.4 or 3.00 to 3). I have tried the same approach as tappat did with no avail. Got any ideas on how to solve this issue?

Thanks!
Mike D

Thanks for the solution. Its written clearly and concisely and works well.

Good effort, we can do loop thru data source also and creating customized way for html. We can also change colors and style.
But I need to do Allow Paging to false and re bind data get all data in single sheet.

I have a GridView that contains a checkbox column (is a database bit field in the database table it is linked to). When exported, it places a checkbox in the Excel file. I'd rather the text "True" or "False" appear than the checkbox. I tried modifying PrepareControlForExport() as follows:

...
ElseIf (TypeOf current Is CheckBox) Then
control.Controls.Remove(current)
If CType(current, CheckBox).Checked Then
control.Controls.AddAt(i, New LiteralControl("True"))
Else
control.Controls.AddAt(i, New LiteralControl("False"))
End If
'control.Controls.AddAt(i, New LiteralControl(CType(current, CheckBox).Checked))
End If
...

This DOESN'T work, furthermore I placed a breakpoint and the code doesn't even execute. How can I get a texual representation of a checkbox to cross over into Excel (True/False, On/Off, 1/0, whatever)?

Thanks, --David

I retract my previous post. My page had EnableEventValidation="false" and also had the stub function Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
End Sub.

Once I implemented the full up class as advertised and eliminated all those historical remnants from past tries, it DOES export TRUE and FALSE for my checkboxes.

--David

Excellent article Matt.

Here's an example in VB.NET which allows you to select which rows in your gridview get exported.

http://blog.evonet.com.au/post/2008/06/17/Gridview-that-exports-selected-rows-to-Excel.aspx

Cheers,
Bartek

The seconds and Time zone of my DateTime column is not showing up in the Excel file. The reason is due to the custom format pattern of EXCEL which is "m/d/yyyy h:m", therefore how can I modify the code to change the EXCEL format to "mm/dd/yyyy hh:mm:ss AM/PM". If I manually modify the Format cell pattern to the pattern above than the seconds and TimeZone is shown. However I want this automated, I don't want the user to have to type is a Format cell pattern in order for the dateTime to display correctly. Other than this issue everything else is working just great.

Awesome code - really helped me out - thanks so much Matt, you deserve a medal for this!!

Hi. Great code. I am trying to use it on my app. One question though. I have a cell that only datagrid that is string but contains this kind of values, "080331E1", "080401E1", "080402E1", etc. When exported they come out in excel as 803310, 804010, 804020, etc. Is there anywhere I can make it so that it will export the exact same data.

Thanks,

how can i remove the command field from the grid view before exporting to excel ?

Hi, I want to export data to excel via ASP.Net, but One of the Column has some Data Validation like,
It should contain only 15 Characters (In MS-EXCEL VIEW -> Data Validation -> Text Length -> 0 To 15)
How can i make this kind of Excel file with formatted column, can i set every column settings from asp.net before write data to excel???

If anybody has answer post ur comment here as well as please send me via email....

Thanks in Advance

The method is throwing error if the extender control is placed inside the griview... how to solve it.. can u please suggust me on it

Thanks, I just solve my part only in few minutes.

Hi, great sample once again - thanks very much.

I have a problem - when I try to use this code over https, I get an error along the lines of "Internet Explorer could not download CallingPageName.aspx from site www.bla.com..."

Any ideas?

Great code.. Do you have any plans (and is it possible) to have something similar for ListView?

Great work , It helped me a lot. thanks

Great work , It helped me a lot. thanks

I have to concur. This was an outstanding article, and it solves a fundamental problem that I've been trying to do, off-and-on, for awhile now.

Great job!!

Sorry for the dumb question - I am pretty new to ASP.NET but I have an existing page using gridview and I'm trying to add this Excel export capability using the VB version.

I always get this error:

Compiler Error Message: BC30451: Name 'GridViewExportUtil' is not declared.

How do I declare or import the GridViewExportUtil class? The sample code fails with this same error.

Thanks

Does this control support multiple language, its one of the issue iam facing every day.