PowerShellからのExcel操作時のApplication.Calculationの設定による速度の違いを測定をしてみた

# 開始時刻
$startTime = (Get-Date)
Write-Host "StartTime : " $startTime.ToString("HH:mm:ss.fffffff")


$excel = New-Object -ComObject Excel.Application
$book = $null

$excel.Visible = $false
$excel.DisplayAlerts = $false
$excel.ScreenUpdating = $false


$fileName  = "Book1.xlsx"
$filePath = (Convert-Path .) + "/" + $fileName

$book = $excel.Workbooks.Open($filePath)
$sheet = $book.Sheets("Sheet1")

$Calculation = "microsoft.office.interop.excel.XlCalculation" -as [type]


# 自動計算 OFF
$excel.Calculation = $Calculation::xlCalculationManual 

# 負荷をかける処理
for ( $i=1; $i -le 10000; $i++){

    # C列:計算式の入力(A列+B列)　※再計算の確認のため、先に数式を用意しておく
    $formula = "=A" + $i + "+B" + $i
    $sheet.Cells.Item($i,3).Formula = $formula
    
    # A列
    $sheet.Cells.Item($i,1).Value = $i
    # B列
    $sheet.Cells.Item($i,2).Value = $i + 1

}

# 自動計算 ON
$excel.Calculation = $Calculation::xlCalculationAutomatic 

$excel.ScreenUpdating = $true

[void]$book.Save()
[void]$book.Close($false)
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($book)

[void]$excel.Quit()
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

# 終了時刻
$endTime = (Get-Date)
Write-Host "EndTime : " $endTime.ToString("HH:mm:ss.fffffff")
# 時間差
Write-Host "TimeSpan : " ($endTime - $startTime).ToString("")


Pause