how can export datatable to excel

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
Tue Sep 16 06:13:52 CDT 2003

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
"Win" <win@iswin.info> wrote in message
news:uv8fp6DfDHA.3216@tk2msftngp13.phx.gbl...
> 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
Tue Sep 16 08:50:53 CDT 2003

Here's another link...

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q319180
"William Ryan" <dotnetguru@nospam.comcast.net> wrote in message
news:OvdLdOEfDHA.3104@TK2MSFTNGP11.phx.gbl...
> 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
> "Win" <win@iswin.info> wrote in message
> news:uv8fp6DfDHA.3216@tk2msftngp13.phx.gbl...
> > 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
> >
> >
> >
>
>

medhanush
Tue Sep 16 12:06:19 CDT 2003

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
"Win" <win@iswin.info> wrote in message news:<uv8fp6DfDHA.3216@tk2msftngp13.phx.gbl>...
> 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

Bernie
Tue Sep 16 22:47:24 CDT 2003

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

"Win" <win@iswin.info> wrote in message
news:uv8fp6DfDHA.3216@tk2msftngp13.phx.gbl...
> 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
>
>
>