Hello All,
There are many ways by which you can trasnform data into excel format. Here in .Net we usually need to transform data from our dataset, datagrid,,, we need to transform the resulted data shown in the datagrid, (after search, view)...
The following class help you to transform data into excel, just create a class let say, DataSetToExcel.vb, with following code
'**************** Class Begin
'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"
Dim stringWrite As New System.IO.StringWriter
Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
Dim dg As New DataGrid
' Make the header text bold
dg.HeaderStyle.Font.Bold = True
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)
'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
'****************************** End Class
And now in any of your aspx, ascx page you can call this functionality for exporting data like the way i use in my case
Private Sub btnExportToExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click
Dim ViewSet As DataSet
If Me.SortCriteria = "" Then Me.SortCriteria = "RegID"
If Me.SortDir = "" Then Me.SortDir = "asc"
If Me.SearchCriteria = "" Then Me.SearchCriteria = " 1=1 "
If Me.SqlString = "" Then
SqlString = "SELECT * FROM Pre_Registration "
SqlString = SqlString & " Where " & Me.SearchCriteria & " ORDER BY " & Me.SortCriteria & " " & Me.SortDir
End If
Try
ViewSet = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings("ConnectionString"), CommandType.Text, SqlString)
DataSetToExcel.Convert(ViewSet, Response)
Catch ex As Exception
'Response.Write(ex.Message)
End Try
End Sub
This code just passes a dataset which then transform into excel file.
This is how you can export data into excel format.
Regards,
Wajahat Abbas
