SQL to Excel 三种方法 (转)

方法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
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值