前回のDBIを使ってデータベースを操作すれば,さまざまな情報を自在に取り出せます。さらに,その結果をExcelファイルやPDF (Portable Document Format)ファイルのように見やすく,扱いやすい形式にしておけば,自分ばかりでなく他の人に渡した際も喜ばれるでしょう。

Excelファイルの作成方法

 Excelファイルは1シートの行や列の数に限りがありますが,書式を整えて出力でき,データを容易に加工できるなど便利な形式であることは確かです。利用できるOSが限定される面がありますが,Linux上ではOpenOfficeやGnumericなどExcelファイルを読み出せるソフトウエアであれば利用できます。

 PerlからExcelファイルを作成する方法としては,Windows上で「Win32::OLE」を利用してMicrosoft Excel(以下,MS Excel)そのものを用いる方法が知られています。しかし,Linuxなど他のOSでも「Spreadsheet::WriteExcel」というモジュールを使えば,簡単にExcelファイルを作成できます。

●Spreadsheet::WriteExcel

 Spreadsheet::WriteExcel(以下Sp::W)はJohn McNamara氏が作成しているExcel95形式のファイルを作成する,Perlのみで開発されたモジュールです。

 Excel95形式のため,セルの文字数が255文字以下でなければならないという制約があります。また,通常は7Mバイトを超える巨大なExcelファイルも作成できません。

 現在,Excel97形式への対応が進められています。さらにSp::Wに含まれている別モジュール「WorkbookBig.pm」を利用すれば,7Mバイトを超えるファイルも作成できます。

●Sp::Wの利用方法

 図2の集計スクリプトを,Sp::Wを用いてExcelファイルを作成するように変更したものが図1です。写真1は,このスクリプトで作成したExcelファイルをMS Excel上で表示したものです。

 1: #!/bin/perl -w
 2: use strict;
 3: use DBI;
 4: use Jcode;                        #文字をSJISにするため
 5: use Spreadsheet::WriteExcel;
 6:
 7: #2. WriteExcelを作成
 8: #2.1 Workbookを追加、対象のワークシートを選択
 9: my $oBook = Spreadsheet::WriteExcel->new('List3.xls');
10: my $oWkS = $oBook->addworksheet(jcode('集計結果')->sjis);
11:
12: #2.2 ワークシートの共通設定(列幅、グリッドライン)
13: $oWkS->set_column(0, 0, 40);      #列の幅を40
14: $oWkS->set_column(1, 1, 20);      #列の幅を20
15: $oWkS->hide_gridlines(1);         #印刷時にはオフ
16:
17: #2.3 書式の作成
18: my $oFmtH = $oBook->addformat();
19: $oFmtH->set_font(jcode('MS P明朝')->sjis);
20: $oFmtH->set_size(16);
21:
22: my $oFmtTH = $oBook->addformat();
23: $oFmtTH->copy($oFmtH);            #書式をコピー
24: $oFmtTH->set_size(11);            #フォントを11ポイントに
25: $oFmtTH->set_align('center');     #中央ぞろえ
26: $oFmtTH->set_bg_color('silver');  #銀色で塗りつぶし
27: $oFmtTH->set_border();            #罫線で囲む
28:
29: my $oFmtTT = $oBook->addformat(); #表データ:タイトル列
30: $oFmtTT->copy($oFmtH);
31: $oFmtTT->set_size(11);            #フォントを11ポイントに
32: $oFmtTT->set_align('left');       #左寄せ
33: $oFmtTT->set_border();            #罫線で囲む
34:
35: my $oFmtTV = $oBook->addformat(); #表データ:値列
36: $oFmtTV->copy($oFmtTT);           #$oFmtTtをコピー
37: $oFmtTV->set_align('right');      #右寄せ
38:
39: my $iRow = 0;
40:
41: #0. 準備
42: my $hDb = DBI->connect('dbi:SQLite:dbname=aclog', '', '',
43:                 {RaiseError=>1, AutoCommit => 0,});
44:
45: #1.集計した結果を出力
46: # (1)曜日毎
47: my $hStS = $hDb->prepare(
48:   q{SELECT WDAY, COUNT(*) FROM ACCESSLOG
49:     GROUP BY WDAY ORDER BY WDAY});
50: $oWkS->write($iRow, 0, jcode('曜日毎')->sjis, $oFmtH);
51: $oWkS->write(++$iRow, 0, jcode('曜日')->sjis, $oFmtTH);
52: $oWkS->write($iRow, 1, jcode('アクセス数')->sjis, $oFmtTH);
53: $hStS->execute();
54: my @aYoubi = qw(日月火水木金土);
55: while(my $raD = $hStS->fetchrow_arrayref()) {
56:       $oWkS->write(++$iRow, 0,
 jcode($aYoubi[$raD->[0]])->sjis,$oFmtTT);
57:     $oWkS->write($iRow, 1, $raD->[1], $oFmtTV);
58: }
59: ++$iRow;
60:
61: #(2)時間毎:木曜日
62: $hStS = $hDb->prepare(
63:   q{SELECT HOUR, COUNT(*) AS CNT FROM ACCESSLOG
64:     WHERE WDAY=4
65:     GROUP BY HOUR ORDER BY CNT DESC});
66: $oWkS->write(++$iRow, 0,
 jcode('時間毎:木曜日(TOP5)')->sjis, $oFmtH);
67: $oWkS->write(++$iRow, 0, jcode('時間')->sjis, $oFmtTH);
68: $oWkS->write($iRow, 1, jcode('アクセス数')->sjis, $oFmtTH);
69: $hStS->execute();
70: my $iCnt = 0;
71: while(my $raD = $hStS->fetchrow_arrayref()) {
72:     $oWkS->write(++$iRow, 0, jcode($raD->[0])->sjis,
 $oFmtTT);
73:     $oWkS->write($iRow, 1, $raD->[1], $oFmtTV);
74:     last if(++$iCnt>=5);
75: }
76: $hStS->finish;
77: ++$iRow;
78:
79: #(3)曜日、時間毎
80: $hStS = $hDb->prepare(
81:   q{SELECT WDAY, HOUR, COUNT(*) AS CNT FROM ACCESSLOG
82:     GROUP BY WDAY, HOUR ORDER BY CNT DESC});
83: $oWkS->write(++$iRow, 0, jcode('曜日,時間毎(TOP5)')->sjis,
 $oFmtH);
84: $oWkS->write(++$iRow, 0, jcode('曜日: 時間')->sjis, $oFmtTH);
85: $oWkS->write($iRow, 1, jcode('アクセス数')->sjis, $oFmtTH);
86: $hStS->execute();
87: $iCnt = 0;
88: while(my $raD = $hStS->fetchrow_arrayref()) {
89:     $oWkS->write(++$iRow, 0,
90:         jcode(sprintf('%-10s : %2d', $aYoubi[$raD->[0]],
 $raD->[1]))->sjis,
91:       $oFmtTT);
92:     $oWkS->write($iRow, 1, $raD->[2], $oFmtTV);
93:     last if(++$iCnt>=5);
94: }
95: $hStS->finish;
96:
97: $hDb->disconnect;
98: $oBook->close();
図1●Sp::Wを用いて集計結果をExcelファイルを作成するスクリプト(List1.pl)

写真1●作成したExcelファイル(List3.xls)をMicrosoft Excelで表示
写真1●作成したExcelファイル(List3.xls)をMicrosoft Excelで表示
シフトJISにすれば,セルだけでなくシート名にも漢字を使えます。
[画像のクリックで拡大表示]

 まず,出力するファイル名を指定してワークブック・オブジェクトを作成しています(図1の9行目)。それにワークシートを追加し,列の幅を指定しています(図1の10・15行目)。なお,フォントの大きさに合うよう,行の高さを明示的に指定することもできます。

 表に使用する文字コードをシフトJISに変換すれば,セルの値やシート名などに漢字を利用できます。ここではJcodeを用いて,シフトJISにコード変換しています。

 さらに,ワークシートの中で利用するセルの書式を作成し,「set_size」や「set_align」などのメソッドを使って書式を設定しています。似ている書式を作成する場合には「copy」によってそれを複製し,異なる個所だけを設定すると簡単です。

 各セルの内容はwriteメソッドによって設定します。引数として,行と列の位置,内容,先ほど設定した書式を指定しています。行,列が“0”から始まることに注意してください。

 Excelファイルの作成が終ったら,closeメソッドで閉じます(図1の98行目)。

この先は会員の登録が必要です。有料会員(月額プラン)は初月無料!

日経 xTECHには有料記事(有料会員向けまたは定期購読者向け)、無料記事(登録会員向け)、フリー記事(誰でも閲覧可能)があります。有料記事でも、登録会員向け配信期間は登録会員への登録が必要な場合があります。有料会員と登録会員に関するFAQはこちら