我的代码中我使用的是数据库来写的,你可以看看~~~
DAO的
Set datx = OpenDatabase(txtpath, False, False, "Excel 8.0;")
Set rx = datx.OpenRecordset("invoice$")
rx.AddNew
rx!f4 = IIf(IsNull(rs!Size), "", rs!Size)
rx!f5 = IIf(IsNull(rs!qty), 0, Format(rs!qty, TT1))
rx!f6 = IIf(IsNull(rs!price), 0, Format(rs!price, TT2))
rx!f7 = IIf(IsNull(rs!amount), 0, Format(rs!amount, TT2))
rx!f8 = IIf(IsNull(rs!jhrq), "", Format(rs!jhrq, "yyyy-MM-dd"))
rx.Update
DAO的
Set datx = OpenDatabase(txtpath, False, False, "Excel 8.0;")
Set rx = datx.OpenRecordset("invoice$")
rx.AddNew
rx!f4 = IIf(IsNull(rs!Size), "", rs!Size)
rx!f5 = IIf(IsNull(rs!qty), 0, Format(rs!qty, TT1))
rx!f6 = IIf(IsNull(rs!price), 0, Format(rs!price, TT2))
rx!f7 = IIf(IsNull(rs!amount), 0, Format(rs!amount, TT2))
rx!f8 = IIf(IsNull(rs!jhrq), "", Format(rs!jhrq, "yyyy-MM-dd"))
rx.Update
SQL = "select * into [;database=" + App.Path + "\temp.mdb ].codesize from codesize where 1=1" datl.Execute "drop table codesize "
datt.Execute SQL
SQL = "INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source= " & App.Path & "\TEMP.mdb')...[KJKM] SELECT kmdm,kmmc,ccbz FROM [KJKM]"
Cn.Execute SQL
偷偷的告诉你~~~ 也许不能成功的
失败了不要怪我呀~~~
Set datl = OpenDatabase(App.Path & "\aa.xls", False, False, "Excel 8.0;")
SQL = "select * into codesize from [;database=" + App.Path + "\temp.mdb ].codesize where 1=1" datl.Execute "drop table codesize "
datt.Execute SQL
End Sub
'将报表中的数据复制到剪贴板上
Dim ClipStr As String
Clipboard.Clear
With Grid
Dim i, j As Integer
For i = 0 To .Rows - 1
For j = 0 To .Cols - 1
ClipStr = ClipStr & .TextMatrix(i, j) & vbTab
Next j
ClipStr = ClipStr & vbCr
Next iEnd With
Clipboard.SetText ClipStr
End Sub然后用xlsheet.Paste进行粘贴。你的程序改为:Private Sub Export()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet Screen.MousePointer = vbHourglass
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
copygrid me.flexgrid
xlsheet.Paste
xlApp.Visible = True
Screen.MousePointer = vbDefault
End Sub
Dim xlBook As Excel.Workbook Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
xlBook.sheets(1).Activate With xlBook.ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL server;SERVER=NF5100;UID=sa;PWD=;WSID=NF5100;DATABASE=xnbank" _
, Destination:=Range("A1"))
.Sql = Array( _
"SELECT * FROM xnbank.dbo.t_Bill t_Bill")
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With xlApp.Visible = True这里面用的是最简单的SQL语句, 里面nf5100是我的服务器,xnbank 是数据库名,我的t_bill 表中有12340条记录,用了整整1分钟,还算快。不过这和MSHFlexGrid没什么关系。其实没关系也没什么,关键是你的MSHFlexGrid是怎么来的。
若是比较复杂,可能要做一些前期处理,方便EXCEL取数。因为EXCEL好象只从表和视图中取数。佩服你的精神,那么快时间都测出来了。从你测的结果来看,少量的数据用剪贴板还挺快的, 若你的MSHFlexGrid太复杂,可不可以考虑将之分批复制过去,比如每100行复制一次。这样,剪贴板可能效果还可以。有时间我也会试一试。
我的MSFlexGrid是通过绑定ADO对象作为数据源的,数据源查询完成大概3到4s
充入控件时间可以忽略不计(3100条数据)
如果直接从表中取数,那我的查询等于要重做一次了其实我是先得到数据,然后在可能需要的情况下才传入EXCEL让客户进行特殊处理的如果像你所说的那样,我想问一下,EXCEL能否从ADO对象中直接读取数据
回答是:当然可以,我做过一个通用的数据报表系统,做它的目的是将报表客户化
为了让它支持各种数据库,所以用ADO通过ODBC连接数据源,直接把用户查询的结果显示到EXCEL中-------
我是从控件中直接取出来的----
不信你来看---
要说比较类似绑定的效果,应该就是我的第二种方法中写的,用QueryTables来做,应该比一个个填快。TO progame:
既然你的recordset 生成只要3-4S, 试试第二种方法,将其中的SQL语句、服务器名、数据库名等替换掉。我做试验用的表有12340条记录,有12个字段,用一分钟,比较快了。
SQL Server 2000的一个表,记录数:10322,字段数:9
工具:VB从表中读取所有的数据并创建一个新的Excel文件
每种方法连续测试5次,在VB中使用MsgBox (DateDiff("s", t1, Now()))计时(秒)
方法1:使用CopyFromRecordset
第一次:49
第二次:45
第三次:43
第四次:43
第五次:42
方法2:使用QueryTable
第一次:10
第二次:6
第三次:3
第四次:4
第五次:4测试代码如下:
方法1:
Option ExplicitPrivate 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 ExplicitPrivate 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
从VB中运行:(结果为0,即不到1秒)
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
out:(从数据库到文件)
d:\1.csv:(输出文件路径)
-w:(使用Unicode)
-t ,:(指定字段分割符为,)
-r \n:(指定纪录分割符为\n)
-S sz09:(服务器名称)
-P xxx: (用户密码)
-U xxx:(用户名,如果使用NT认证可以省略)
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
'Open the text file
Set oBook = oExcel.Workbooks.Open("d:\1.csv")
'Save as Excel workbook and Quit Excel
oBook.SaveAs "d:\1.xls", xlWorkbookNormal
oExcel.Quit