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('ＭＳ Ｐ明朝')->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();
|
