Matt Paulson

Entrepreneur, Author, Private Equity Investor
  • Facebook
  • Linkedin
  • Twitter
  • Rss
  • Blog
    • Book Recommendations
    • Church Life & Ministry
    • Community Projects
    • Entrepreneurship
    • Quaterly Updates
    • Startup Community
    • Startup Q&A Show
    • Web Development
  • About
    • Resume
  • My Companies
    • Angel Investments
  • My Books
    • Online Business from Scratch
    • Automatic Income
    • The Ten-Year Turnaround
    • Email Marketing Demystified
    • 40 Rules for Internet Business Success
  • Media Appearances
  • Contact
Home» Web Development » How to Export Data to Excel in C# and ASP.NET

How to Export Data to Excel in C# and ASP.NET

Posted on May 27, 2009 by matt in Web Development 4 Comments
Tweet

If you develop a web-application for a client that involves creating and viewing reports, it’s very likely that they’re going to want to be able to export that data to excel very readily. Fortunately, you won’t need any special libraries or hundreds of lines of code to make that happen in ASP.NET. All you need is a GridView with some data in it and a few lines of code to make that happen.

Here’s how to export data from a GridView in ASP.NET:

(1) Create a Grid View

First, we’ll need a GridView control on the page that we can load our report or table into.

<asp:GridView ID=”GridView1″ runat=”server”></asp:GridView>

(2) Disable Event Validation

Because we will be creating a fresh HTTP response, ASP.NET’s event validator will likely run into an error. Specifically, the message you would receive is “Invalid postback or callback argument.” In order to get around this, we need to disable event validation for the page that is used to export to excel. To do that, add the following tag to the top of your .ASPX page:

<%@Page EnableEventValidation=”true” %>

(3) Add data to your GridView

At this point, you’ve got everything you need done on the HTML side of things, and it’s time to write some C# (or VB or J#) if you prefer. The first thing we need to do in our server-side code is to add data to the gridview. In the code below, I’m using a function from the database utilities library mentioned earlier this month to get the data and return it as a DataTable. You’re more than welcome to use whatever data-access code you would like to pull the resultset you would like to get from the database. This code will most likely end up in your Page_Load() function.

DataTable dtResults= SQLSelect(“Select * from Users”);
GridView1.DataSource = dtResults;
GridView1.DataBind();

(4) Building your export-to-excel function

You’ll need about 10 lines of code to make the actual export happen. Essentially, it creates a new HTTP response, re-renders the gridview and puts it in a downloadable XLS format. You can either put this in your Page_Load() function or in the event handle for a button if you would like to preview the gridview before exporting it.

Here’s the export code:

Response.Clear();
Response.ContentType = “application/ms-excel”;
Response.Charset = “”;
Page.EnableViewState = false;
Response.AddHeader(“Content-Disposition”, “inline;filename=report.xls”);

System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);

GridView1.RenderControl(hw);

Response.Write(tw.ToString());
Response.End();

(5) Override the VerifyRenderingInServerForm Method

Add this function to your C# class for the page to prevent a rendering error:

public override void VerifyRenderingInServerForm(System.Web.UI.Control control)
{
//confirms that an HtmlForm control is rendered for the
//specified ASP.NET server control at run time.
}

(6) You’re done!

That’s really all it takes. If you’d like a good example file, you can download one from ASPAlliance.

Other Resources:

You might also be interested in reading these articles about exporting files to excel:

  • How to Export Data to Excel from an ASP.NET Application + Avoid the File Format Differ Prompt (MSDN Blogs)
  • CodeSnip: Exporting GridView to Excel (CodeSnip)
  • ASP.Net 2.0: Export GridView to Excel (C-Sharp Corner)
Tweet

Comments

4 comments on “How to Export Data to Excel in C# and ASP.NET”

  1. Ganesh says:
    August 3, 2009 at 5:46 am

    Hi,
    Thanks for u r coding. But 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. plz replay …

    Reply
    • Nashan says:
      April 1, 2014 at 9:18 am

      Mr.Ganesh i found that you published your problem in several web sites.I am having same problem can u please reply me I think you got the solution

      Reply
  2. help me says:
    December 19, 2009 at 2:53 am

    i gave an error for rendering
    what shoul write in public override void VerifyRenderingInServerForm(System.Web.UI.Control control)
    {
    //confirms that an HtmlForm control is rendered for the
    //specified ASP.NET server control at run time.
    } ????

    Reply
  3. Julian Ivey says:
    March 8, 2010 at 8:39 am

    What happened to the .zip file for download?

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Get Email Updates

Enter your email address below to receive a steady stream of tricks, tips and ideas to help you build a better and more profitable business.

Email Marketing Demystified

The second edition of Email Marketing Demystified is now available. This book teaches you how to build a massive email list, write marketing copy that converts and generate more sales in your business.

Click Here to Get Your Copy of Email Marketing Demystified

Recent Posts

  • Introducing MarketBrew, a Craft Beer from MarketBeat and Remedy Brewing Company

    April 22, 2021
  • How to Find and Pitch Investors for Your Startup

    April 13, 2021
  • My Quarterly Update: Q2 2021

    April 6, 2021
  • The Future of Seed-Stage Startup Capital in the Dakota’s

    March 31, 2021
  • Next Steps for Sioux Falls’ Startup Ecosystem

    March 16, 2021

    Follow me on Facebook & YouTube

    Contact

    • [email protected]
    • Contact Us
    • matthew-paulson
      • Facebook
      • Twitter
      • Linkedin
      • Instagram
      • Rss

    © Matthew Paulson 2003-2021. All Rights Reserved.

    Privacy Policy