PowerShellでExcelファイルを生成し、CSVから読み込んだ内容を反映する処理を実装したのでメモ

実際に実装した時にはあっちこっち検索して大変だったので、PowerShellでExcelファイルを生成する時に実装するであろう機能はある程度書いておきたいと思います。

結合したセルを折り返し可能にしており、高さを自動調整するのは、ExcelをGUI操作していてもうまくいきません。その場合は一度セルの結合を解除して、高さを取得し...という処理も完成系には実装していますので、ぜひ参考にしてみてください。

環境

Windows 10 Home, Core i7 1.8GHz, 16GB
PowerShell 5.1

今回作成する完成系イメージ

今回はこのファイルを作り、CSVを読み込んで、チェックリスト項目を設定し、行の高さを変えるところまで実装します。

PowerShellでExcelを扱うと、プロセスを消さないと、プロセスにMicrosoft Excelが溜まり続けてしまいます。プロセスを消す方法は、この記事が非常にわかりやすいので、私も参考にしました。try-catch文にして、fainallyでプロセスクリア処理を入れています。
もし、`$height = $sheet.Cells.Item($row, 3).RowHeightなどと変数にExcel内の何かを代入した場合は、$heightもリリースする必要がありました。この辺が一番実装で苦労しました。

Excelファイル全体

# オブジェクト生成
$excel = New-Object -ComObject Excel.Application
# Excel画面を表示しない設定
$excel.Visible = $False
# 上書き保存時に表示されるアラートなどを非表示にする
$excel.DisplayAlerts = $False

# ブックを作成
$excel.Workbooks.Add()
# ブックを開く場合
# $excel.Workbooks.Open($path)

# 開いたブックのシートを取得(シート名でも取得可)
$sheet = $excel.Worksheets.Item(1)

# ----- ここで処理を行う

# ファイルの保存(第二引数はバージョンや保存形式)
$book.SaveAs($filepath, 51)

51というのは、xlWorkbookDefaultを指定しています。
Excel95, 97の.xls指定やExcelバージョン2.0なんてものも指定できます。

保存時の引数
保存形式の定数

上書き保存時に怒られる場合

同じPowerShell5.1を使っていたのですが、上書き保存しようとすると、ファイルを開いていなくてもPowerShell上でエラーが発生する場合があります。ググると、$excel.DisplayAlerts = $Falseでアラートを非表示にすればいい。と出てきますが、エラーが発生して、保存できていない事がありました。

その場合は、SaveAsではなく、Saveを使う事で上書き保存可能です。

if (Test-Path -Path $filepath) {
    # ファイルが存在
    $book.Save($filepath)
} else {
    # 新規保存
    $book.SaveAs($filepath)
}

目盛線

作成するExcelの目盛線を非表示にできます。
シートを取得した後に記述しないと、DisplayGridlinesなんてメンバ変数ないよ！と怒られます。

$excel.ActiveWindow.DisplayGridlines = $False

印刷設定

印刷範囲の指定や、縦横1ページに印刷する設定が可能です。

# 印刷範囲の設定
$sheet.PageSetup.PrintArea = "A1:F19"

# 1x1ページで印刷する設定
$sheet.PageSetup.Zoom = $False
$sheet.PageSetup.FitToPagesWide = 1
$sheet.PageSetup.FitToPagesTall = 1
$sheet.PageSetup.CenterHorizontally = $True

シート名の設定

$sheet.Name = "シート名"

シートは削除することも可能です。
$excel.WorkSheets.item("シート名").Delete()

セルの取得

フォントを指定したり、セルに値を挿入するには、対象のセルを指定する必要があります。
対象範囲の指定方法は主に4パターンあります。

# 列で指定(A列を取得)
$sheet.Columns("A")
# 行で指定(2行目を取得)
$sheet.Rows(2)
# 範囲で指定(A2～C5)
$sheet.Range("A2:C5")
# セルを指定D6(行, 列)
$sheet.Cells.Item(6, 4)

セルの値

セルには、値を入れる事はもちろん、数式の指定も可能です。

# 値指定
$sheet.Cells.Item(1, 1).Value = 'セルの値'
# 数式
$sheet.Cells.Item(5, 5).Formula = "=TODAY()"

セルの書式設定

日付型で、年月日表示がしたい。などのセルの書式設定

$sheet.Cells.Item(5, 5).NumberFormat = "yyyy年m月d日"

NumberFormat

折り返して表示

複数行のセルを折り返して表示する場合

$sheet.Cells.Item(1, 1).WrapText = $True

フォントの指定

フォントは、セルごとでも、範囲指定してまとめてでも指定可能です。

# フォントサイズ
$sheet.Cells.Item(1, 1).Font.Size = 16
# フォントの種類
$sheet.Ragne("B:C").Font.Name = "Meiryo UI"
# 太字
$sheet.Cells.Item(1, 2).Font.Bold = $True

文字寄せ

文字を寄せる場合、= centerなどとできればわかりやすいのですが、定数で指定する必要があります。

# 左右中央寄せ
$sheet.Cells.Item(2, 2).HorizontalAlignment = -4108
# 上下中央寄せ
$sheet.Cells.Item(2, 2).VerticalAlignment = -4108

表示位置の定数

色の指定

文字色、背景色を指定します。

ColorIndexで指定する場合はExcelのカラーパレットの番号になるため、パレットを変えていると、ColorIndexの値は変わるため、環境によって左右されます。

HEXで指定することが可能なので、HEX指定したほうが確実です。System.Drawingを使うことで、RGBでの指定もできます。

# colorindexで指定
$sheet.Cells.Item(1, 2).Font.ColorIndex = 1
# HEXで指定 &Hで始めるとHEX指定
$sheet.Cells.Item(1, 2).Font.Color = "&HD9D9D9"
# RGBで指定
$sheet.Cells.Item(1, 2).Font.Color = [System.Drawing.Color]::FromArgb(255, 204, 255)

# 背景色はInterior
$sheet.Cells.Item(1, 2).Interior.Color = "&H000000"

ColorIndex一覧

セルの結合

結合したい範囲をRangeで指定します。

$sheet.Range("A1:B10").MergeCells = $True

罫線

Borders()に定数を指定すれば、斜線や左、上などの指定が可能です。何も指定しなければ周囲すべてに罫線が引かれます。

# C1:D5のすべてのセルに普通の太さの実践を引く場合
$sheet.Range("C1:D5").Borders.LineStyle = 1
$sheet.Range("C1:D5").Borders.Weight = 2

# C1:D5の周囲を太線で囲む場合
[Void]$sheet.Range("C1:D5").BorderAround(1, -4138)

BorderAroundは[Void]型指定しなかった場合、返り値が実行画面に表示されます。

ロック

セルの保護

# 保護する
$sheet.Cells.Item(1, 1).Locked = $True
# 保護解除
$sheet.Cells.Item(1, 2).Locked = $False

セルを保護したら、シートの保護をしましょう

パスワードを指定しない場合は、誰でも保護を解除できます。

$sheet.Protect("password")

保護したシートに図形の貼り付けを許可するなど追加指定可能なオプション

入力値をリスト形式にする

入力規則を設定する事も可能です。
下ではリストから選択し、リストにない値の場合警告を表示する設定をしています。

リスト形式で指定する場合、すでに入力規則が設定されていると、エラーが発生するため、設定を削除してから、設定します。

[Void]$sheet.Cells.Item(5, 10).Validation.Delete()
[Void]$sheet.Cells.Item(5, 10).Add(3, 1, 1, "合格,不合格,保留")

入力規則の設定時の引数

完成系

項目設定用の取り込みCSV

checklist.csv

アイスは3日分の蓄えがあるか
ビールは2本以上冷蔵庫に入っているか
"水が冷やされているか
(炭酸水2本以上, 2L 1本)"
ハンカチが忘れない場所にストックされているか

try {
    # Excelファイルの保存先
    $filename = "チェックリスト.xlsx"
    $csvname = "checklist.csv"

    # 保護パスワード
    $password = "password"

    # ----- Do not change
    # このファイルのディレクトリを取得
    $path = [System.IO.Path]::GetDirectoryName($MyInvocation.MyCommand.Path)
    $filepath = $path + "\" + $filename
    $csvpath = $path + "\" + $csvname

    if (!(Test-Path -Path $csvpath)) {
        Throw "項目設定用CSVファイルなし"
    }

    # Excelの作成
    try {
        $excel = New-Object -ComObject Excel.Application
        
        # 表示しない
        $excel.Visible = $False

        # アラートを非表示
        $excel.DisplayAlerts = $False

        # ブックを作成
        $book = $excel.Workbooks.Add()

        # シートを取得
        $sheet = $excel.Worksheets.Item(1)

        # 開いたシートの目盛線のチェックを外す(目盛線非表示に)
        $excel.ActiveWindow.DisplayGridlines = $False
        
        # シート名を指定
        $sheet.Name = "チェックリスト"

        # 印刷設定
        $sheet.PageSetup.PrintArea = "A1:F19"
        $sheet.PageSetup.Zoom = $False
        $sheet.PageSetup.FitToPagesWide = 1
        $sheet.PageSetup.FitToPagesTall = 1
        $sheet.PageSetup.CenterHorizontally = $True
        
        # シートの中身の作成
        ## フォントを指定
        $sheet.Range("B:E").Font.Name = "Meiryo UI"
        ## 列の幅を指定
        $sheet.Columns("C").ColumnWidth = 50
        $sheet.Columns("E").ColumnWidth = 14.75

        ## タイトル部分
        $sheet.Range("B2:E2").MergeCells = $True
        $sheet.Cells.Item(2, 2).Value = "チェックリスト"
        $sheet.Cells.Item(2, 2).Font.Size = 16
        $sheet.Cells.Item(2, 2).Font.Bold = $True
        $sheet.Cells.Item(2, 2).HorizontalAlignment = -4108

        ## 氏名・更新日部分
        $sheet.Range("D4:E5").Borders.LineStyle = 1
        $sheet.Range("D4:E5").Borders.Weight = 2

        ### 氏名
        $sheet.Cells.Item(4, 4).Value = "氏名"
        $sheet.Cells.Item(4, 5).Locked = $False

        ### 更新日
        $sheet.Cells.Item(5, 4).Value = "更新日"
        $sheet.Cells.Item(5, 5).Formula = "=TODAY()"
        $sheet.Cells.Item(5, 5).NumberFormat = "yyyy年m月d日"

        ## 項目部分
        $sheet.Range("B7:E16").Borders.LineStyle = 1
        $sheet.Range("B7:E16").Borders.Weight = 2
        [Void]$sheet.Range("B7:E16").BorderAround(1, -4138)
        $sheet.Range("B7:E7").Interior.Color = "&HD9D9D9"
        $sheet.Range("B7:E7").HorizontalAlignment = -4108
        $sheet.Range("C7:D7").MergeCells = $True
        $sheet.Cells.Item(7, 2).Value = "No"
        $sheet.Cells.Item(7, 3).Value = "項目"
        $sheet.Cells.Item(7, 5).Value = "確認日"

        ### 項目の値部分
        for ($i = 8; $i -le 16; $i++) {
            $sheet.Range("C${i}:D${i}").MergeCells = $True
            $sheet.Range("C${i}:D${i}").WrapText = $True
            $sheet.Cells.Item($i, 5).NumberFormat = "yyyy年m月d日"
            $sheet.Cells.Item($i, 5).Locked = $False
        }

        ## 結果記入欄
        $sheet.Range("D18:E18").Borders.LineStyle = 1
        $sheet.Range("D18:E18").Borders.Weight = 2
        $sheet.Range("D18:E18").Interior.Color = [System.Drawing.Color]::FromArgb(255, 204, 255)
        $sheet.Cells.Item(18, 4).Value = "結果"
        [Void]$sheet.Cells.Item(18, 5).Validation.Delete()
        [Void]$sheet.Cells.Item(18, 5).Validation.Add(3, 1, 1, "合格,不合格,保留")
        $sheet.Cells.Item(18, 5).Locked = $False
        
        ## チェックリスト項目をCSVから作成
        $csv = Import-Csv -Path $csvpath -Encoding UTF8 -Header "list"
        for ($i = 0; $i -lt $csv.Length; $i++) {
            $row = $i + 8
            $sheet.Cells.Item($row, 2).Value = $i + 1
            $sheet.Cells.Item($row, 3).Value = [String]$csv[$i].list
            # 高さを取得・変更するために一度結合を解除
            $sheet.Range("C${row}:D${row}").MergeCells = $False
            # 項目列の高さに行の高さを合わせる
            $sheet.Rows($row).RowHeight = [Double]$sheet.Cells.Item($row, 3).RowHeight
            # セルを再結合
            $sheet.Range("C${row}:D${row}").MergeCells = $True
        }

        # シートを保護
        $sheet.Protect($password)

        # ファイルを保存
        $book.SaveAs($filepath, 51)

    } catch {
        Write-Error $_.toString()
    } finally {
        # プロセスを閉じて、ガベージコレクト
        if ($sheet) {
            [System.Runtime.InteropServices.Marshal]::ReleaseComObject($sheet) | Out-Null
            $sheet = $null
            Remove-Variable sheet -ErrorAction SilentlyContinue
        }
        if ($book) {
            [System.Runtime.InteropServices.Marshal]::ReleaseComObject($book) | Out-Null
            $book = $null
            Remove-Variable book -ErrorAction SilentlyContinue
        }

        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
        [System.GC]::Collect()

        if ($excel) {
            $excel.Quit()

            [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
            $excel = $null
            Remove-Variable -Name excel -ErrorAction SilentlyContinue

            [System.GC]::Collect()
            [System.GC]::WaitForPendingFinalizers()
            [System.GC]::Collect()
        }

    }
} catch {
    Write-Error $_.toString()
    Exit
}

生成結果

最後に

また、これどうやるのかなーと思ったときは、VBAのコードがそのまま使えたりもするので、「PowerShell Excel -vba ～～～」で知らべるよりも、最初からVBAで調べたほうがうまくいったりします。