Export To Excel

[wbb]

any one got idea how to export a dataset to ms excel ???

[Bodybag]

Example of use.

 

export.exportToExcell(Datatable,"C:\test.xls")

 

 

 

 

 

//CODE

 

Public Class Export

Private Shared Sub DataGridToExcel(ByVal dgExport As Web.UI.WebControls.DataGrid, ByVal spath As String)

'clean up the response.object

Try

'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)

 

 

Dim dg As New Web.UI.WebControls.DataGrid

dg = dgExport

dg.DataBind()

 

'tell the datagrid to render itself to our htmltextwriter

dg.RenderControl(htmlWrite)

'output the html

Dim w As New IO.StreamWriter(spath, False)

w.Write(stringWrite.ToString)

w.Close()

Catch err As Exception

MsgBox(err.Message)

End Try

 

End Sub

 

 

 

Public Shared Sub exportToExcell(ByVal Dattable As DataTable, ByVal spath As String)

Dim a As New Web.UI.WebControls.DataGrid

 

 

a.DataSource = Dattable

 

a.DataBind()

 

DataGridToExcel(a, spath)

End Sub

End Class

[Bodybag]

Sorry that was for a windows app , try this for asp

 

 

 

 

 

 

 

Public Shared Sub exportToExcell(ByVal Dattable As DataTable)

Dim a As New Web.UI.WebControls.DataGrid

 

a.DataSource = Dattable

 

a.DataBind()

 

DataGridToExcel(a)

End Sub

 

Public Shared Sub DataGridToExcel(ByVal dgExport As DataGrid, ByVal response As HttpResponse)

'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

' just set the input datagrid = to the new dg grid

dg = dgExport

 

' I want to make sure there are no annoying gridlines

dg.GridLines = GridLines.None

' Make the header text bold

dg.HeaderStyle.Font.Bold = True

 

' If needed, here's how to change colors/formatting at the component level

'dg.HeaderStyle.ForeColor = System.Drawing.Color.Black

'dg.ItemStyle.BackColor = System.Drawing.Color.White

'dg.BorderColor = System.Drawing.Color.White

 

 

 

'bind the modified datagrid

dg.DataBind()

'tell the datagrid to render itself to our htmltextwriter

dg.RenderControl(htmlWrite)

'output the html

response.Write(stringWrite.ToString)

response.End()

End Sub

[wbb]

hmm there is a error !

 

public Sub exportToExcell(ByVal Dattable As DataTable)

Dim a As New Web.UI.WebControls.DataGrid

 

a.DataSource = Dattable

 

a.DataBind()

 

DataGridToExcel(a)--> error

End Sub

 

 

do i need to bind the datasource to the grid ??

 

can u do a sample project and paste in ?? thanx

[Bodybag]

Simplified version just pass it a datagrid that has been populated and your response object and it should work.You don't need to display datagrid. If you have more problems i will mail you a dll that will do it for you.

 

datagridtoExcell (datagrid,response)

 

 

Public Shared Sub DataGridToExcel(ByVal dgExport As DataGrid, ByVal response As HttpResponse)

'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

' just set the input datagrid = to the new dg grid

dg = dgExport

 

' I want to make sure there are no annoying gridlines

dg.GridLines = GridLines.None

' Make the header text bold

dg.HeaderStyle.Font.Bold = True

 

' If needed, here's how to change colors/formatting at the component level

'dg.HeaderStyle.ForeColor = System.Drawing.Color.Black

'dg.ItemStyle.BackColor = System.Drawing.Color.White

'dg.BorderColor = System.Drawing.Color.White

 

 

 

'bind the modified datagrid

dg.DataBind()

'tell the datagrid to render itself to our htmltextwriter

dg.RenderControl(htmlWrite)

'output the html

response.Write(stringWrite.ToString)

response.End()

End Sub

[wbb]

can u sent the file to north99@singnet.com.sg

thankk

[wbb]

are u ard ?? i sent a email to u

[Bodybag]

I sent a mail but it said it could not be delivered.Here it is again.

 

 

 

Me.adapscheduler.Fill(dattable) ->> This is the adapter that you have to create your self.

 

 

 

Try this

 

Dim myCommand As New Odbc.OdbcDataAdapter("select * from item", MyConnection)

 

Dim ds As New DataSet

 

myCommand.Fill(ds, "item")

 

Excellexport.Export.exportToExcellAsp(ds.Tables("item")., Me.Response)