2018年7月17日 / 最終更新日時 : 2020年8月8日 sito IT

PowerShellでExcelをRC形式で呼び出し繰り返し実行する

PowerShellでは.NetFrameworkのオブジェクトを処理することができます。同じマシンの中にMicrosoft Officeが入っていれば、例えば外部からExcelをComObjectとして操作してやることができます。せっかくscriptを組んで処理するのですから、「1行ごとに処理を繰り返して、全行処理させたい」時があります。そのためにはRC形式での指定が便利です。
Web上にあまりそういったことが載っていなかったので、メモ

IT業界にいると、Key-Valueストアって便利だよねとかXMLオブジェクトでXPathで値を指定っていう世界に（否が応でも）慣れていくんですが、世の中そういう世界だけではありません。まだまだExcelの台帳管理とかは生きています。

そうしないと自分しか処理ができない世界が待っていて、おちおち休みも取れなくなります。不在の間派遣さんに「このExcelに書いておいてくれれば良いから」と言って任せることができるのは小規模な事務量の世界ではまだ有効です。

と、いうことでExcelの台帳をサーバが読みに行ってサーバ自身のアクセス権を処理するScriptを組むことにしました。
Excelをハンドリングするところから、読み取り、書き込みあたりまでを実際に試していきます。

PowerShellからExcelを起動する

ExcelをPowerShellから操作するには、PowerShell内のオブジェクトとして掴むことが必要です。

単純に掴むだけであれば、-ComObjectとしてExcelを指定すると（別スレッドで）新しくExcelが起動します。

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible =$true

1 2	$Excel = New-Object -ComObject Excel.Application $Excel.Visible =$true

既存のExcel.exeプロセスがあれば、下記の命令でもオブジェクトを作る事ができます。

$Excel = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")
$Excel.Visible =$true

1 2	$Excel = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application") $Excel.Visible =$true

これ環境によっては掴めないようで、色々試したところ、こんな感じ。

他のユーザが実行しているExcelプロセスは掴めない（当然）
「管理者として実行」したPowerShellは、「管理者として実行」しているExcelプロセスしか掴めない（！！）同じユーザのプロセスであっても一般権限で動いているExcelは掴めない。
同じPowerShellのプロセスが起動したExcelプロセスは掴むことができる
New-Objectで作成した場合、ExcelAddinが無効の状態で起動する。

今回の場合、Excelを読み込んだ後システムへの変更処理を組み込む予定なのでPowerShellを管理者権限で動かす必要があります。普段のオペレーションでExcelを管理者権限で動かすなんて危険なので止しておきたい。
If文かtry～catch句を使って掴めなかった場合の処理をしておいた方が良さそうです。

ちなみに、掴めなかった時のエラーはこんな感じで返ってきます。

PS C:\windows\system32> [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")
"1" 個の引数を指定して "GetActiveObject" を呼び出し中に例外が発生しました: "操作を利用できません (HRESULT からの例外:0x800401E3 (MK_E_UNAVAILABLE))"
発生場所 行:1 文字:1
+ [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Appl ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : COMException

PS C:\windows\system32> [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")

"1" 個の引数を指定して "GetActiveObject" を呼び出し中に例外が発生しました: "操作を利用できません (HRESULT からの例外:0x800401E3 (MK_E_UNAVAILABLE))"

発生場所行:1 文字:1

+ [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Appl ...

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException

+ FullyQualifiedErrorId : COMException

0x800401E3のコードで検索すると「Running Object Table (ROT)」アプリケーションが無いという内容で「起動からROTに載るまでは少しタイムラグがある」というFAQが見つかります。このExceptionは、Excelが全く起動していない時でも同じコードで返ってきます。

また、出来上がったオブジェクトのクラスが異なる事があります。

PS C:\script> $ComExcel = New-Object -ComObject Excel.Application
PS C:\script> $ComExcel.gettype().fullname
Microsoft.Office.Interop.Excel.ApplicationClass

PS C:\script> $ComExcel = New-Object -ComObject Excel.Application

PS C:\script> $ComExcel.gettype().fullname

Microsoft.Office.Interop.Excel.ApplicationClass

起動しているExcelプロセスをPowerShellから掴むとこんな感じ。

PS C:\script> $Excel = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")
PS C:\script> $Excel.gettype().Fullname
System.__ComObject

PS C:\script> $Excel = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")

PS C:\script> $Excel.gettype().Fullname

System.__ComObject

InteropServices.MarshalクラスからGetすると必ずSystem.__ComObjectになるかというとそうでもなくて、色々試したところの推定は以下の通りです。

ユーザが起動したExcelがある
→[System.Runtime.InteropServices.Marshal]::GetActiveObject(“Excel.Application”)でオブジェクトをgetする
→netFrameworkのWrapperで包んでSystem.__ComObjectになる。

New-ObjectでPowerShellからExcelプロセスを起動する
→Microsoft.Office.Interop.Excel.ApplicationClassのオブジェクトができる
→[System.Runtime.InteropServices.Marshal]::GetActiveObject(“Excel.Application”)でExcelプロセスを掴む
→Microsoft.Office.Interop.Excel.ApplicationClassのオブジェクトになる。

PS C:\&gt; $ComExcel = New-Object -ComObject Excel.Application
PS C:\> $ComExcel.gettype().Fullname
Microsoft.Office.Interop.Excel.ApplicationClass

PS C:\> $Excel = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")
PS C:\>$Excel.gettype().Fullname
Microsoft.Office.Interop.Excel.ApplicationClass

PS C:\> $Excel -eq $ComExcel
True

PS C:\> $ComExcel = New-Object -ComObject Excel.Application

PS C:\> $ComExcel.gettype().Fullname

Microsoft.Office.Interop.Excel.ApplicationClass

PS C:\> $Excel = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")

PS C:\>$Excel.gettype().Fullname

Microsoft.Office.Interop.Excel.ApplicationClass

PS C:\> $Excel -eq $ComExcel

True

（参考）
https://docs.microsoft.com/ja-jp/dotnet/framework/interop/runtime-callable-wrapper

出来上がったオブジェクトによって使えるMethodが異なるのですが、PowerShellで外側から弄ってやろうという程度だと実用上の差はないと思います。

興味がある方は、下記を見てください。

$Excel|Get-Member &gt; Excel_Member.txt

1	$Excel\|Get-Member > Excel_Member.txt

Eventが拾えるかとかそういう違いなので、そこまでしたいのであればC#とかVBとかでコンパイルしたものを実行した方が良いのでは。

既存のExcelブックを開く

次に、Excelブックを。開いていない場合は、Workbooks.Openメソッドで開けます。下記の例はScriptと同じフォルダにテスト用ブックを作った前提の命令です。

$BookPath = "C:\script\"
$BookName = "test.xlsx"
$Book= $Excel.Workbooks.Open($BookPath+$BookName)

$BookPath = "C:\script\"

$BookName = "test.xlsx"

$Book= $Excel.Workbooks.Open($BookPath+$BookName)

既に開いているExcelの中に目的のブックがある場合は、Workbooksコレクションの中に含まれているのでそれを指定してやれば掴むことができます。

$Book = $Excel.Workbooks.Item($BookName)

1	$Book = $Excel.Workbooks.Item($BookName)

これもIf文かtry～catch句使ってハンドリングしましょう。

Excelブックの中身を読み取る

Excelを開いてセルを読み込もうとすると、Activeシートから読み込もうとするのがデフォなので、きちんとシート名を指定したほうがいいです。
下記のようにすると、A1セルの内容を$Dataに格納します。

$DataSheetName = "データ"
$Sheet         = $book.Worksheets.Item($DataSheetName)

$Row    = 1
$Column = 1
$Data   = $Sheet.Cells($Row,$Column).text

$DataSheetName = "データ"

$Sheet = $book.Worksheets.Item($DataSheetName)

$Row = 1

$Column = 1

$Data = $Sheet.Cells($Row,$Column).text

Excelブックに書き込む

Cells自体はオブジェクトですが、値を代入することでSetメソッドが実行されます。

$Data   =10
$Row    = 1
$Column = 1
$Sheet.Cells($Row,$Column)  = $Data

$Data =10

$Row = 1

$Column = 1

$Sheet.Cells($Row,$Column) = $Data

式の場合はRC3と入れてやるとRC列3行目を参照するようなので、同じ行でもR[0]C[-2]といったようにRの後ろに数字を入れてやりましょう。

RC形式でRange指定する

Rangeオブジェクトは$Sheet.Range(“A1:D3”)と書けばA1:D3範囲を選択してくれます。
RC形式だと範囲の開始セルと終了セルをオブジェクトとして指定する必要があります。
1列目から10列目を範囲指定してデータを消去（データ形式は保持）するのだとこんな感じ。

$消去対象開始セル =$sheet.Cells.item($Row, 1)
$消去対象終了セル =$sheet.Cells.item($Row, 10)
$Sheet.Range($消去対象開始セル, $消去対象終了セル).ClearContents()

$消去対象開始セル =$sheet.Cells.item($Row, 1)

$消去対象終了セル =$sheet.Cells.item($Row, 10)

$Sheet.Range($消去対象開始セル, $消去対象終了セル).ClearContents()

1行目にあるヘッダを読み込んで列番号を取得する

Excelの先頭行にヘッダを設定しているのはよくあると思います。
ところが、Excelだと気軽に列挿入されてしまいます。
列番号指定より列名指定で処理した方が楽だろう、ということで変数に入れてしまいます。

#($Sheet.Cells.Item(1,$Column).text を呼び出して、何も入ってないとfalse(0)が返ってくるので、入っている限りHeaderを取ります。
for($Column= 1;[bool]($sheet.Cells.Item(1,$Column).text);$Column++)
{
$HeaderName      = $Sheet.Cells.Item(1,$Column).text
$HeaderColumnMax = $Column
Write-Host $HeaderName $Column "列目"

#PowerShellでは２バイトコードを変数にできるので、ヘッダの名前にカラム位置を入れてやります。
#()など特殊文字は使えないので、運用上注意する必要があります。
Set-Variable -name $HeaderName -Value $Column
}

#($Sheet.Cells.Item(1,$Column).text を呼び出して、何も入ってないとfalse(0)が返ってくるので、入っている限りHeaderを取ります。

for($Column= 1;[bool]($sheet.Cells.Item(1,$Column).text);$Column++)

{

$HeaderName = $Sheet.Cells.Item(1,$Column).text

$HeaderColumnMax = $Column

Write-Host $HeaderName $Column "列目"

#PowerShellでは２バイトコードを変数にできるので、ヘッダの名前にカラム位置を入れてやります。

#()など特殊文字は使えないので、運用上注意する必要があります。

Set-Variable -name $HeaderName -Value $Column

}

こうしてやると、行数と列名で処理できるので、あとはLoop処理して1行ごとに処理をしていけます。

カテゴリー: IT