数据库

img fangke

SQL to Excel 三种方法 (转)

发表于2004/9/24 19:23:00  979人阅读

方法1。使用CopyFromRecordset(适用于Access,SQL)

第一次:49
第二次:45
第三次:43
第四次:43
第五次:42

方法2:使用QueryTable(适用于Access,SQL)

第一次:10
第二次:6
第三次:3
第四次:4
第五次:4

方法3:使用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
 

 

阅读全文
0 0

相关文章推荐

img
取 消
img