view release on metacpan or  search on metacpan

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

    #@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};
    my $x_column      = $opt->{x_column};
    my $y_column      = $opt->{y_column};
    my $y_last_column = $opt->{y_last_column};
    unless ( defined $y_last_column ) {
        $y_last_column = $y_column;
    }

    # Set axes' scale
    my $x_max_scale = $opt->{x_max_scale};
    my $x_min_scale = $opt->{x_min_scale};
    if ( !defined $x_min_scale ) {
        $x_min_scale = 0;
    }
    if ( !defined $x_max_scale and exists $opt->{x_scale_unit} ) {
        my $x_scale_unit = $opt->{x_scale_unit};
        my $x_min_value  = List::Util::min( @{ $opt->{x_data} } );
        my $x_max_value  = List::Util::max( @{ $opt->{x_data} } );
        $x_min_scale = int( $x_min_value / $x_scale_unit ) * $x_scale_unit;
        $x_max_scale = ( int( $x_max_value / $x_scale_unit ) + 1 ) * $x_scale_unit;
    }

    my $y_scale;
    if ( exists $opt->{y_data} ) {
        $y_scale = $self->_find_scale( $opt->{y_data}, $first_row, $last_row );
    }

    #@type Excel::Writer::XLSX::Chart
    my $chart = $workbook->add_chart(
        type     => 'scatter',
        subtype  => 'straight_with_markers',
        embedded => 1
    );

    # [ $sheetname, $row_start, $row_end, $col_start, $col_end ]
    #  #"=$sheetname" . '!$A$2:$A$7',
    for my $y_col ( $y_column .. $y_last_column ) {
        $chart->add_series(
            categories => [ $sheet_name, $first_row, $last_row, $x_column, $x_column ],
            values     => [ $sheet_name, $first_row, $last_row, $y_col,    $y_col ],
        );
    }
    $chart->set_size( width => $width, height => $height );

    # 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
    $chart->set_plotarea( border => { color => 'black', }, );

    $sheet->insert_chart( $top, $left, $chart );

    return;
}

sub draw_2y {
    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};
    my $x_column  = $opt->{x_column};
    my $y_column  = $opt->{y_column};
    my $y2_column = $opt->{y2_column};

    # Set axes' scale
    my $x_max_scale = $opt->{x_max_scale};
    my $x_min_scale = $opt->{x_min_scale};
    if ( !defined $x_min_scale ) {
        $x_min_scale = 0;
    }
    if ( !defined $x_max_scale and exists $opt->{x_scale_unit} ) {
        my $x_scale_unit = $opt->{x_scale_unit};
        my $x_min_value  = List::Util::min( @{ $opt->{x_data} } );
        my $x_max_value  = List::Util::max( @{ $opt->{x_data} } );
        $x_min_scale = int( $x_min_value / $x_scale_unit ) * $x_scale_unit;
        $x_max_scale = ( int( $x_max_value / $x_scale_unit ) + 1 ) * $x_scale_unit;
    }

    my $y_scale;
    if ( exists $opt->{y_data} ) {
        $y_scale = $self->_find_scale( $opt->{y_data}, $first_row, $last_row );
    }

    my $y2_scale;
    if ( exists $opt->{y2_data} ) {
        $y2_scale = $self->_find_scale( $opt->{y2_data}, $first_row, $last_row );
    }

    #@type Excel::Writer::XLSX::Chart
    my $chart = $workbook->add_chart(
        type     => 'scatter',
        subtype  => 'straight_with_markers',
        embedded => 1
    );

    # [ $sheetname, $row_start, $row_end, $col_start, $col_end ]
    #  #"=$sheetname" . '!$A$2:$A$7',
    $chart->add_series(
        categories => [ $sheet_name, $first_row, $last_row, $x_column, $x_column ],
        values     => [ $sheet_name, $first_row, $last_row, $y_column, $y_column ],
    );

    # second Y axis
    $chart->add_series(
        categories => [ $sheet_name, $first_row, $last_row, $x_column,  $x_column ],
        values     => [ $sheet_name, $first_row, $last_row, $y2_column, $y2_column ],
        marker  => { type => 'square', size => 6, fill => { color => 'white', }, },
        y2_axis => 1,
    );
    $chart->set_size( width => $width, height => $height );

    # 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
    $chart->set_plotarea( border => { color => 'black', }, );

    $sheet->insert_chart( $top, $left, $chart );

    return;
}

sub draw_xy {
    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;

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

    my $x_scale;
    if ( exists $opt->{x_data} ) {
        $x_scale = $self->_find_scale( $opt->{x_data}, $first_row, $last_row );

    }
    my $y_scale;
    if ( exists $opt->{y_data} ) {
        $y_scale = $self->_find_scale( $opt->{y_data}, $first_row, $last_row );
    }

    #@type Excel::Writer::XLSX::Chart
    my $chart = $workbook->add_chart( type => 'scatter', embedded => 1 );

    # [ $sheetname, $row_start, $row_end, $col_start, $col_end ]
    #  #"=$sheetname" . '!$A$2:$A$7',
    $chart->add_series(
        categories => [ $sheet_name, $first_row, $last_row, $x_column, $x_column ],
        values     => [ $sheet_name, $first_row, $last_row, $y_column, $y_column ],
        marker => { type => 'diamond' },
        $add_trend
        ? ( trendline => {
                type => 'linear',
                name => 'Linear Trend',
            }
            )
        : (),
    );
    $chart->set_size( width => $width, height => $height );

    # 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
    $chart->set_plotarea( border => { color => 'black', }, );

    $sheet->insert_chart( $top, $left, $chart );

    return;
}

sub draw_dd {
    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};
    my $x_column      = $opt->{x_column};
    my $y_column      = $opt->{y_column};
    my $y_last_column = $opt->{y_last_column};
    unless ( defined $y_last_column ) {
        $y_last_column = $y_column;
    }

    # Set axes' scale
    my $x_max_scale = $opt->{x_max_scale};
    my $x_min_scale = $opt->{x_min_scale};
    if ( !defined $x_min_scale ) {
        $x_min_scale = 0;
    }
    if ( !defined $x_max_scale and exists $opt->{x_scale_unit} ) {
        my $x_scale_unit = $opt->{x_scale_unit};
        my $x_min_value  = List::Util::min( @{ $opt->{x_data} } );
        my $x_max_value  = List::Util::max( @{ $opt->{x_data} } );
        $x_min_scale = int( $x_min_value / $x_scale_unit ) * $x_scale_unit;
        $x_max_scale = ( int( $x_max_value / $x_scale_unit ) + 1 ) * $x_scale_unit;
    }

    my $y_scale;
    if ( exists $opt->{y_data} ) {
        $y_scale = $self->_find_scale( $opt->{y_data} );
    }

    #@type Excel::Writer::XLSX::Chart
    my $chart = $workbook->add_chart(
        type     => 'line',
        embedded => 1
    );

    # [ $sheetname, $row_start, $row_end, $col_start, $col_end ]
    #  #"=$sheetname" . '!$A$2:$A$7',
    for my $y_col ( $y_column .. $y_last_column ) {
        $chart->add_series(
            categories => [ $sheet_name, $first_row, $last_row, $x_column, $x_column ],
            values     => [ $sheet_name, $first_row, $last_row, $y_col,    $y_col ],
        );
    }
    $chart->set_size( width => $width, height => $height );

    # 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
    $chart->set_plotarea( border => { color => 'black', }, );

    $sheet->insert_chart( $top, $left, $chart );

    return;
}

sub _find_scale {
    my $self      = shift;
    my $dataset   = shift;
    my $first_row = shift;
    my $last_row  = shift;

    my $axis = Chart::Math::Axis->new;

    my @data;
    if ( !defined $first_row ) {
        if ( ref $dataset->[0] eq 'ARRAY' ) {
            for ( @{$dataset} ) {
                push @data, @{$_};
            }
        }
        else {
            push @data, @{$dataset};
        }
    }
    else {
        if ( ref $dataset->[0] eq 'ARRAY' ) {
            for ( @{$dataset} ) {