PowerShellでExcel操作

f:id:IsThisAPen:20180826152830j:plain:w200

POINT

PowerShellでExcel操作する方法．
データファイルの内容をテンプレートファイルに書き込む操作を自動化した．

PowerShellでIEを操作したのに引き続き，Excelを操作してみました．やってみるととても簡単です！

やりたいこと
実行結果
作成したコード
- 実行方法
- コード
参考記事

やりたいこと

デスクトップに日付名（yyyyMMddhhmmss）フォルダを作成する．
テンプレートファイルをコピーする．
データファイル「各行」の値を，テンプレートファイルの指定したセルに書き込む（詳しくは，次の「実行結果」を参照）．
作成したファイルを，最初に作った日付名フォルダに保存する．

実行結果

以下のようなデータファイル（左）の各行の値を，テンプレートファイル（右）に書き込みます．今回は，実行結果に示すように，

データファイルの各行のA列を，作成するファイル名に指定
データファイルの各行のA, B列を，テンプレートファイルのB列に上書きする
データファイルの各行のC〜E列を，テンプレートファイルのA列に上書きする

という操作を行ってみました（もちろん，今回作成したスクリプトを少し書き換えるだけで，どんな書き込み方でも可能です）

実行すると，左下のような日付フォルダの中にファイルが作成されます．このフォルダの中のa4.xlsxの中身が右下の図です．a4.xlsxは，データファイルの4行目の値をもとにテンプレートファイルを編集したものです．まとめると，

データファイルの各行が，作成する1ファイル分のデータを表す
各ファイルの名前：データファイルの各行の1列目の値（テンプレートファイルをコピーし，データを書き込んだ後，a1.xlsx〜a16.xlsxという名前で保存する）．
テンプレートファイルに上書きしたセル：緑でハイライトする．
データの書き込み方：
- 最初の2列：B列に書き込み
- 2〜5列目：A列に書き込み

となっています．

実行結果．左：作成されたフォルダとファイル．右：a4.xlsxの内容（データファイルの4行目の内容を，テンプレートファイルの緑の部分に上書きした）．

excelファイルの内容 — 実行結果．左：作成されたフォルダとファイル．右：a4.xlsxの内容（データファイルの4行目の内容を，テンプレートファイルの緑の部分に上書きした）．

作成したコード

上で実行したPowerShellスクリプトについて解説します．

実行方法

実行方法を詳しく書いておきます：

下で紹介するコードを「Excel.ps1」という名前で保存する．
次の記事で作成した，PowerShell実行用のバッチファイルにドラッグ・アンド・ドロップする．
コマンドプロンプトが起動し，"データファイルのpath" を聞かれる．データファイルとして使うexcelファイルをコマンドプロンプトにドラッグ・アンド・ドロップ（これで，コマンドプロンプトにパスが入力される）してリターンキーを入力．
"テンプレートファイルのpath"を聞かれるので，テンプレートファイルとして使うexcelファイルをドラッグ・アンド・ドロップしてリターンキーを入力．
デスクトップに日付名フォルダが作成され，実行結果のファイルが保存される．

コード

各処理の解説はコメントとして記入しています．やっていることはすごく単純です：

配列の中にデータを入れる
配列のデータをExcelのセルに代入する

配列の成分は0,1,2,....で指定されるのに対し，Excelのセルは1,2,3,...で指定されることに注意が必要です．

# ---  ファイルの指定 ---
$dataFilePath = Read-Host "データファイルのpath"
$templateFilePath = Read-Host "テンプレートファイルのpath"

#  ---  DesktopにyyyyMMddのフォルダ作成 ---
$path = ${HOME} + "\Desktop\"
$dateFormat = "yyyyMMddhhmmss"
$saveDirName = $(Get-Date).ToString($dateFormat)
New-Item -path $path -name $saveDirName -type directory

$savePath = $path + $saveDirName

# ---  Excelの起動 ---
$excel = New-Object -ComObject Excel.Application # Excelのプロセスを起動
$excel.Visible = $false                          # Excelを非表示
$excel.DisplayAlerts = $false                    # 警告メッセージを非表示

# --- データ処理 ---
# データファイルを開く
$dataBook = $excel.Workbooks.Open($dataFilePath)

# シートを取得
$dataSheet = $dataBook.Worksheets.Item(1)

# データ範囲取得
$lastRowIndex = $dataSheet.UsedRange.Rows.Count       # 最終行の行数取得
$lastColumnIndex = $dataSheet.UsedRange.Columns.Count # 最終列の列数取得

Write-Host "データ範囲："$lastRowIndex "×" $lastColumnIndex

# データを配列に格納
$array = New-Object "object[,]" $lastRowIndex,$lastColumnIndex  # 配列を定義
for($i = 0; $i -lt $lastRowIndex; $i++){

    $k = $i + 1
    Write-Host "データ取得中(" $k "/" $lastRowIndex ")"

    for($j = 0; $j -lt $lastColumnIndex; $j++){
        $array[$i,$j] = $dataSheet.Cells.Item($i+1,$j+1).Text
    }
}


# Read-Host "Enterで続行"

# テンプレートにデータ入力・保存
for($i = 0; $i -lt $lastRowIndex; $i++){
    $templateBook = $excel.Workbooks.Open($templateFilePath)    # テンプレートを開く
    $templateSheet = $templateBook.Worksheets.Item(1)           # シートを取得
    $newFileName = $array[$i,0] + ".xlsx"                       # ファイル名を指定

    # 進捗状況の表示
    $k = $i + 1
    Write-Host "データ書き込み中(" $k "/" $lastRowIndex ")"

    # データ入力
    for($j = 0; $j -lt $lastColumnIndex; $j++){
        if ($j -le 1){
            # j ≦ 1 を(j,2)セルへ書き込み
            $templateSheet.Cells.Item($j + 1, 2) = $array[$i,$j]
            $templateSheet.Cells.Item($j + 1, 2).interior.ColorIndex = 4      # 上書きしたセルの塗りつぶし（明るい緑）
        }else{
            # j > 1 を(j,1)セルへ書き込み
            $templateSheet.Cells.Item($j + 1, 1) = $array[$i,$j]
            $templateSheet.Cells.Item($j + 1, 1).interior.ColorIndex = 4      # 上書きしたセルの塗りつぶし（明るい緑）
        }
#        Write-Host $array[$i,$j]
    }

    # ファイルを保存する
    $templateBook.SaveAs("${savePath}\${newFileName}")                  # 保存
    Write-Host ${savePath}\${newFileName} を保存中...
}



# --- Excelの終了処理 ---
# Excelの終了(ワークブックを閉じるだけ)
$excel.Quit()

# プロセスを解放する
$excel = $null
[GC]::Collect()


Write-Host "終了！"

やりたいこと

実行結果

作成したコード

実行方法

コード

参考記事