CODEDIGEST
Home » Articles
Search
 

Technologies
 

Sponsored links
 

CodeDigest Navigation
 

Technology News
No News Feeds available at this time.
 

Community News
No News Feeds available at this time.
 
Export to Excel in ASP.Net 2.0 – Gridview to Excel, DataTable to Excel

By Satheesh babu
Posted On Sep 13,2008
Article Rating:
Be first to rate
this article.
No of Comments: 43
Category: ASP.Net
Print this article.

Export to Excel in ASP.Net 2.0 – Gridview to Excel, DataTable to Excel

 

Exporting contents to excel or preparing a excel report of the data displayed on a webpage is one of the most common tasks in real world web applications. Most frequently, we will export the contents of GridView control to excel. In this article, i will implement some of the common ways of exporting a table of data displayed on a web page to a excel file.

 

Export to Excel

Ø       Rendering the Gridview Control to Excel

Ø       Rendering the underlying DataTable to Excel

 

Rendering the Gridview Control to Excel

This is one of the most commonly done approach where we set MIME type and use Gridview’s RenderControl() method, similar to we do for a Datagrid control.

        string attachment = "attachment; filename=Employee.xls";

        Response.ClearContent();

        Response.AddHeader("content-disposition", attachment);

        Response.ContentType = "application/ms-excel";

        StringWriter stw = new StringWriter();

        HtmlTextWriter htextw = new HtmlTextWriter(stw);

        gvEmployee.RenderControl(htextw);

        Response.Write(stw.ToString());

        Response.End();

 

When we execute the above code, it will give the following error.

 

Control 'gvEmployee' of type 'GridView' must be placed inside a form tag with runat=server

 

We can resolve the error by 2 ways,

1.      Adding the Gridview to a HtmlForm object programmatically

2.      Overriding VerifyRenderingInServerForm Event in the page.

 

Adding the Gridview to a HtmlForm object programmatically

So, instead of rendering the gridview add the gridview to an HtmlForm object and render the form.

 

        HtmlForm form = new HtmlForm();

        string attachment = "attachment; filename=Employee.xls";

        Response.ClearContent();

        Response.AddHeader("content-disposition", attachment);

        Response.ContentType = "application/ms-excel";

        StringWriter stw = new StringWriter();

        HtmlTextWriter htextw = new HtmlTextWriter(stw);

        form.Controls.Add(gvEmployee);

        this.Controls.Add(form);

        form.RenderControl(htextw);

        Response.Write(stw.ToString());

        Response.End();

 

This will solve the error.

 

Overriding VerifyRenderingInServerForm Event in the page

Adding this event in the codebehind confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time.

 

protected void Button1_Click(object sender, EventArgs e)

{

string attachment = "attachment; filename=Employee.xls";

        Response.ClearContent();

        Response.AddHeader("content-disposition", attachment);

        Response.ContentType = "application/ms-excel";

        StringWriter stw = new StringWriter();

        HtmlTextWriter htextw = new HtmlTextWriter(stw);

        gvEmployee.RenderControl(htextw);

        Response.Write(stw.ToString());

        Response.End();

}

 

public override void VerifyRenderingInServerForm(Control control)

    {

 

    }

 


Rendering the underlying DataTable to Excel

This is one of the easiest ways of exporting the data to excel.  The content of the DataTable should be written to the response by setting ContentType attribute to "application/vnd.ms-excel" and by setting its header.

 

protected void Button3_Click(object sender, EventArgs e)

    {

        DataTable dt = GetData();

        string attachment = "attachment; filename=Employee.xls";

        Response.ClearContent();

        Response.AddHeader("content-disposition", attachment);

        Response.ContentType = "application/vnd.ms-excel";

        string tab = "";

        foreach (DataColumn dc in dt.Columns)

        {

            Response.Write(tab + dc.ColumnName);

            tab = "\t";

        }

        Response.Write("\n");

 

        int i;

        foreach (DataRow dr in dt.Rows)

        {

            tab = "";

            for (i = 0; i < dt.Columns.Count; i++)

            {

                Response.Write(tab + dr[i].ToString());

                tab = "\t";

            }

            Response.Write("\n");

        }

        Response.End();

    }

Execute the page and see export to excel in action.

 

Download the code attached with this article. No need to change any settings as it uses the Sql Express database in App_Data folder.

 

Downloads

Source Code 

 

Conclusion

Exporting data to excel sheet is one of the common feature we will provide for a data driven website. We will normally provide reports in a webpage using GridView control. So, it will be better if we provide an export to excel functionality with the GridView itself. This article will helps us in doing it. Thanks for reading this article.

Happy Coding!!

 

Similar Articles
You can contribute to CodeDiget.Com:
Donate to CodeDigest.com
Article Feedback
Comments
I am the new one
I l?ive youir b?og.. ver? nice colors & theme. Di? yo? create
tthis web?ite yoourself or did you hire s?meone to do it for you?
Plz r?ply ?? I'm looking to design my own blog and would like to find out whe?e u got this from.
thanks

Al?o visit my web-site :: [url=http://www.okx.ro/item.php?id=68150&mode=1]runscape gold[/url]
I am the new one
I l?ive youir b?og.. ver? nice colors & theme. Di? yo? create
tthis web?ite yoourself or did you hire s?meone to do it for you?
Plz r?ply ?? I'm looking to design my own blog and would like to find out whe?e u got this from.
thanks

Al?o visit my web-site :: [url=http://www.okx.ro/item.php?id=68150&mode=1]runscape gold[/url]
I am the new one
I l?ive youir b?og.. ver? nice colors & theme. Di? yo? create
tthis web?ite yoourself or did you hire s?meone to do it for you?
Plz r?ply ?? I'm looking to design my own blog and would like to find out whe?e u got this from.
thanks

Al?o visit my web-site :: [url=http://www.okx.ro/item.php?id=68150&mode=1]runscape gold[/url]
I am the new one
I l?ive youir b?og.. ver? nice colors & theme. Di? yo? create
tthis web?ite yoourself or did you hire s?meone to do it for you?
Plz r?ply ?? I'm looking to design my own blog and would like to find out whe?e u got this from.
thanks

Al?o visit my web-site :: [url=http://www.okx.ro/item.php?id=68150&mode=1]runscape gold[/url]
help
i have two grids on same aspx page with different no of columns like 8 & 2 .. but when i export that page to excel the second column of the second grid takes the width of the first grid column!! so that the format in excel will not proper !! plz help me in this
problem
when i export gridview result to excel, i cant handle the persian font, i mean its shown in seprate alphabetes an stuff, can any body help me?
Export to excel
I am using Paging concept in grid view, can i recive all data in excel.........
Problem
Hello, I am using update panel with ajax. This is throwing some Jscript error like "Microsoft JScript runtime error: Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed." What to do for this?
Problem
Hello, I am using update panel with ajax. This is throwing some Jscript error like "Microsoft JScript runtime error: Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed." What to do for this?
Raju
Article is fine.

I have a WebPart .cs file in sharepoint and I have used this code (after doing some modifications) to Download the Gridview Data to Excelsheet.

But Here the problem is. I have 2 buttons (button1, button 2). If i am clicking on Button 1 the file is getting downloaded. its fine. but after that none of the controls like button 2 is not at all working.


reply me to Vaddi@contactoffice.co.uk
Awesome Article
Awesome .....
Export to excel from datatable
it is working fine but when suppse Addresfield containing mor characters in datatable it is appeared in another line at that it is not workin fine plesae provide solution foe that.
thanQ
vinay
best
thanx.........!!!!!!!!
Getting Error
This is very good article. However format of excel get disturb when it finds any blank fields. Please provide soultion for it.
Ajay
prabhakar
XML to Excel
Can anyone tell how to convert data in XML format to Excel
Very Nice
This is such a good article that i cant express because this helped me in a very good manner when it was urgent......so thanksssssssss a lot...
good tips
good tips
Gridview to Excel
This is exactly what I am looking for and it works perfectly. Thankyou for sharing!
Export to Excel problems
Here is the complete solution for all the problems we face in export to excel from Gridview.
http://praveenbattula.blogspot.com/2010/09/gridview-and-export-to-excel.html
Good Solution for US
This article is very use ful for me.
GOOD ENOUGH
but you should define the get data() function I think because beginners never ever get this much idea about so for get dta function is this/* public DataTable gvContentbind()// retrun type is data table which is call on button click.
{
con.Open();
SqlDataAdapter sda = new SqlDataAdapter("select * from data", con);
DataTable ds = new DataTable();
sda.Fill(ds);
con.Close();
return ds;

}*/ and call this function instead of get data and gv is my grid name.
Error by excecuting the above code
"RegisterForEventvalidation can only be called at Render" I am getting this error by executing that code.plz help me how to resolve this.
Great...!!!
Hey Hello...
Very very nice article....
Export to Excel
Sorry for last post The file should be added in AppCode folder not in AppData folder
New Solution
Add GridViewExportUtil.cs in AppData folder
public class GridViewExportUtil
{
/// <summary>
///
/// </summary>
/// <param name="fileName"></param>
/// <param name="gv"></param>
public static void Export(string fileName, GridView gv)
{
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())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a form to contain the grid

Table table = new Table();

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

// add each of the data rows to the table
foreach (GridViewRow row in gv.Rows)
{
GridViewExportUtil.PrepareControlForExport(row);
table.Rows.Add(row);
}

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

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

// render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}

/// <summary>
/// Replace any of the contained controls with literals
/// </summary>
/// <param name="control"></param>
private static void PrepareControlForExport(Control control)
{
for (int i = 0; i < control.Controls.Count; i++)
{
Control current = control.Controls[i];
if (current is LinkButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as LinkButton).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 ? "True" : "False"));
}

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

Now call the CS file on button event to export grid
protected void Button2_Click(object sender, EventArgs e)
{
GridViewExportUtil.Export("Filename.xls", this.GridView1);
}
How to give lines color?
Thank you very much for wonderful help.
I have a question though. Can "Rendering the underlying DataTable to Excel" code be changed to give lines in Excel color based on content of a particular field, e.g. "Color" field, that contains color information, e.g. y, b, p, a2, g?
Your can answer privately to dpreznik@hotmail.com.
Thank you very much.
Export to Excel in ASP.Net 2.0 – Gridview to Excel, DataTable to Excel
This is very good article. But can you tell me how to make a column in excel readonly using your code.
I mean if i have four columns in grid and when i click on generate exxcel button the excel file should have first two columns as readonly and last two columns as editable.
If possible then ping me on ajaykulkarnir@hotmail.com
Thanks in advance.
Error
I did everything, like from tag etc. But still its giveing me error,
System.InvalidOperationException: RegisterForEventValidation can only be called during Render();

Whats the error?
Excellent sample
Brilliant piece of code. Was having issues with IE6 not recognizing the text/csv mime type and I used the above example of rendering the datable to excel and it worked like a charm.
Thanks once again!
datatable to excel export on page with master page
I have a task to convert datatable or gridview record into excel file. I am on a page which have master page.

protected void Button1_Click(object sender, EventArgs e)
{
string attachment = "attachment; filename=Employee.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter stw = new StringWriter();
HtmlTextWriter htextw = new HtmlTextWriter(stw);
gvEmployee.RenderControl(htextw);
Response.Write(stw.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{

}

this code is working for page without master page but when i tries it on page with master page it asks for FORM tag issue...i did eventvalidation=false also
but it gives
RegisterForEventValidation can only be called during Render();
now i using
HtmlForm form = new HtmlForm();
string attachment = "attachment; filename=Employee.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter stw = new StringWriter();
HtmlTextWriter htextw = new HtmlTextWriter(stw);
form.Controls.Add(gvEmployee);
this.Controls.Add(form);
form.RenderControl(htextw);
Response.Write(stw.ToString());
Response.End();

but i am not getting file to download

pls suggest me

Thanks
Bhupendra
Export to Excel
Hi,
One doubt for Excel Download. 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(Using ASP.Net-05, C#).
Is it possible.
Please help me...

Thanxxx.

Thanks
Very helpful.
Data to multiple tabs
How can i export data to multiple tabs. Any help is greatly appreciated.
Nice one
Txs a lot mate..nicely coded...Working gr8...

Vijay
HELP URGENT
Is there any way to write the data from different tables of a dataset into multiple sheets of a single excel workbook
Excellent
This is a very wonderful code here. It was just a one stop for me.thank you very much
It helps
Thanks, It helped me. Good work done.
cell layout
Can I make the headers bold. And is there how do I set cell sizes programatically
sadfas
asdfasdfs
Export in Diffferent Tab
Hi ,
I am using asp.net 2.0 and i have 2 Grid view on form which contains data. I already export both grid 's data in single excel file but i want data should display in 2 different tabs( worksheets) for each grid.

How can i do this ?
Gracias
Thank you very much for the Info,,, you help a lot somebody in Chihuahua
Mr
Great great "little" tips turn out to be a big time saver. THANK YOU SO MUCH for sharing with us!
John