package Local::WriteExcel;
#==============================================================================
# creates xls or xlsx file, provides Spreadsheet::WriteExcel::Simple methods
# write_row(), write_bold_row(), data() & save()
# returns data for use in email attachments and/or saves file to disk
# xlsx supports multiple worksheets, but only sequentially (ie complete one and
# move on - does not support $ws1->write_row, $ws2->write_row, $ws1->write_row
# because (global) row counter is reset on each new worksheet - TODO: maybe
# store row counter as hash using worksheet name as key)
# see t/write-excel.t for usage
#==============================================================================
use Spreadsheet::WriteExcel::Simple; # for xls files
use Excel::Writer::XLSX 0.98; # for xlsx files; require 'get_worksheet_by_name'
use Data::Printer;
use IO::Scalar;
use IO::All;
use Moo;
use feature 'say';
# supported file types:
my @exts = qw(xls xlsx);
# filename, optionally passed in (if not then need filetype):
has filename => ( is => 'ro' );
# filetype (xls or xlsx); passed in at object creation or captured in builder
# from filename extension (.xls[x]):
has filetype => ( # use builder to trigger on ->new rather than on usage
is => 'ro',
isa => sub {
my $ft = shift; # say $ft;
die "unsupported filetype $ft" unless grep { $ft eq $_ } @exts;
},
builder => 1,
);
sub _build_filetype { # say "Building filetype";
my $self = shift;
# only get here if filetype *not* passed into object, so we capture filetype
# from filename extension, so need to check filename *is* passed:
$self->filename
|| die "require either filename or filetype in args to object";
$self->filename =~ m!\.(?<ext>[^\.]+)$!;
# return extension (xls or xlsx):
return $+{ext};
}
# for Excel::Writer::XLSX (provides reference to scalar):
has xlsx_data => ( is => 'ro', default => sub { return \my $tmp } );
# xl_object: Spreadsheet::WriteExcel::Simple or Excel::Writer::XLSX object
has xl_object => ( is => 'rwp' ); # set in BUILD block
# set Excel::Writer::XLSX worksheet name:
has worksheet_name => ( is => 'rw', default => 'sheet1', trigger => 1 );
# check required filetype, create new worksheet by name:
sub _trigger_worksheet_name {
my $self = shift;
die "filetype must be xlsx to use worksheet_name"
unless $self->filetype eq 'xlsx'; # p $self->worksheet_name;
{ # create new worksheet if it doesn't already exist:
my $xl = $self->xl_object;
my @worksheets = map $_->get_name(), $xl->sheets(); # existing worksheets
unless ( grep $self->worksheet_name eq $_, @worksheets ) {
# add new worksheet by name:
$xl->add_worksheet($self->worksheet_name);
# reset row_counter:
$self->row_counter(1);
}
} # p $self->worksheet_name;
}
# new Excel::Writer::XLSX worksheet:
has worksheet => ( is => 'lazy' );
sub _build_worksheet {
my $self = shift; # p $self->worksheet_name;
return $self->xl_object->add_worksheet($self->worksheet_name);
}
# for Excel::Writer::XLSX write_row():
has row_counter => ( is => 'rw', default => 1 ); # 1st data row
# only close xlsx file once:
has xlsx_close_event => ( is => 'rw', predicate => 1 );
sub BUILD {
my ($self, $args) = @_; # p $self; p $args;
my $xl_object; # is an Excel::Writer::XLSX or Spreadsheet::WriteExcel::Simple:
if ( $self->filetype eq 'xlsx' ) {
my $scalar_ref = IO::Scalar->new($self->xlsx_data); # warn ref $scalar_ref;
$xl_object = Excel::Writer::XLSX->new($scalar_ref);
}
else {
$xl_object = Spreadsheet::WriteExcel::Simple->new();
}
$self->_set_xl_object($xl_object);
}
#===============================================================================
sub write_bold_row {
my $self = shift; # p $self->worksheet_name;
my $ref = shift; # aref
my $xl = $self->xl_object;
if ( $self->filetype eq 'xls' ) {
$xl->write_bold_row($ref);
}
else {
my $hdr_format = $xl->add_format(bold => 1);
$self->_write_xlsx_row($ref, $hdr_format);
}
}
#-------------------------------------------------------------------------------
sub write_row {
my $self = shift;
my $ref = shift; # aref
if ( $self->filetype eq 'xls' ) {
$self->xl_object->write_row($ref);
}
else {
$self->_write_xlsx_row($ref);
}
}
#-------------------------------------------------------------------------------
# return data in format eg for email attachment:
sub data {
my $self = shift; # p $self;
if ( $self->filetype eq 'xls' ) {
return $self->xl_object->data;
}
else {
$self->_close_xlsx_file; # only if 1st time called
return ${ $self->xlsx_data };
}
}
#-------------------------------------------------------------------------------
sub save {
my ($self, $filename) = @_;
# filename optional, may already have been provided in object creation
# could also be overridden by passing in a new value:
$filename ||= $self->filename or die 'cannot call save() without a '
. 'filename arg unless already provided at object construction';
if ( $self->filetype eq 'xls' ) {
$self->xl_object->save($filename);
}
else {
$self->_close_xlsx_file; # only if 1st time called
io($filename)->print(${ $self->xlsx_data });
}
}
#-------------------------------------------------------------------------------
sub _write_xlsx_row {
my ($self, $ref, $format) = @_; # format optional
my $row = 'A' . $self->row_counter; # p $row; p $ref;
my $sheet = $self->xl_object->get_worksheet_by_name($self->worksheet_name);
$sheet->write_row($row, $ref, $format); # A1, A2, A3, A4, etc
# auto-increment & set new row counter:
$self->row_counter($self->row_counter + 1);
}
#-------------------------------------------------------------------------------
# closes xlsx file (if 1st time called) to allow data return or printing:
sub _close_xlsx_file {
my $self = shift; # p $self->has_xlsx_close_event;
# return if already closed (maybe doesn't matter if closed more than once):
return if $self->has_xlsx_close_event; # warn 'here';
# xl_object->close returns true if it succeeds:
$self->xlsx_close_event( $self->xl_object->close );
}
1;
