Export data to excel too slow

[Agnes]

I can export the data to excel, but it is really really slow. need 5-6 mins
to export 30 fields (a hundred records) . for my old vfp application, less
than 3 minutes. for 500-800 records.
Does any idea to improve the alog ?? Or what Can I do in my sql server ?
Thanks a lot

'For displaying the column name in the the excel file.
For intColumn = 0 To dsExcelExport.Tables(0).Columns.Count - 1
.Cells(1, intColumn + 1).Value =
dsExcelExport.Tables(0).Columns(intColumn).ColumnN ame.ToString
Next
'For displaying the column value row-by-row in the the excel
file.
For intRow = 0 To dsExcelExport.Tables(0).Rows.Count - 1
For intColumnValue = 0 To
dsExcelExport.Tables(0).Columns.Count - 1
.Cells(intRow + 1, intColumnValue + 1).Value =
dsExcelExport.Tables(0).Rows(intRow).ItemArray(int ColumnValue).ToString
Next
Next

[Ken Tucker [MVP]]

Hi,

I prefer to create an xml spread sheet and transform it to an xls.
Here is an example of how to do it. The sample is for asp.net app but the
method will work with a windows forms app.

http://support.microsoft.com/default...b;en-us;319180

Ken
--------------------
"Agnes" <ag***@dynamictech.com.hk> wrote in message
news:e3**************@TK2MSFTNGP09.phx.gbl...

I can export the data to excel, but it is really really slow. need 5-6 mins
to export 30 fields (a hundred records) . for my old vfp application, less
than 3 minutes. for 500-800 records.
Does any idea to improve the alog ?? Or what Can I do in my sql server ?
Thanks a lot

'For displaying the column name in the the excel file.
For intColumn = 0 To dsExcelExport.Tables(0).Columns.Count - 1
.Cells(1, intColumn + 1).Value =
dsExcelExport.Tables(0).Columns(intColumn).ColumnN ame.ToString
Next
'For displaying the column value row-by-row in the the excel
file.
For intRow = 0 To dsExcelExport.Tables(0).Rows.Count - 1
For intColumnValue = 0 To
dsExcelExport.Tables(0).Columns.Count - 1
.Cells(intRow + 1, intColumnValue + 1).Value =
dsExcelExport.Tables(0).Rows(intRow).ItemArray(int ColumnValue).ToString
Next
Next

[Paul Clement]

On Tue, 25 Oct 2005 18:15:54 +0800, "Agnes" <ag***@dynamictech.com.hk> wrote:

¤ I can export the data to excel, but it is really really slow. need 5-6 mins
¤ to export 30 fields (a hundred records) . for my old vfp application, less
¤ than 3 minutes. for 500-800 records.
¤ Does any idea to improve the alog ?? Or what Can I do in my sql server ?
¤ Thanks a lot
¤
¤ 'For displaying the column name in the the excel file.
¤ For intColumn = 0 To dsExcelExport.Tables(0).Columns.Count - 1
¤ .Cells(1, intColumn + 1).Value =
¤ dsExcelExport.Tables(0).Columns(intColumn).ColumnN ame.ToString
¤ Next
¤ 'For displaying the column value row-by-row in the the excel
¤ file.
¤ For intRow = 0 To dsExcelExport.Tables(0).Rows.Count - 1
¤ For intColumnValue = 0 To
¤ dsExcelExport.Tables(0).Columns.Count - 1
¤ .Cells(intRow + 1, intColumnValue + 1).Value =
¤ dsExcelExport.Tables(0).Rows(intRow).ItemArray(int ColumnValue).ToString
¤ Next
¤ Next
¤

What is the data source of your export?
Paul
~~~~
Microsoft MVP (Visual Basic)

[Agnes]

MS SQL Server
"Paul Clement" <Us***********************@swspectrum.com>
???????:hq********************************@4ax.com ...

On Tue, 25 Oct 2005 18:15:54 +0800, "Agnes" <ag***@dynamictech.com.hk>
wrote:

¤ I can export the data to excel, but it is really really slow. need 5-6
mins
¤ to export 30 fields (a hundred records) . for my old vfp application,
less
¤ than 3 minutes. for 500-800 records.
¤ Does any idea to improve the alog ?? Or what Can I do in my sql server ?
¤ Thanks a lot
¤
¤ 'For displaying the column name in the the excel file.
¤ For intColumn = 0 To dsExcelExport.Tables(0).Columns.Count -
1
¤ .Cells(1, intColumn + 1).Value =
¤ dsExcelExport.Tables(0).Columns(intColumn).ColumnN ame.ToString
¤ Next
¤ 'For displaying the column value row-by-row in the the excel
¤ file.
¤ For intRow = 0 To dsExcelExport.Tables(0).Rows.Count - 1
¤ For intColumnValue = 0 To
¤ dsExcelExport.Tables(0).Columns.Count - 1
¤ .Cells(intRow + 1, intColumnValue + 1).Value =
¤ dsExcelExport.Tables(0).Rows(intRow).ItemArray(int ColumnValue).ToString
¤ Next
¤ Next
¤

What is the data source of your export?
Paul
~~~~
Microsoft MVP (Visual Basic)

[Paul Clement]

On Wed, 26 Oct 2005 18:02:31 +0800, "Agnes" <ag***@dynamictech.com.hk> wrote:

¤ MS SQL Server

How about the following code (table format is assumed to be the same or column names will need to be
specified):

Function ExportExcelToSQLServer() As Boolean

Dim ExcelConnection As New
System.Data.OleDb.OleDbConnection("Provider=Micros oft.Jet.OLEDB.4.0;" & _
"Data Source=" & "E:\My
Documents\Book5.xls" & ";" & _
"Extended Properties=""Excel
8.0;HDR=No""")

ExcelConnection.Open()

'Existing table
Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [ODBC;Driver={SQL
Server};Server=(local);Database=Northwind;Trusted_ Connection=yes].[Orders2] SELECT * FROM
[Orders$];", ExcelConnection)
'New table
'Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT INTO [ODBC;Driver={SQL
Server};Server=(local);Database=Northwind;Trusted_ Connection=yes].[Orders2] FROM [Orders$];",
ExcelConnection)

ExcelCommand.ExecuteNonQuery()
ExcelConnection.Close()

End Function
Paul
~~~~
Microsoft MVP (Visual Basic)