Export DataSets to Excel ...

Often we need to load the data from a dataset into an excel spreadsheet to be manipulated and / or saved off to a local file. There are several ways to accomplish this using either Crystal Reports or ActiveReports. However there is a simple, elegant way to Do The Same Thing without The need for a reporting Tool.

BY:

Ken Walker

Date: March 14, 2003 Download The Code. Article Rating: 4.5 Printer Friendly Version

This Article Will Show How To create a class which does The export. The class contains a communications SO That We can pass in diffrent Kinds of Information:

OVERLOAD # 1

A dataset the response object of the web page overload # 2

A Dataset An Index Value of Which Table from The DataSet The Response Object of The Web Page Overload # 3

A DataSet The Table Name of a Table in The DataSet The Response Object of The Web Page

The method of the method.

What makes this task so straight forward is the elegance of the .NET Framework design. It turns out that most web controls have a RenderControl method which will write an html text stream. All we need to do is set up the response object, call the RenderControl Method of a DataGrid and Tell The Response Object To Output The "Rendering". Pretty Simple!

Here's The Code for the Class (DatasettoExcel.vb):

'Class to convert a dataset to an html stream which can be used to display the dataset'in MS Excel'The Convert method is overloaded three times as follows' 1) Default to first table in dataset' 2) Pass an index to tell us which table in the dataset to use '3) Pass a table name to tell us which table in the dataset to usePublic Class DataSetToExcel Public Shared Sub Convert (ByVal ds As DataSet, ByVal response As HttpResponse)' first let's clean up the response.object response.Clear () response.Charset = "" 'set the response mime type for excel response.ContentType = "application / vnd.ms-excel"' create a string writer Dim stringWrite As New System.IO.StringWriter 'create an htmltextwriter which uses the stringwriter Dim htmlWrite As New System.Web.UI.HtmlTextWriter (stringWrite) 'instantiate a datagrid Dim dg As New DataGrid' set the datagrid datasource to the dataset passed in dg.DataSource = ds.Tables (0) 'bind the DataGrid Dg.DATABIND () 'TE ll the datagrid to render itself to our htmltextwriter dg.RenderControl (htmlWrite) 'all that's left is to output the html response.Write (stringWrite.ToString) response.End () End Sub Public Shared Sub Convert (ByVal ds As DataSet, ByVal tableIndex As Integer, ByVal response As HttpResponse) 'lets make sure a table actually exists at the passed in value' if it is not call the base method If tableIndex> ds.Tables.Count - 1 Then Convert (ds, response) End If 'We've Got A Good Table So' Let's Clean Up The Response.Object Response.clear () Response.charset = "" '

set the response mime type for excel response.ContentType = "application / vnd.ms-excel" 'create a string writer Dim stringWrite As New System.IO.StringWriter' create an htmltextwriter which uses the stringwriter Dim htmlWrite As New System.Web. UI.HtmlTextWriter (stringWrite) 'instantiate a datagrid Dim dg As New DataGrid' set the datagrid datasource to the dataset passed in dg.DataSource = ds.Tables (tableIndex) 'bind the datagrid dg.DataBind ()' tell the datagrid to render itself to our htmltextwriter dg.RenderControl (htmlWrite) 'all that's left is to output the html response.Write (stringWrite.ToString) response.End () End Sub Public Shared Sub Convert (ByVal ds As DataSet, ByVal TableName As String, ByVal response As HttpResponse) 'let's make sure the table name exists' if it does not then call the default method If ds.Tables (TableName) Is Nothing Then Convert (ds, response) End If 'we've got a good table so' Let's clef an up the response.object response.Clear () response.Charset = "" 'set the response mime type for excel response.ContentType = "application / vnd.ms-excel"' create a string writer Dim stringWrite As New System.IO .StringWriter 'create an htmltextwriter which uses the stringwriter Dim htmlWrite As New System.Web.UI.HtmlTextWriter (stringWrite)' instantiate a datagrid Dim dg As New DataGrid 'set the datagrid datasource to the dataset passed in dg.DataSource = ds.Tables (TableName) 'Bind The DataGrid Dg.Database ()'

tell the datagrid to render itself to our htmltextwriter dg.RenderControl (htmlWrite) 'all that's left is to output the html response.Write (stringWrite.ToString) response.End () End SubEnd ClassEditor's Note: The class above was compiled using the following Visual Basic Compiler Directive:

VBRARY /R: SyiStem.dll /R :system.web.dll /r:System.data.dll /r:System.xml.dll DataSettoExcel.vb

My example web page is based upon creating a dataset from SQL Server (the authors table from the pubs database), but it does not matter how you get the dataset created, so modify your page according to your methodology. The example simply creates the DataSet and calls the class method from the page_load.

Here's The Code for the Calling page. First The .aspx Page Which Is Really Just A Shell To Give US Sometting To Call. As Usual, All The Work Is Done in the code-behind file.

DataToExcel.aspx

<% @ Page language = "vb" autoeventwireup = "false" codebehind = "datatoExcel.aspx.vb" inherits = "DatatoExcel"%>