File Coverage

blib/lib/DBIx/DataModel/Schema/ResultAs/Xlsx.pm

Criterion	Covered	Total	%
statement	55	55	100.0
branch	3	6	50.0
condition			n/a
subroutine	14	14	100.0
pod	1	7	14.2
total	73	82	89.0

#----------------------------------------------------------------------
package DBIx::DataModel::Schema::ResultAs::Xlsx;
#----------------------------------------------------------------------
use warnings;
use strict;
use Carp::Clan           qw[^(DBIx::DataModel::|SQL::Abstract)];
use Excel::Writer::XLSX;
use Params::Validate qw/validate_with SCALAR/;

use parent 'DBIx::DataModel::Schema::ResultAs';

use namespace::clean;

sub param_spec { # class method
  return {
    -worksheet    => {type => SCALAR, default => 'Data'},
    -tech_details => {type => SCALAR, default => 'Technical_details'},
  };
}

sub new {
  my $class = shift;

  # the first positional arg is the output file .. string or a filehandle
  my $file  = shift
    or croak 'select(..., -result_as => [xlsx => $file]): file is missing';

  # other args as a hash of named options
  my %self = validate_with(
    params      => \@_,
    spec        => $class->param_spec,
    allow_extra => 0);

  # assemble and bless
  $self{file} = $file;
  return bless \%self, $class;
}


sub get_result {
  my ($self, $statement) = @_;

  # create the Excel workbook
  my $workbook  = $self->create_workbook;

  # gather data from the statement
  $statement->execute;
  $statement->make_fast;
  my @headers   = $statement->headers;
  my @rows;
  while (my $row = $statement->next) {
    push @rows, [@{$row}{@headers}];
  }

  # create the data worksheet
  my $worksheet = $self->create_data_worksheet($workbook);
  $self->populate_worksheet($worksheet, \@headers, \@rows);

  # optionally insert another sheet with technical details
  $self->add_technical_details($workbook, $statement) if $self->{-tech_details};

  # finalize
  $statement->finish;
  $workbook->close;

  # return filename or filehandle
  return $self->{file};
}


sub create_workbook {
  my ($self) = @_;

  my $workbook  = Excel::Writer::XLSX->new($self->{file})
    or die "open Excel file $self->{file}: $!";

  return $workbook;
}


sub create_data_worksheet {
  my ($self, $workbook) = @_;
  my $worksheet = $workbook->add_worksheet($self->{-worksheet});

  return $worksheet;
}


sub populate_worksheet {
  my ($self, $worksheet, $headers, $rows) = @_;

  # insert data as an Excel table
  $worksheet->add_table(0, 0, scalar(@$rows), scalar(@$headers)-1, {
    data       => $rows,
    columns    => [ map { {header => $_}} @$headers ],
    autofilter => 1,
   });
}


sub add_technical_details {
  my ($self, $workbook, $statement, $n_rows) = @_;

  my $tech_worksheet = $workbook->add_worksheet($self->{-tech_details});
  $tech_worksheet->write_col(0, 0, [
    scalar(localtime),               # time of the extraction
    $statement->schema->dbh->{Name}, # database name
    $statement->row_num . " rows",   # number of rows
    $statement->sql,                 # SQL and bind values
   ]);

  return $tech_worksheet;
}


1;


__END__