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

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

# きっかけ
仕事で、どうしてもExcelVBAで作業する必要があるものの、どうしてもxlsmに保存しなくてはならない（基本的には作業ファイルに保存する）というのが、めんどくさく感じていたところ、以下の[PowerShell で Excel をどうのこうのすることに興味を持ってくれると嬉しい](https://qiita.com/miyamiya/items/161372111b68bad0744a "PowerShell で Excel をどうのこうのすることに興味を持ってくれると嬉しい")を読んで試してみました。

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

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

# 備忘録
## スクリプトの実行

```PowerShell: スクリプトの実行
# 同じディレクトリで
.\ScriptFileName.ps1 -fileName ExcelFile.xlsx
```

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

## Excelファイルを開く
```PowerShell:ブックを開く
#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)
```

## シート
```PowerShell:シート
#シートを取得（シート名か、シートの位置）
$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))
```

## セル
```PowerShell:セル
#セルの文字列を取得
$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で取得して取り出す（複数セルの場合）](https://shift101.hatenablog.com/entry/2021/05/29/223218)


## 列、行の操作
```PowerShell:列、行の操作
[void]$currentSheet.Columns(1).Delete() #行番号
```

## Alignの設定
```PowerShell: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
```PowerShell: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]
    }
```

## テーブル
```PowerShell
#テーブルの取得
$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)

## CSVに変換して保存する
$myData | ConvertTo-Csv > newname.csv
### PowerShell 5の場合は、-NoTypeInformation を付けると情報ヘッダーが出力されないので、推奨（PowerShell6以降は標準でつかない）
```


* 以下を参考にした

https://blog.jbs.co.jp/entry/2022/09/30/100757

https://qiita.com/jooji/items/8bedb307a68154c4b0df

* 補足説明
    * `$myTable.Range.Value2`で、テーブルのヘッダー行を含めた値が取得できる
        * 配列として返されるが、[真の二次元配列](https://learn.microsoft.com/ja-jp/powershell/module/microsoft.powershell.core/about/about_arrays?view=powershell-5.1#rank)のため、アクセスする際は、`$srcValuies[1,1]`などと表記する必要があり、`$srcValues[1][1]`ではアクセスできない
        * 0行目、0列目は、Nullである


## 保存、閉じる、終了
```PowerShell
$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 テーブルを追記