Whiteのふりーとーく

{Perl}ExcelPrinter.pm

なぜか奥様の仕事の手伝いなどしていて、その過程でレポートをExcel形式で出さねばならない→しかしSpreadsheet::WriteExcelはいまいち俺の好みではない、ということでSpreadsheet::WriteExcelのラッパーなど作ってみた。

ExcelPrinter.pm:

package ExcelPrinter;
use strict;
use Spreadsheet::WriteExcel;
sub new{
    my $class = shift;
    my $filename = shift;
    die 'no filename' unless($filename);
    my $workbook = Spreadsheet::WriteExcel->new($filename);
    my $self = {
	'filename' => $filename,
	'workbook' => $workbook,
	'worksheets' => {},
	'row' => 0,
	'col' => 0,
	'max_col' => 0,
    };
    return bless $self,$class;
}
sub close{
    (shift)->workbook->close();
}
sub workbook{
    return (shift)->{workbook};
}
sub worksheet{
    return (shift)->{worksheet};
}
sub autofilter{
    my $self = shift;
    $self->worksheet->autofilter(0,0,$self->{row} - 1,$self->{max_col});
}
sub opensheet{
    my $self = shift;
    my $sheetname = shift;
    $self->{'worksheet'} = $self->{worksheets}->{$sheetname};
    unless($self->{'worksheet'}){
	$self->{'worksheet'} = $self->workbook->add_worksheet($sheetname);
	$self->{worksheets}->{$sheetname} = $self->{'worksheet'};
	$self->{row} = 0;
	$self->{col} = $self->{max_col} = 0;
    }
}
sub print{
    my $self = shift;
    my $col = $self->{col};
    foreach(@_){
	$self->worksheet->write($self->{row}, $col, $_);
	$col++;
    }
    $self->{max_col} = $col - 1  if ($self->{max_col} < $col);
    # finally, break to new-line.
    $self->{row} += 1;
    $self->{col}  = 0;
}
1;

使い方。

#!/usr/bin/perl
use strict;
use utf8;
use ExcelPrinter;
my $eprint = new ExcelPrinter('sample.xls');
$eprint->opensheet('シート１');
$eprint->print("foo",'bar',112);
$eprint->print("xxx",'a',113);
$eprint->opensheet('シート２');
$eprint->worksheet->freeze_panes(1,1)
$eprint->worksheet->set_column(0,0,6);
$eprint->worksheet->set_column(1,2,10);
$eprint->print('id','how','do');
$eprint->print('1','simple','print');
$eprint->print('2','pretty','work');
$eprint->autofilter();
$eprint->close();

という感じで必要なところでは適当に都合よくSpreadsheet::WriteExcelの機能を直接呼び出す。Spreadsheet::WriteExcel::Simpleではシンプルすぎるが、Spreadsheet::WriteExcelを全部直接使うのは面倒という感じの落としどころ。