Export to Excel is one of the most common functionalities required in ASP.Net pages. Users can download the data from the datagrid into an Excel spreadsheet for offline verification and/or computation. This article includes the source code for such functionality.
How it works
This main functionality to Export a datagrid from an ASP.Net Web Form to an Excel format is actually very simple. There are several solutions for this implementation and in this example we will convert the datagrid to excel format by manipulating the MIME type (media type or Content Type) of the Response. The RenderControl method available in the .Net Framework provides the server control content to an HtmlTextWriter which is subsequently written out to the Response Stream.
- private void Button1_Click(object sender, System.EventArgs e)
- {
- //export to excel
- Response.Clear();
- Response.Buffer = true;
- Response.ContentType = "application/vnd.ms-excel";
- Response.Charset = "";
- this.EnableViewState = false;
- System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
- System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
- this.ClearControls(dg);
- dg.RenderControl(oHtmlTextWriter);
- Response.Write(oStringWriter.ToString());
- Response.End();
- }
And just one more detail
There's just one thing to take care of. A run-time error occurs if the DataGrid contains any controls other than the LiteralControl. This means that enabling Sorting, Paging or adding Template Columns or Button columns to the datagrid can cause an error. There are several approaches to work around this limitation. We will remove all the non-Literal controls in the DataGrid and replace the controls with a text representation, where possible. To do so, we will make use of Reflection. instead of querying each type of control and working out a replacement.
For all controls that have a SelectedItem property, we replace the control with the literal value of the SelectedItem property of the control. This covers most lists. For all controls that have a Text property, we replace the control with the literal value of the Text property of the control. This covers TextBox, Buttons, Button Columns, TemplateColumns. We make an exception only for TableCell controls. This takes care of most of the cases and you can add more checks and balances as required. The only drawback for this generalized formula is the order of the controls within a single cell could get changed.
- private void ClearControls(Control control)
- {
- for (int i = control.Controls.Count - 1; i >= 0; i--)
- {
- ClearControls(control.Controls[i]);
- }
- if (!(control is TableCell))
- {
- if (control.GetType().GetProperty("SelectedItem") != null)
- {
- LiteralControl literal = new LiteralControl();
- control.Parent.Controls.Add(literal);
- try
- {
- literal.Text = (string)control.GetType().GetProperty("SelectedItem").GetValue(control, null);
- }
- catch
- {
- }
- control.Parent.Controls.Remove(control);
- }
- else
- if (control.GetType().GetProperty("Text") != null)
- {
- LiteralControl literal = new LiteralControl();
- control.Parent.Controls.Add(literal);
- literal.Text = (string)control.GetType().GetProperty("Text").GetValue(control, null);
- control.Parent.Controls.Remove(control);
- }
- }
- return;
- }
In our sample web form, we connect to the Sample Pubs SQL Server database and display the data from the Employees table. The sample datagrid uses paging and a dummy Edit Column.
Complete Code Listing
christo rajPosted Nov 29, 2011, 12:35 AM
hi buddy, its working for literal control,but i have attached lot of sublinks.that should be not show in excel...
Ade RuyaniPosted Oct 6, 2011, 3:12 AM
hi.. choksi,, can i learn your code?? and can i download your source file? Regards, Ade Ruyani
jitendra pateleditedPosted Jul 21, 2010, 4:02 AMEdited Nov 29, 2011, 8:45 AM
try { Literal.Text =(string)control.GetType().GetProperty("SelectedItem").GetValue(control, null); } catch { }
Shailender ReddyPosted May 28, 2010, 2:58 AM
Cannot export data in all the pages on the datagrid,able to export only first page. i have paging on datagrid,need to get all data to excel can u give suggestion
anu rosePosted Dec 12, 2009, 6:27 AM
cxzvvzczxcvzxc
S GanesanPosted Oct 3, 2009, 5:48 AM
Hi , My Datatable contains value 002 whenever i download in Datatable to Excel. It shows only 2 but i need the following format 002 in Excel download file(ASP.Net-05, C#).Is it possible. plz replay ... Regards, Tankxx.
suresh sureshPosted Jul 15, 2009, 5:48 AM
what is the code for asp.net2.0 with vb soure code....
Thomas MathewPosted Mar 31, 2009, 1:38 AM
To create PDF file using asp.net http://techdotnets.blogspot.com/
steven loweditedPosted Mar 14, 2009, 9:38 PMEdited Mar 15, 2009, 8:02 AM
Response.Clear(); Response.Buffer= true; Response.ContentType = "application/vnd.ms-excel"; Response.Charset = ""; this.EnableViewState = false; System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); DataGrid dg = new DataGrid(); dg.DataSource = GetDataSource(); //??DataSet?DataTable dg.DataBind(); dg.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.End(); Source Code
Francio MaestrePosted Dec 11, 2008, 2:01 PM
With master pages had several issues like gridview is out of form tag etc etc.. below code works for me. protected void cmdExportToExcel_Click(object sender, EventArgs e) { Response.Clear(); Response.Buffer = true; Response.ContentType = "application/vnd.ms-excel"; Response.Charset = ""; this.EnableViewState = false; System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); System.Web.UI.HtmlControls.HtmlForm frm = new HtmlForm(); this.Controls.Add(frm); frm.Controls.Add(grdViewPlan); frm.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.End(); }
Tim KielyeditedPosted Mar 10, 2008, 12:26 PMEdited Mar 10, 2008, 12:26 PM
OfficeWriter is a commercial product, but it allows you to read, write, or modify binary Excel files on a web server. It also reads and writes Word files.
Ettore CefalaPosted Feb 7, 2008, 9:50 AM
I think this is the usual HTML export of a Gridview to Excel. Take a look at http://www.gridviewtoexcel.com ! You'll find a tool which allows to export data from a Gridview and its datasource to real Excel XML or XLSX formats.
Kumaresh BabuPosted Nov 24, 2007, 12:04 AM
Error when i run code to export gridview values to excel: Control 'dg' of type 'GridView' must be placed inside a form tag with runat=server. Rectify my error
ajay manralPosted Oct 9, 2007, 8:20 AM
While Exporting gridview bound column data , How to hide the TemplateField HeaderText Is there any sol's .pls help me . with regards Ajay
ajay manralPosted Oct 9, 2007, 8:10 AM
While Exporting gridview bound column data , How to hide the TemplateField HeaderText Is there any sol's .pls help me . with regards Ajay
ajay manralPosted Oct 9, 2007, 7:59 AM
While Exporting gridview bound column data , How to hide the TemplateField HeaderText Is there any sol's .pls help me . with regards Ajay
ajay manralPosted Oct 9, 2007, 7:53 AM
While Exporting gridview bound column data , How to hide the TemplateField HeaderText Is there any sol's .pls help me . with regards Ajay
ajay manralPosted Oct 9, 2007, 7:39 AM
While Exporting gridview bound column data , How to hide the TemplateField HeaderText Is there any sol's .pls help me . with regards Ajay
hakeem kazmiPosted Sep 10, 2007, 10:25 AM
HI ALL,THANKS FOR UR POST WHICH HELP ME ALOT,I WANT THE CODE WHERE INSTEAD OF MAKING THE BUTTONFIELD DISPLAED LIKE A TEXT IN EXCEL REPORT CAN WE DO LIKE THE BUTTONFIELDS SHOULD BE BE EXPORTED OR HIDE SO THAT THE EXCEL SHEET SHOULD HAVE ONLY DATA. MY GRIDVIEW HAS 7 COLUMNS AND OUT OF THEM 3 ARE BUTTONFIELD. SO PLZ PLZ HELP ME IN THIS. THANKS IN ADVANCE, KAZMI
VVSN MurthyPosted Aug 17, 2007, 9:00 AM
normal datagrid exporting to excel is easy but datagrid with link buttons is not easy for that you have provided a very good method thanks for that
VVSN MurthyPosted Aug 17, 2007, 8:57 AM
normal datagrid exporting to excel is easy but datagrid with link buttons is not easy for that you have provided a very good method thanks for that
smriti mallaPosted Jul 26, 2007, 2:25 PM
I have displayed temperature on my web page with the unit °F. For that i have added the following to the code.   & deg F...this works fine but when i do an export to excel "& deg F" is displayed as the unit in the excel spreadsheet..i would want the character degree to be displayed in the excel file too..How can i do that ?
smriti mallaeditedPosted Jul 26, 2007, 2:23 PMEdited Jul 26, 2007, 2:28 PM
I have displayed temperature on my web page with the unit °F. For that i have added the following to the code. " & deg F"...this works fine but when i do an export to excel "& deg F" is displayed as the unit in the excel spreadsheet..i would want the character degree to be displayed in the excel file too..How can i do that ?
Sobeer SinghPosted Mar 23, 2007, 5:38 AM
Thanks Dipal This is a great help! Sobeer
Sobeer SinghPosted Mar 23, 2007, 5:37 AM
Thanks Dipal, This is a great help!
Mahesh ChandPosted Nov 14, 2006, 9:46 AM
Here is a good resource on how to export to Excel, Word, and Text files: http://www.codersource.net/published/view/283/exporting_data_grid_to_excel.aspx
Mahesh ChandPosted Nov 14, 2006, 9:46 AM
Here is a good resource on how to export to Excel, Word, and Text files: http://www.codersource.net/published/view/283/exporting_data_grid_to_excel.aspx