从VB6导出更快，csv还是工作簿？ [英] Exporting from VB6 which is faster, csv or workbook?

问题描述

快速简单的问题，在vb6中导出到这些文件时是否有任何性能差异？



被要求检查一段代码，它使用工作簿将大量数据导出到excel并将其保存为.xlsx，我想知道是否只是打印到csv的行会更快吗？



目前它已导出22个字段，并且根据您希望导出的数据月数可能会有所不同，但平均值是每个月6000行。



不是最大的数量，但仍然需要几分钟才能运行，所以如果通过切换可以获得任何好处，我很好奇。 br />


数据被预加载到列表视图中供用户查看，因此不是数据库查询我要改进。

 对于 X =  1   To  .ColumnHeaders.Count 
 XSheet.Cells（ 1 ，X）= .ColumnHeaders（X）.text 
 下一步 
 I =  2  
 对于 Y =  1  要 .ListItems.Count 
 如果 .ListItems（Y）.Selected =  True  那么 
 XSheet.Cells （I +  1 ， 1 ）=。ListItems（Y）.text 
 对于 X =  1  要 .ColumnHeaders.Count  -  < span class =code-digit> 1  
 XSheet.Cells（I +  1 ，X +  1 ）=。ListItems（Y）.ListSubItems（X）.text 
  Next  
 I = I +  1  
 结束 如果 
 下一步 

解决方案

这个问题的答案只是基于意见...



上述方法都不比其他方法快。将数据写入目标文件的速度取决于数据的存储方式（与访问数据的速度一样快）。



将数据保存到Excel / csv文件的最快方法是使用ADODB方法。请参阅：

在Microsoft Visual Basic中使用ADO [ ^ ]

如何通过自动化将数据从ADO Recordset传输到Excel [ ^ ]

使用ADO CopyFromRecordset导出为CSV文件 [ ^ ]

CSV导出确实会更快，但你将失去做公式等电子表格的能力，你只能输出原始数据。

Quick and easy question, is there any performance difference when exporting to these files in vb6?

Got asked to check on a piece of code which exports a large amount of data to excel using workbooks and saves it as .xlsx and I'm wondering if just printing the rows to csv would be quicker?

Currently it's 22 fields total that's exported and depending on how many months of data you wish to export it can vary but the average is 6000 rows for each month.

Not the largest amount but it still takes a few minutes to run so if there is anything to gain by switching I'm curious.

The data is preloaded into a listview for users to view so it's not the database querying that I'm after to improve.

For X = 1 To .ColumnHeaders.Count
                XSheet.Cells(1, X) = .ColumnHeaders(X).text
            Next
            I = 2
            For Y = 1 To .ListItems.Count
                If .ListItems(Y).Selected = True Then
                    XSheet.Cells(I + 1, 1) = .ListItems(Y).text
                    For X = 1 To .ColumnHeaders.Count - 1
                        XSheet.Cells(I + 1, X + 1) = .ListItems(Y).ListSubItems(X).text
                    Next
                    I = I + 1
                End If
            Next

解决方案

The answer to this question is opinion based only...

None of above methods is faster than other. The speed of writing data to destination file depends on the way the data are stored (as fast as access to the data).

The fastest way to save data to Excel/csv file is to use ADODB methods. Please, see:
Using ADO with Microsoft Visual Basic[^]
How to transfer data from an ADO Recordset to Excel with automation[^]
Using ADO CopyFromRecordset to export to CSV file[^]

CSV export will indeed be quicker but you'll lose the ability to do "spread sheet" things like formulas etc, you can only output raw data.

这篇关于从VB6导出更快，csv还是工作簿？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！