view release on metacpan or  search on metacpan

lib/AlignDB/ToXLSX.pm  view on Meta::CPAN

package AlignDB::ToXLSX;
use Moose;
use Carp;
use YAML::Syck;

use Excel::Writer::XLSX;
use Statistics::Descriptive;
use Chart::Math::Axis;
use List::Util qw();
use List::MoreUtils qw();

our $VERSION = '1.2.1';

# Mysql dbh
has dbh => ( is => 'ro', isa => 'Object' );

# outfiles
has outfile  => ( is => 'ro', isa => 'Str' );        # output file, autogenerable
has workbook => ( is => 'ro', isa => 'Object' );     # excel workbook object
has format   => ( is => 'ro', isa => 'HashRef' );    # excel formats

# worksheet cursor
has row    => ( is => 'rw', isa => 'Num', default => sub {0}, );
has column => ( is => 'rw', isa => 'Num', default => sub {0}, );

# charts
has font_name => ( is => 'rw', isa => 'Str', default => sub {'Arial'}, );
has font_size => ( is => 'rw', isa => 'Num', default => sub {10}, );
has width     => ( is => 'rw', isa => 'Num', default => sub {320}, );
has height    => ( is => 'rw', isa => 'Num', default => sub {320}, );
has max_ticks => ( is => 'rw', isa => 'Int', default => sub {6} );

# Replace texts in titles
has replace => ( is => 'rw', isa => 'HashRef', default => sub { {} } );

sub BUILD {
    my $self = shift;

    # set outfile
    unless ( $self->{outfile} ) {
        $self->{outfile} = "auto.xlsx";
    }

    # Create $workbook object
    my $workbook;
    unless ( $workbook = Excel::Writer::XLSX->new( $self->{outfile} ) ) {
        confess "Cannot create Excel file.\n";
        return;
    }
    $self->{workbook} = $workbook;

    # set $workbook format
    my %font = (
        font => $self->{font_name},
        size => $self->{font_size},
    );
    my %header = (
        align    => 'center',
        bg_color => 42,
        bold     => 1,
        bottom   => 2,
    );
    my $format = {
        HEADER => $workbook->add_format( %header, %font, ),
        HIGHLIGHT => $workbook->add_format( color => 'blue',  %font, ),
        NORMAL    => $workbook->add_format( color => 'black', %font, ),
        NAME      => $workbook->add_format( bold  => 1,       color => 57, %font, ),
        TOTAL     => $workbook->add_format( bold  => 1,       top => 2, %font, ),
        DATE => $workbook->add_format(
            align      => 'left',
            bg_color   => 42,
            bold       => 1,
            num_format => 'yyyy-mm-dd hh:mm',
            %font,
        ),
        URL       => $workbook->add_format( color => 'blue', underline => 1, %font, ),
        URLHEADER => $workbook->add_format( color => 'blue', underline => 1, %header, %font, ),
    };
    $self->{format} = $format;