
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列に書き込み
となっています.
作成したコード
上で実行した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 "終了!"