Search notes:
PowerShell: Filling Excel sheets with multiple threads
This example tries to demonstrate how multiple threads, started with
start-threadJob
, can be used to fill Excel worksheets concurrently in PowerShell:
$xlsPath = "$env:temp\thread-test.xlsx"
if (test-path $xlsPath) {
remove-item $xlsPath
}
$xls = new-object -comObject excel.application
$xls.visible = $true
$su = $xls.screenUpdating
$xls.screenUpdating = $false
$wb = $xls.workbooks.add()
$shNames = 'one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine', 'ten'
$threads = @()
foreach ($shName in $shNames) {
$threads += start-threadJob {
param($wb, $shName)
$sh = $wb.worksheets.add()
$sh.name = $shName
foreach ($c in 1 .. 30) {
foreach ($r in 1 .. 30) {
$sh.cells($r, $c).numberFormat = '@'
$sh.cells($r, $c).value = "$c / $r"
}}
return $sh
} `
-throttleLimit $shNames.length `
-argumentList $xls,$shName
}
$null = wait-job -job $threads
$shs = @()
foreach ($thread in $threads) {
$shs += receive-job $thread
}
$wb.SaveAs($xlsPath)
$xls.screenUpdating = $su
# $xls.visible = $true
read-host "Press Enter to finish"
$xls.quit()
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb )
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xls)
foreach ($sh in $shs) {
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sh)
}
[GC]::Collect()
[GC]::WaitForPendingFinalizers()