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).ColumnName.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(intColumnValue).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.aspx?scid=kb;en-us;319180

Ken

 

[Paul Clement]

¤ 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).ColumnName.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(intColumnValue).ToString
¤ Next
¤ Next
¤

What is the data source of your export?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Agnes]

MS SQL Server

 

[Paul Clement]

¤ 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=Microsoft.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)