【自動化】データのリストから神エクセルを量産する【改良版】

下記記事の改良版です。ちょっと下準備の手順が減ったのと、ちょっと処理速度が上がりました。

※当記事に登場する人名地名等はすべて架空です。実在の人物地名その他いかなる個人情報とも関係ありません。
※※当記事は随時サイレント修正しています。コードの修正や重大なバグがあった場合のみお知らせいたします。

手順通りに下準備して実行するだけなので、制限のかかっていないWindowsのPCならソフトのインストール等をしなくてもできます。実行するだけならプログラミング知識も不要です。

これを

こうする（そしてたくさんつくる）

データを雛形にひとつひとつコピペすればいいのですが、とにかく数が多いので自動化します。
ひな形のフォーマットは問わないので、「指摘なし」のレビュー記録表なんかも量産できます。

どうやる

① テンプレを用意する

ひな型となるファイルを用意し、「target.xlsx」という名前で保存します。

※以前の記事ではひな型は「template.xlsx」でしたが、今回はtarget.xlsxです

② データを用意する

下記画像のようなデータを用意し、「data.xlsx」という名前で保存します。

・1行目を見出し行とすること
・A列に生成予定のファイル名を書いておくこと
・データ部分に数式を使っていてもOK

③ ファイルを2つ新規作成する

下記コードをメモ帳にコピーし、指定の通りに名前を付けて保存します。

Excelファイルを量産.bat

powershell -NoProfile -ExecutionPolicy Unrestricted "./Excelファイルを量産.ps1" >> Excelファイルを量産.log
exit

Excelファイルを量産.ps1

####################
#下準備：
#　「data.xlsx」データのリスト。1行目は見出し行、A列は生成予定のファイル名とする　数式を使ってもOK
#　「target.xlsx」生成予定のテンプレート
#　当ps1ファイルの設定部分(10行目～)に情報を入力する
#準備ができたらbatファイルをたたく
####################

####################
#設定
$tgtPath = Convert-Path "./target.xlsx"
$srcPath = Convert-Path "./data.xlsx"
#
#ひな形が何シート目にあるか
$tgtSheetNo = 1
#
#データが何シート目にあるか
$srcSheetNo = 1
#
#セルの設定
#コピー先のセルを、data.xlsxのデータシートの列数分だけ定義する
$tgtCellMap = @{
    #A列はファイル名なので定義しない
    "B" = "G5" #B列のデータのコピー先
	"C" = "AA5" #C列のデータのコピー先（以下同様にD,E,F…の要領でコピー先をリストに追加）
	"D" = "R8"
	"E" = "AF3"
}
#
####################


#　アセンブリの読み込み インプットボックス表示に必要
Add-Type -Assembly System.Windows.Forms

function Log($str) {
	$timestamp = Get-Date
	Write-Host "$timestamp $str"
}

function NumToAlphabet($num) {

    if($num -eq 0) {
        return $num
    }
}


try {

	Log "********* Excelファイルを量産.ps1 起動 ***********"

	$msgBoxTxt = "実行前にテンプレートを用意してください。用意手順はソースをご覧ください。実行してもよろしいですか？"
	$msgBoxInput = [System.Windows.Forms.MessageBox]::Show($msgBoxTxt,'アプリの実行確認','YesNo','Question')

	if($msgBoxInput -ne "Yes") {
		Log "ユーザー入力により処理を中止します。"
		Exit
	}
	
	# エクセルを開く準備
	$excel = New-Object -ComObject Excel.Application
	$excel.Visible = $false
	$excel.DisplAyalerts = $false
    $excel.ScreenUpdating = $false
    $excel.EnableEvents = $false

   	$updateLinks = 0
	$isReadOnly = $false

	try {
		#srcブックを開く
		$srcBook = $excel.Workbooks.Open($srcPath, $updateLinks, $isReadOnly)
	}
	catch {
		Log "エクセルのオープンでエラーが発生しました。"
		throw
	}
	
	try {

        #srcブック1シート目からデータを取得
		$srcSheet = $srcBook.Sheets.Item($srcSheetNo)
		$maxRowCnt = $srcSheet.UsedRange.Rows.Count
		
		#1列目は見出し行なので2から始める
		for($rowIndex = 2; $rowIndex -le $maxRowCnt; $rowIndex++) {
		
			$bookName = $srcSheet.Cells($rowIndex, 1).Text

			if([string]::IsNullOrEmpty($bookName)){
                Log "A列が未入力の行はスキップします。"
                continue
			}
			
			Log "$bookName を作成中..."
			
			#tgtファイルを開く
			$tgtBook = $excel.Workbooks.Open($tgtPath, $updateLinks, $isReadOnly)
			$tgtSheet = $tgtBook.Sheets.Item($tgtSheetNo)
			
			$tgtCellMap.Keys | ForEach-Object {

				$tgtCellStr = $tgtCellMap[$_]
                $srcColumnStr = $_
				
				try {
                    $text = $srcSheet.Range($srcColumnStr + $rowIndex).Text
                }
				catch {
                    Log ("データを取り出す際にエラーが発生しました。「セルの設定」左側を確認してみてください。エラーが発生したセル：" + $srcColumnStr + $rowIndex)
					throw
				}
				
				$tgtSheet.Activate()
				try {
                    $tgtSheet.Range($tgtCellStr) = $text
				}
				catch {
                    Log ("取り出したデータをテンプレートに書き込む際にエラーが発生しました。「セルの設定」右側を確認してみてください。エラーが発生したセル：" + $tgtCellStr)
					throw
				}
			}
			
			$path = Join-Path (Convert-Path .) $bookName
			
			$tgtSheet.Cells(1,1).Select() | Out-Null
			
			$tgtBook.SaveAs($path)
			$isSaveChanges = $true
			$tgtBook.Close($isSaveChanges)
			
			Log "$bookName を作成しました。"
			
        }# End of for
	}
	catch {
		Log "ファイルの作成でエラーが発生しました。"
		throw
	}
}
catch {
	$errorStr = $error[0] | Out-String
	Log $errorStr
	Log "エラーが発生したため、処理を終了します。"
}
finally {
    try {
		if ($excel -ne $null) {
			$excel.Quit()
		}
		$tgtSheet = $null
		$srcSheet = $null
		$tgtBook = $null
		$srcBook = $null
		$excel = $null
		[System.GC]::Collect()
	}
	catch {
		Log "finally処理の中でエラーが発生しました。"
		$errorStr = $error[0] | Out-String
		Log $errorStr
    }
	Log "********* Excelファイルを量産.ps1 終了 ***********"
	pause
}

④ Excelファイルを量産.ps1 の設定を編集する

ひな形シート・データシートが何番目にあるか（デフォルトでは1）、何列目のデータをどのセルにコピーするのかを書きます
"Listシートの列のアルファベット" = "ひな形シートのコピー先のセル" と書いていきます
コピー先のセルが結合されている場合は一番左上のセルだけを書けばよいです

⑤ data.xlsx、target.xlsx、Excelファイルを量産.ps1を保存して閉じる

⑥ Excelファイルを量産.batをダブルクリック

準備できてますか？と訊いてくるので、「はい(Y)」を押してください。

処理中はPCを操作しないでください。

この表示になったら処理終了です。Enterキーを押してください。

⑦実行結果

ファイルが作成されます

Excelファイルを量産.logというファイルも生成されます。ログなので適宜削除してください。

改善したこと

• 処理が速くなった
  前回は、ExcelとExcelを反復横跳びしデータを値コピペ→最後に書式コピペという力業で処理していたのですが、値コピペを下記のように記述することで、反復横跳びをやめクリップボードを介さなくなったので早くなりました。書式コピペもいらなくなりました
  $tgtSheet.Range($tgtCellStr) = $srcSheet.Range($srcColumnStr + $rowIndex).Text
• 下準備が減った
  前述の「最後に書式コピペ」がいらなくなったので下準備が減りました
• $srcSheet.UsedRange.Rows.Count を使うことで、Aセルに値がない場合はスキップして次の行を処理できるようになりました（前はAセルに値がなかったら処理をやめていた）

課題

• セルの設定がまだるっこしい
• 最初はデータを配列に格納していたのだが、RowとColumnでループを回して必要なセルだけ取り出すのが想像以上に面倒だったのでやめました。結果的にコードのスマートさがありません
• 生成した神エクセルの再利用の方が重要？

参考資料