为什么在VBScript中写Excel计算单元值快而在PowerShell中写慢?

Why is it that writing cell values to Excel is a lot faster in VBScript than in PowerShell? Isn't PowerShell the new thing, and VBScript the deprecated MS scripting language?

为什么在VBScript中为Excel编写单元格值要比在PowerShell中快得多?PowerShell不是新事物吗，VBScript不是不赞成的MS脚本语言吗?

VBScript example (save to filename.vbs) This runs in a split second.

VBScript示例(保存为filename.vbs)这将在一秒内运行。

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = false
Set objWorkbook = objExcel.Workbooks.Add()

' Edit: increased number of writes to 500 to make speed difference more noticeable
For row = 1 To 500
     'Edit: using .cells(row,1) instead of .cells(50,1) - this was a mistake
     objWorkbook.workSheets(1).cells(row,1).value = "test"
Next

objWorkbook.SaveAs(CreateObject("Scripting.FileSystemObject").GetParentFolderName(WScript.ScriptFullName) & "\test.xlsx")
objExcel.Quit
msgbox "Done."

PowerShell example (save to filename.ps1) This takes multiple seconds to run (problematic on thousands of records)

PowerShell示例(保存为filename.ps1)运行需要多秒(在数千条记录上存在问题)

#need this to work around bug if you use a non-US locale: http://support.microsoft.com/default.aspx?scid=kb;en-us;320369
[System.Threading.Thread]::CurrentThread.CurrentCulture = "en-US" 

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $False
$xls_workbook = $excel.Workbooks.Add()

# Edit: using foreach instead of for
# Edit: increased number of writes to 500 to make speed difference more noticeable
foreach ($row in 1..500) {
    # Edit: Commented out print-line, slows down the script
    #"Row " + $row
    # This is very slow! - http://forums.redmondmag.com/forums/forum_posts.asp?tid=4037&pn=7
    $xls_workbook.sheets.item(1).cells.item($row,1) = "test"
}

$xls_workbook.SaveAs($MyInvocation.MyCommand.Definition.Replace($MyInvocation.MyCommand.Name, "") + "test.xlsx")
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

I want to use this for thousands of records. If there is no fast way to do this, PowerShell is not an option. Are there better alternatives?

我想把它用于成千上万条记录。如果没有快速的方法，PowerShell不是一个选项。有更好的选择吗?

5 个解决方案

#1

You can speed things up by not looping through individual cells:

你可以通过不通过单个细胞循环来加快速度:

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $True
$xls_workbook = $excel.Workbooks.Add()

$range = $xls_workbook.sheets.item(1).Range("A1:A100")
$range.Value2 = "test"

If you want to write an array of values to a range, here is a nice blog post that demonstrates similar technique:

如果你想在一个范围内写一个值数组，这里有一个不错的博客文章，展示了类似的技术:

How to Get Data into an Excel Spreadsheet Very Quickly with PowerShell

如何用PowerShell快速地将数据导入Excel电子表格

#2

some things don't add up here:

有些东西在这里没有意义:

your VBScript, writes on ONE cell over and over, while your PowerShell code writes into 100 cells

您的VBScript，在一个单元格上反复写入，而PowerShell代码则写入100个单元格

objWorkbook.workSheets(1).cells(50,1).value = "test"

$xls_workbook.sheets.item(1).cells.item($row,1) = "test"

you are executing "Row " + $row on PowerShell - this might offset comparison too.

您正在PowerShell上执行“Row”+ $ Row—这可能也会抵消比较。

If you want to write into multiple cells, you should think about using arrays and wrinting onto whole ranges, because this has better performance.

如果想要写入多个单元格，应该考虑使用数组并将其封装到整个范围内，因为这样有更好的性能。

#3

You can shave a little time off the PowerShell version by eliminating the for loop test and using a foreach.

通过消除for循环测试并使用foreach，您可以减少PowerShell版本的一些时间。

for ($row = 1; $row -le 100; $row++)

goes to:

去:

foreach ($row in 1..100)

By doing this you eliminate the comparison and increment.

这样做可以消除比较和增量。

But aside from that, my observations match yours (see my comments on Jook's answer).

但除此之外，我的看法与你的一致(参见我对Jook答案的评论)。

#4

You're still interfacing with Excel through COM though. That's adding some overhead due to COMInterop processing.

你仍然通过COM与Excel进行交互。由于COMInterop处理，这会增加一些开销。

#5

PowerShell, by its very design and use of cmdlets is a non-standard mess, at least for basic things. VBScript, which any programmer should be able to use and understand, has a general way of doing basic things that does not require special cmdlets to be installed or included with the deployed code. I believe this is a step backwards in many respects.

PowerShell，通过它的设计和使用cmdlet是一个非标准的混乱，至少对于基本的事情。VBScript是任何程序员都应该能够使用和理解的，它有一种通用的方法来做一些基本的事情，不需要特殊的cmdlet来安装或包含在已部署的代码中。我认为这在很多方面都是倒退。

Before anyone trashes me and says I just don't PowerShell, I must mention I have a long history of UNIX shell scripting behind me. PowerShell is similar, obviously, but to me its not nearly as well implemented.

在有人批评我并说我不支持PowerShell之前，我必须指出，我有很长的UNIX shell脚本编写历史。PowerShell很明显是类似的，但对我来说，它的实现还不够好。

I do know that reality dictates that I will end up using PowerShell sooner or later - I just hope it evolves into a more "standard" replacement in the future.

我知道现实告诉我，我迟早会使用PowerShell——我只是希望它在未来发展成为一个更“标准”的替代品。

#1