Excelのデータ接続を自動更新し、更新後のテーブル内容をPowerShellでJSON化する方法を紹介します。
# PowerShellでExcelのデータ接続を更新し、最新のテーブルをJSON化する方法
## 概要
Excelには外部データ接続機能があり、データベースやWebクエリなどの最新情報を取得できます。
本記事では、PowerShell 5.x環境でExcelの「データ接続をすべて更新」し、更新後のテーブル内容をJSONに変換する方法を紹介します。
---
## スクリプト概要
以下のスクリプトは、指定したExcelファイルを開き、`RefreshAll()`でデータ接続を更新します。
更新が完了したら、各ワークシート内のテーブル（ListObject）を走査し、JSON形式で出力します。
---
## スクリプト全文
```powershell
# Requires -Version 5.1
param(
[Parameter(Mandatory=$true)]
[string]$ExcelPath,
[Parameter(Mandatory=$false)]
[string]$OutputJson = "$(Split-Path $ExcelPath -Parent)\output.json"
)
# Excel COM オブジェクトを起動
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false
try {
# ブックを開く
$wb = $excel.Workbooks.Open($ExcelPath)
# すべてのデータ接続を更新
Write-Host "Refreshing all connections..."
$wb.RefreshAll()
# 更新が完了するまで待機
while ($excel.CalculateUntilAsyncQueriesDone() -eq $false) {
Start-Sleep -Milliseconds 500
}
Start-Sleep -Seconds 2 # 保険で少し待つ
# 最初のワークシートからテーブルを取得（必要に応じて変更）
$ws = $wb.Worksheets.Item(1)
$tables = @()
foreach ($table in @($ws.ListObjects)) {
Write-Host "Reading table: $($table.Name)"
$headers = @()
for ($c = 1; $c -le $table.ListColumns.Count; $c++) {
$headers += $table.ListColumns.Item($c).Name
}
$rows = @()
foreach ($row in $table.DataBodyRange.Rows) {
$obj = @{}
for ($c = 1; $c -le $headers.Count; $c++) {
$obj[$headers[$c-1]] = $row.Columns.Item($c).Text
}
$rows += New-Object psobject -Property $obj
}
$tables += [pscustomobject]@{
TableName = $table.Name
Data = $rows
}
}
# JSON 出力
$json = $tables | ConvertTo-Json -Depth 5 -Encoding UTF8
$json | Out-File -FilePath $OutputJson -Encoding UTF8
Write-Host "✅ JSON exported to $OutputJson"
}
catch {
Write-Error $_.Exception.Message
}
finally {
$wb.Close($false)
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
}
```
---
## 実行例
```powershell
powershell -ExecutionPolicy Bypass -File .\Export-ExcelTableToJson.ps1 `
-ExcelPath "C:\Data\Report.xlsx" `
-OutputJson "C:\Data\Report.json"
```
---
## 出力サンプル
```json
[
{
"TableName": "SalesData",
"Data": [
{"Product": "Keyboard", "Price": "2500", "Qty": "12"},
{"Product": "Mouse", "Price": "1200", "Qty": "20"}
]
}
]
```
---
## 応用ポイント
- 複数のテーブルがある場合、すべてJSONにまとめて出力します。
- 特定シートのみを対象にする場合は `$wb.Worksheets.Item("SheetName")` に変更。
- 非同期クエリを使用している場合も、`CalculateUntilAsyncQueriesDone()`で完了を待ちます。
---
## まとめ
このスクリプトを使えば、Excelファイルのデータ接続を自動更新し、
PowerShellから即座にJSON形式で最新データを取得できます。
定期ジョブ化すれば、BI連携やNotion自動更新などにも応用可能です。