几种从数据库读取数据生成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