• Files
• Commits
• Log
• Graph
• Stats

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
# limited to single worksheet for xlsx file TODO: support multiple worksheets
#==============================================================================

use Spreadsheet::WriteExcel::Simple; # for xls files
use Excel::Writer::XLSX; # for xlsx files
use Data::Printer;
use IO::Scalar;
use IO::All;
use Moo;

# supported file types:
my @exts = qw(xls xlsx);
# for Excel::Writer::XLSX - provides data for email attachment & file save:
my $xlsx_data;

# filename, optionally passed in:
has filename => ( is => 'ro' );
# file extension (xls or xlsx); passed in or set in BUILDER:
has filetype => ( is => 'rw' );
# xl_object: Spreadsheet::WriteExcel::Simple or Excel::Writer::XLSX object
has xl_object => ( is => 'rw' ); # set in BUILDER

# set Excel::Writer::XLSX worksheet name:
has worksheet_name => ( is => 'rw', default => 'sheet1' );
#------------------------------------------------------------------------
# is this the correct place to validate? isa doesn't have access to $self
around worksheet_name => sub {
    my ($orig, $self) = @_; # p $self;
    # worksheet names only used by Excel::Writer::XLSX:
    die "worksheet names only supported by xlsx file format"
		unless $self->filetype eq 'xlsx';
    return $orig->($self); # expects a return value
};
#------------------------------------------------------------------------

# new Excel::Writer::XLSX worksheet:
has worksheet => ( is => 'lazy' );
sub _build_worksheet { $_[0]->xl_object->add_worksheet($_[0]->worksheet_name) }

# for Excel::Writer::XLSX write_row():
has row_counter => ( is => 'rw', default => 'A1' ); # 1st data row

sub BUILD {
    my ($self, $args) = @_; # p $self; p $args;

    die "require either filename or filetype in args to object"
        if ! grep $args->{$_}, qw(filename filetype);
    my $filename = $args->{filename}; # optional

    my $extension;
    if ( $filename ) {
        $filename =~ m!\.(?<ext>[^\.]+)$!;
        $extension = $+{ext}; # p $extension;
        # add filetype extension (xls or xlsx):
        $self->filetype($extension);
    }
    else {
        $extension = $args->{filetype};
    }
    die "unsupported file type $extension" unless grep $extension eq $_, @exts;

    # xl_object is Excel::Writer::XLSX or Spreadsheet::WriteExcel::Simple:
    my $xl_object = ( $extension eq 'xlsx' )
        ? Excel::Writer::XLSX->new( IO::Scalar->new(\$xlsx_data) )
        : Spreadsheet::WriteExcel::Simple->new();
    $self->xl_object($xl_object);
}

sub write_bold_row {
    my $self = shift;
    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);
    }
}

sub _write_xlsx_row {
    my ($self, $ref, $format) = @_; # format optional
    
    my $row = $self->row_counter; # p $row;
    $self->worksheet->write_row($row, $ref, $format); # A1, A2, A3, A4, etc
    # auto-increment & set new row counter:
    $self->row_counter(++$row);
}

# return data in format for email attachment:
sub data {
    my $self = shift; # p $self;
    if ( $self->filetype eq 'xls' ) {
        return $self->xl_object->data;
    }
    else {
        $self->xl_object->close;
        return $xlsx_data;
    }
}

sub save {
    my ($self, $filename) = @_; # filename optional, can already be passed in object creation
    
    $filename ||= $self->filename or die 'cannot call save() without a '
        . 'filename arg unless already provided in object construction';

    if ( $self->filetype eq 'xls' ) {
        $self->xl_object->save($self->filename);
    }
    else {
        $self->xl_object->close; # is OK to call this twice (ie if data() visited)
        io($self->filename)->print($xlsx_data);
    }
}

1;