Exporting a DataGrid to Excel in ASP.NET and C#

Quick Search

Navigator: Home - Advanced - Exporting a DataGrid to Excel in ASP.NET and C#

This tutorial will show how we can export the data displayed in a DataGrid into an Excel worksheet at the touch of a button. C# version.

Download the Full Working Version of this Project written with Visual Studio.NET C# Here!

Looking for the VB.NET 2005 Version? Click Here!

Looking for more .NET Database Tutorials? Click Here!

In this tutorial, we will be using a small sample database to show how we can export a dataset from a DataGrid to a new Excel worksheet. We will do this on a button click event. First, we need to create a sample database. For this demonstration, we simply have three columns; ID, Name and City.

We connect to the database through web.config:

</connectionStrings>

The SqlDataSource was created through Visual Studio, so the web.config was auto-generated. The entire web.config file looks like this:

<?xml version="1.0"?>
<!--

Note: As an alternative to hand editing this file you can use the
web admin tool to configure settings for your application. Use
the Website->Asp.Net Configuration option in Visual Studio.
A full list of settings and comments can be found in
machine.config.comments usually located in
\Windows\Microsoft.Net\Framework\v2.x\Config

-->
<configuration>

</sectionGroup>

</configSections>
<appSettings/>
<connectionStrings>

</connectionStrings>
<system.web>

<!--

Set compilation debug="true" to insert debugging
symbols into the compiled page. Because this
affects performance, set this value to true only
during development.

-->
<compilation debug="true">

</assemblies>

</compilation>
<!--

The <authentication> section enables configuration
of the security authentication mode used by
ASP.NET to identify an incoming user.

-->
<authentication mode="Windows"/>
<!--

The <customErrors> section enables configuration
of what to do if/when an unhandled error occurs
during the execution of a request. Specifically,
it enables developers to configure html error pages
to be displayed in place of a error stack trace.

</customErrors>
-->
<pages>

</controls>

</pages>
<httpHandlers>

</httpHandlers>
<httpModules>

</httpModules>

</system.web>
<system.codedom>

</compiler>
<compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" warningLevel="4" type="Microsoft.VisualBasic.VBCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">

</compiler>

</compilers>

</system.codedom>
<!--

The system.webServer section is required for running ASP.NET AJAX under Internet
Information Services 7.0. It is not necessary for previous version of IIS.

-->
<system.webServer>

</modules>
<handlers>

</handlers>

</system.webServer>
<runtime>

</dependentAssembly>
<dependentAssembly>

</dependentAssembly>

</assemblyBinding>

</runtime>

</configuration>

We are going to create a DataGrid Control, and a Button. The DataGrid will display all the data in our sample database, and then the button will export the data to Excel. The ASPX will look something like this:

<asp:DataGrid ID="DataGrid1" runat="server" Width="405px"

DataSourceID="SqlDataSource1">

</asp:DataGrid>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM [Table1]"></asp:SqlDataSource>
<br />

<asp:Button ID="Button1" runat="server" Text="Export Table to Excel"

onclick="Button1_Click" />
<br />

</form>

Finally, we can add the code to the button click event. The code-behind will look something like this:

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class _Default : System.Web.UI.Page
{

protected void Page_Load(object sender, EventArgs e)
{

}

protected void Button1_Click(object sender, EventArgs e)
{

Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;
System.IO.StringWriter stringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWriter = new System.Web.UI.HtmlTextWriter(stringWriter);
DataGrid1.RenderControl(htmlWriter);
Response.Write(stringWriter.ToString());
Response.End();

}

Download the Full Working Version of this Project written with Visual Studio.NET C# Here!

Looking for the VB.NET 2005 Version? Click Here!

Looking for more .NET Database Tutorials? Click Here!