VBGood网站全文搜索 Google

搜索VBGood全站网页(全文搜索)
首页 - 经验之谈 - 几种从数据库读取数据生成excel文件的比较
发表评论(0)作者:不详, 平台:VB6.0+Win98, 阅读:11669, 日期:2001-09-29
测试环境: 赛扬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