Perlモジュール: HandleOleExcel.pm
2023/05/05
|
[Parent]
,
[Perl Top]
|
HandleOleExcel.pmとは
- HandleOleExcel.pmはWin32::OLEを利用してExcelファイルのデータにアクセスするPerlモジュールです。
- Win32::OLEを利用...つまりExcel自身をPerlスクリプトから操作するため、PerlとExcelが動作するWindows環境下(*1)で使用することができます。
- 本来ならVBAで記述する処理を、Excelファイルの外で、かつPerlで記述することが可能になります。またExcelオブジェクトの扱い方についてはネット上にある膨大なVBAの記述を参照しつつ、自身のツールを作成することができます。
ParseXLSXと何が違うのか
- PerlのExcel Parserとしてメジャーなのは"ParseXLSX"です。このモジュールはExcelファイルをXMLデータとして解析/アクセスします。そのためParseXLSXはLinux等Excelが無い環境でもExcelデータを利用できます。
- ですが...ParseXLSXは日本語などマルチバイト文字の処理を苦手としているため、使いにくい点がありました。また画像データや名前付きレンジ等の処理もできません。
- HandleOleExcel.pmは、ParseXLSXではやりにくい処理に対応するため作成しました。両モジュールとも長所/短所あるため適材適所で利用することになります。
|
免責事項
- 本モジュールを利用したことで生じる全ての結果に対し、モジュール作成者は責を負いません。
ダウンロード
モジュール名
- HandleOleExcel
- WindowsのOLE機能を利用してExcelファイルにアクセスします。
Version
使用環境
- HandleOleExcel.pmはPerl及びMicrosoft ExcelがインストールされたWindows上で動作します。
- "Win32::OLE"と"Image::Size"モジュールを利用します。モジュールが無い場合はCPANよりインストールして下さい。
- Strawberry Perl環境ではコマンドプロンプトからcpanコマンドでインストールすることができます。> cpan Win32::OLE
> cpan Image::Size
使用例
- レンジの値取得例
#!perl -w
use lib '../'; # HandleOleExcel.pmの置き場所
use HandleOleExcel;
use strict;
{
# HandleOleExcelオブジェクト取得
my $obj = HandleOleExcel->new();
# Excelファイルオープン
$obj->openExcelFile('sample.xlsx');
# Sheet1の'A1:B3'レンジ値取得(戻り値は2次元リストリファレンス)
my $ret = $obj->getValRange(range=>'A1:B3', sheet=>'Sheet1');
for (my $row=0; $row<@{$ret}; $row++) {
for (my $col=0; $col<@{$ret->[$row]}; $col++) {
print "value ($row,$col) = $ret->[$row]->[$col]\n";
}
}
# Excelファイルクローズ
$obj->closeExcelFile();
} |
|
Index
- メソッド
- オブジェクト作成
- ExcelファイルOpen/Close/Save系
- ワークシート/セルデータGet系
- ワークシート/セルデータSet系
- setValRange(sheet=>$sheet, range=>$range, value=$value)
- setValCurrentRegion(sheet=>$sheet, range=>$range, value=>$value)
- setValUsedRange(sheet=>$sheet, value=>$value)
- setImgRange(sheet=>$sheet, range=>$range, img=>$img)
- setShapeRange(sheet=>$sheet, range=>$range, shape=>$shape)>
- clearContentsRange(sheet=>$sheet, range=>$range)
- ワークシート操作系
- その他
- Appendix
|
メソッド: オブジェクト作成
- new()
- newメソッドはHandleOleExcelのオブジェクトを作成します。
my $objExcel = HandleOleExcel->new();
|
メソッド: ExcelファイルOpen/Close/Save系
- openExcelFile($file_name)
- openExcelFileメソッドは引数で指定したExcelファイルをOpenします。
my $ret = $objExcel->openExcelFile('..\sample.xlsx');
# 成功: $ret = 1
# 失敗: $ret = undef
|
- ファイル指定には絶対パスと相対パスの両方を使用できます。
- closeExcelFile()
- closeExcelFileメソッドはオブジェクトがOpenしているExcelファイルをCloseします。
my $ret = $objExcel->closeExcelFile();
# 成功: $ret = 1
# 失敗: $ret = undef
|
- saveExcelFile()
- saveExcelFileメソッドはオブジェクトがOpenしているExcelファイルに変更内容を"上書き"Saveします。
my $ret = $objExcel->saveExcelFile();
# 成功: $ret = 1
# 失敗: $ret = undef
|
- saveAsExcelFile($file_name)
- saveAsExcelFileメソッドはオブジェクトが持つExcelデータを指定ファイルに"別名"Saveします。
my $ret = $objExcel->saveAsExcelFile('..\sample_save_as.xlsx');
# 成功: $ret = 1
# 失敗: $ret = undef
|
- createExcelFile($file_name)
- createExcelFileメソッドは指定名のExcelファイルを新規作成します。
my $ret = $objExcel->createExcelFile('..\sample_created.xlsx');
# 成功: $ret = 1
# 失敗: $ret = undef
|
- createExcelFileメソッド実行後、HandleOleExcelオブジェクトの操作対象は新規作成したExcelファイルになります。
- 既に存在するExcelファイルは指定できません。
- openExcelFileメソッドで既に操作対象Excelファイルを持つHandleOleExcelオブジェクトは、createExcelFileメソッドを実行できません。
メソッド: ワークシート/セルデータGet系
- getListWorksheets()
- getListWorksheetsメソッドはオブジェクトが持つExcelのワークシート名リスト(リファレンス)を取得します。
my $ref_list = $objExcel->getListWorksheets();
# 成功: $ref_list = Worksheet名リストのリファレンス
# 失敗: $ref_list = undef
|
- getValWorksheet($sheet)
- getValWorksheetメソッドは指定したワークシートが持つ全セルの値を取得します。
my $ret_some = $objExcel->getValWorksheet('Sheet1');
# 成功: $ret_some = 2次元リストのリファレンス
# 失敗: $ret_some = undef
|
- getListNamedRanges()
- getListNamedRangesメソッドはオブジェクトが持つ名前付きレンジのリスト(リファレンス)を取得します。
my $ref_list = $objExcel->getListNamedRanges();
# 成功: $ref_list = 名前付きレンジリストのリファレンス
# 失敗: $ref_list = undef
|
- getInfoNamedRange($named_range)
- getInfoNamedRangesメソッドは指定した名前付きレンジ構造情報のリファレンスを取得します。
my $info = $objExcel->getInfoNamedRange($named_range);
# 成功: $info = 名前付きレンジ構造情報のリファレンス
# 失敗: $info = undef
|
- 戻り値オブジェクトは、名前付きレンジの各要素データについて下記の情報を持ちます。
$info->[
{sheet=>シート名, range=>レンジ名, array=>0or1, row=>行数, col=>列数},
{sheet=> ... }
...
];
# sheet: 各要素レンジが置かれるシート名 (例: Sheet1)
# range: 各要素レンジのA1型レンジ名 (例: $A$2:$B$3)
# array: 各要素レンジが2次元データなら1, スカラーなら0
# row: 各要素レンジの行数(2次元データの場合最小値1。スカラーは0)
# col: 各要素レンジの列数(2次元データの場合最小値1。スカラーは0)
|
- getValRange(sheet=>$sheet, range=>$range)
- getValRangeメソッドは指定ワークシート内の指定レンジから値を取得します。
- 単一セルのレンジ指定(例:'A2')
my $scalar = $objExcel->getValRange(sheet=>'Sheet1', range=>'A2');
# 成功: $scalar = 指定レンジの値
# 失敗: $scalar = undef
|
- 複数セルのレンジ指定(例:'A1:B3')
my $ref_list2 = $objExcel->getValRange(sheet=>'Sheet1', range=>'A1:B3');
# 成功: $ref_list2 = レンジの値を持つ2次元リストリファレンス(->[行]->[列])
# +---------------------------+---------------------------+
# | $ref_list2->[0]->[0] = A1 | $ref_list2->[0]->[1] = B1 |
# | $ref_list2->[1]->[0] = A2 | $ref_list2->[1]->[1] = B2 |
# | $ref_list2->[2]->[0] = A3 | $ref_list2->[2]->[1] = B3 |
# +---------------------------+---------------------------+
# 失敗: $ref_list2 = undef
|
- 名前付きセルのレンジ指定(例:'named_sum')
my $ret_some = $objExcel->getValRange(range=>'named_sum');
# 成功: $ret_some = 指定レンジの値(構成セルが単一の場合)
# 成功: $ret_some = レンジの値を持つ1次元リストリファレンス(構成セルが複数の場合)
# 失敗: $ret_some = undef
|
- 名前付きレンジを"range引数"に指定した場合、"sheet引数"指定があっても無視されます。
名前付きレンジ自身がシート名+セル名の構成情報を持つためです。
- 名前付きレンジが複数セル構成の場合、要素毎にスカラーと2次元リストリファレンスが混在します。
- getValCurrentRegion(sheet=>$sheet, range=>$range)
- getValCurrentRegionメソッドは指定レンジ(単一セル)からのCurrent Region(値を持つ隣接セル)を取得します。
- 単一セルのレンジ指定(例:'B2')
my $ret_some = $objExcel->getValCurrentRegion(sheet=>'Sheet1', range=>'B2');
# 成功: $ret_some = CurrentRegionの値を持つ2次元リストリファレンス
# 成功: $ret_some = スカラー(隣接セルに値が無い場合、指定セルの値のみ)
# 失敗: $ret_some = undef
|
- 名前付きレンジの指定(例:'named_sum')
my $ret_some = $objExcel->getValCurrentRegion(range=>'named_sum');
# 成功: $ret_some = CurrentRegionの値を持つ2次元リストリファレンス
# 成功: $ret_some = スカラー(隣接セルに値が無い場合、指定セルの値のみ)
# 失敗: $ret_some = undef
|
- 名前付きレンジを"range引数"に指定した場合、"sheet引数"指定があっても無視されます。
- getValUsedRange($sheet)
- getValUsedRangeメソッドは指定ワークシートのUsedRange(値を持つセルのテーブル)を取得します。
my $ret_some = $objExcel->getValUsedRange('Sheet1');
# 成功: $ret_some = UsedRangeの値を持つ2次元リストリファレンス
# 成功: $ret_some = スカラー(値も持つセルが一つ)
# 失敗: $ret_some = undef
|
- getValWorksheetは"A1セルからxlCellTypeLastCell"までの値を戻しますが、
getValUsedRangeは"値を持つセル範囲を切り出して"戻します。
- 例えば1行目と1列目が空白の場合、getValUsedRangeの戻り値$ret->[0]->[0]は"B2セル"を指します。
メソッド: ワークシート/セルデータSet系
- setValRange(sheet=>$sheet, range=>$range, value=$value)
- setValRangeメソッドは指定ワークシート内の指定レンジに値を設定します。
- 単一セルのレンジ指定(例:'A2')
my $ret = $objExcel->setValRange(sheet=>'Sheet1', range=>'A2', value=>$scalar);
# $scalar = 'A2の値';
# 成功: $ret = 1
# 失敗: $ret = undef
|
- 複数セルのレンジ指定(例:'A2:B3'): valueには2次元リストリファレンスを渡す
my $ret = $objExcel->setValRange(sheet=>'Sheet1', range=>'A2:B3', value=>$ref_list2);
# $ref_list2->[0]->[0] = 'A2の値'; $ref_list2->[0]->[1] = 'B2の値';
# $ref_list2->[1]->[0] = 'A3の値'; $ref_list2->[1]->[1] = 'B3の値';
# 成功: $ret = 1
# 失敗: $ret = undef
|
- 名前付きセル(単一セル)のレンジ指定(例:'named_sum')
my $ret = $objExcel->setValRange(range=>'named_sum', value=>$scalar);
# $scalar = 'named_sumの値';
# 成功: $ret = 1
# 失敗: $ret = undef
|
- 名前付きレンジを"range引数"に指定した場合、"sheet引数"指定があっても無視されます。
- 名前付きセル(複数セル)のレンジ指定(例:'named_sum2')。
valueには1次元リストリファレンス要素の構成に合わせてスカラー又は2次元リストリファレンスを渡します。
my $ret = $objExcel->setValRange(range=>'named_sum2', value=>$ref_list);
# $ref_list->[0] = 'named_sum2の指定1番目';
# $ref_list->[1] = 'named_sum2の指定2番目';
# ...
# 成功: $ret = 1
# 失敗: $ret = undef
|
- setValCurrentRegion(sheet=>$sheet, range=>$range, value=>$value)
- getValCurrentRegionメソッドは指定レンジを基準にしたCurrent Regionに値を設定します。
- valueの2次元リストリファレンスが持つデータ数/アスペクトによってCurrent Regionの範囲を変更します。
- データ範囲が既存領域より小さい場合、元のデータは値のみ消去されます。
- 名前付きレンジを"range引数"に指定した場合、"sheet引数"指定があっても無視されます。
my $ret = $objExcel->setValCurrentRegion(sheet=>'Sheet1', range=>'A2', value=>$ref_list2);
# $ref_list2->[0]->[0] = 'Region左上端の値'; $ref_list->[0]->[1] = '列隣接値';...
# $ref_list2->[1]->[0] = '行隣接値'; ...
# ...
# 成功: $ret = 1
# 失敗: $ret = undef
|
- setValUsedRange(sheet=>$sheet, value=>$value)
- getValUsedRangeメソッドは指定ワークシートのUsed Rangeに値を設定します。
- valueの2次元リストリファレンスが持つデータ数/アスペクトによってUsed Rangeの範囲を変更します。
- データ範囲が既存領域より小さい場合、元のデータの値のみ消去されます。
my $ret = $objExcel->setValUsedRange(sheet=>'Sheet1', value=>$ref_list2);
# $ref_list2->[0]->[0] = 'Used Range左上端の値'; $ref_list->[0]->[1] = '列隣接値';...
# $ref_list2->[1]->[0] = '行隣接値'; ...
# ...
# 成功: $ret = 1
# 失敗: $ret = undef
|
- setImgRange(sheet=>$sheet, range=>$range, img=>$img)
- setImgRangeメソッドは指定レンジにjpg/bmp/png等のイメージファイルを置きます。
- range引数に名前付きセルを指定した場合、sheet引数は無視されます。
my $ret = $objExcel->setImgRange(sheet=>'Sheet1', range=>'A2', img=>'..\sample.png');
# 成功: $ret = 画像のshapeオブジェクト
# 失敗: $ret = undef
|
- setImgRangeメソッドは、sheet/range/img以外のオプション引数を持ちます。
- adjust
0: 画像を原寸サイズでセルに置く
1: 画像を指定レンジのセルサイズに合わせる(デフォルト)
- aspect
0: 画像をセルサイズに合わせる(adjust=1)際、アスペクトを保持しない
1: 画像をセルサイズに合わせる(adjust=1)際、アスペクトを保持する(デフォルト)
- center
0: 画像左上を指定セルの左上に合わせて置きます
1: 画像中心を指定セルの中心に合わせて置きます(デフォルト)
- link
0: 画像データ自身をExcelファイル内のオブジェクトとしてコピーします(デフォルト)
1: 画像ファイルのリンクをExcelファイル内に置きます
- offset
セル内画像サイズの縮小ピクセル数(デフォルト=1)
※この値を0にするとセル罫線が画像の下になります。
※正の値を設定すると、セルサイズに対し画像が縮小されます。
※負の値を設定すると、セルサイズに対し画像が拡大されます。
- setShapeRange(sheet=>$sheet, range=>$range, shape=>$shape)
- setShapeRangeメソッドは指定レンジにshape(circle/oval/rectangle)を置きます。
- 指定レンジのセルサイズに合わせてshapeのサイズを調整します。
- range引数に名前付きセルを指定した場合、sheet引数は無視されます。
my $ret = $objExcel->setShapeRange(sheet=>'Sheet1', range=>'A2', shape=>'circle');
# 成功: $ret = 画像のshapeオブジェクト
# 失敗: $ret = undef
|
- shape引数には下記が指定できます。必要に応じてshape種類追加予定です。
- circle: 丸(真円)
- oval: 楕円
- rectangle: 長方形
- 現状塗りつぶし無し、線幅1ポイント固定です。これも必要に応じて指定追加予定です。
- setShapeRangeメソッドは、sheet/range/shape以外のオプション引数を持ちます。
- offset
セル内Shapeサイズの縮小ピクセル数(デフォルト=1)
※この値を0にするとセル罫線がShapeの下になります。
※正の値を設定すると、セルサイズに対しShapeが縮小されます。
※負の値を設定すると、セルサイズに対しShapeが拡大されます。
- clearContentsRange(sheet=>$sheet, range=>$range)
- clearContentsRangeメソッドは引数で指定されたレンジのコンテンツをクリアします。
my $ret = $objExcel->clearContentsRange(sheet=>'Sheet1', range=>"A1:B3");
# 成功: $ret = 1
# 失敗: $ret = undef
|
- 本メソッドは名前付きレンジに対応していません。名前付きレンジのコンテンツをクリアするには
getInfoNamedRange()
で要素毎のsheet/range情報を取得し、要素毎に本メソッドを適用して下さい。
# 名前付きレンジコンテンツクリア例
my $info = getInfoNamedRange($named_range);
foreach my $ele (@{$info}) {
clearContentsRange(sheet=>$ele->{sheet}, range=>$ele->{range});
}
|
ワークシート操作系
- addWorksheet(name=>$sheet0)
- addWorksheetメソッドは引数で指定された名前のワークシートを追加します。
my $ret = $objExcel->addWorksheet('Sheet2');
# 成功: $ret = 追加シートオブジェクト
# 失敗: $ret = undef
|
- addWorksheetメソッドは、下記のオプション引数を持ちます。
- after
指定したワークシートの「後(after)」にワークシートを追加します。
後述のbefore及びafterとも指定無し(デフォルト)の場合最終ワークシートの後に追加します。
- before
指定したワークシートの「前(before)」にワークシートを追加します。
afterと同時指定時はafterが優先されます。
- count
ワークシートの追加枚数を指定します。(デフォルト=1)
- copyWorksheet(base=>$sheet0)
- copyWorksheetメソッドは指定されたワークシートをコピーします。
my $ret = $objExcel->copyWorksheet(base=>'Sheet1');
# 成功: $ret = コピーシートオブジェクト
# 失敗: $ret = undef
|
- copyWorksheetメソッドは、下記のオプション引数を持ちます。
- copy
copyワークシートの名前を指定します。
- after
指定したワークシートの「後(after)」にコピーワークシートを追加します。
後述のbefore及びafterとも指定無し(デフォルト)の場合最終ワークシートの後に追加します。
- before
指定したワークシートの「前(before)」にコピーワークシートを追加します。
afterと同時指定時はafterが優先されます。
- removeWorksheet($sheet)
- removeWorksheetメソッドは引数で指定されたワークシートを削除します。
my $ret = $objExcel->removeWorksheet('Sheet1');
# 成功: $ret = 1
# 失敗: $ret = undef
|
メソッド: その他
- getTypeVal($var)
- getTypeValメソッドは引数で指定された変数のデータ型を判定します。
my $ret = $objExcel->getTypeVal($var);
# $ret = 0: スカラー
# $ret = 1: 1次元リストリファレンス
# $ret = 2: 2次元リストリファレンス
# $ret = undef: undef
|
- getStrDatWorksheet(sheet=>$sheet, range=>$range, mode=>$mode)
- getStrDatWorksheetメソッドは、指定シートの指定セルを基準に読み込んだ表データを構造化して戻します。
my $ret = getStrDatWorksheet(sheet=>'Sheet1', range=>'B3', mode=>'r_c');
# 成功: $ret = 構造化データリファレンス
# 失敗: $ret = undef
|
- mode引数には下記を指定することができます。
- c: 指定セルから見て {列タグ}->[インデックス番号] によりデータを構造化します(デフォルト)。
- r: 指定セルから見て {列タグ}->[インデックス番号] によりデータを構造化します。
- c_r: 指定セルから見て {列タグ}->{行タグ} の形式でデータを構造化します。
- r_c: 指定セルから見て {行タグ}->{列タグ} の形式でデータを構造化します。
- getStrDatWorksheetメソッドが戻すリファレンスはutf8とcp932の漢字コードを持ちます。Perlスクリプト側で読み取りデータに対して正規表現を適用するケースはutf8を使用します。
- $ret->{cp932}: cp932漢字コードのデータです
- $ret->{utf8}: utf8漢字コードのデータです
- シート中のデータと構造化データの関係について例を示します。赤枠が基準セルです。
Figure: cモード(左)とrモード(右)のデータ配置とタグ/インデックスの関係
Figure: c_rモードとr_cモードのデータ配置とタグの関係
|
Appendix: レンジの種類とデータ型
- レンジのデータはスカラーか2次元リストリファレンス
- 通常のレンジ、例えば "B4"や"D4:E6"等を指定し、getValRangeメソッド等を実行すると下記が値として戻ります。
- 単一セルのレンジ: スカラー
my $ret = $obj->getValRange(sheet=>'Sheet1', range=>'B4');
- 複数セルのレンジ: 2次元リストリファレンス
my $ret = $obj->getValRange(sheet=>'Sheet1', range=>'D4:E6');
Figure A-01: 単一セル/複数セルレンジの値
- 1行又は1列の複数セルを持つレンジも2次元リファレンスリストが戻ります。
- 複数セルのレンジ: 2次元リストリファレンス
my $ret = $obj->getValRange(sheet=>'Sheet1', range=>'G4:G6');
Figure A-02: 複数セル(1行又は1列)レンジの値
- 名前付きレンジの複数セルデータは1次元リストリファレンス
- 名前付きレンジでは、単一セルで構成されている場合スカラーが戻ります。複数セル構成の場合1次元リストリファレンスが戻ります。
- 複数セルの名前付きレンジ: 1次元リストリファレンス
my $ret = $obj->getValRange(range=>'named_range_0');
Figure A-03: 名前付きレンジ複数セル構成例
- 上記例の戻り値($ret:1次元リストリファレンス)が持つデータ構造を示します。
my $ret = $obj->getValRange(range=>'named_range_0');
# $ret->[0]: 1番目の要素"L5"の値をスカラーとして持つ
print $ret->[0]; # L5
# $ret->[1]: 2番目の要素"M6:M7"の値を参照する2次元リストリファレンスを持つ
my $ref1 = $ret->[1]; # 見やすさのために$ref1で受ける
print $ref1->[0]-[0]; # M6
print $ref1->[1]-[0]; # M7
# $ret->[2]: 3番目の要素"L9:M10"の値を参照する2次元リストリファレンスを持つ
my $ref2 = $ret->[2]; # 見やすさのために$ref2で受ける
print $ref2->[0]-[0]; # L9
print $ref2->[0]-[1]; # M9
print $ref2->[1]-[0]; # L10
print $ref2->[1]-[1]; # M10
|
- 名前付きレンジで値を設定するには、与えるデータ構造を設定対象レンジのデータ構造と一致させることが必須です。そのため名前付きレンジのデータ構造情報取得メソッド
getInfoNamedRange($named_range)
を用意しています。
|
今後の予定
|
Notes
- 詳細調査が難しいため、あえて「ざっくり」と書いています。ご了承ください。
- テストケースが少ないためバグが有ると思います。自分自身で使用しながらデバッグ及び機能追加継続予定です。
|
2023/05/05: Ver.0.09: getStrDatWorksheetメソッド追加
2023/04/23: Ver.0.08: get/setValCurrentRegionの名前付きレンジ扱い修正
2023/04/16: Ver.0.07: createExcelFileメソッド追加
2023/04/10: Ver.0.06: getInfoNmaedメソッド追加及び名前付きレンジ扱いについての説明修正
2023/04/02: Ver.0.05: get_dim_value_of_range関数(private)追加
2023/04/01: Ver.0.04: clearContentsRangeメソッド追加
2023/03/29: Ver.0.03: copyWorksheetメソッド追加
2023/03/28: Ver.0.02: add/removeWorksheet,setShapeRangeメソッド追加
2022/02/19: Ver.0.01: 試行版
|
[Parent]
,
[Perl Top]
|