SQL to Excel 三種方法

來源:互聯網
上載者:User
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




相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。