データのコピペを自動化してリストから神エクセルを量産する

## 改良しました

本記事のコードを改良して新たに記事を書きました。本記事も一応残しておきますが、これからやる方は新しいほうの記事↓を基にしたほうが良いです。

https://qiita.com/pippi1013/items/8f18a98bbb3debcd7ff9



## 改良前の記事ここから

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


## これを
![元データ.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3505596/596218e2-317f-d3cd-7e4b-023fd3e66102.png)

## こうする（そしてたくさんつくる）
![実行結果.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3505596/51125b0a-e804-d5fa-0e62-6d7f8fda1df4.png)


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


## どうやる

### ① ファイルを2つ作る

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

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

```powershell:Excelファイルを量産.ps1
####################
#生成方法
#①「template.xlsx」（ひな形）を用意する。
#②「template.xlsx」を同じフォルダにコピーする。コピーしたファイルの名前を「target.xlsx」に変更する。
#③「template.xlsx」の一番後ろにシートを追加し、シート名を「List」に変更する。
#④「List」シートのA列に作成予定のファイル名をリストアップする。B列以降に他の項目のリストを書いたり貼り付けたりする。数式を使ってもOK 1行目は見出しとする
#⑤「List」シートの空白セルに何もデータが入っていないことを確認する（④で入力したセル以外は一度削除すると確実）
#⑥「target.xlsx」の、レビュー記録表のテンプレートが記載されているシートを全選択し、セルの結合を解除する
#⑦「template.xlsx」「target.xlsx」を保存し、エクセルを閉じる
#⑧当ps1ファイルの「設定」部分(18行目～)に変数を設定し、保存する
#上記①～⑧を実行し、下記の状態になったら準備完了：
#「template.xlsx」編集していない状態のひな形がある。このほか、「List」シートがある
#「target.xlsx」すべてのセルの結合を解除した状態の、レビュー記録表のひな形シートがある
#当ps1ファイルの設定部分(18行目～)に情報を入力してある
####################

####################
#設定
$targetPath = Convert-Path "./target.xlsx"
$templatePath = Convert-Path "./template.xlsx"
#
#ひな形が何シート目にあるか
$sheetNo = 1
#
#セルの設定
#コピー先のセルを、target.xlsxのListシートの列数分だけ定義する
$targetCellMap = @{
    #A列はファイル名なので定義しない
    "B" = "I2" #B列のデータのコピー先
	"C" = "C2" #C列のデータのコピー先（以下同様にD,E,F…の要領でコピー先をリストに追加）
	"D" = "C4"
	"E" = "C5"
	"F" = "C6"
	"G" = "C7"
}
#
####################


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

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

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 {
		#テンプレートファイルを開く
		$templateBook = $excel.Workbooks.Open($templatePath, $updateLinks, $isReadOnly)
	}
	catch {
		Log "エクセルのオープンでエラーが発生しました。"
		throw
	}
	
	try {

        $row = 2 #見出しを飛ばすため2
		$listSheet = $templateBook.Sheets.Item("List")
		$templateSheet = $templateBook.Sheets.Item($sheetNo)

		while ($true) {
		
            $bookName = $listSheet.Cells.Range("A" + $row).Text
		
            if(($bookName -eq $null) -or ($bookName -eq "")) {
                Log "A列にファイル名がないため、ループを抜けます。"
                break
			}
			
			Log "$bookName を作成中..."
			
            #ターゲットファイルを開く
			$targetBook = $excel.Workbooks.Open($targetPath, $updateLinks, $isReadOnly)
            $targetSheet = $targetBook.Sheets.Item($sheetNo)

            #TemplateブックのListシートから情報をコピー＆Targetブックに貼り付け
			$targetCellMap.Keys | ForEach-Object {
                $targetCell = $targetCellMap[$_]
                $sourceColumn = $_
				
				$listSheet.Range($sourceColumn + $row).Copy() | Out-Null
                $targetSheet.Range($targetCell).PasteSpecial(-4163) | Out-Null #値のみ貼り付け
			}
			
            #Templateブックのレビュー記録表シートから書式をコピー＆Targetブックに貼り付け
			$templateSheet.Cells.Copy() | Out-Null
			$targetSheet.Cells.PasteSpecial(-4122) | Out-Null #書式のみ貼り付け
			
			$path = Join-Path (Convert-Path .) $bookName
			
			$targetSheet.Cells(1,1).Select() | Out-Null
			$targetBook.SaveAs($path)
			$isSaveChanges = $true
			$targetBook.Close($isSaveChanges)
			
			Log "$bookName を作成しました。"
			
			$row++
		}
	}
	catch {
		Log "ファイルの作成でエラーが発生しました。"
		throw
	}
}
catch {
	$errorStr = $error[0] | Out-String
	Log $errorStr
	Log "エラーが発生したため、処理を終了します。"
}
finally {
    try {
		if ($excel -ne $null) {
			$excel.Quit()
		}
		$targetSheet = $null
		$listSheet = $null
		$templateSheet = $null
		$targetBook = $null
		$templateBook = $null
		$excel = $null
		[System.GC]::Collect()
	}
	catch {
		Log "finally処理の中でエラーが発生しました。"
		$errorStr = $error[0] | Out-String
		Log $errorStr
    }
	Log "********* Excelファイルを量産.ps1 終了 ***********"
	pause
}
```

### ②ひな形をtemplate.xlsxという名前で用意する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3505596/84455a47-c491-5b35-709b-9a2718f2d644.png)
シート名はなんでもよいです。他にシートがあってもかまいません

### ③ ②のファイルをコピーし、target.xlsxという名前に変更する。テンプレート部分のセルの全結合を解除しておく
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3505596/9712796e-4eb6-9a7b-76b3-facb2da266f9.png)
target.xlsxはこういう状態になります。これをやらないと処理の中でペーストに失敗します。

:::note info
きれいな状態の「template.xlsx」と、セル結合が解除された状態の「target.xlsx」ができればOKです。
:::

### ④ ②のtemplate.xlsxの一番後ろにListというシートを作成し、A列には生成予定のファイル名を書く。B列以降に元になるデータを書いておく。
こんな感じで書くなりコピペするなりします。数式などを使ってもかまいません。

:::note info
・1行目を見出し行とすること
・A列に生成予定のファイル名を書いておくこと
:::

![データ編集.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3505596/b404cda9-a6e4-41ee-4e8a-34b99684e44b.png)

### ⑤Excelファイルを量産.ps1 の設定を編集する
ひな形シートが何番目にあるか（デフォルトでは1）、何列目のデータをどのセルにコピーするのかを書きます
"Listシートの列のアルファベット" = "ひな形シートのコピー先のセル" と書いていきます
コピー先のセルが結合されている場合は一番左上のセルだけを書けばよいです
![コード編集.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3505596/e6127a6d-3654-e066-3869-f69671600217.png)

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

### ⑦Excelファイルを量産.batをダブルクリック
![キャプチャ.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3505596/9bb98cdf-b78b-096e-d3a9-4755b68c8a4f.png)
準備できてますか？と訊いてくるので、「はい(Y)」を押してください。

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3505596/21a1f5c5-7b6f-abd2-bfad-64364cad1313.png)
この表示になったら処理終了です。Enterキーを押してください。

### ⑧実行結果
ファイルが作成されます
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3505596/cc8b311f-24d4-46ac-a31a-ed765cb89943.png)
Excelファイルを量産.logというファイルも生成されます。ログなので適宜削除してください。


## 課題
- セルの設定がまだるっこしい
- 下準備は大したことないのに工数が多い
- ListシートのTextをコピペするのではなく配列に格納した方がスマートだと思ったが、1発で配列に格納する方法がわからなかった だれかご存知の方おしえてください
- 生成した神エクセルの再利用の方が重要？

## 参考資料

http://powershell.web.fc2.com/Html/Data/2012/12/12/0000.html

http://kamifuji.dyndns.org/PS-Support/PS_Excel/index.html

https://qiita.com/miyamiya/items/161372111b68bad0744a

https://qiita.com/ishi720/items/bac74397ec3521f8f868