AlignDB-ToXLSX

 view release on metacpan or  search on metacpan

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

# 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;

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

    # 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;

    return;
}

sub increase_row {
    my $self = shift;
    my $step = shift || 1;

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

    my $self = shift;

    #@type Excel::Writer::XLSX::Worksheet
    my $sheet = shift;
    my $opt   = shift;

    #@type Excel::Writer::XLSX::Workbook
    my $workbook   = $self->{workbook};
    my $sheet_name = $sheet->get_name;

    my $font_name = $opt->{font_name} || $self->{font_name};
    my $font_size = $opt->{font_size} || $self->{font_size};
    my $height    = $opt->{height}    || $self->{height};
    my $width     = $opt->{width}     || $self->{width};

    # E2
    my $top  = $opt->{top}  || 1;
    my $left = $opt->{left} || 4;

    # 0 based
    my $first_row     = $opt->{first_row};
    my $last_row      = $opt->{last_row};

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

    # Remove title and legend
    $chart->set_title( none => 1 );
    $chart->set_legend( none => 1 );

    # Blank data is shown as a gap
    $chart->show_blanks_as('gap');

    # set axis
    $chart->set_x_axis(
        name      => $self->_replace_text( $opt->{x_title} ),
        name_font => { name => $font_name, size => $font_size, },
        num_font  => { name => $font_name, size => $font_size, },
        line            => { color   => 'black', },
        major_gridlines => { visible => 0, },
        minor_gridlines => { visible => 0, },
        major_tick_mark => 'inside',
        min             => $x_min_scale,
        max             => $x_max_scale,
        exists $opt->{cross} ? ( crossing => $opt->{cross}, ) : (),
    );
    $chart->set_y_axis(
        name      => $self->_replace_text( $opt->{y_title} ),
        name_font => { name => $font_name, size => $font_size, },
        num_font  => { name => $font_name, size => $font_size, },
        line            => { color   => 'black', },
        major_gridlines => { visible => 0, },
        minor_gridlines => { visible => 0, },
        major_tick_mark => 'inside',
        defined $y_scale
        ? ( min => $y_scale->{min}, max => $y_scale->{max}, major_unit => $y_scale->{unit}, )
        : (),
    );

    # plorarea

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

    my $self = shift;

    #@type Excel::Writer::XLSX::Worksheet
    my $sheet = shift;
    my $opt   = shift;

    #@type Excel::Writer::XLSX::Workbook
    my $workbook   = $self->{workbook};
    my $sheet_name = $sheet->get_name;

    my $font_name = $opt->{font_name} || $self->{font_name};
    my $font_size = $opt->{font_size} || $self->{font_size};
    my $height    = $opt->{height}    || $self->{height};
    my $width     = $opt->{width}     || $self->{width};

    # E2
    my $top  = $opt->{top}  || 1;
    my $left = $opt->{left} || 4;

    # 0 based
    my $first_row = $opt->{first_row};
    my $last_row  = $opt->{last_row};

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

    # Remove title and legend
    $chart->set_title( none => 1 );
    $chart->set_legend( none => 1 );

    # Blank data is shown as a gap
    $chart->show_blanks_as('gap');

    # set axis
    $chart->set_x_axis(
        name      => $self->_replace_text( $opt->{x_title} ),
        name_font => { name => $font_name, size => $font_size, },
        num_font  => { name => $font_name, size => $font_size, },
        line            => { color   => 'black', },
        major_gridlines => { visible => 0, },
        minor_gridlines => { visible => 0, },
        major_tick_mark => 'inside',
        min             => $x_min_scale,
        max             => $x_max_scale,
    );
    $chart->set_y_axis(
        name      => $self->_replace_text( $opt->{y_title} ),
        name_font => { name => $font_name, size => $font_size, },
        num_font  => { name => $font_name, size => $font_size, },
        line            => { color   => 'black', },
        major_gridlines => { visible => 0, },
        minor_gridlines => { visible => 0, },
        major_tick_mark => 'inside',
        defined $y_scale
        ? ( min => $y_scale->{min}, max => $y_scale->{max}, major_unit => $y_scale->{unit}, )
        : (),
    );
    $chart->set_y2_axis(
        name      => $self->_replace_text( $opt->{y2_title} ),
        name_font => { name => $font_name, size => $font_size, },
        num_font  => { name => $font_name, size => $font_size, },
        line            => { color   => 'black', },
        major_gridlines => { visible => 0, },
        minor_gridlines => { visible => 0, },
        major_tick_mark => 'inside',
        defined $y2_scale
        ? ( min => $y2_scale->{min}, max => $y2_scale->{max}, major_unit => $y2_scale->{unit}, )
        : (),
    );

    # plorarea

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

    my $self = shift;

    #@type Excel::Writer::XLSX::Worksheet
    my $sheet = shift;
    my $opt   = shift;

    #@type Excel::Writer::XLSX::Workbook
    my $workbook   = $self->{workbook};
    my $sheet_name = $sheet->get_name;

    my $font_name = $opt->{font_name} || $self->{font_name};
    my $font_size = $opt->{font_size} || $self->{font_size};
    my $height    = $opt->{height}    || $self->{height};
    my $width     = $opt->{width}     || $self->{width};

    # trendline
    my $add_trend = $opt->{add_trend};

    # E2
    my $top  = $opt->{top}  || 1;
    my $left = $opt->{left} || 4;

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

    # Remove title and legend
    $chart->set_title( none => 1 );
    $chart->set_legend( none => 1 );

    # Blank data is shown as a gap
    $chart->show_blanks_as('gap');

    # set axis
    $chart->set_x_axis(
        name      => $self->_replace_text( $opt->{x_title} ),
        name_font => { name => $font_name, size => $font_size, },
        num_font  => { name => $font_name, size => $font_size, },
        line            => { color   => 'black', },
        major_gridlines => { visible => 0, },
        minor_gridlines => { visible => 0, },
        major_tick_mark => 'inside',
        defined $x_scale
        ? ( min => $x_scale->{min}, max => $x_scale->{max}, major_unit => $x_scale->{unit}, )
        : (),
    );
    $chart->set_y_axis(
        name      => $self->_replace_text( $opt->{y_title} ),
        name_font => { name => $font_name, size => $font_size, },
        num_font  => { name => $font_name, size => $font_size, },
        line            => { color   => 'black', },
        major_gridlines => { visible => 0, },
        minor_gridlines => { visible => 0, },
        major_tick_mark => 'inside',
        defined $y_scale
        ? ( min => $y_scale->{min}, max => $y_scale->{max}, major_unit => $y_scale->{unit}, )
        : (),
    );

    # plorarea

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

    my $self = shift;

    #@type Excel::Writer::XLSX::Worksheet
    my $sheet = shift;
    my $opt   = shift;

    #@type Excel::Writer::XLSX::Workbook
    my $workbook   = $self->{workbook};
    my $sheet_name = $sheet->get_name;

    my $font_name = $opt->{font_name} || $self->{font_name};
    my $font_size = $opt->{font_size} || $self->{font_size};
    my $height    = $opt->{height}    || $self->{height};
    my $width     = $opt->{width}     || $self->{width};

    # E2
    my $top  = $opt->{top}  || 1;
    my $left = $opt->{left} || 4;

    # 0 based
    my $first_row     = $opt->{first_row};
    my $last_row      = $opt->{last_row};

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

    # Remove title and legend
    $chart->set_title( none => 1 );
    $chart->set_legend( none => 1 );

    # Blank data is shown as a gap
    $chart->show_blanks_as('gap');

    # set axis
    $chart->set_x_axis(
        name      => $self->_replace_text( $opt->{x_title} ),
        name_font => { name => $font_name, size => $font_size, },
        num_font  => { name => $font_name, size => $font_size, },
        line            => { color   => 'black', },
        major_gridlines => { visible => 0, },
        minor_gridlines => { visible => 0, },
        major_tick_mark => 'inside',
        min             => $x_min_scale,
        max             => $x_max_scale,
        exists $opt->{cross} ? ( crossing => $opt->{cross}, ) : (),
    );
    $chart->set_y_axis(
        name      => $self->_replace_text( $opt->{y_title} ),
        name_font => { name => $font_name, size => $font_size, },
        num_font  => { name => $font_name, size => $font_size, },
        line            => { color   => 'black', },
        major_gridlines => { visible => 0, },
        minor_gridlines => { visible => 0, },
        major_tick_mark => 'inside',
        defined $y_scale
        ? ( min => $y_scale->{min}, max => $y_scale->{max}, major_unit => $y_scale->{unit}, )
        : (),
    );

    # plorarea