Histogram in the same worksheet using perl

user_17108014 0
Viewed 101

im trying to plot histogram on the same worksheet as the table of data. Below is the perl script im using. The problem i faced here is multiple chart worksheets are created but i want the chart to be in the same worksheet as the data table.

use strict;
use warnings;
use Excel::Writer::XLSX;
use IO::Uncompress::Gunzip qw(gunzip $GunzipError);

{
    my $output_fn = 'result.xlsx';
    my $input_fn = 'accuracy_final.txt.gz';
    my $workbook = Excel::Writer::XLSX->new( $output_fn );
    my $worksheet = $workbook->add_worksheet();
    my $zip = IO::Uncompress::Gunzip->new( $input_fn )
      or die "gunzip failed: $GunzipError\n";
 
    my $col = 0;
    my $row = 1;
    while (!$zip->eof()) {
        my $line = $zip->getline();
        chomp($line);
        next if $line !~ /\S/;  # skip empty lines
        my $value = $line;
        $worksheet->write( $row, $col, $value );
        $row++;
        
        # Add a worksheet chart.
        my $chart = $workbook->add_chart( type => 'line' );
 
        # Configure the chart.
        $chart->add_series(
            values => '=Sheet1!$A$2:$A$5000',
        );    
    }
    $workbook->close();
}

Expected output is as shown below:

enter image description here

Output Im getting:

enter image description here

histogram charts perl excel
1 Answers

The following works for me. You need to use the insert_chart() method to add the chart to the sheet. Also set the embedded option of the chart as described in the documentation:

use feature qw(say);
use strict;
use warnings;
use Excel::Writer::XLSX;
use IO::Uncompress::Gunzip qw(gunzip $GunzipError);

{
    my $output_fn = 'result.xlsx';
    my $input_fn = 'input.txt.gz';
    my $workbook = Excel::Writer::XLSX->new( $output_fn );
    my $worksheet = $workbook->add_worksheet();
    my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
    my $zip = IO::Uncompress::Gunzip->new( $input_fn )
      or die "gunzip failed: $GunzipError\n";

    my $col = 0;
    my $row = 1;
    while (!$zip->eof()) {
        my $line = $zip->getline();
        chomp($line);
        next if $line !~ /\S/;  # skip empty lines
        my $value = $line;
        $worksheet->write( $row, $col, $value );
        $row++;

    }
    # Configure the chart.
    $chart->add_series(
        values => '=Sheet1!$A$2:$A$6',
    );
    $worksheet->insert_chart( 'B7', $chart );
    $workbook->close();
}

enter image description here

user_21737730
Related