説明
開いているアクティブなエクセルのワークブックのアクティブなシートからデータを取得したい。
仕様
- EXCELワークブックは開いていて、アクティブな状態。
- データはアクティブなシートから取得。
- 日付はC列から取得。
- 数値はF列から取得。
- 出力先はPowershellのコンソール画面。
起動中のEXCELを取得
$excel = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")
$excel.gettype().name
$excel.Name
$excel.Path
$excel.ActiveWorkbook.Name
$excel.ActiveWorkbook.Path
$excel.ActiveSheet.Name
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null実行結果。
__ComObject
Microsoft Excel
C:\Program Files\Microsoft Office\root\Office16
Book1.xlsx
C:\Users\0905s\MY_WORK\PowerShell\SAMPLE02
Sheet1- :: の意味
-
静的なプロパティや静的なメソッドにアクセスするために使用される演算子。
::演算子を使って、クラスの静的メソッドGetActiveObjectを呼び出します。 - 静的メソッドとは
-
クラスのインスタンスを作成せずに、クラスそのものに対して直接呼び出せるメソッドのこと。
- インスタンスが不要：クラス名で直接呼び出すことができる。
- クラスに属する：静的メソッドはクラスに属するため、特定のオブジェクトの状態に依存せず、
どのインスタンスからでも同じ振る舞いを提供する - インスタンスメンバーへのアクセス制限：
(インスタンスが存在しないため)クラスのインスタンスメンバー(プロパティや他のインスタンスメソッド)には
直接アクセスできない。 - 共通の処理(計算、フォーマット、変換など)を提供するためによく使われる。
- 文字列操作やファイルの読み書きなど、特定のクラスに限定されない汎用的な処理を提供する。
- $Excel
-
取得したEXCELのCOMオブジェクトがこの変数に格納される。
- [System.Runtime.InteropServices.Marshal]
-
名前空間の指定。[System.Runtime.InteropServices.Marshal]で、Marshalクラスがどの名前空間に
属しているかを指定します。 -
- .NETフレームワークの’Marshal’クラス：
COMオブジェクトとやり取りするための様々なメソッドを提供する。
※COMオブジェクトを使用した後は、リソースを適切に解放する必要がある。
※COMオブジェクトの操作は、エラーが発生する可能性があるので、適切なエラーハンドリングが推奨される
- .NETフレームワークの’Marshal’クラス：
- GetActiveObject(“Excel.Application”)
-
指定されたプログラムID(ProgID)に基づいて、現在実行中のCOMオブジェクトのインスタンスを取得する。
- COMオブジェクト(Component Object Model)
-
異なるプログラミング言語やアプリケーション間で機能を共有し、連携するためのフレームワークのこと。
(特にWindows環境では広く使用されていて、オフィスアプリケーションの自動化やシステム管理の分野で
頻繁に利用される)
共通のインターフェースを通じてお互いにオブジェクトを操作できるようにすること。
データを取得→加工→出力
# 実行中のエクセル取得
$excel = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")
# アクティブなワークブックを取得
$workbook = $excel.ActiveWorkbook
# ワークブック内の特定のシートを取得 (例: Sheet1)
$sheet = $workbook.Sheets.Item("Sheet1")
# C列の最終行Noを取得
$lastRowC = $sheet.Cells($sheet.Rows.Count, 3).End(-4162).Row
# F列の最終行Noを取得
$lastRowF = $sheet.Cells($sheet.Rows.Count, 6).End(-4162).Row
# 日付ごとの合計を保存するためのハッシュテーブル
$dateSums = @{}
# C列の日付ごとにF列の数字を集計
for ($row = 1; $row -le [math]::Min($lastRowC, $lastRowF); $row++) {
$date = $sheet.Cells.Item($row, 3).Value()
$value = $sheet.Cells.Item($row, 6).Value()
# 日付と数値が有効な場合にのみ集計
if ($date -and $value -ne $null) {
try {
$date = [DateTime]::Parse($date) # 日付をDateTime型に変換
# 日付がハッシュテーブルに存在しない場合は初期化
if (-not $dateSums.ContainsKey($date)) {
$dateSums[$date] = 0
}
$dateSums[$date] += $value
} catch {
Write-Output "日付のパースエラー: $date"
}
}
}
# 日付を小さい順にソート
$sortedDates = $dateSums.Keys | Sort-Object
# 結果を出力
Write-Output "日付ごとの合計:"
foreach ($date in $sortedDates) {
# 確認: $date と $dateSums[$date] の内容を表示
# 年/月/日の形式で日付を表示
$formattedDate = $date.ToString("yyyy/MM/dd")
$sum = $dateSums[$date]
Write-Output ("{0}: {1}" -f $formattedDate, $sum)
#Write-Output $formattedDate, $sum
}
# メモリを解放
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($sheet) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()実行結果。
日付ごとの合計:
2024/07/01: 550
2024/07/02: 250
2024/07/03: 300- [GC]::Collect()
-
.NET Frameworkでガベージコレクションを手動でトリガーするためのメソッド。
- ガベージコレクション：
-
- メモリの効率的な管理：使用が終了したオブジェクトを自動的に検出し、メモリを解放する。
- パフォーマンスの最適化：メモリの断片化を防ぎ、パフォーマンスを維持する。
- メモリリークの防止：不要なオブジェクトがメモリに残り続けることを防ぐ。
-
通常、ガベージコレクションは.NETランタイムによって自動的に実行されるが、特定のタイミングで手動で
実行することもできる。
特にCOMオブジェクトなど、明示的に解放する必要があるオブジェクトを扱う場合、ガベージコレクションを
手動で実行することで、メモリが適切に解放することができる - [GC]::WaitForPendingFinalizers()
-
ガーベジ コレクションによって解放されるべきオブジェクトのファイナライザーが実行されるまで待機します。
この操作により、特に大規模なデータ処理や COM オブジェクトを頻繁に扱う場合に、メモリの使用を最適化できます。ただし、通常の使用では .NET ランタイムによって自動的にガーベジ コレクションが管理されるため、頻繁に手動で呼び出す必要はありません。
VBAを使用せずPowerShellだけで目的は達したのでOK！
コメント