• Files
• Commits
• Log
• Graph
• Stats

package Local::WriteExcel;

#==============================================================================
# extends Spreadsheet::WriteExcel::Simple methods to Excel::Writer::XLSX
# creates xls or xlsx file, provides write_row(), write_bold_row() & save()
# limited to single worksheet for Excel::Writer::XLSX, auto-saves file
# TODO: support multiple worksheets, return in-memory data for email attachment
#==============================================================================

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

# supported file types:
my @exts = qw(xls xlsx);

has filename => ( is  => 'ro', required => 1 );

# xl_object: Spreadsheet::WriteExcel::Simple or Excel::Writer::XLSX object
# filetype: filename extension (xls or xlsx)
has $_ => ( is => 'rw' ) for qw(xl_object filetype); # set in BUILDER

# Excel::Writer::XLSX worksheet:
has worksheet => ( is => 'lazy' );
sub _build_worksheet { shift->xl_object->add_worksheet }

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

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

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

sub write_row {
    my $self = shift;
    my $ref  = shift; # aref
    
    if ( $self->filetype eq 'xls' ) {
        $self->xl_object->write_row($ref);
    }
    else {
        my $row = $self->row_counter; # p $row;
        $self->worksheet->write_row('A' . $row++, $ref); # A2, A3, A4, etc
        # increment row counter:
        $self->row_counter($row);
    }
} 

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->worksheet->write('A1', $ref, $hdr_format); # row 0 - headers
    }
}

# return data in format for email attachment:
sub data {
    my $self = shift;
    return $self->filetype eq 'xls'
        ? $self->xl_object->data
        : undef;
}

sub save {
    my $self = shift;

    $self->filetype eq 'xls'
        ? $self->xl_object->save($self->filename)
        : $self->xl_object->close; # auto saves to $self->filename
}

1;