PowerShellスクリプトでのExcel操作

## 概要
PowerShellスクリプトでのExcel操作方法について
備忘録として記載いたします。

## 実行環境
windows：Windows11Pro 23H2
Windows PowerShell:5.1.26100.4061

### PowerShellのバージョン確認
確認コマンド：$PSVersionTable

### PowerShellとWindows PowerShellの違い
下記のサイトが参考になります。

https://blog.janjan.net/2025/02/09/powershell-update-does-not-change-in-windows-terminal/



## 注意点
・param はスクリプトの先頭に配置する必要がある。
　関数定義の後に param を記述すると、PowerShell がスクリプトの実行時に param を認識できず、
 　エラーになる。
・スクリプト内に日本語の表記が存在しているとエラーとなる場合がある。
　解決策として、ファイルの拡張子を.txtにして
　メモ帳を開いて「名前を付けて保存」の選択時に「エンコード」の項目から「UTF-8（BOM 付き）」を選択する。
　そして再度拡張子を.ps1にしてスクリプトを実行する。
## 実行形式ファイル(.exe)の作成
下記の記事を参考にした

https://qiita.com/denkiuo604/items/c55dc2bc34cdae9e35cf

１）PS2EXEのインストール
・管理者権限でPowerShellを起動して、下記のコマンドを実行する
```
Install-Module -Name PS2EXE -Scope CurrentUser
```
２）実行形式ファイル(.exe)の作成
```
ps2exe.ps1 -inputFile <Script.ps1> -outputFile <Output.exe>
```

## チェックボックスの使用
```test.ps1
# 必要なアセンブリをロード
Add-Type -AssemblyName System.Windows.Forms

# フォームの作成
$form = New-Object System.Windows.Forms.Form
$form.Text = "項目と教科選択"
$form.Width = 600
$form.Height = 300

# ラベルの作成（画面上部に「項目を選んでください」を追加）
$label = New-Object System.Windows.Forms.Label
$label.Text = "項目を選んでください"
$label.Font = New-Object System.Drawing.Font("Arial", 10)
$label.AutoSize = $true
$label.Location = New-Object System.Drawing.Point(20, 20)
$form.Controls.Add($label)

# チェックボックス1
$checkbox1 = New-Object System.Windows.Forms.CheckBox
$checkbox1.Text = "項目1"
$checkbox1.Location = New-Object System.Drawing.Point(20, 50)
$form.Controls.Add($checkbox1)

# チェックボックス2
$checkbox2 = New-Object System.Windows.Forms.CheckBox
$checkbox2.Text = "項目2"
$checkbox2.Location = New-Object System.Drawing.Point(120, 50)
$form.Controls.Add($checkbox2)

# チェックボックス3
$checkbox3 = New-Object System.Windows.Forms.CheckBox
$checkbox3.Text = "項目3"
$checkbox3.Location = New-Object System.Drawing.Point(220, 50)
$form.Controls.Add($checkbox3)

# ラベルの作成（画面上部に「教科を選んでください」を追加）
$subjectlabel = New-Object System.Windows.Forms.Label
$subjectlabel.Text = "教科を選んでください"
$subjectlabel.Font = New-Object System.Drawing.Font("Arial", 10)
$subjectlabel.AutoSize = $true
$subjectlabel.Location = New-Object System.Drawing.Point(20, 80)
$form.Controls.Add($subjectlabel)

# チェックボックス「英語」
$SubjectEnglish = New-Object System.Windows.Forms.CheckBox
$SubjectEnglish.Text = "英語"
$SubjectEnglish.Location = New-Object System.Drawing.Point(20, 100)
$form.Controls.Add($SubjectEnglish)

# チェックボックス「数学」
$SubjectMath = New-Object System.Windows.Forms.CheckBox
$SubjectMath.Text = "数学"
$SubjectMath.Location = New-Object System.Drawing.Point(120, 100)
$form.Controls.Add($SubjectMath)

# チェックボックス「物理」
$SubjectPhysics = New-Object System.Windows.Forms.CheckBox
$SubjectPhysics.Text = "物理"
$SubjectPhysics.Location = New-Object System.Drawing.Point(220, 100)
$form.Controls.Add($SubjectPhysics)

# 選択された「項目」を格納する変数
$selectedItems = [ref] "未登録"
$subjectItems = [ref] "未登録"

# OKボタン
$okButton = New-Object System.Windows.Forms.Button
$okButton.Text = "OK"
$okButton.Location = New-Object System.Drawing.Point(150, 200)
$okButton.Add_Click({
    # 選択された項目と教科を取得
    $selectedItems.Value = ""
    $subjectItems.Value = ""
    $selectedItemsCount = 0
    $subjectItemsCount = 0

    if ($checkbox1.Checked) {$selectedItems.Value += "項目1"; $selectedItemsCount++}
    if ($checkbox2.Checked) {$selectedItems.Value += "項目2"; $selectedItemsCount++}
    if ($checkbox3.Checked) {$selectedItems.Value += "項目3"; $selectedItemsCount++}
    if ($SubjectEnglish.Checked) {$subjectItems.Value += "英語"; $subjectItemsCount++}
    if ($SubjectMath.Checked) {$subjectItems.Value += "数学"; $subjectItemsCount++}
    if ($SubjectPhysics.Checked) {$subjectItems.Value += "物理"; $subjectItemsCount++}
    
    
    # 選択された項目と教科を画面に表示
    # [System.Windows.Forms.MessageBox]::Show("選択された項目: $($selectedItems.Value)")
    # [System.Windows.Forms.MessageBox]::Show("選択された教科: $($subjectItems.Value)")

    # 項目と教科が選択されているか確認
    if ([string]::IsNullOrWhiteSpace($selectedItems.Value) -or [string]::IsNullOrWhiteSpace($subjectItems.Value)) {
        [System.Windows.Forms.MessageBox]::Show("項目と教科を最低1つずつ選択してください。", "警告", [System.Windows.Forms.MessageBoxButtons]::OK, [System.Windows.Forms.MessageBoxIcon]::Warning)
        return
    }

    # 項目と教科が2つ以上選択されているか確認
    if ($selectedItemsCount -gt 1 -or $subjectItemsCount -gt 1) {
        [System.Windows.Forms.MessageBox]::Show("項目と教科はそれぞれ最大1つ選択してください。", "警告", [System.Windows.Forms.MessageBoxButtons]::OK, [System.Windows.Forms.MessageBoxIcon]::Warning)
        return
    }

    # フォームを閉じる
    $form.DialogResult = [System.Windows.Forms.DialogResult]::OK
})

$form.Controls.Add($okButton)

# フォームを表示
$result = $form.ShowDialog()

$FinalItems=$($selectedItems.Value)
$Finalsub=$($subjectItems.Value)

# フォーム閉じた後の選択項目を出力
Write-Host "項目番号: $FinalItems"
Write-Host "教科番号: $Finalsub"

```

実行結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3559550/b0329063-5141-4f38-a4ae-053142787066.png)

### COMオブジェクトの解放
```ComObjectfree.ps1
# COMオブジェクトの作成
$excel = New-Object -ComObject Excel.Application

# 何らかの操作を実行
$excel.Visible = $true

# アプリケーションを終了
$excel.Quit()

# COMオブジェクトの解放
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
$excel = $null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()
```
### JSONファイルの値を取得する方法
```config.json
{
  "main": {
    "SheetName": "データシート"
  },
  "section1": {
    "Cell":{
      "CellAddress": "B3",
      "CellAddressName": "CellAddress"
    },
    "Cell1":{
      "CellAddress1": "A1",
      "CellAddress1Name": "CellAddress1"
    }
  }
}
```
```get_json_data.ps1

# Excel COMオブジェクトを使用
param (
    [string]$ConfigFile,
    [string]$LogFile
)

# デフォルト値をハッシュテーブルで管理
$DefaultConfig = @{
    ConfigFile   = "config.json"
    LogFile      = "result.txt"
}

# デフォルト値を適用
foreach ($key in $DefaultConfig.Keys) {
    if (-Not (Get-Variable -Name $key -ErrorAction SilentlyContinue).Value) {
        Set-Variable -Name $key -Value $DefaultConfig[$key]
    }
}

################################################################
### 関数定義
################################################################
# LogFileに追記する関数
function Add-Log {
    param ([string]$LogFile, [string]$Message)
    $Message | Add-Content -Path $LogFile -Encoding UTF8
}

# フォルダの存在確認を行う関数
function Check-Folder {
    param ([string]$Folder, [string]$LogFile)
    if (-Not (Test-Path $Folder)) {
        Add-Log -LogFile $LogFile -Message "エラー: フォルダ '$Folder' が見つかりません。"
        return $false
    }
    return $true
}

# フォルダ選択ダイアログを表示する関数
function Select-Folder($Description) {
    Add-Type -AssemblyName System.Windows.Forms
    $folderDialog = New-Object System.Windows.Forms.FolderBrowserDialog
    $folderDialog.Description = $Description
    
    if ($folderDialog.ShowDialog() -eq [System.Windows.Forms.DialogResult]::OK) {
        return $folderDialog.SelectedPath
    } else {
        Write-Host "フォルダ選択がキャンセルされました。"
        exit 1
    }
}


# Excel COMオブジェクトを初期化する関数
function Initialize-Excel {
    $Excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $false
    $Excel.DisplayAlerts = $false
    return $Excel
}

# Excel COMオブジェクトをクリーンアップする関数
function Cleanup-Excel {
    param ([object]$Excel)
    $Excel.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
}

# 指定セルの値を取得する関数
function Get-CellValue {
    param ([object]$Sheet, [string]$CellAddress)
    return $Sheet.Range($CellAddress).Text
}

# 空白セル判定を行う関数
function Is-EmptyCell {
    param ([string]$CellValue)
    return [string]::IsNullOrWhiteSpace($CellValue)
}

# Excelファイルを処理する関数
function Process-ExcelFile-Test {
    param ([string]$Folder, [object]$Excel, [string]$SheetName, [Array]$CellArr, [string]$LogFile)

    Add-Log -LogFile $LogFile -Message "フォルダ: $Folder"
    $ExcelFiles = Get-ChildItem -Path $Folder -Filter "*.xlsx" | Select-Object -ExpandProperty FullName

    foreach ($File in $ExcelFiles) {
        Add-Log -LogFile $LogFile -Message "ファイル名：$File"
        $Workbook = $Excel.Workbooks.Open($File)
        $Sheet = $Workbook.Sheets.Item($SheetName)

        foreach ($Celldata in $CellArr) {
            $Cell = $Celldata[0]
            Add-Log -LogFile $LogFile -Message "セル座標：$Cell"
            $CellValue = Get-CellValue -Sheet $Sheet -CellAddress $Celldata[0]

            if (Is-EmptyCell -CellValue $CellValue) {
                Add-Log -LogFile $LogFile -Message "空白"
            }
        }

        Add-Log -LogFile $LogFile -Message ""
        $Workbook.Close($false)
    }
}

################################################################
### 実処理
################################################################
# 設定値の取得
# JSONファイルを読み込む
$JsonContent = Get-Content -Path $ConfigFile -Raw -Encoding UTF8| ConvertFrom-Json

# JSONデータから値を取得
$SheetName = $JsonContent.main.SheetName
$CellAddress = $JsonContent.section1.Cell.CellAddress
$CellAddressName = $JsonContent.section1.Cell.CellAddressName
$CellAddress1 = $JsonContent.section1.Cell1.CellAddress1
$CellAddress1Name = $JsonContent.section1.Cell1.CellAddress1Name

# 配列の方法２：多次元配列
$AddressMulArr=@(($CellAddress,$CellAddressName),($CellAddress1,$CellAddress1Name))
# foreach($Address in $AddressMulArr){
#     # ペアを出力する 例)($CellAddress,$CellAddressName)
#     Write-Host $Address
#     # 最初のインデックスを出力する CellAddress,CellAddress1などが表示される
#     Write-Host $Address[0]
#     # 2番目のインデックスを出力する CellAddressName,CellAddress1Nameなどが表示される
#     Write-Host $Address[1]
# }


# フォルダをユーザーが選択
$TargetFolder = Select-Folder "処理対象フォルダを選択してください"
$TestFolder = Select-Folder "テスト用フォルダを選択してください"

# Excel COMオブジェクトの作成
$Excel = Initialize-Excel

# フォルダの処理
$Folders = @($TargetFolder, $TestFolder)

# 現在の日時をフォーマットしてLogFile名に追加
$timestamp = (Get-Date).ToString("yyyyMMdd_HHmmss")
$LogFile = "$($timestamp)_$LogFile"


foreach ($Folder in $Folders) {
    if (Check-Folder -Folder $Folder -LogFile $LogFile) {
        Process-ExcelFile-Test -Folder $Folder -Excel $Excel -SheetName $SheetName -CellArr $AddressMulArr -LogFile $LogFile
    }
}

# Excelプロセス終了
Cleanup-Excel -Excel $Excel


Write-Host "'$LogFile' に出力しました。"

```
### 記載ありかなしかを調べる
```test.ps1

# Excel COMオブジェクトを使用
param (
    [string]$ConfigFile,
    [string]$LogFile
)

# デフォルト値をハッシュテーブルで管理
$DefaultConfig = @{
    ConfigFile   = "config.json"
    LogFile      = "result.txt"
}

# デフォルト値を適用
foreach ($key in $DefaultConfig.Keys) {
    if (-Not (Get-Variable -Name $key -ErrorAction SilentlyContinue).Value) {
        Set-Variable -Name $key -Value $DefaultConfig[$key]
    }
}

################################################################
### 関数定義
################################################################
# LogFileに追記する関数
function Add-Log {
    param ([string]$LogFile, [string]$Message)
    $Message | Add-Content -Path $LogFile -Encoding UTF8
}

# フォルダの存在確認を行う関数
function Check-Folder {
    param ([string]$Folder, [string]$LogFile)
    if (-Not (Test-Path $Folder)) {
        Add-Log -LogFile $LogFile -Message "エラー: フォルダ '$Folder' が見つかりません。"
        return $false
    }
    return $true
}

# フォルダ選択ダイアログを表示する関数
function Select-Folder($Description) {
    Add-Type -AssemblyName System.Windows.Forms
    $folderDialog = New-Object System.Windows.Forms.FolderBrowserDialog
    $folderDialog.Description = $Description
    
    if ($folderDialog.ShowDialog() -eq [System.Windows.Forms.DialogResult]::OK) {
        return $folderDialog.SelectedPath
    } else {
        Write-Host "フォルダ選択がキャンセルされました。"
        exit 1
    }
}


# Excel COMオブジェクトを初期化する関数
function Initialize-Excel {
    $Excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $false
    $Excel.DisplayAlerts = $false
    return $Excel
}

# Excel COMオブジェクトをクリーンアップする関数
function Cleanup-Excel {
    param ([object]$Excel)
    $Excel.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
}

# 指定セルの値を取得する関数
function Get-CellValue {
    param ([object]$Sheet, [string]$CellAddress)
    return $Sheet.Range($CellAddress).Text
}

# 空白セル判定を行う関数
function Is-EmptyCell {
    param ([string]$CellValue)
    return [string]::IsNullOrWhiteSpace($CellValue)
}

# Excelファイルを処理する関数
function Process-ExcelFile-Test {
    param ([string]$Folder, [object]$Excel, [string]$SheetName, [Array]$CellArr, [string]$LogFile)

    Add-Log -LogFile $LogFile -Message "フォルダ: $Folder"
    $ExcelFiles = Get-ChildItem -Path $Folder -Filter "*.xlsx" | Select-Object -ExpandProperty FullName

    foreach ($File in $ExcelFiles) {
        Add-Log -LogFile $LogFile -Message "ファイル名：$File"
        $Workbook = $Excel.Workbooks.Open($File)
        $Sheet = $Workbook.Sheets.Item($SheetName)
        $CellA1value=Get-CellValue -Sheet $Sheet -CellAddress $CellArr[0]
        $CellA2value=Get-CellValue -Sheet $Sheet -CellAddress $CellArr[1]   
        $CellA3value=Get-CellValue -Sheet $Sheet -CellAddress $CellArr[2]
        $CellB1value=Get-CellValue -Sheet $Sheet -CellAddress $CellArr[3]
        $CellB2value=Get-CellValue -Sheet $Sheet -CellAddress $CellArr[4]
        $CellB3value=Get-CellValue -Sheet $Sheet -CellAddress $CellArr[5]

        #空行判定
        $Cell1Rownoted=$false
        $Cell2Rownoted=$false
        $Cell3Rownoted=$false
        $CellRownoted=$false

        #空行判定の配列
        if ( -not (Is-EmptyCell -CellValue $CellA1value) -and  -not (Is-EmptyCell -CellValue $CellB1value)) {
            $Cell1Rownoted=$true
        }
        
        if (-not (Is-EmptyCell -CellValue $CellA2value) -and -not (Is-EmptyCell -CellValue $CellB2value )) {
            $Cell2Rownoted=$true
        }
        
        if ( -not (Is-EmptyCell -CellValue $CellA3value) -and -not (Is-EmptyCell -CellValue $CellB3value)) {
            $Cell3Rownoted=$true
        }

        $Rownoted=@($Cell2Rownoted,$Cell3Rownoted)
        if($Cell1Rownoted -eq $false){
            Add-Log -LogFile $LogFile -Message "1行目が空行です:"
        }else{

            for($i = 0;$i -lt $Rownoted.Length;$i++){
                for($j=1;$j -lt $Rownoted.Length;$j++){
                    if(($Rownoted[$i] -eq $false) -and ($Rownoted[$j] -eq $true)){
                        Add-Log -LogFile $LogFile -Message "空行があります:"
                    }
                }
            }
        }

        Add-Log -LogFile $LogFile -Message ""
        $Workbook.Close($false)
    }
}

################################################################
### 実処理
################################################################
# 設定値の取得
# JSONファイルを読み込む
$JsonContent = Get-Content -Path $ConfigFile -Raw -Encoding UTF8| ConvertFrom-Json

# JSONデータから値を取得
$SheetName = $JsonContent.main.SheetName
$CellA1Address = $JsonContent.section1.CellA1.CellA1Address
$CellA1AddressName = $JsonContent.section1.CellA1.CellA1AddressName
$CellA2Address = $JsonContent.section1.CellA2.CellA2Address
$CellA2AddressName = $JsonContent.section1.CellA2.CellA2AddressName
$CellA3Address = $JsonContent.section1.CellA3.CellA3Address
$CellA3AddressName = $JsonContent.section1.CellA3.CellA3AddressName
$CellB1Address = $JsonContent.section1.CellB1.CellB1Address
$CellB1AddressName = $JsonContent.section1.CellB1.CellB1AddressName
$CellB2Address = $JsonContent.section1.CellB2.CellB2Address
$CellB2AddressName = $JsonContent.section1.CellB2.CellB2AddressName
$CellB3Address = $JsonContent.section1.CellB3.CellB3Address
$CellB3AddressName = $JsonContent.section1.CellB3.CellB3AddressName

# 配列の方法２：多次元配列
$CellFirst=@(($CellA1Address,$CellA1AddressName),($CellB1Address,$CellB1AddressName))
$CellSecond=@(($CellA2Address,$CellA2AddressName),($CellB2Address,$CellB2AddressName))
$CellThird=@(($CellA3Address,$CellA3AddressName),($CellB3Address,$CellB3AddressName))
$CellAll=@($CellA1Address,$CellA2Address,$CellA3Address,$CellB1Address,$CellB2Address,$CellB3Address)

# フォルダをユーザーが選択
$TargetFolder = Select-Folder "処理対象フォルダを選択してください"
$TestFolder = Select-Folder "テスト用フォルダを選択してください"

# Excel COMオブジェクトの作成
$Excel = Initialize-Excel

# フォルダの処理
$Folders = @($TargetFolder, $TestFolder)

# 現在の日時をフォーマットしてLogFile名に追加
$timestamp = (Get-Date).ToString("yyyyMMdd_HHmmss")
$LogFile = "$($timestamp)_$LogFile"


foreach ($Folder in $Folders) {
    if (Check-Folder -Folder $Folder -LogFile $LogFile) {
        Process-ExcelFile-Test -Folder $Folder -Excel $Excel -SheetName $SheetName -CellArr $CellAll -LogFile $LogFile
    }
}

# Excelプロセス終了
Cleanup-Excel -Excel $Excel


Write-Host "'$LogFile' に出力しました。"
```
```config.json
{
  "main": {
    "SheetName": "データシート"
  },
  "section1": {
    "CellA1":{
      "CellA1Address": "A1",
      "CellA1AddressName": "A1の値"
    },
    "CellA2":{
      "CellA2Address": "A2",
      "CellA2AddressName": "A2の値"
    },
    "CellA3":{
      "CellA3Address": "A3",
      "CellA3AddressName": "A3の値"
    },
    "CellB1":{
      "CellB1Address": "B1",
      "CellB1AddressName": "B1の値"
    },
    "CellB2":{
      "CellB2Address": "B2",
      "CellB2AddressName": "B2の値"
    },
    "CellB3":{
      "CellB3Address": "B3",
      "CellB3AddressName": "B3の値"
    }
  }
}
```