業務Powershell：テンプレExcelファイルにTSVを貼り付け別名で保存する。

はじめに

どういった業務シーンで使用されているか。

• データベースから日次でTSVファイルを作成している。
  • こちらは自動化済み
• Excelファイルの特定シートにそれを貼り付ける作業を手動で行っている。
  • 更新したシートをVLOOKUPして書式や計算がついたシートを利用する形
• データ接続による更新は余計なダイアログが出るので不可
  • 同様の理由でExcelマクロも不可
• 無論Windows標準のツール＋Officeの範囲で処理。

解決方法

俺たちの銀の弾丸、リーサル・ウェポン、 PowerShell の出番である。

実行環境

• Windows7 SP1 64bit
• PowerShell 2.0

実装

ソース

$templateFile = "C:\hoge.xlsx"
$saveFile = "C:\today.xlsx"
$tsvSheetName = "tsvsheet"

# Excelを起動
$excel = New-Object -ComObject Excel.Application
try {
    # 非表示、警告なしに設定
    $excel.Visible = $false
    $excel.DisplayAlerts = $false
    # テンプレートファイルを元に新しいファイルを作成
    $book = $excel.Workbooks.Add($templateFile)
    try{
        # 展開用シートを選択
        $sheet = $book.Sheets.Item($tsvSheetName)
        try{
            # シートをアクティブにしA1を選択する。
            $sheet.Activate()
            $sheet.Range("A1").Select()
            # 文字コードをSJISに変更する。
            $OutputEncoding = [Console]::OutputEncoding
            # CSVファイルをクリップボードに展開する
            Get-Content $tsvFile | Clip
            # 貼付けを行う
            $sheet.paste()
            # US-ASCII に戻す
            $OutputEncoding = New-Object System.Text.ASCIIEncoding
        }catch{}
        finally{
            # シートを開放する。
            [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)
        }
        # 新しい名前でファイルを保存
        $book.SaveAs($saveFile)
    }catch{}
    finally{
        #ブックを開放する。
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($book)
    }
}catch{}
finally{
    # Excel の終了
    $excel.Quit()  
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
}

参考にした記事

PowerShell で Excel をどうのこうのすることに興味を持ってくれると嬉しい
https://qiita.com/miyamiya/items/161372111b68bad0744a
Excelファイルを C# と VB.NET で読み込む "正しい" 方法
https://qiita.com/midori44/items/acab9106e6dad9653e73

ちょっとだけ解説

Excel.exeの開放について

　独学でExcelとからんだC#やVB.NETでの開発経験がある方であれば、必ず一度は通るExcel.exeプロセス残りまくり問題。現在では上記で記載した @midori44　さんの簡潔かつわかりやすい記事にスマートな最適解があります。
しかし、業務PowerShellの舞台では、上記記事の最適解は使用できません。

今回の様に日に一回の作業を自動化した際する際には、プロセス開放をお漏らしせずやる必要があるわけですが、PowerShell2.0 から try{}chatch{}finally{} 構文に対応しているので、@midori44さんの記事で それでもやりたいなら と記載して頂いている方策をPowerShellでは利用可能となっています。

クリップボードを利用したExcelファイルへの展開について

テキストの全データを展開するため、手っ取り早い手段としてクリップボードを経由する方法を利用しています。
PowerShell5.0 以降であれば Set-ClipBoard を利用できますが、Windows7標準のPowerShell2.0 での利用を最優先としていますので、今回はパイプで Get-Content した内容を Clip へ渡しています。