CodeSnip: Export a DataGrid to a Formatted Excel Spreadsheet
page 1 of 1
Published: 03 Oct 2005
Unedited - Community Contributed
Abstract
In this code snippet, Sameer Lal provides the simple code necessary to export a DataGrid to an Excel spreadsheet, which allows the user to work with the data on his or her own computer. The Excel spreadsheet will be formatted and correctly identified as an .xsl file.
by Sameer Lal
Feedback
Average Rating: 
Views (Total / Last 10 Days): 29684/ 38

It is a simple task to export data from an ASP.NET DataGrid to an Excel spreadsheet. In this code snippet, we will transfer the DataGrid output to an Excel spreadsheet format by manipulating the MIME type of the HTTP response. The content for the Excel spreadsheet is obtained by using the RenderControl method of a web server control. This method writes its output to an HtmlTextWriter object, which is then written out to the HTTP response stream. We can manipulate this process so that we can apply a few formatting expressions such as inserting a line, formatting text to bold, and extending a column's span.

Scenario:

On the web page we present a DropDownList control from which the user can select an employee's name. When the Submit button is clicked, the selected employee's name is displayed with a Label control and the employee's details displayed with a DataGrid control..

When the Export button is clicked, the same DataGrid control is used to prepare an Excel spreadsheet, where formatting is done through code. In order to return the Excel spreadsheet with an appropriate filename and type, we make use of the AddHeader method of the HttpResponse object.

Listing 1: Web Form Code-behind

Sub Bind()
    Dim strName As String = DropDownList1.SelectedItem.Text
    LblEmpName.Text = strName
    OleDbCommand1.CommandText = _
        "SELECT * FROM tbEmployee WHERE EmployeeName Like '%" & strName & "%'"
    Dim adp As New OleDb.OleDbDataAdapter(OleDbCommand1)
    Dim ds As New DataSet
    adp.Fill(ds)
    DataGrid1.DataSource = ds
    DataGrid1.DataBind()
End Sub

Private Sub Submit_Click(sender As Object, e As EventArgs) Handles Submit.Click
    Bind()
End Sub

Private Sub Export_Click(sender As Object, e As EventArgs) Handles Export.Click

    ' Fill the DataGrid
    Bind()
    
    'Provide a filename
    Response.AddHeader("content-disposition", "attachment;filename=Employee Report.xls")
    
    ' Set MIME type to Excel.
    Response.ContentType = "application/vnd.ms-excel"
    
    ' Remove the charset from the Content-Type header.
    Response.Charset = ""
    
    ' Prepare to export the DataGrid
    Dim strw As New System.IO.StringWriter
    Dim htmlw As New System.Web.UI.HtmlTextWriter(strw)
    
    ' Use the Label control to add the employee's name.
    strw.GetStringBuilder.Append("<B>")
    strw.WriteLine("Employee Report:  ")
    strw.GetStringBuilder.Append("</B>")
    strw.GetStringBuilder.Append("<br>")
    strw.GetStringBuilder.Append("<br>")
    strw.GetStringBuilder.Append("<table>")
    strw.GetStringBuilder.Append("<tr>")
    strw.GetStringBuilder.Append("<td colspan=3 bgcolor=Gainsboro align=center>")
    strw.GetStringBuilder.Append("<B>")
    LblEmpName.RenderControl(htmlw)
    strw.GetStringBuilder.Append("</B>")
    strw.GetStringBuilder.Append("</td>")
    strw.GetStringBuilder.Append("</tr>")
    
    ' Use the DataGrid control to add the employee's details
    DataGrid1.RenderControl(htmlw)
    
    ' Finish the Excel spreadsheet and send the response
    strw.GetStringBuilder.Append("</table>")
    Response.Write(strw.ToString())
    Response.End()
    
End Sub

I hope you will find this code snippet to be useful.



User Comments

Title: Gazi Tasarım   
Name: Gazi
Date: 2010-02-01 6:06:17 AM
Comment:
That is very useful. Thank you very much...
Title: Export To MS Excel Sheet   
Name: Tatya
Date: 2009-09-22 5:25:35 AM
Comment:
I Don't know to how to Export Excel
pls Tell me something about
Title: Export To Excel   
Name: Venkatesh
Date: 2009-07-31 5:51:07 AM
Comment:
The GetStringBuilder().Append doesn't work.... in C# ASP.NET
Any namespaces have to be included, i am getting an error GetStringBuilder it is function and used like property;
Title: Show Progress Indicator   
Name: Dhana
Date: 2008-11-18 8:04:24 AM
Comment:
Anybody help me to show Progress Indicator while preparing the Excel Data

Thanks
Title: how to format row height ,,colomn width   
Name: rahul
Date: 2008-11-14 12:43:20 PM
Comment:
hey please anyone can give comp eg to format row height and colomn width before exporting to excel..

thanks in advance
Title: new worksheet   
Name: ryan
Date: 2008-11-03 6:37:15 AM
Comment:
hi.. this CodeSnip works!!, but how do i need to add another (new)sheet in the Employee Report.xls workbook?? pls help me
Title: Exporting DataGrid putting reports headers into Excel File in asp.net using VB.Net   
Name: Dnyandeo Narkhede
Date: 2008-05-16 9:17:19 AM
Comment:
Its really good one i have tried for this and it works excellent.
Title: Export data to excel(set allignment)   
Name: JEFF
Date: 2008-01-11 4:47:25 AM
Comment:
hi!anyone can help me?
how to set alignment to right when Export data to excel?TQ
Title: Only export grid portion   
Name: Good
Date: 2007-07-18 3:20:15 AM
Comment:
code convert the entire page not only Grid portion to excel sheet.In excel we get other portion as a blank instead of cells. I want only datagrid portion to be export to excel sheet.
Title: Datagrid Inside a datagrid   
Name: confused!
Date: 2007-05-17 2:19:13 AM
Comment:
hi,
i have a datagrid inside a datagrid and i want to export this to excel with the values of the inner datagrid. how do i do it? plz help
Title: Doubt   
Name: Prem
Date: 2006-11-24 11:21:25 AM
Comment:
How to use this code for datagrid with paging
Title: Our application employees this but I have problem   
Name: Harry Nehman
Date: 2006-11-05 5:36:51 PM
Comment:
Our application employees this technique but I have a problem returning to the page I have a button on to call this action. When I return to the calling page that has a fair amout of JavaScript on it for menu dropdowns, it appears that processing of the calling page does not continue and I lose the drop down menu items.

I have tryied to use response.flush() instead of response.end() in the hope to continue processing on the page after the excell is written, but that did not work.

Any suggestions to get the calling page to continue processing the scripts?
thank you
Title: Good   
Name: Gagan
Date: 2006-10-12 10:47:59 PM
Comment:
Great
Title: Setting Column Width   
Name: Shyam
Date: 2006-09-25 5:24:04 PM
Comment:
Can anyone tell me how to set the width of Column

it would of great help!!
Title: Whats actually needed is given   
Name: Scira
Date: 2006-07-29 5:59:19 AM
Comment:
Thanks,I just started with google in search of this export of datagrid to excel.What I needed to my appln,I got in this
Title: Problem !!!!!   
Name: Lobraza
Date: 2006-07-24 6:45:45 AM
Comment:
I have this process in my page under a click event of a button and it works fine, but I need that the page will be updated at the same time. I mean, I have two grids in my page, when the user clicks on the button I export the content of the first one to excel, save a record in a databse and I need the second one updates with this new record. I have the code but I think the line Response.End() ignore the rest of the code.
Please could you help me???
Title: Very Nice   
Name: nihas
Date: 2006-07-19 7:14:22 AM
Comment:
Nice article
Title: Good   
Name: Chandan Agarwala
Date: 2006-07-14 1:09:19 AM
Comment:
Good, But if the Excel formmating is provided then it will be better. This code convert the entire page not only Grid portion.
Title: Fantastic   
Name: Prasanth Joseph
Date: 2006-06-19 5:18:39 AM
Comment:
Iam looking for the same stuff.I got it and working properly
Thank you Sameer.
Title: Help me out   
Name: Maya
Date: 2006-06-16 1:54:18 AM
Comment:
I have hyperlinks in my data grid and i dont want them in the excel to which i have exported this datagrid. Please help me out how to do thaT?
Title: Question   
Name: Raghav(RGV)
Date: 2006-06-08 8:45:44 AM
Comment:
How about entering a new sheet and adding data to it.
Title: Excellent advice   
Name: Julia Boswell
Date: 2006-05-17 10:33:56 AM
Comment:
This is great advice. I've been searching for simple export to excel code for ages and most people make it very complicated. This is very simple. However is there any advice on formatting the excel columns?
Title: Great article   
Name: anuj
Date: 2006-05-15 7:12:41 AM
Comment:
Very Good Article. I searched the web but this is simply the best
Title: Extra   
Name: Sami
Date: 2006-05-12 10:33:40 AM
Comment:
I am trying get this work. It work but I need it to send to digital camera. Can you give me all code for that, and I need bank application, can you give me code and demo for that, and I need new shoes, can you give me that. Also can you write me good resume, give me that. Just send me give all you have belong to you.
Title: Nice   
Name: Archit
Date: 2006-05-05 2:38:02 AM
Comment:
Thanks it help me a lot
But if i have hyperlinks in my datagrid can i remove those hyperlink or change those hyperlink into labels
Title: Oledb   
Name: Alwin
Date: 2006-04-12 2:27:12 AM
Comment:
I want to link this with Stored procedure in Access 2002. Can u make it working
Title: Awesome   
Name: Dr. Hacker
Date: 2006-03-24 10:58:02 AM
Comment:
Thanks for an excellent tip about the Microsoft Maze.
Title: good   
Name: Bala Lakshmana Moorthy .M
Date: 2006-03-21 12:45:31 AM
Comment:
This is a good one
Title: Nice   
Name: Anshul
Date: 2006-02-16 6:40:36 AM
Comment:
Can u tell how to export Datagrid (with linkbutton) to Excel
Title: Helpful code   
Name: Symmetry
Date: 2006-02-09 4:50:14 PM
Comment:
The addheader part of the code helped me. Now the user can save and get print preview of the excel sheet before printing.
Title: how to connect thru database   
Name: hiren
Date: 2005-12-30 7:56:17 AM
Comment:
thanks for code
but facing problem in connection.
pls send me codes for connection.
asherhiren@gmail.com
Title: nice   
Name: semi
Date: 2005-12-05 5:58:36 AM
Comment:
Can U provide Nice Demo
Title: Mr   
Name: Michael Lim
Date: 2005-12-05 1:46:01 AM
Comment:
Superb, this really helped me in my codes, thanks a lot, Sameer....
Title: Very Useful and easy   
Name: Ramesh
Date: 2005-11-18 8:36:52 AM
Comment:
Hi Sameer,
This is looking very nice and very easy to understand.
Thanks foryour great help on this.
Title: More Faster   
Name: Samik Dutta
Date: 2005-11-10 6:28:13 AM
Comment:
Hi,
I am using the same line of code but I find my application is taking very much time to generate the excel file when the amount of data is about more than 30000; please suggest.
Samik.
Title: Hello   
Name: Sameer
Date: 2005-11-04 8:56:49 PM
Comment:
Brett, Thank you very much for putting in this info. You are the MEN.
Title: RE: System.Web.HttpException   
Name: Brett
Date: 2005-11-02 1:14:22 PM
Comment:
sam:

You need to turn off the automatic sorting before you do a databind. The render only handles literal controls and the headers of each column becomes a linkbutton with sorting on. You will encounter the same problem if you have dropdowns, checkboxes, etc. inside your datagrid or footers.

In my Export_click I turn off paging, sorting, and hide the footer. That takes care of the 3 most likely
Title: Programmer   
Name: sam
Date: 2005-10-31 9:15:04 AM
Comment:
Hi,
I received this error:
Control 'Datagrid1__ctl2__ctl0' of type 'DataGridLinkButton' must be placed inside a form tag with runat=server.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: Control 'Datagrid1__ctl2__ctl0' of type 'DataGridLinkButton' must be placed inside a form tag with runat=server.
Title: Datagrid2 Excel   
Name: kamaraj.v
Date: 2005-10-24 10:39:21 AM
Comment:
Hi...
Really your code is useful to me. Thanks a lots to u
Title: a question   
Name: sen
Date: 2005-10-21 5:19:13 AM
Comment:
Nice one. But i wanted to know if the column width of excel sheet can also be set by using any method.would be really helpful if coumn formating could be done. Is there a way?
Title: Great Work!!!   
Name: Luca Mellano
Date: 2005-10-19 5:55:47 AM
Comment:
Great Job. IT helped me a lot!

I've one problem! How can I create an Excel page to print horizontally?

Thanks.
Title: very good   
Name: venkat
Date: 2005-10-17 3:05:30 AM
Comment:
very nice code.It helped me a lot.thanks.....
Title: Good Work!!!   
Name: Priya
Date: 2005-10-14 4:10:35 PM
Comment:
Well done!!!!!!!
Title: Mr   
Name: Steve
Date: 2005-10-13 2:38:45 PM
Comment:
Very simple and useful.Excatly what I was looking for.
Title: Does it work for all Versions of Excel   
Name: Frank
Date: 2005-10-13 4:05:09 AM
Comment:
Does this work for all versions of Excel. And how do we handle Clients that do not have Excel. Can we Save it in a txt file.
Title: well done   
Name: Bejoy Maliakal
Date: 2005-10-07 5:43:18 AM
Comment:
Thanks, it was very use full
Title: live demo   
Name: eds
Date: 2005-10-05 9:56:55 PM
Comment:
can you provide a live demo?
Title: Nice   
Name: Simone Busoli
Date: 2005-10-03 4:52:45 AM
Comment:
Nice article, has as surplus the addition of formatting.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2017 ASPAlliance.com  |  Page Processed at 2017-07-20 6:36:47 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search