PowerShellでExcelの値をRangeで取得して取り出す（複数セルの場合）

Windows PowerShell

はじめに

PowerShellでExcelのセルの値を取得する場合、Range("A1:D1")のように複数セルを指定することができます。 代入時はRange()で指定した範囲すべてが代入値に置換されるのでわかりやすくなっていましたが、取得の場合はひと工夫が必要になります。

そこで今回は、複数セルを範囲指定した場合の値の取り出し方について記載します。

環境

【結論】取得方法

初めに結論だけ記載します。

結果は配列で取得が可能です。このとき、Range().Textではなく、Range()までにするのがポイントです。

その後、取得したオブジェクトに添字をつけて.Textで取得できます。

PS D:\> $readData = $sheet.Range("A1:B20")

PS D:\> $readData[1].Text
$A$1

PS D:\>

準備

今回の検証には以下のデータを使用。 f:id:shift101:20210529222002p:plain

ソースコードは、PowerShellでExcelを扱う場合、一般的だと思われるものを作成。

#------------------------------------------------------------------------

#【設定】Excelファイルのパス
$bookPath = "D:\workspace\powershell\検証用.xlsx"

#【設定】対象のシート名
$sheetName = "Sheet1"

#------------------------------------------------------------------------


#Excel起動
$excel = New-Object -ComObject Excel.Application

#可視化設定（通常利用時はFalse。挙動確認の際はTrueだとわかりやすい。）
#$excel.Visible = $False
$excel.Visible = $True

#アラート無効化
$excel.DisplayAlerts = $False

#Excelファイル（ブック）を開く
$book = $excel.Workbooks.Open($bookPath)

#処理対象のシートを取得
$sheet = $book.Sheets($sheetName)

#------------
# ここでいろいろ取得したりいろいろする。
#------------


#検証終了時は以下を実行
#------------------------------------------------------------------------
#Excel終了
$excel.Quit()

#プロセス解放
$excel = $Null
[GC]::collect()
#------------------------------------------------------------------------

検証

1セルの値を取得する

$readData = $sheet.Range("A1").Text

$readData

実行結果

PS D:\> $readData = $sheet.Range("A1").Text

PS D:\> $readData
$A$1

PS D:\>

→取得できる

同じように2セル以上の値を取得してみる（失敗）

$readData = $sheet.Range("A1:B20").Text

$readData

実行結果

PS D:\> $readData = $sheet.Range("A1:B20").Text

PS D:\> $readData


PS D:\>

→何も取得できない

結果が配列であることを考慮して取得（成功）

$readData = $sheet.Range("A1:B20")

$readData[1].Text

実行結果

PS D:\> $readData = $sheet.Range("A1:B20")

PS D:\> $readData[1].Text
$A$1

PS D:\>

※ちなみに、添字[0]はエラーになるようです。

PS D:\> $readData[0].Text
HRESULT からの例外:0x800A03EC
発生場所 行:1 文字:1
+ $readData[0].Text
+ ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

ループを回す際などには配慮が必要となりそうです。

取得した値を順番に取り出すには

#取得値を順次出力
$readData | % { $_.Text }
  • パイプ「|」で取得したオブジェクトを渡す
  • ForEach-Objectの別表記「%{ }」で、受け取ったオブジェクトを1つずつ取り出して処理する
  • 「$_」はパイプで受け取ったオブジェクトを指す変数
実行結果
PS D:\> $readData | % { $_.Text }
$A$1
$B$1
$A$2
$B$2
$A$3
$B$3
$A$4
$B$4
$A$5
$B$5
$A$6
$B$6
$A$7
$B$7
$A$8
$B$8
$A$9
$B$9
$A$10
$B$10
$A$11
$B$11
$A$12
$B$12
$A$13
$B$13
$A$14
$B$14
$A$15
$B$15
$A$16
$B$16
$A$17
$B$17
$A$18
$B$18
$A$19
$B$19
$A$20
$B$20

PS D:\>

無事取得できました。

おわりに

これだけだと、何の役に立つのかというところですが、Get-ChildItem -File -Filter *.xlsxやAdd-Contentなどと組み合わせて、特定ディレクトリにあるExcelファイルの特定の範囲の値を取得して、CSVに整形して出力するなど、応用の幅は広いと思います。

また、行と列で指定するCells.Item(r,c)は1セルずつのため、複雑な制御をしない場合は、Range()のほうが楽なのではないでしょうか。

感想など
  • 自動処理が欲しいと思うぐらいExcelを作っていかないといけない現状をどうにかしたい。
  • PowerShellでExcelを操作する強みは、ブック間を跨いだり、外部ファイルを取り込むなど、Excelの外の機能とつないでいろいろできるところにあると思います。
参考