Dataset to Excel


Export Data Sets to Excel in crystal reports...

Introduction:
Often we need to load the data from a data set 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.
Hear we 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:

1. Overload #1
1. A Dataset
2. The response object of the web page
2. Overload #2
1. A Dataset
2. An index value of which table from the dataset
3. The response object of the web page
3. Overload #3
1. A Dataset
2. The table name of a table in the dataset
3. 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!

DataSetToExcel.vb:


Public Class DataSetToExcel

Public Shared Sub Convert(ByVal ds As DataSet, ByVal response As HttpResponse)

response.Clear()
response.Charset = ""

response.ContentType = "application/vnd.ms-excel"

Dim stringWrite As New System.IO.StringWriter

Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)

Dim dg As New DataGrid

dg.DataSource = ds.Tables(0)

dg.DataBind()

dg.RenderControl(htmlWrite)

response.Write(stringWrite.ToString)
response.End()
End Sub

Public Shared Sub Convert(ByVal ds As DataSet, ByVal TableIndex As Integer, ByVal response As HttpResponse)

If TableIndex > ds.Tables.Count - 1 Then
Convert(ds, response)
End If

response.Clear()
response.Charset = ""

response.ContentType = "application/vnd.ms-excel"

Dim stringWrite As New System.IO.StringWriter

Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)

Dim dg As New DataGrid

dg.DataSource = ds.Tables(TableIndex)

dg.DataBind()

dg.RenderControl(htmlWrite)

response.Write(stringWrite.ToString)
response.End()
End Sub

Public Shared Sub Convert(ByVal ds As DataSet, ByVal TableName As String, ByVal response As HttpResponse)

If ds.Tables(TableName) Is Nothing Then
Convert(ds, response)
End If

response.Clear()
response.Charset = ""

response.ContentType = "application/vnd.ms-excel"

Dim stringWrite As New System.IO.StringWriter

Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)

Dim dg As New DataGrid

dg.DataSource = ds.Tables(TableName)

dg.DataBind()

dg.RenderControl(htmlWrite)

response.Write(stringWrite.ToString)
response.End()
End Sub

End Class


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.

DExcel.aspx

DExcel.aspx.vb


Public Class DataToExcel
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "


Private Sub InitializeComponent()

End Sub


Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init

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.AppSettings("PubsConnection"))
Dim cmd As New SqlClient.SqlCommand("select * from authors", myConnection)
Dim da As New SqlClient.SqlDataAdapter(cmd)

Dim ds As New DataSet
Try

da.Fill(ds)
Finally

If myConnection.State = ConnectionState.Open Then
myConnection.Close()
End If

myConnection.Dispose()
End Try

DataSetToExcel.Convert(ds, Response)
End Sub

End Class


This is My a simple solution to a common problem.
it must works out for you!



All The Best

Reference: http://naveenkumarm.page.tl


Comments

Guest Author: patrick06 Jun 2012

I have a windows application winforms not a webpage
How do i do this?



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: