将记录集导出到 Excel

发布于 2024-10-23 22:48:22 字数 147 浏览 7 评论 0原文

我正在尝试通过 VB 6.0 应用程序将 Adodb Recordset 导出到 Excel。我可以通过 For 循环来做到这一点。但记录集包含 100 列和 200000 行。因此，完成象牙需要花费大量时间。有时它会被吊死。有没有一种快速的方法可以达到同样的效果？提前致谢

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

海之角 2024-10-30 22:48:22

有一些方法可以显着提高 ADODB.Recordset 对象的性能。我用来加快速度的最佳技巧之一是创建显式 field 对象：

Dim rs As New ADODB.Recordset
Dim fldLastName As ADODB.Field, fldFirstName As ADODB.Field

rs.Open "SELECT au_lname, au_fname FROM authors", "DSN=pubs", , , adCmdText

Set fldLastName = rs.Fields("au_lname")
Set fldFirstName = rs.Fields("au_fname")

Do Until rs.EOF
    csvOutput = csvOutput & fldLastName & "," & fldFirstName & vbCrLf
    rs.MoveNext
Loop
rs.Close

这确实可以加快速度，因为您可以避免在引用 Recordset.Field.Value 项时进行后期绑定。尝试一下，您应该会看到性能的巨大改进。我曾经处理过像您所描述的那样的大型记录集，并且使用这种技术已经使性能变得可以忍受。

顺便说一句，MSDN 上有一篇很好的文章，其中有一些其他技巧可能有助于提高 ADODB 代码的性能 - ADO 性能最佳实践。这篇文章现在已经很老了，但我认为多年来第一次再次阅读它后，它仍然适用。

There are some ways to dramatically improve the performance of ADODB.Recordset objects. One of the best tips I've used to speed things up is to create explicit field objects in your loop:

Dim rs As New ADODB.Recordset
Dim fldLastName As ADODB.Field, fldFirstName As ADODB.Field

rs.Open "SELECT au_lname, au_fname FROM authors", "DSN=pubs", , , adCmdText

Set fldLastName = rs.Fields("au_lname")
Set fldFirstName = rs.Fields("au_fname")

Do Until rs.EOF
    csvOutput = csvOutput & fldLastName & "," & fldFirstName & vbCrLf
    rs.MoveNext
Loop
rs.Close

This can really speed things up because you avoid late binding in referring to the Recordset.Field.Value items. Give it a try and you should see a huge improvement in performance. I have worked with large recordsets like the one you described and the performance has been made tolerable using this technique.

By the way, there is a good article on MSDN that has some other tips that might help improve the performance of your ADODB code here - ADO Performance Best Practices. The article is quite old now but I think it all still applies after reading it again for the first time in years.

回复收藏 0 原文

葬心 2024-10-30 22:48:22

一些建议：

Excel 对象模型有一个 CopyFromRecordset 方法。

Recordset 对象有一个 GetRows 方法来返回一个数组，您可以将其“转置”到 Excel Range 对象的 Value 属性中 ('transpose' =列到行，反之亦然 - Excel 有一个 Transpose 工作表函数，可以通过其对象模型调用）。

请注意，在 Excel 2007 之前，工作表仅限于 65,536 行，您可能会发现上述 Excel 方法也受到类似限制。

Recordset 对象有一个 GetString 方法，可以将所有行一次写入字符串（无需循环）。

您也许可以完全绕过记录集并使用 Access SQL（ACE、Jet 等）将数据直接写入 Excel，例如

SELECT *
  INTO [Excel 8.0;DATABASE=C:\MyNewWorkbook.xls].MyNewTable 
  FROM [ODBC;Driver={SQL Server};Server=MyServer;Database=MyDB;UID=Admin;PWD=***].MyDBTable;

Some suggestions:

The Excel object model has a CopyFromRecordset method.

The Recordset object has a GetRows method to return an array which you can 'transpose' into an Excel Range object's Value property ('transpose' = columns to rows and vice versa -- Excel has a Transpose worksheet function that can be invoked via its object model).

Note that before Excel 2007, worksheets were limited to 65,536 rows and you may find that the abovementioned Excel methods are similarly limited.

The Recordset object has a GetString method to write all rows at once to a string (no need to loop).

You may be able bypass the recordset completely and use Access SQL (ACE, Jet, whatever) to write the data directly to Excel e.g.

SELECT *
  INTO [Excel 8.0;DATABASE=C:\MyNewWorkbook.xls].MyNewTable 
  FROM [ODBC;Driver={SQL Server};Server=MyServer;Database=MyDB;UID=Admin;PWD=***].MyDBTable;

回复收藏 0 原文

~没有更多了~