Here are some of the approaches.
1. Transform DataGrid
http://www.dotnetjohn.com/articles.aspx?articleid=36
3. Use the Export approach
http://www.aspnetpro.com/NewsletterA...200309so_l.asp
---->
This article will show how to create a class which does the export. The
class contains a convert method which has three overloads so that we
can pass in different 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 methods will be shared methods so that we don't have to instaniate
the class in order to use 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 use
Public 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()
'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 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 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(TableName)
'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
End Class
Editor's Note: The class above was compiled using the following Visual
Basic Compiler directive:
vbc /t:library /r:system.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 doesn't 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 event handler of the page.
Here's the code for the calling page. First the .aspx page which is
really just a shell to give us something 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" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>DataSetToExcel</title>
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema"
content="http://schemas.microsoft.com/intellisense/ie5">
</head>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
</form>
</body>
</html>
DataToExcel.aspx.vb
Public Class DataToExcel
Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
End Sub
'NOTE: The following placeholder declaration is required by the Web
Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim myConnection As New
SqlClient.SqlConnection(ConfigurationSettings.AppS ettings("PubsConnection"))
Dim cmd As New SqlClient.SqlCommand("select * from authors",
myConnection)
Dim da As New SqlClient.SqlDataAdapter(cmd)
'instantiate a dataset
Dim ds As New DataSet
Try
'populate the dataset
da.Fill(ds)
Finally
'check on connection status
If myConnection.State = ConnectionState.Open Then
myConnection.Close()
End If
'get rid of connection object
myConnection.Dispose()
End Try
'call our class method
DataSetToExcel.Convert(ds, Response)
End Sub
End Class
This is really a simple solution to a common problem. Hope it works out
for you!
