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()

Index

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:51 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(51): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(66): id_of(Object(PDO), 'uri', '/notes/Windows/...') #2 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/Windows/...', 1759398181, '216.73.216.42', 'Mozilla/5.0 App...', NULL) #3 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/Windows/PowerShell/command-inventory/noun/threadJob/start/excel(93): insert_webrequest() #4 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 51