几种从数据库读取数据生成excel文件的比较
测试环境: 赛扬600+128M+Win2k Prof.(English.)+SQL Server 2000+Excel
2000+VB6(sp4)+ADO2.5
测试表记录数：10322，字段数：9 返回表中所有的纪录(select * from
table1),每种方法连续测试5次，在VB中使用MsgBox (DateDiff("s", t1,
Now()))计时(秒)
方法1。使用CopyFromRecordset(适用于Access,SQL)
第一次：49
第二次：45
第三次：43
第四次：43
第五次：42
方法2：使用QueryTable(适用于Access,SQL)
第一次：10
第二次：6
第三次：3
第四次：4
第五次：4
方法2：使用bcp(适用于SQL)
从命令行直接运行时间为701毫秒，从VB中返回时间为0
测试代码如下：
方法1：
Option Explicit
Private Sub Command1_Click()
Dim t1 As Date
t1 = Now()
Dim strConn As String
strConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=mlog;Data Source=SZ09"
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CreateObject("ADODB.Connection")
cn.Open strConn
cn.CursorLocation = adUseServer
Set rs = cn.Execute("table1", , adCmdTable)
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1").CopyFromRecordset rs
oBook.SaveAs "d:\1.xls"
oExcel.Quit
Set oSheet = Nothing
Set oBook = Nothing
Set oExcel = Nothing
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
MsgBox (DateDiff("s", t1, Now()))
End Sub
方法 2：
Option Explicit
Private Sub Command1_Click()
Dim t1 As Date
t1 = Now()
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
Dim strConn As String
strConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=mlog;Data Source=SZ09"
'Create the QueryTable
Dim oQryTable As Object
Set oQryTable = oSheet.QueryTables.Add( _
"OLEDB;" & strConn & ";", oSheet.Range("A1"), "Select * from table1")
oQryTable.RefreshStyle = xlInsertEntireRows
oQryTable.Refresh False
'Save the Workbook and Quit Excel
oBook.SaveAs "d:\1.xls"
oExcel.Quit
Set oSheet = Nothing
Set oBook = Nothing
Set oExcel = Nothing
MsgBox (DateDiff("s", t1, Now()))
End Sub
方法3：
Private Sub Command1_Click()
Dim t1 As Date
t1 = Now()
Dim sCmd As String
sCmd = "bcp mlog..table1 out d:\1.csv -w -t , -r \n -S sz09 -P
kenfil"
Dim WSH As Object
Set WSH = CreateObject("WScript.Shell")
WSH.Run sCmd, True
MsgBox (DateDiff("s", t1, Now()))
End Sub
Note: cvs本身是一个可以被excel使用的文件（你可以直接在excel中打开这个文
件),如果你希望将这个文件转换成xls文件，很简单：
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open("d:\1.csv")
'Save as Excel workbook and Quit Excel
oBook.SaveAs "d:\1.xls", xlWorkbookNormal
oExcel.Quit