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.

If you're looking for a really good web host, try Server Intellect - we found the setup procedure and control panel, very easy to adapt to and their IT team is awesome!

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>

Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.

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();

}

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

Looking for more .NET Database Tutorials? Click Here!

We used over 10 web hosting companies before we found Server Intellect. Their dedicated servers and add-ons were setup swiftly, in less than 24 hours. We were able to confirm our order over the phone. They respond to our inquiries within an hour. Server Intellect's customer support and assistance are the best we've ever experienced.

Download Project Source - Enter your Email to be emailed a link to download the Full Source Project used in this Tutorial!

100% SPAM FREE! We will never sell or rent your email address!