excel - 使用 perl 在同一工作表中的直方图

Question

我试图在与数据表相同的工作表上绘制直方图。下面是我正在使用的 perl 脚本。我在这里面临的问题是创建了多个图表工作表，但我希望图表与数据表位于同一个工作表中。

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();
}

预期输出如下所示:

输出我得到:

Answer 1

以下对我有用。您需要使用 insert_chart() 方法将图表添加到工作表。同时设置embedded文档中描述的图表选项:

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();
}