Exporting a DataGrid to Excel in ASP.NET and VB

Quick Search

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

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

Try Server Intellect for Windows Server Hosting. Quality and Quantity!

Looking for the C#.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>

Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!

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:

Partial Class _Default

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

End Sub

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Dim stringWriter As New System.IO.StringWriter()
Dim htmlWriter As New System.Web.UI.HtmlTextWriter(stringWriter)
DataGrid1.RenderControl(htmlWriter)
Response.Write(stringWriter.ToString())
Response.End()

End Sub

End Class

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.

Looking for the C#.NET 2005 Version? Click Here!

Looking for more .NET Database Tutorials? Click Here!

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!