给你我写的一个函数,速度很快的,两千条记录大概2秒钟,将数据写到Listview里 '======================================== '函数名 showADOData '函数简介 在Listview中显示指定表数据 '简要说明 ColHeadersText 与 FieldsName 的个数必须与 Recordset 的 Fields 的数目一样 ' Listview 的 View 属性应该是 lvwReport ' 可以省略列表头文字,默认与 FieldsName 文字一致 '入口参数 lvwToShow As ListView, ' ado_RS As Recordset ' FieldsName As Collection, ' Optional ColHeadersText As Collection = FieldsName, ' Optional widthAverage As Boolean = False '返回值 Boolean '创建日期 2002-2-9 '作者 刘丰 '========================================Public Function showADOData(lvwToShow As ListView, ByVal ado_RS As Recordset, ByVal FieldsName As Collection, Optional ByVal ColHeadersText As Collection, Optional ByVal widthAverage As Boolean = False) As Boolean On Error Resume Next Dim i, tableFields As Integer 'tableFields表字段计数器 Dim isHave As Boolean Dim oldSorted As Boolean '设置 Sorted 属性,可以加快速度 Dim oldPosition As Long '保存Recordset记录指针位置
'设置默认值 If ColHeadersText.Count Then Set ColHeadersText = FieldsName 'MsgBox ColHeadersText.Count
'列标头与Recordset的Fields个数必须一样 If Not ColHeadersText.Count = FieldsName.Count Or FieldsName.Count > ado_RS.Fields.Count Then showADOData = False Exit Function End If
'添加列标头 For i = 1 To ColHeadersText.Count If widthAverage Then .ColumnHeaders.Add , , ColHeadersText(i), .Width / ColHeadersText.Count Else .ColumnHeaders.Add , , ColHeadersText(i) End If Next '添加内容 If Not ado_RS.EOF Then ado_RS.MoveFirst While Not ado_RS.EOF .ListItems.Add , , ado_RS.Fields(FieldsName(1)) tableFields = tableFields + 1 For i = 1 To FieldsName.Count 'DoEvents '影响速度 If IsNull(ado_RS.Fields(FieldsName(i))) Then .ListItems(tableFields).ListSubItems.Add , , "" End If .ListItems(tableFields).ListSubItems.Add , , ado_RS.Fields(FieldsName(1 + i)) Next ado_RS.MoveNext isHave = True Wend If isHave = False Then lvwToShow.ListItems.Clear .Sorted = oldSorted End With showADOData = True ado_RS.AbsolutePosition = oldPosition End Function
直接输出到打印对象,最快而且格式可以定制建议你看一下我说的那个网止
'========================================
'函数名 showADOData
'函数简介 在Listview中显示指定表数据
'简要说明 ColHeadersText 与 FieldsName 的个数必须与 Recordset 的 Fields 的数目一样
' Listview 的 View 属性应该是 lvwReport
' 可以省略列表头文字,默认与 FieldsName 文字一致
'入口参数 lvwToShow As ListView,
' ado_RS As Recordset
' FieldsName As Collection,
' Optional ColHeadersText As Collection = FieldsName,
' Optional widthAverage As Boolean = False
'返回值 Boolean
'创建日期 2002-2-9
'作者 刘丰
'========================================Public Function showADOData(lvwToShow As ListView, ByVal ado_RS As Recordset, ByVal FieldsName As Collection, Optional ByVal ColHeadersText As Collection, Optional ByVal widthAverage As Boolean = False) As Boolean
On Error Resume Next
Dim i, tableFields As Integer 'tableFields表字段计数器
Dim isHave As Boolean
Dim oldSorted As Boolean '设置 Sorted 属性,可以加快速度
Dim oldPosition As Long '保存Recordset记录指针位置
'设置默认值
If ColHeadersText.Count Then Set ColHeadersText = FieldsName
'MsgBox ColHeadersText.Count
'列标头与Recordset的Fields个数必须一样
If Not ColHeadersText.Count = FieldsName.Count Or FieldsName.Count > ado_RS.Fields.Count Then
showADOData = False
Exit Function
End If
With lvwToShow
'初始化
.ColumnHeaders.Clear
.ListItems.Clear
oldSorted = .Sorted
.Sorted = False
oldPosition = ado_RS.AbsolutePosition
'添加列标头
For i = 1 To ColHeadersText.Count
If widthAverage Then
.ColumnHeaders.Add , , ColHeadersText(i), .Width / ColHeadersText.Count
Else
.ColumnHeaders.Add , , ColHeadersText(i)
End If
Next
'添加内容
If Not ado_RS.EOF Then ado_RS.MoveFirst
While Not ado_RS.EOF
.ListItems.Add , , ado_RS.Fields(FieldsName(1))
tableFields = tableFields + 1
For i = 1 To FieldsName.Count
'DoEvents '影响速度
If IsNull(ado_RS.Fields(FieldsName(i))) Then
.ListItems(tableFields).ListSubItems.Add , , ""
End If
.ListItems(tableFields).ListSubItems.Add , , ado_RS.Fields(FieldsName(1 + i))
Next
ado_RS.MoveNext
isHave = True
Wend
If isHave = False Then lvwToShow.ListItems.Clear
.Sorted = oldSorted
End With
showADOData = True
ado_RS.AbsolutePosition = oldPosition
End Function
http://www.csdn.net/cnshare/soft/12/12442.shtm
于是逐一的将这个二维表元素写到EXECL的每个单元格内,
一共用了不到两分钟
这个时间还是很长的,查询的速度还是很快的,看来时间主要在了逐一的
写入EXECL的单元格时用掉了。
除了有一次批量写入的方法,
不然的话,若是一条一条的写,速度一定不会快起来的
写文本就快多了