PowerShell で Excel をいじる方法の備忘録

この記事について

いつも読むだけだったのですが、PowerShellでExcelをいじる情報が少なく、自分の備忘録としてもまとめたかったので、初めて記事にまとめてみました。
いろいろなところから引っ張ってきたコピペと、試してみてわかったものが混在しています。

きっかけ

仕事で、どうしてもExcelVBAで作業する必要があるものの、どうしてもxlsmに保存しなくてはならない（基本的には作業ファイルに保存する）というのが、めんどくさく感じていたところ、以下のPowerShell で Excel をどうのこうのすることに興味を持ってくれると嬉しいを読んで試してみました。

自分について

• 非IT職
• 基本情報技術者試験
• 家ではMac、仕事はWindows
• プログラミングは、独学の初級者レベル（ラズパイ財団のCodeClubのPythonを一通り終了した程度）

備忘録

スクリプトの実行

スクリプトの実行

# 同じディレクトリで
.\ScriptFileName.ps1 -fileName ExcelFile.xlsx

スクリプトの引数

引数

#スクリプトファイルの最初に書く
# mandatory = $trueとしている引数は必須。指定していないと聞かれる
# $visible = $true は初期値。消しておきたい場合は、実行時に -visible $falseとする
param(
    [parameter(mandatory=$true)]$fileName,
    $visible = $true)

Excelファイルを開く

ブックを開く

#Excelを開く
$excel = New-Object -ComObject Excel.Application
$book = $null
$excel.Visible = $visible
$excel.DisplayAlerts = $false
#ブックを開く
$FullPath = (Get-ChildItem -Path $filename).FullName
$book = $excel.Workbooks.Open($FullPath)

シート

シート

#シートを取得（シート名か、シートの位置）
$currentSheet = $book.Sheets("シート名")
$currentSheet = $book.Sheets(1)

#シートの名前を取得
$book.Sheets(1).Name
#シートの名前を変更
$book.Sheets("今のシート名").Name = "新しいシート名"

#シートを新規作成し、任意の名前に変更
$newSheet = $book.WorksSheets.Add()
$newSheet.Name = "新しいシート"

#シートの移動（1番目に移動）
$currentSheet.Move($book.Sheets(1))

セル

セル

#セルの文字列を取得
$R2C2Text = $currentSheet.Cells.Item(2, 2).Text
$B2Text = $currentSheet.Range("B2").Text

# 複数セルの文字列を取得（配列）
## R1C1形式
$B4B5Texts = $currentSheet.Range($currentSheet.Cells.Item(4,2),$currentSheet.Cells.Item(5,2))
## A1形式
$B4B5Texts = $currentSheet.Range("B4:B5")
## 取り出す
$B4B5Texts[1].Text #B4セルの文字列（例によって、インデックスは1始まり）
$B4B5Texts[2].Text #B5セルの文字列

複数セルの値の取得については以下を参照した

PowerShellでExcelの値をRangeで取得して取り出す（複数セルの場合）

列、行の操作

列、行の操作

[void]$currentSheet.Columns(1).Delete() #行番号

Alignの設定

Alignの設定

#右寄せ、左寄せ等
$currentSheet.Range("A:A").HorizontalAlignment = -4108 #xlCenter
$currentSheet.Range("A:A").VerticalAlignment = -4160 #xlTop
$currentSheet.Range("B:B").HorizontalAlignment = -4131 #xlLeft
$currentSheet.Range("B:B").VerticalAlignment = -4160 #xlTop
$currentSheet.Cells.WrapText = "True"

for

for

#列の幅
$listWidths = @(10, 20, 30, 40, 50, 60)
#Excelの行と列は1始まり（R1C1）なので、1ずらす
for ($i = 1; $i -le $listWidths.Count; $i++) {
     $currentSheet.Columns($i).ColumnWidth = $listWidths[$i -1]
    }

テーブル

#テーブルの取得
$myTable = $currentSheet.ListObjects("TableName")

#テーブルのデータをArrayListに格納する
##関数
function Get-TableData {
    param (
        [Parameter(Mandatory=$true)][System.Object]$srcTable
    )
    $srcValues = $srcTable.Range.Value2
    $dstArrayList = New-Object System.Collections.ArrayList
    for ($i = 2; $i -le $srcValues.GetLength(0); $i++) {
        $tmpProperty = New-Object -TypeName PSObject
        for ($j = 1; $j -le $srcValues.GetLength(1); $j++) {
            $tmpProperty | Add-Member -MemberType NoteProperty -Name $srcValues[1, $j] -Value $srcValues[$i, $j]
        }
        [void]$dstArrayList.Add($tmpProperty)
        $tmpProperty = ""
    }
    return $dstArrayList
}
##実行
$myData = Get-TableData($myTable)

• 以下を参考にした

• 補足説明
  • $myTable.Range.Value2で、テーブルのヘッダー行を含めた値が取得できる
    • 配列として返されるが、真の二次元配列のため、アクセスする際は、$srcValuies[1,1]などと表記する必要があり、$srcValues[1][1]ではアクセスできない
    • 0行目、0列目は、Nullである

保存、閉じる、終了

$book.Save()
[void]$book.Close()
[void]$excel.Quit()
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

#ガベージコレクション
Get-Variable | Where-Object Value -is [__ComObject] | Clear-Variable
[gc]::Collect()
[gc]::WaitForPendingFinalizers()

notes

もうちょっと追記しようとか思って、寝かせていたら、本当に寝たままになっていたので、現時点で公開してみることにしました。
下書きを作った後も、いろいろ知ったこともあるので、余裕が出来たら、追記するかもしれません。

• 20230820 複数セルの値の取得を追記
• 20231208 テーブルを追記