加快在Powershell中阅读Excel文件 [英] Speed up reading an Excel File in Powershell

问题描述

我想知道是否有任何方法可以加快使用powerhell读取Excel文件。许多人会说，我应该停止使用直到，但问题是我需要的不好，因为在我的Excel表中可以有2行或5000行。我明白5000行需要一些时间。但是2行不应该需要90秒+。

  $ Excel = New-Object -ComObject Excel.Application 
 $ Excel .Visible = $ true 
 $ Excel.DisplayAlerts = $ false 
 $ Path = EXCELFILEPATH 
 $ Workbook = $ Excel.Workbooks.open（$ Path）
 $ Sheet1 = $ Workbook .Worksheets.Item（test）
 
 $ URows = @（）
 Do {$ URows + = $ Sheet1.Cells.Item（$ Row，1）.Text; $ row = $ row + [int] 1} until（！$ Sheet1.Cells.Item（$ Row，1）.Text）
 $ URows | foreach {
 $ MyParms = @ {}; 
 $ SetParms = @ {}; 
  

我在脚本中也有30次：

  If（$ Sheet1.Cells.Item（$ Row，2）.Text）{$ var1 = $ Sheet1.Cells.Item（$ Row，2）.Text 
 $ MyParms.Add（PAR1，$ var1）
 $ SetParms.Add（PAR1，$ var1）} 
} 
  pre> 
 
 
我有同时运行$ MyParms的想法，但我不知道如何。任何建议？
 
 
 
或
 
 
 
提高阅读速度，但我不知道如何实现没有破坏读取直到没有任何东西。
 
 
 
或
 
 
 
速度是正常的，我不应该抱怨。
解决方案
附加到 + = 运算符的数组非常慢，因为它将所有元素从现有的数组复制到一个新的数组。使用这样的东西：
  $ URows = for（$ row = 1;！$ Sheet1.Cells.Item（$ row ，1）.Text; $ row ++）{
 if（$ Sheet1.Cells.Item（$ Row，2）.Text）{
 $ MyParms ['PAR1'] = $ Sheet1.Cells.Item （$ Row，2）.Text）
 $ SetParms ['PAR1'] = $ Sheet1.Cells.Item（$ Row，2）.Text）
} 
 $ Sheet1.Cells。项目（$行，1）.Text 
} 
  
您的循环基本上是一个计数循环。这种循环的规范形式是（init counter; condition; increment counter）{
 ... $ {
 
 $ b 
 b $ b} 
  
所以我相应地改变了循环。当然，你会得到如下相同的结果：
  $ row = 1 
 $ URows = Do {
 ... 
 $ row + = 1 
} 
  
这只是意味着更多的代码没有任何好处。但是，这种修改没有任何的性能影响。
 
 
 
相对于性能而言，另外两个变化是：
 
 
我把代码填入第一个循环内的哈希表，所以代码不会在数据上循环两次。使用索引和赋值运算符代替添加方法将值分配给散列表可防止代码在哈希表中已存在密钥时引发错误。
 
 
代码不再附加到数组（具有上述性能影响），代码现在只是回显循环中的单元格文本，PowerShell会自动将其变成列表。然后将该列表分配给变量 $ URows 。
 
 
 
I wonder if there is any way to speed up reading an Excel file with powershell. Many would say I should stop using the do until, but the problem is I need it badly, because in my Excel sheet there can be 2 rows or 5000 rows. I understand that 5000 rows needs some time. But 2 rows shouldn't need 90sec+.
$Excel               = New-Object -ComObject Excel.Application
    $Excel.Visible       = $true
    $Excel.DisplayAlerts = $false
$Path = EXCELFILEPATH
$Workbook            = $Excel.Workbooks.open($Path)
$Sheet1 = $Workbook.Worksheets.Item(test)

$URows = @()
Do {$URows += $Sheet1.Cells.Item($Row,1).Text; $row = $row + [int] 1} until (!$Sheet1.Cells.Item($Row,1).Text)
$URows | foreach {
$MyParms = @{};
$SetParms = @{};
And i got this 30 times in the script too:    
If ($Sheet1.Cells.Item($Row,2).Text){$var1    = $Sheet1.Cells.Item($Row,2).Text
$MyParms.Add("PAR1",$var1)
$SetParms.Add("PAR1",$var1)}
                 }
I have the idea of running the $MyParms stuff contemporarily, but I have no idea how. Any suggestions?

Or

Increase the speed of reading, but I have no clue how to achieve that without destroying the "read until nothing is there".

Or

The speed is normal and I shouldn't complain.
解决方案
Appending to an array with the += operator is terribly slow, because it will copy all elements from the existing array to a new array. Use something like this instead:
$URows = for ($row = 1; !$Sheet1.Cells.Item($row, 1).Text; $row++) {
           if ($Sheet1.Cells.Item($Row,2).Text) {
             $MyParms['PAR1']  = $Sheet1.Cells.Item($Row, 2).Text)
             $SetParms['PAR1'] = $Sheet1.Cells.Item($Row, 2).Text)
           }
           $Sheet1.Cells.Item($Row,1).Text
         }
Your Do loop is basically a counting loop. The canonical form for such loops is
for (init counter; condition; increment counter) {
  ...
}
so I changed the loop accordingly. Of course you'd achieve the same result like this:
$row = 1
$URows = Do {
           ...
           $row += 1
         }
but that would just mean more code without any benefits. This modification doesn't have any performance impact, though.

Relevant in terms of performance are the other two changes:

I moved the code filling the hashtables inside the first loop, so the code won't loop twice over the data. Using index and assignment operators instead of the Add method for assigning values to the hashtable prevents the code from raising an error when a key already exists in the hashtable.
Instead of appending to an array (which has the abovementioned performance impact) the code now simply echoes the cell text in the loop, which PowerShell automatically turns into a list. The list is then assigned to the variable $URows.


                        
这篇关于加快在Powershell中阅读Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！