CODEDIGEST
Home Articles CodeDigest Tutorials FAQs
Skip Navigation LinksHome » Article » ASP.Net Article » Export to Excel in ASP.Net 2.0 –Gridview to Excel, DataTable to Excel   You are not logged in.
Search
 

Technologies
 

Sponsors
 

CodeDigest Navigation
 

Technology News
 

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: (Login)
Be first to rate
this article.
No of Comments: 39
Print this article.
Category: ASP.Net

Subscribe to our feed!

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)

    {

 

    }

 



ASP.Net Hosting

Recent Articles

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 CodeDigest.Com:
    Article Feedback
    Title  
    Submitted By  
    Comment  
    Enter the verification number
     
    Comments
    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