how can export datatable to excel

[Win]

how can export datatable to excel
i try to use new excel application then fill data to excel
but it very slow
how can i to do export datatable to excel fast
thanks

 

[William Ryan]

It's going to be slow compared to native VBA code. There are a few ways to
do thiis...the best of which is to query the DataBase directly from Excel.
If you check out www.aspnetPRO.com (October 2003) they discuss Exporting to
Excel in depth, albiet primarily from the perspective of ASP.NET.

What code are you using? If we could see it, maybe I could be of more help.

Cheers,

Bill

 

[William Ryan]

Here's another link...

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q319180

 

[MeDhanush]

u can try generating CSV file and provide link to CSV file on the web page,
but the user need to have mapped the CSV file to Excel

HTH
Kishore

 

[Bernie Yaeger]

Hi Win,

Here's a function that I use to do exactly what you are trying to do. You
also will need at least some of these import statements at the top of you
code:
Imports Excel.XlFileFormat

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports System.IO


Public Function sqltabletocsvorxls(ByVal dt As DataTable, ByRef strpath As
String, ByVal dtype As String, ByVal includeheader As Boolean) As Integer

' signature:

' dim funcs as new imcfunctionlib.functions

' dim xint as integer

' xint = funcs.sqltabletocsvorxls(dsmanifest.tables(0),mstrpath,
"csv",false)

' where mstrpath = , say, "f:\imcapps\xlsfiles\test.xls"

sqltabletocsvorxls = 0

Dim objxl As Excel.Application

Dim objwbs As Excel.Workbooks

Dim objwb As Excel.Workbook

Dim objws As Excel.Worksheet

Dim mrow As DataRow

Dim colindex As Integer

Dim rowindex As Integer

Dim col As DataColumn

Dim fi As FileInfo = New FileInfo(strpath)

If fi.Exists = True Then

Kill(strpath)

End If

objxl = New Excel.Application

'objxl.Visible = False ' i may not need to do this

objwbs = objxl.Workbooks

objwb = objwbs.Add

objws = CType(objwb.Worksheets(1), Excel.Worksheet)

' i many want to change this to pass in a variable to determine

' if i want to have a column name row or not

If includeheader Then

For Each col In dt.Columns

colindex += 1

objws.Cells(1, colindex) = col.ColumnName

Next

rowindex = 1

Else

rowindex = 0

End If

For Each mrow In dt.Rows

rowindex += 1

colindex = 0

For Each col In dt.Columns

colindex += 1

objws.Cells(rowindex, colindex) = mrow(col.ColumnName).ToString()

Next

Next

If dtype = "csv" Then

objwb.SaveAs(strpath, xlCSV)

Else

objwb.SaveAs(strpath)

End If

objxl.DisplayAlerts = False

objws.Close()

objxl.DisplayAlerts = True

Marshal.ReleaseComObject(objws)

objxl.Quit()

Marshal.ReleaseComObject(objxl)

objws = Nothing

objwb = Nothing

objwbs = Nothing

objxl = Nothing

End Function

HTH,

Bernie Yaeger