Why does my Perl code replace the location of my value in a spreadsheet?

Question

I am trying to create a chart in a spreadsheet (using Spreadsheet::Write). I only want to put 1 category and 1 value, but somehow the value, well, my "value" will be overwritten.

I add a series as follows:

my $chart = $workbook->add_chart( type => 'column', embedded => 1 );

$chart->add_series(
        categories  => '='.$worksheet->get_name().'!$A$'.$aecCells[0],
        values      => '='.$worksheet->get_name().'!$B$'.$aecCells[0],
         name       => $aecParams[0],
     );

print "\n--->".$aecCells[0];

 $chart->set_title (name => 'Plots');
 $worksheet->insert_chart('I2', $chart );

Technically, it should take a value from a cell Bx, but it’s not: /, it takes one of Ax... which is actually not a numeric value, but a string, so I get a useless graph.

Any ideas on what I'm doing wrong?

Answer 1

Putting the name of the worksheet inside the quotation marks and using cell ranges should solve your problem:

$chart->add_series(
        categories  => '=\''.$worksheet->get_name().'\'!$A$1:$A$'.$aecCells[0],
        values      => '=\''.$worksheet->get_name().'\'!$B$1:$B$'.$aecCells[0],
         name       => $aecParams[0],
     );