PowerShell練習 Excel.Applicationを起動してxlsxファイルを作成

メモ

• 2次元配列を Range.Value2 に代入
• Excel.Applicationの起動に時間がかかるので他の方法を考えたほうが良いかもしれない

使い捨てスクリプト

• エラー処理やCOMオブジェクトの解放を気にしていないので練習以外では実行しない

# PowerShell 5.1, Excel 2019, Windows 11 (2023年6月頃)

using namespace System.Collections.Generic

Add-Type -AssemblyName Microsoft.Office.Interop.Excel

Set-StrictMode -Version Latest

$ErrorActionPreference = "Continue"
$VerbosePreference = "Continue"

# UNCパス形式
$homeUNC = . {
  # C:\Users\username を \\localhost\C$\Users\username に置換
  $pathUNC0 = ${env:USERPROFILE} -replace '^([A-Z]):', '\\localhost\$1$$'

  $null = ${env:USERPROFILE} -match '\A([A-Z]):(.*?)\z'
  $pathUNC1 = "\\localhost\" + $Matches.1 + "$" + $Matches.2

  $pathUNC2 = (Resolve-Path -LiteralPath $pathUNC1).ProviderPath

  Write-Host "`$pathUNC0: $pathUNC0"
  Write-Host "`$pathUNC1: $pathUNC1"
  Write-Host "`$pathUNC2: $pathUNC2"

  return $pathUNC2
}

$xlObjMemberList = [List[PSObject]]::new()

try {
  # Applicationオブジェクト
  $xlApplication = [Activator]::CreateInstance([Type]::GetTypeFromProgID("Excel.Application"))
  $xlApplication.Visible = $false
  $xlApplication.DisplayAlerts = $false
  $xlApplication.EnableEvents = $false
  $xlApplication.ScreenUpdating = $false

  @(Get-Member -InputObject $xlApplication).ForEach({
    $_ | Add-Member -NotePropertyMembers @{ "Object" = "Application" }
    $xlObjMemberList.Add($_)
  })

  try {
    # Workbooksオブジェクト
    $xlWorkbooks = $xlApplication.Workbooks
    @(Get-Member -InputObject $xlWorkbooks).ForEach({
      $_ | Add-Member -NotePropertyMembers @{ "Object" = "Workbooks" }
      $xlObjMemberList.Add($_)
    })

    try {
      # Workbookオブジェクト
      $xlWorkbook = $xlWorkbooks.Add([Microsoft.Office.Interop.Excel.XlWBATemplate]::xlWBATWorksheet)
      # ブックの計算 手動
      $xlApplication.Calculation = [Microsoft.Office.Interop.Excel.XlCalculation]::xlCalculationManual

      @(Get-Member -InputObject $xlWorkbook).ForEach({
        $_ | Add-Member -NotePropertyMembers @{ "Object" = "Workbook" }
        $xlObjMemberList.Add($_)
      })

      try {
        # Worksheetsオブジェクト
        $xlWorksheets = $xlWorkbook.Worksheets()
        @(Get-Member -InputObject $xlWorksheets).ForEach({
          $_ | Add-Member -NotePropertyMembers @{ "Object" = "Worksheets" }
          $xlObjMemberList.Add($_)
        })

        try {
          # Worksheetオブジェクト
          $xlWorksheet = $xlWorksheets.Item(1)
          @(Get-Member -InputObject $xlWorksheet).ForEach({
            $_ | Add-Member -NotePropertyMembers @{ "Object" = "Worksheet" }
            $xlObjMemberList.Add($_)
          })

          try {
            # Rangeオブジェクト
            $xlCells = $xlWorksheet.Cells()
            @(Get-Member -InputObject $xlCells).ForEach({
              $_ | Add-Member -NotePropertyMembers @{ "Object" = "Range" }
              $xlObjMemberList.Add($_)
            })

            try {
              # 左上B3セル 右下D5セル
              $xlRange = $xlCells.Range($xlCells.Item(3, 2), $xlCells.Item(5, 4))

              # 2次元配列
              $xlRngVal = [object[,]]::new(3,3)
              $xlRngVal[0, 0] = 'abc';      $xlRngVal[0, 1] = 'def';   $xlRngVal[0, 2] = 123;
              $xlRngVal[1, 0] = '=PI()';    $xlRngVal[1, 1] = '=PI()'; $xlRngVal[1, 2] = '=B4';
              $xlRngVal[2, 0] = '2023/4/1'; $xlRngVal[2, 1] = '23:45'; $xlRngVal[2, 2] = 'テスト';

              try {
                # C4セル
                $xlRangeTemp = $xlCells.Item(4, 3)
                # 表示形式 文字列
                $xlRangeTemp.NumberFormatLocal() = "@"
              }
              catch { throw $_ } finally { $xlRangeTemp = $null }

              $xlRange.Value2() = $xlRngVal
            }
            catch { throw $_ } finally { $xlRange = $null }
          }
          catch { throw $_ } finally { $xlCells = $null }

          $xlFileBaseName = "test_" + (Get-Date -Format FileDateTime) + "_" + [IO.Path]::GetFileNameWithoutExtension([IO.Path]::GetRandomFileName())
          $xlFilePathUNC = "$($homeUNC)\Desktop\$($xlFileBaseName).xlsx"
          $xlWorksheet.SaveAs($xlFilePathUNC)
        }
        catch { throw $_ } finally { $xlWorksheet = $null }
      }
      catch { throw $_ } finally { $xlWorksheets = $null }
    }
    catch { throw $_ } finally { $xlWorkbook = $null }

    $xlWorkbooks.Close()
  }
  catch { throw $_ } finally { $xlWorkbooks = $null }

  $xlApplication.Visible = $false
  $xlApplication.Quit()
}
catch { throw $_ } finally { $xlApplication = $null }

$xlObjMemberList | Select-Object -Property Object, Name, MemberType, Definition, TypeName | Out-GridView

$Error | Out-GridView

Get-Variable -Name *xl*

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