| SQL to Excel 三种方法 |
|
| 作者：佚名 文章来源：http://www.daima.com.cn/Info/86/Info30795/ 点击数： 更新时间：2005-11-13 |
方法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 |
| 文章录入：Haihua 责任编辑：Haihua |
|
上一篇文章： 小议MSSQL Server 2000的安全及管理
下一篇文章： SQL SERVER 与ACCESS、EXCEL的数据转换 |
| 【字体：小 大】【发表评论】【加入收藏】【告诉好友】【打印此文】【关闭窗口】 |
用户登录 
新用户注册 
网友评论：（只显示最新10条。评论内容只代表网友观点，与本站立场无关！）
