【解説】PowerShellでExcelを操作（起動・開く・閉じる・セル操作）

悩んでいる人

Excelを用いた繰り返し作業が発生したため、自動化したい。実装例とともにやり方を教えて欲しい。

こんなお悩みを解決します。

今回は、データ整理などで、繰り返し作業にかかる時間を減らしたい方向けの記事となります。

ここでは、Excelの決まった位置に値を貼り付ける作業をPowerShellを用いて自動化し、仕事の効率化を目指します。

プログラミング処理が含まれてしまうので、万人向けではないですが、プログラムを書いたことがある方にとっては有用な内容になると思います。

PowerShellでExcelを操作する際の手順を解説していくので、興味がある方はぜひ最後までご覧ください。

今回の作業内容の想定

今回は、以下の左図に示すテキストファイルの内容を以下の右図に示すExcelファイルのC列3行目以降に貼り付ける、という作業を自動化したいと思います。

またExcelファイルのF列には、数式が入力されており、C列にデータが入力されると自動的に計算が行われるようになっています。

ここで、上記の状況について補足しておきます。

私は、システムエンジニアとして働いており、とある仕事で実機の処理時間計測を行っていました。

演算結果に対する品質保証の関係上、Excelファイルを利用して対応する必要がありました。

データは複数得られるため、取得したまとまりごとに分けて記録する必要があり、これらを手作業で行うと対応しきれなくなると考え、自動化して対応することにしました。

実装方針

内容に入る前に、実装方針を示しておきたいと思います。

以下の図に示すように、今回の作業内容を次の4ステップで実現します。

1. PowerShell経由でExcelを起動する。
2. PowerShell経由でデータの格納先が書かれたファイル一覧を取得する。
3. 2.で取得した一覧を順に参照し、書き込むデータを読み込む。
4. 読み込んだデータをExcelのワークシートに書き込む。

2.は、以下のような構成になっています。

今回の場合、20回分の計測結果があるため、3.と4.は20回繰り返されることになります。

./data/processTime_01.txt
./data/processTime_02.txt
./data/processTime_03.txt
./data/processTime_04.txt
./data/processTime_05.txt
./data/processTime_06.txt
./data/processTime_07.txt
./data/processTime_08.txt
./data/processTime_09.txt
./data/processTime_10.txt
./data/processTime_11.txt
./data/processTime_12.txt
./data/processTime_13.txt
./data/processTime_14.txt
./data/processTime_15.txt
./data/processTime_16.txt
./data/processTime_17.txt
./data/processTime_18.txt
./data/processTime_19.txt
./data/processTime_20.txt

実装

いつも通り、実装結果をGitHubに格納しておきます。

また、今回は記事中でも実装の話を解説したいと思います。

また、簡単のため、貼り付け先のExcelにはテンプレート用のシートを作成し、このシートをコピーする運用とします。

PowerShellスクリプト

まずは、PowerShellのスクリプトを以下に示しておきます。

# 引数処理
# 書き込むExcel Book
Param(
    [Parameter(Mandatory=$true)]$filename = "sample.xlsx",
    [Parameter(Mandatory=$true)]$listFile = "datalist.txt",
    $relativePath = "."
)

try {
    # 現在のディレクトリを取得
    $currentPath = (Convert-Path $relativePath)
    # テンプレートをコピー
    $templateFile = (Join-Path $currentPath "template.xlsx")
    $excelFile = (Join-Path $currentPath $filename)
    Copy-Item -Path $templateFile -Destination $excelFile -Force

    # Excelの起動
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false      # 画面上に表示させない
    $excel.DisplayAlerts = $true # 警告メッセージは表示する
    # Excel Bookを開く
    $book = $excel.Workbooks.Open($excelFile)

    # 読み込む対象ファイルを逆順で取得
    $lists = @(Get-Content -Path (Join-Path $currentPath $listFile))
    $lists[($lists.Length-1)..0] | ForEach-Object {
        $filepath = $_
        Write-Host $filepath
        # データ取得
        $values = @(Get-Content -Path (Join-Path $currentPath $filepath))

        # 「template」の後ろにシートをコピー
        $templateSheet = $book.Worksheets.item("template")
        $templateSheet.copy([System.Reflection.Missing]::Value, $templateSheet)
        # コピー後のシートを選択・シート名を変更
        $sheet = $book.Sheets($templateSheet.Next.Index)
        $sheet.Name = [System.IO.Path]::GetFileNameWithoutExtension($filepath)
        # 格納先の範囲を指定
        $target = "C3:C{0}" -f (3 + $values.Length - 1)
        # 指定した範囲に値を格納
        $sheet.Range($target).Value2 = $excel.WorksheetFunction.Transpose([double[]]$values)
        # 300ms待機
        Start-Sleep -m 300
    }
    # Excel Bookを保存して閉じる
    $book.Save()
    $excel.Workbooks.Close()
    # Excelの終了
    $excel.Quit()
}
finally {
    # 後処理
    $templateSheet, $sheet, $book, $excel | ForEach-Object{
        if ($null -ne $_) {
            [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($_)
        }
    }
}

以降では、実装方針で示した内容と照らし合わせて解説します。

また、スクリプトの引数は、最後にまとめて説明します。

①Excelの起動

Excelの起動に関連する処理を抜き出して説明します。

New-Object -ComObject Excel.Applicationにより、Excelプロセスが起動します。

処理過程を描画する必要はないため、Visibleをfalseとします。

ただし、警告メッセージは表示させたいため、DisplayAlertsをtrueとします。

そして、$excel.Workbooks.Open($excelFile)により、対象のExcelファイルを開きます。

    # Excelの起動
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false      # 画面上に表示させない
    $excel.DisplayAlerts = $true # 警告メッセージは表示する
    # Excel Bookを開く
    $book = $excel.Workbooks.Open($excelFile)

また、開いたExcelファイルには変更を加えているため、保存して閉じます。

    # Excel Bookを保存して閉じる
    $book.Save()
    $excel.Workbooks.Close()
    # Excelの終了
    $excel.Quit()

Excelファイルは閉じましたが、最初に起動したExcelプロセスは起動したままなので、Excelプロセスの終了処理を実行します。

ここでは、途中で例外が発生したことを考慮し、変数のnullチェックを行っています。

    # 後処理
    $templateSheet, $sheet, $book, $excel | ForEach-Object{
        if ($null -ne $_) {
            [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($_)
        }
    }

②データの格納先取得

Get-Contentコマンドを用いて、データの格納先が書かれたファイル一覧を配列に格納します。

    $lists = @(Get-Content -Path (Join-Path $currentPath $listFile))

③データの取得

こちらも同様に、Get-Contentコマンドを用いて、格納先のデータを配列に格納します。

        $values = @(Get-Content -Path (Join-Path $currentPath $filepath))

④Excelを操作してデータを書き込む

今回のメイン処理になります。実装の最初で示したように、テンプレートシートをベースに効率化をしていきます。

手順としては以下のようになります。

1. テンプレートシートを隣にコピーする。
2. コピーしたシート名を読み込むファイル名（拡張子なし）に変更する。
3. コピーしたデータをセルに貼り付ける。

PowerShellのコードと対応付けて示すと以下のようになります。

ここで、シートをコピーするcopyメソッドには、第一引数と第二引数があり、それぞれ以下のような使い分けをします。

ここで、[System.Reflection.Missing]::Valueは、該当する変数（ここでは第一引数）がないことを表します。

        # 「template」の後ろにシートをコピー
        $templateSheet = $book.Worksheets.item("template")
        #
        # 1. テンプレートシートを隣にコピーする。
        # 
        $templateSheet.copy([System.Reflection.Missing]::Value, $templateSheet)
        # コピー後のシートを選択・シート名を変更
        #
        # 2.コピーしたシート名を読み込むファイル名（拡張子なし）に変更する。
        #
        $sheet = $book.Sheets($templateSheet.Next.Index)
        $sheet.Name = [System.IO.Path]::GetFileNameWithoutExtension($filepath)
        #
        # 3.コピーしたデータをセルに貼り付ける。
        #   今回の場合、C3始まりで32個のデータを貼り付ける
        #
        # 格納先の範囲を指定
        $target = "C3:C{0}" -f (3 + $values.Length - 1)
        # 指定した範囲に値を格納
        $sheet.Range($target).Value2 = $excel.WorksheetFunction.Transpose([double[]]$values)

値の格納時にも注意点が存在します。配列をそのまま代入する場合、横方向に代入されます。

今回は、縦方向に代入処理を行いたいため、Transpose関数を用いて配列を転置した上で代入します。

また、配列内の要素がObjectとして扱われているため、セルに代入する際に数値になるよう、double型にキャストしています。

呼び出し方

今回作成したPowerShellのスクリプトは、実行時引数を取るような構成としています。

該当する箇所は以下の部分となります。

Param(
    [Parameter(Mandatory=$true)]$filename = "sample.xlsx",
    [Parameter(Mandatory=$true)]$listFile = "datalist.txt",
    $relativePath = "."
)

また、引数の意味は、それぞれ以下のようになります。

普段、PowerShellを呼び出す際のWrapperをbatファイルで作成しているため、ここでも同じようにbatファイルを作成しました。

実行時は、以下の内容が記載されたbatファイルを作成し、ダブルクリックにより実行することになります。

@echo off
cd /d %~dp0

powershell -ExecutionPolicy Unrestricted .\excel_automation.ps1 summary.xlsx list.txt

実行結果

一部となりますが、実行結果は以下のようになります。

シートのコピーにより、コピー後のシート名も更新されています。

また、該当のセルが期待通り更新できていることが分かります。

今回参考にしたサイト

今回は、Microsoftの公式サイトを参考に、実装をしています。

https://docs.microsoft.com/ja-jp/office/vba/api/excel.worksheet.copy

https://docs.microsoft.com/ja-jp/office/vba/api/excel.worksheets.item

https://docs.microsoft.com/ja-jp/office/vba/api/excel.worksheetfunction.transpose

PowerShellを活用して効率を上げよう！

この記事では、以下の2点について説明しました。

• PowerShellでExcelを操作できる。
  例：ファイルを開く、セルに値を書き込む、ファイルを保存する
• PowerShellでテキストファイルを読み込む方法を習得できる。

また、今回はテキストファイルに記載された値の貼り付けがメインとなりましたが、応用することで他のユースケースに対しても利用できると思います。

さらに、Microsoftの公式サイトを参照することで、Excelの操作はカバーできました。

今回のユースケースを参考に、PowerShellの利用が広まると良いなぁと思っています。