Powershell脚本使用Excel运行缓慢

So i have this script that i coded on my laptop that works just fine, the job is to combine two .csv-files into one .xls-file. And running the script with two .csv-files containing a couple of thousand rows takes a few seconds max.

所以我有这个脚本,我在我的笔记本电脑上编码,工作得很好,工作是将两个.csv文件合并到一个.xls文件中。使用包含几千行的两个.csv文件运行脚本最多需要几秒钟。

But when i try to run it on the server where it should be located, it takes... hours. I haven't done a full run, but writing one line in the .xls-file takes maybe 2-3 seconds.

但是当我尝试在它应该位于的服务器上运行它时,需要几个小时。我没有完整运行,但在.xls文件中写一行可能需要2-3秒。

So what im wondering is what is causing the huge increase in runtime. I'm monitoring the CPU-load while the script is running, and it's at 50-60% load.

所以我想知道是什么导致了运行时的巨大增长。我在脚本运行时监视CPU负载,并且负载为50-60%。

The server has loads of Ram, and two CPU-core. How can i speed this up?

服务器有大量的Ram和两个CPU核心。我怎样才能加快速度呢?

The script looks like this:

脚本如下所示:

$path = "C:\test\*"
$path2 = "C:\test"
$date = Get-Date -Format d
$csvs = Get-ChildItem $path -Include *.csv | Sort-Object LastAccessTime -Descending | Select-Object -First 2
$y = $csvs.Count

Write-Host "Detected the following CSV files: ($y)"
foreach ($csv in $csvs) {
    Write-Host " "$csv.Name
}

$outputfilename = "regSCI " + $date
Write-Host Creating: $outputfilename

$excelapp = New-Object -ComObject Excel.Application
$excelapp.sheetsInNewWorkbook = $csvs.Count
$xlsx = $excelapp.Workbooks.Add()
$sheet = 1
$xlleft = -4131

foreach ($csv in $csvs) {
    $row = 1
    $column = 1
    $worksheet = $xlsx.Worksheets.Item($sheet)
    $worksheet.Name = $csv.Name
    $worksheet.Rows.HorizontalAlignment = $xlleft
    $file = (Get-Content $csv)
    Write-Host Worksheet created: $worksheet.Name

    foreach($line in $file) {
        Write-Host Writing Line
        $linecontents = $line -split ',(?!\s*\w+")'

        foreach($cell in $linecontents) {
            Write-Host Writing Cell
            $cell1 = $cell.Trim('"')
            $worksheet.Cells.Item($row, $column) = $cell1
            $column++
        }
        $column = 1
        $row++
        $WorkSheet.UsedRange.Columns.Autofit() | Out-Null
    }
    $sheet++
    $headerRange = $worksheet.Range("a1", "q1") 
    $headerRange.AutoFilter() | Out-Null 
}

$output = $path2 + "\" + $outputfilename
Write-Host $output
$xlsx.SaveAs($output)
$excelapp.Quit()

1 个解决方案

#1

To speed up your existing code, add these just after creating Excel object:

要加速现有代码,请在创建Excel对象后立即添加:

$excelapp.ScreenUpdating = $false
$excelapp.DisplayStatusBar = $false
$excelapp.EnableEvents = $false
$excelapp.Visible = $false

And these just before SaveAs:

在SaveAs之前这些:

$excelapp.ScreenUpdating = $true
$excelapp.DisplayStatusBar = $true
$excelapp.EnableEvents = $true

This causes excel not to render the worksheet in realtime and fire events every time you change the contets. Most probably DisplayStatusBar and ScreenUpdating doesn't matter if you make an application invisible, but I included it just in case.

这导致excel不会在每次更改争议时实时渲染工作表并触发事件。如果你使一个应用程序不可见,很可能DisplayStatusBar和ScreenUpdating并不重要,但我把它包括在内以防万一。

Also, you're running Autofit() after every line. This certainly doesn't help with performance.

此外,您在每行之后运行Autofit()。这肯定对性能没有帮助。

#1