PowerShellでExcelを操作する方法｜読み取り・書き込み・自動処理をサンプルで解説

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false

$workbook = $excel.Workbooks.Open("C:\Data\report.xlsx")
$sheet = $workbook.Sheets.Item(1)

# セルの内容を取得
$value = $sheet.Cells.Item(2, 1).Text
Write-Host "A2の値は：$value"

# 終了処理
$workbook.Close($false)
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false

$workbook = $excel.Workbooks.Open("C:\Data\report.xlsx")
$sheet = $workbook.Sheets.Item(1)

# A2セルに書き込み
$sheet.Cells.Item(2, 1).Value2 = "売上合計"

# 上書き保存して閉じる
$workbook.Save()
$workbook.Close($false)
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false

$workbook = $excel.Workbooks.Add()
$sheet = $workbook.Sheets.Item(1)

# 見出しとデータを入力
$sheet.Cells.Item(1,1).Value2 = "名前"
$sheet.Cells.Item(1,2).Value2 = "売上"

$sheet.Cells.Item(2,1).Value2 = "山田"
$sheet.Cells.Item(2,2).Value2 = 120000

# ファイルを保存
$savePath = "C:\Data\new_report.xlsx"
$workbook.SaveAs($savePath)
$workbook.Close()
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

Write-Host "Excelファイルを保存しました：$savePath"

$data = @(
@{Name = "佐藤"; Sales = 100000},
@{Name = "田中"; Sales = 95000},
@{Name = "鈴木"; Sales = 110000}
)

$row = 2
foreach ($entry in $data) {
$sheet.Cells.Item($row, 1).Value2 = $entry.Name
$sheet.Cells.Item($row, 2).Value2 = $entry.Sales
$row++
}