PowerShellでExcel操作

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

POINT

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

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

やりたいこと

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


実行結果

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

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

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

f:id:IsThisAPen:20180826145004j:plainf:id:IsThisAPen:20180826144751j:plain
左:データファイル.右:テンプレートファイル.



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

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

となっています.

f:id:IsThisAPen:20180901150715j:plainf:id:IsThisAPen:20180901150628j:plain
実行結果.左:作成されたフォルダとファイル.右:a4.xlsxの内容(データファイルの4行目の内容を,テンプレートファイルの緑の部分に上書きした).

作成したコード

実行方法

一応,実行方法を詳しく書いておきます:
  1. 下で紹介するコードを「Excel.ps1」という名前で保存する.
  2. 次の記事で作成した,PowerShell実行用のバッチファイルにドラッグ・アンド・ドロップする.
    www.mynote-jp.com
  3. コマンドプロンプトが起動し,"データファイルのpath" を聞かれる.データファイルとして使うexcelファイルをコマンドプロンプトにドラッグ・アンド・ドロップ(これで,コマンドプロンプトにパスが入力される)してリターンキーを入力.
  4. "テンプレートファイルのpath"を聞かれるので,テンプレートファイルとして使うexcelファイルをドラッグ・アンド・ドロップしてリターンキーを入力.
  5. デスクトップに日付名フォルダが作成され,実行結果のファイルが保存される.

コード

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

  1. 配列の中にデータを入れる
  2. 配列のデータを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 "終了!"


参考記事

プライバシーポリシー

お問い合わせ