PerlのWin32::OLEでExcel操作
2022/02/11
|
|
[Prev]
[Next]
[Top]
,
Perl
[Prev]
[Next]
,
[Perl Top]
|
Excelファイルに画像の書き込み(挿入)をしたいと思ったけれど
- 従来の業務だとワークマシンがLinuxで、更に画像データを扱う機会も無く、Excelからデータを読むとしてもセルのデータが読めれば良かったのですが、業務内容が変わってから、作成したデータをExcelへ戻したり、画像データを挿入したいと要求されるようになりました。あとはグラフなどの視覚要素を入れたいと言われるのも時間の問題かと。
- 数年前に"日本語が入ったExcelのデータ読み込み"について"調べた"のですが、書き込みは特に触れず、ましてや画像挿入など意識にもありませんでした。VBAやVBScriptならネット上の解説が豊富で困らないのですが、この2言語はデータ構造化や正規表現をPerlほど気軽に使用できない(*1)ため、ツールバリエーションが増えそうな場合は使用を避けていました。
VBScriptはOLEを使ってる...ん? Perlも使えるのでは?
- とりあえず"画像挿入"についてはVBScriptからExcelを操作する方法(*2)でやっつけました。OLE(*3)を経由してExcelオブジェクトを掴み、あとはVBScript内で操作するだけ。
- 作ったツールを渡して、小心者の私がようやく落ち着いた頃、"ん? OLEってPerlでも使えないか?"ということに気付きました。遅すぎるよ...自分...。そして調べるとありました。"Win32::OLE"です。
- そこでOLEを利用してExcelにアクセスするモジュール(パッケージ)を作ってみました。HandleOleExcel.pmです。類似ツール作成のためにコードの一部分を示しつつ注意点を説明したいと思います。
Excelというアプリケーション自身のオブジェクトを取得
- Excelというアプリケーション自身のオブジェクトを取得します。VBScriptで取得する方法は下記です。
- Set objExcel = CreateObject("Excel.Application")
- PerlのWin32::OLEによる記述は下記です。オブジェクトの作成/取得は他のクラス/パッケージ同様"new"になります。
- my $objExcel = Win32::OLE->new("Excel.Application");
- 一度オブジェクトを掴んでしまえば、データの操作はオブジェクトのメソッド又はプロパティを用いるので、以降はVBScript/VBA/Perlでも同じになります。厳密にはメソッド/プロパティにアクセスする演算子の違いはありますが、"."(ドット)を"->"(アロー)に読み替えればOKです。
Excelから見てカレントディレクトリ/フォルダは無い
- Excelブックを開く場合
- my $objBook = $objExcel->Workbooks->Open("C:\Users\ユーザ名\Documents\sample.xlsx");
のように記述しますが、下記のようにスクリプト実行の場所から見える相対パスでファイル名を渡すと開けません。これはVBA及びVBScriptでも同様です。
- my $objBook = $objExcel->Workbooks->Open('..\sample.xlsx'); # Openできない
- Excelオブジェクトがスクリプトのカレントディレクトリを認識していないことが原因のため、一度絶対パスに変換してExcelに渡す必要があります。VBAやVBScriptでは、FileSystemObject(つまりエクスプローラ)のメソッドを利用するのがよくある手ですが、当然PerlのWin32::OLEでも適用できます。
-
my $objFso = Win32::OLE->new('Scripting.FileSystemObject'); # エクスプローラオブジェクト取得
my $absPath = $objFso->GetAbsolutePathName('..\sample.xlsx'); # 絶対パスへ変換
my $objBook = $objExcel->Workbooks->Open($absPath); # 絶対パスを与えてExcelブックOpen
戻り値の型が様々...
- VBAやVBScript(変数型が無い)では、原則Variant型変数を用いることで、リスト/スカラーの区別が緩くなっていますが、Perlの場合は変数の型を意識する必要があります。
- Excelのレンジから値を読むと、様々な型で値が戻ってくるからです。下記はその例になりますが、レンジ名("C4"等)を "$range変数" に入れて値を取得するコードです。
- my $objRange = $objSheet->Range($range);
my $objRet = $objRange->{Value};
# $objSheet: 取得済みのワークシートオブジェクト
# $objRange: レンジオブジェクト
# $objRet: $objRangeのValueプロパティ戻り値を受ける変数
# $range: 指定レンジ名(例: "A1:B2", "C4"等)
- Perl側で受け取った変数 "$objRet" ですが、下記のようにレンジのセル構成によって戻り値型が変わります。通常のレンジ指定では、値が複数の場合2次元リストのリファレンスが戻ります。
- 単一セル構成: "C4"等: スカラーが戻る
- 複数セル構成: "A1:B4"等: 2次元リストのリファレンスが戻る
- そして"名前付きレンジ"(単一又は複数セルの集合に名前を付けたレンジ)の場合は下記です。隣接しない複数セルで構成されるケースもあるため1次元リストのリファレンスが戻ります。
- 単一セル構成: スカラーが戻る
- 複数セル構成: 1次元リストのリファレンスが戻る
- そして、UsedRangeやCurrentRegion...これは値の有無によってスカラーか2次元リストに変化します。
- 値を持つセルが単一: スカラーが戻る
- 値を持つセルが複数: 2次元リストのリファレンスが戻る
- このように戻り値型に変化があるため、ref関数等でリスト/スカラー型を確認し、次の処理へ繋げなければなりません。
- そして書き込みについても上記のルールが適用されます。スカラー/リスト(リファレンス)の型一致が必要です。
Quitしないとプロセスが残る
- 考えてみると当たり前ですが、OLEによるExcel操作とは、Excelアプリーケーションを介しているので、当然処理完了後はExcelを終了/閉じることが必要です。
- Windowsのデスクトップ上でExcelを使用している場合、アプリケーション終了処理はあまり意識せずとも行っているでしょう。ですがプログラムから使用する場合、視覚的にExcelは見えないので、終了処理(Quit)を忘れがちです。
- またQuit()メソッドの実行し忘れだけでなく、そもそもプログラムがエラーで途中終了してしまった場合も同様の問題が起きます。いちいちタスクマネージャを起動して残りプロセス終了では、あまりにも使いにくい。
- そこでデストラクタ(DESTROY関数)を利用し、もしオブジェクト破棄時に"Application.Excel"のオブジェクトがある(undefでは無い)場合、Quit()メソッドを実行してExcelを終了させる仕掛けが必要になります。
- sub DESTROY {
my ($obj) = @_;
$obj->{obj_excel}->Quit() if ($obj->{obj_excel});
...
- そしてDESTROY()を使うということは、Applicaiton.Excelを利用するPerl側のオブジェクトはClassであることが望ましいと言えます。
後は慣れたPerlで自由に
- Win32::OLEによるExcel利用の注意点/考慮点を書いてきました。Excelオブジェクトのアクセスについては、VBAやVBScriptの記述例を参照(Google先生に聞く)すれば良いですし、取得した値の処理に正規表現を利用するのもPerlなら楽々です。
- 他の例では、画像サイズ取得時にVBScriptだとpngファイルは扱いにくいのですが、Perlだと意識することもありません。
- PerlスクリプトだとPerlをインストールしたPC以外で利用できないという話もありますが、PAR::Packerを使用して、Executableにすれば対応できるでしょう。
- もちろんExcelファイルをzip圧縮されたXMLとしてアクセスするパッケージも有用ですが、XMLハンドラを使うかOLEを使うかはやりたい処理に合わせて好きな方を選べば良いかなと思っています。
|
|
Notes
|
2022/02/10: 初版
2022/02/11: タイトル変更
|
|
[Prev]
[Next]
[Top]
,
Perl
[Prev]
[Next]
,
[Perl Top]
|