Perlでxlsxファイルを出力する

ワークブックの作成

新規xlsxファイルを作成するには以下のようにする。

# Excel::Writer::XLSXモジュールを使用
use Excel::Writer::XLSX;

# ワークブック作成
my $wb = Excel::Writer::XLSX->new('my_data.xlsx');

# 書き込みの終了
my $wb->close();

全ての書き込みが終わったら必ずclose()を呼んでおく。同名ファイルがすでに存在する場合は、そのファイルを開いて編集や追加ではなく、新規ファイルで完全に上書きになるので注意。

ファイルパスの代わりに、通常のopen()であらかじめ開いたファイルハンドルを渡すこともできる。

open(my $fh, '>', 'my_data.xlsx');
my $wb = Excel::Writer::XLSX->new($fh);

ファイルハンドルとして$fhの代わりに\*STDOUTとすれば標準出力に出力できる。

ワークシートの追加

ワークブックを作成したらまずはワークシートを追加する。

# ワークシートを追加
my $ws = $wb->add_worksheet();

# ワークシート名を指定して追加
my $ws = $wb->add_worksheet('Data');

値の書き込み

セルに値を書き込むには、write()を使う。

セルの位置は、行と列をそれぞれ0から始まる数字で指定する方法と、「A1」のような文字列で指定する方法がある。

$ws->set_column('A:B', 15);
$ws->write(0, 0, 'ここはA1セル');
$ws->write(1, 0, 'ここはA2セル');
$ws->write(0, 1, 'ここはB1セル');
$ws->write('B2', 'ここはB2セル');

また、配列のリファレンスを用いて複数のセルを一度に書き込むこともできる。

$ws->write('A1', [
	['ここはA1セル', 'ここはA2セル'],
	['ここはB1セル', 'ここはB2セル']
]);

日本語の取り扱い

ワークシート名やセルの値に日本語を使う場合、add_worksheet()やwrite()に渡す日本語の文字列は、UTF-8やShift_JISのバイト列ではなく、Perlの内部文字列でなければならない。

以下は、日本語が正常に書き込める例である。

• Encode::decodeでリテラルのバイト列を内部文字列化して渡す。

  # ソースコードはUTF-8
  use Excel::Writer::XLSX;
  use Encode;
  
  my $wb = Excel::Writer::XLSX->new('my_data.xlsx');
  my $ws = $wb->add_worksheet(Encode::decode('utf-8', '日本語'));
  

• ソースコードをUTF-8で記述し、use utf8;を使用して、リテラルで渡す。

  # ソースコードはUTF-8
  use Excel::Writer::XLSX;
  use utf8;
  
  my $wb = Excel::Writer::XLSX->new('my_data.xlsx');
  my $ws = $wb->add_worksheet('日本語');
  

以降のサンプルコードは全て、後者のuse utf8;の方法が取られているものとする。

列の幅

列の幅はset_column()で設定できる。0から始まる列番号でも範囲指定できる。

$ws->set_column('A:A', 20);
$ws->write('A1', 'A列は幅20');

$ws->set_column('B:D', 5);
$ws->write('B1', 'B列～D列は幅5');

$ws->set_column(4, 5, 3);
$ws->write('E1', 'E列～F列は幅3');

set_column()で指定する幅の数値の単位は、その幅に入る半角文字数とおよそ一致し、Excel上でセル幅をマウスで変更しようとしたときに表示される「幅：8.38（72ピクセル）」といったポップアップ表示の「8.38」の方の値に概ね一致する（フォントに依存するのでピッタリは一致しない模様）。

ポップアップ表示の「72ピクセル」の方の値で指定したい場合は、set_column_pixels()が使える（こちらも指定した値ピッタリには一致させられない模様）。

$ws->set_column_pixels('A:A', 160);
$ws->write('A1', 'A列は幅160ピクセル');

$ws->set_column_pixels('B:D', 40);
$ws->write('B1', 'B列～D列は幅40ピクセル');

$ws->set_column_pixels(4, 6, 24);
$ws->write('E1', 'E列～G列は幅24ピクセル');

行の高さ

行の高さはset_row()で設定できる。set_column()と異なり、こちらは1行ずつしか設定できない模様。

$ws->set_column(0, 0, 15);

$ws->set_row(0, 10);
$ws->write('A1', '行1は高さ10');

$ws->set_row(1, 20);
$ws->write('A2', '行2は高さ20');

$ws->set_row(2, 30);
$ws->write('A3', '行3は高さ30');

行の高さで指定する数値の単位は「ポイント」で、Excel上で行の高さをマウスで変更しようとしたときに表示される「高さ：13.50（18ピクセル）」といったポップアップ表示の「13.50」の方の値に概ね一致する（画面上では1ピクセル単位でしか変更できないため、ピッタリは一致しない模様）。

オートフィルター

絞り込みや並べ替えをするためのオートフィルターを適用するには、autofilter()を使う。

$ws->set_column(0, 2, 10);
$ws->write('A1', [
    ['項目1', 111, 1111, 11111],
    ['項目2', 222, 2222, 22222],
    ['項目3', 333, 3333, 33333]
]);

# 列A～Cにオートフィルターを適用
$ws->autofilter('A:C');

ただし、オートフィルターは適用される（上の画像のように1行目にプルダウンが表示される）が、オートフィルターで並べ替えをすることはできない模様。ただ、プログラムで出力するのだから、プログラム内であらかじめ並べ替えてから出力すれば特に困ることは少なさそうではある。

先頭行固定

データ数が多い場合などで、先頭行を固定表示にしたい場合は、freeze_panes()を使う。

$ws->set_column(0, 2, 10);
$ws->write('A1', [
    ['項目1', 111, 1111, 11111],
    ['項目2', 222, 2222, 22222],
    ['項目3', 333, 3333, 33333]
]);

# 先頭行を固定
$ws->freeze_panes(1, 0);

freeze_panes()は、第1引数が固定したい行数、第2引数が固定したい列数である。以下のような指定が可能である。

# 先頭1行を固定
$ws->freeze_panes(1, 0);

# 先頭1列を固定
$ws->freeze_panes(0, 1);

# 先頭1行と先頭1列を固定
$ws->freeze_panes(1, 1);

# 先頭2行と先頭3列を固定
$ws->freeze_panes(2, 3);

フォーマットの作成

セルに書式を指定するには、add_format()であらかじめ書式を表すオブジェクトを作り、それを値と一緒にwrite()に渡すことで指定する。この書式オブジェクトは、同じ書式であれば使い回すことができる。

my $format = $wb->add_format(color => 'red');
$ws->write('A1', '赤い文字', $format);

複数の書式を同時に指定することもできる。

my $format = $wb->add_format(color => 'red', bold => 1, size => 24);
$ws->write('A1', '赤くて太字でサイズ24', $format);

書式を後から追加するセッターのようなメソッドも存在する。

my $format = $wb->add_format();
$format->set_color('red');
$format->set_bold();
$format->set_size(24);

set_column()やset_row()に渡して、列全体、行全体に指定することもできる。

# A列は全て文字色を赤色にする（第2引数のundefは「列幅を指定しない」の意）
$ws->set_column('A:A', undef, $wb->add_format(color => 'red'));

# 1行目は全て文字色を赤色にする（第2引数のundefは「行高を指定しない」の意）
$ws->set_row(0, undef, $wb->add_format(color => 'red'));

文字の書式

文字の色やフォントなどは以下のように指定する。

$ws->set_column(0, 0, 20);

$ws->write('A1', 'color => 10',         $wb->add_format(color => 10));
$ws->write('A2', 'bg_color => 10',      $wb->add_format(bg_color => 10));
$ws->write('A3', 'fg_color => 10',      $wb->add_format(fg_color => 10));
$ws->write('A4', 'bold => 1',           $wb->add_format(bold => 1));
$ws->write('A5', 'italic => 1',         $wb->add_format(italic => 1));
$ws->write('A6', 'underline => 1',      $wb->add_format(underline => 1));
$ws->write('A7', 'font => HGゴシックE', $wb->add_format(font => 'HGゴシックE'));
$ws->write('A8', 'size => 24',          $wb->add_format(size => 24));

boldやitalicに渡す値は真偽値であるが、Perlなので1が真となる。

bg_colorとfg_colorは、塗りのパターンの指定が無い場合はいずれもセルの背景色が設定される。

色は、'#000000'といったRGB値でも指定できるし、以下の色番号や色名でも指定できる。

$ws->set_column(0, 7, 5);
$ws->write(int($_ / 8), $_ % 8, "$_",
    $wb->add_format(bg_color => $_, align => 'center', valign => 'vcenter')
) for 0 .. 63;

上の画像は、0～63まで全ての色番号を背景色にしてみたもので、下はその中で定義済みの色名の一覧である。

文字の位置

セル内の文字の位置は、水平方向はalign、垂直方向はvalignで指定できる。

$ws->set_column(0, 1, 20);
$ws->set_row($_, 30) for 0 .. 2;

$ws->write('A1', 'align => left',      $wb->add_format(align => 'left'));
$ws->write('A2', 'align => center',    $wb->add_format(align => 'center'));
$ws->write('A3', 'align => right',     $wb->add_format(align => 'right'));
$ws->write('B1', 'valign => top',      $wb->add_format(valign => 'top'));
$ws->write('B2', 'valign => vcenter',  $wb->add_format(valign => 'vcenter'));
$ws->write('B3', 'valign => bottom',   $wb->add_format(valign => 'bottom'));

文字の折り返し

長い文字列をセル内で折り返すには、text_wrapを指定する。

$ws->set_column('A:A', 10);
$ws->write('A1', '長い長い長い長い長い長い長い長い長い長い文字列');
$ws->write('A3', '長い長い長い長い長い長い長い長い長い長い文字列', $wb->add_format(text_wrap => 1));

未指定のA1セルは改行されずにはみ出しているが、text_wrapを指定したA3セルはセル内に収められている。

なお、セルに改行を含める場合は、\nで改行し、かつ、text_wrap => 1を指定するとうまくいくようである。

セルの罫線

上下左右の罫線を一度に指定するborderの他、top、rightなどで個別に指定することもできる。値は0が罫線なし、1が通常の実線で、2～13は様々なスタイルの線となる。

罫線の色はborder_colorで指定できる（個別指定の場合は、top_color、right_colorなどがある）。

$ws->set_column($_, $_, 10) for 1, 3, 5, 7, 9;
$ws->set_column($_, $_, 3) for 0, 2, 4, 6, 8;

$ws->write($_ * 2 + 1, 1, "border => $_", $wb->add_format(border => $_)) for 0 .. 4;
$ws->write($_ * 2 + 1, 3, "top => $_",    $wb->add_format(top => $_))    for 0 .. 4;
$ws->write($_ * 2 + 1, 5, "right => $_",  $wb->add_format(right => $_))  for 0 .. 4;
$ws->write($_ * 2 + 1, 7, "bottom => $_", $wb->add_format(bottom => $_)) for 0 .. 4;
$ws->write($_ * 2 + 1, 9, "left => $_",   $wb->add_format(left => $_))   for 0 .. 4;

数字の書式

数字の書式はnum_formatで指定できる。

$ws->set_column('A:A', 32);
$ws->set_column('B:B', 12);

$ws->write('A1', '小数点以下第3位まで表示');
$ws->write('B1', 123, $wb->add_format(num_format => '0.000'));

$ws->write('A2', '小数点以下第1位までの百分率で表示');
$ws->write('B2', 0.1234, $wb->add_format(num_format => '0.0%'));

$ws->write('A3', '「年4桁/月2桁/日2桁」の日付として表示');
$ws->write('B3', 36526, $wb->add_format(num_format => 'yyyy/mm/dd'));

$ws->write('A4', '「時2桁:分2桁:秒2桁」の時刻として表示');
$ws->write('B4', 45296 / 86400, $wb->add_format(num_format => 'hh:mm:ss'));

日付や時刻の書式を効かせるには、文字列ではなく数値（日付なら1900/01/01からの日数、時刻なら1日を1とする割合値）で入力しなければならない。

# 日付の書式が効かず「2000/01/01」となってしまう（Excelに日付として認識されない）。
$ws->write('A1', '2000/01/01', $wb->add_format(num_format => 'dd mmm yyyy'));

# 日付の書式が効いて「01 Jan 2000」となる（Excelに日付として認識される）。
$ws->write('A2', 36526, $wb->add_format(num_format => 'dd mmm yyyy'));

あるいは、write_date_time()という日時入力専用のメソッドも存在する。ただし、「2000/01/01」などではダメで「2000-01-01T00:00:00.000」という形式でないと認識されないようだ。

# write_date_time()で日付を入力
$ws->write_date_time('A3', '2000-01-01T00:00:00.000', $wb->add_format(num_format => 'dd mmm yyyy'));

基本

Excel::Writer::XLSXではグラフを作成することも可能で、しかもかなり細かい設定までサポートしている。機能が非常に多く全ては試せていないため、基本的な部分のみ解説する。

• Excel::Writer::XLSX::Chart - A class for writing Excel Charts. - metacpan.org

まずは、グラフを作成するためのほぼ最小限のコードは以下の通りである。

# データの準備
$ws->write('A1', [
    ['', '2020年', '2021年', '2022年', '2023年'], # A列
    ['系列A', 10, 70, 40, 80], # B列
    ['系列B', 20, 60, 50, 100] # C列
]);

# 縦棒グラフの作成
my $chart = $wb->add_chart(type => 'column', embedded => 1);

# 系列の追加
$chart->add_series(
    name       => '=Sheet1!$B$1',
    categories => '=Sheet1!$A$2:$A$5',
    values     => '=Sheet1!$B$2:$B$5'
);
$chart->add_series(
    name       => '=Sheet1!$C$1',
    categories => '=Sheet1!$A$2:$A$5',
    values     => '=Sheet1!$C$2:$C$5'
);

# ワークシートへの追加
$ws->insert_chart('D2', $chart, { x_offset => 8, y_offset => 8 });

上記のコードで以下のようなグラフが生成できる。

特に指定が無い場合、タイトルは表示されず、凡例は右に表示される。また、グラフの色などは自動的に選択される。

なお、系列が1つだけで凡例を表示させた場合、凡例には系列名ではなく項目名が表示され、タイトルが系列名となるようである。これは円グラフを考慮しての仕様かと思われるが、円グラフ以外のグラフに合うように系列名の表示にする方法は分からなかった。系列が2つ以上ある場合は問題無く系列名が表示される。

グラフの作成

グラフの作成は、Workbookオブジェクトのadd_chart()で行なう。

my $chart = $wb->add_chart(type => 'column', embedded => 1);

ワークシート上に追加する場合は、embedded => 1を指定する。指定しないと、グラフシートで作成される。

グラフの種類はtypeとsubtypeの2つのオプションで決定する。主に以下のようなものが指定できる。

プロパティの指定方法

これ以降で説明するデータ系列の追加やグラフ要素の設定では、線や塗り、フォントなどの指定をする箇所が複数あるが、そこで指定するオブジェクトは共通の形式となっているため、先に説明しておく。

以下は、add_series()のlineオプションで「線」のオブジェクトを使用し、「赤色で太さ5の線」を指定した例である。

$chart->add_series(
    name       => '=Sheet1!$B$1',
    categories => '=Sheet1!$A$2:$A$5',
    values     => '=Sheet1!$B$2:$B$5',
    line => { color => 'red', width => 5 }
);

「線」を指定するには以下のキーを持つオブジェクトを渡す。

「塗り」を指定するには以下のキーを持つオブジェクトを渡す。

「マーカー」を指定するには以下のキーを持つオブジェクトを渡す。

「文字」を指定するには以下のキーを持つオブジェクトを渡す。

「位置」を指定するには以下のキーを持つオブジェクトを渡す。

「位置とサイズ」を指定するには以下のキーを持つオブジェクトを渡す。

データ系列の追加

データ系列の追加は、add_chart()で作成したグラフオブジェクトのadd_series()で行なう。複数の系列を追加するには、複数回add_series()を呼び出す。円グラフの場合は、最初の1回のみが有効となるようである。

$chart->add_series(
    name       => '=Sheet1!$B$1',
    categories => '=Sheet1!$A$2:$A$5',
    values     => '=Sheet1!$B$2:$B$5'
);

引数には以下のオプションを渡す。

グラフ要素の設定

軸や目盛線は、set_x_axis()、set_y_axis()で設定できる。

$chart->set_y_axis(
    name => '売上'
    min => 0,
    max => 10000
);

引数には以下のオプションを渡す。

グラフのタイトルは、set_title()で設定できる。

$chart->set_title(
    name => '年ごとの売上の比較'
);

引数には以下のオプションを渡す。

グラフの凡例は、set_legend()で設定できる。

$chart->set_legend(
    position => 'overlay_top_right'
);

引数には以下のオプションを渡す。

プロットエリアはset_plotarea()で設定できる。引数には以下のオプションを渡す。

グラフエリアはset_chartarea()で設定できる。引数には以下のオプションを渡す。

ワークシートへの追加

グラフが作成できたら、最後にWorksheetオブジェクトのinsert_chart()でワークシートに挿入する。

$ws->insert_chart('D2', $chart, {
    x_offset => 8,
    y_offset => 8
});

1つ目の引数は挿入する場所のセル、2つ目の引数が作成したグラフオブジェクトで、3つ目の引数には任意で以下のキーを持つオブジェクトを渡す。

デフォルトでは480×288ピクセルで作成されるとドキュメントには書かれていたが、私の環境では何も指定しない場合、約540×260ピクセルくらいであった。グラフオブジェクトのset_size()というメソッドでwidthとheightをピクセルで直接指定する方法もあるようだが、こちらも同じく指定した通りの正確なピクセル数にはならず、正確なサイズを指定する方法は分からなかった。

数式

セルに書き込むwrite()メソッドで、通常のExcel同様に=から始まる数式も入力できる。

$ws->write('A1', '数値A');
$ws->write('B1', '数値B');
$ws->write('C1', '数値C');
$ws->write('D1', '合計');
$ws->write('E1', '平均');
for (my $i = 1; 4 >= $i; $i++) {
	my $n = $i + 1;
	$ws->write($i, 0, int(rand(100)));
	$ws->write($i, 1, int(rand(100)));
	$ws->write($i, 2, int(rand(100)));
	$ws->write($i, 3, "=SUM(A$n:C$n)");
	$ws->write($i, 4, "=AVERAGE(A$n:C$n)");
}

条件付き書式（未稿）

条件付き書式の指定もできるようであるが、まだ試せていない。

ただ、これもオートフィルターの並べ替えと同じで、そもそもプログラムで生成しているのだから、プログラムで条件の判断をすればいいのであって、Excelの条件付き書式をわざわざ使用しなければいけない場面は少ないかもしれない。

その他（未稿）

他にもまだ多数のExcelの機能に対応しているようである。