Why can't I add a chart to my Excel spreadsheet with Perl's Spreadsheet::WriteExcel

Question

When creating a chart in a spreadsheet using Spreadsheet::WriteExcel, the file it creates keeps coming up with an error reading

Excel found unreadable content in "Report.xls"

and asks me if I want to recover it. I have worked it out that the problem line in the code is where I actually insert the chart, with

$chartworksheet->insert_chart(0, 0, $linegraph, 10, 10);

If I comment out this one line, the data is fine (but of course, there's no chart). The rest of the relevant code is as follows (any variables not defined here are defined earlier in the code, like $lastrow).

printf("Creating\n");
my $chartworksheet = $workbook->add_worksheet('Graph');
my $linegraph = $workbook->add_chart(type => 'line', embedded => 1);
$linegraph->add_series(values => '=Data!$D$2:$D$lastrow', name => 'Column1');
$linegraph->add_series(values => '=Data!$E$2:$E$lastrow', name => 'Column2');
$linegraph->add_series(values => '=Data!$G$2:$G$lastrow', name => 'Column3');
$linegraph->add_series(values => '=Data!$H$2:$H$lastrow', name => 'Column4');
$linegraph->set_x_axis(name => 'x-axis');
$linegraph->set_y_axis(name => 'y-axis');
$linegraph->set_title(name => 'title');
$linegraph->set_legend(position => 'bottom');
$chartworksheet->activate();
$chartworksheet->insert_chart(0, 0, $linegraph, 10, 10);
printf("Finished\n");

I am at a total loss here, and I can't find any answers. Help please!

Answer 1

Looking at the expression:

'=Data!$D$2:$D$lastrow'

Is $lastrow some convention in Spreadsheet::WriteExcel or is it a variable from your script to be interpolated into the string expression? If it's your var, then this code probably won't do what you want inside single quotes, and you may want to use something like

'=Data!$D$2:$D' . $lastrow
"=Data!\$D\$2:\$D:$lastrow"
sprintf('=Data!$D2:$D%d',$lastrow)

Answer 2

The problem, as mobrule correctly points out, is that you are using single quotes on the series string and $lastrow doesn't get interpolated.

You can avoid these type of issues entirely when programmatically generating chart series strings by using the xl_range_formula() utility function.

$chart->add_series(
    categories    => xl_range_formula( 'Sheet1', 1, 9, 0, 0 ),
    values        => xl_range_formula( 'Sheet1', 1, 9, 1, 1 ),
);

# Which is the same as:
$chart->add_series(
    categories    => '=Sheet1!$A$2:$A$10',
    values        => '=Sheet1!$B$2:$B$10',
);

See the following section of the WriteExcel docs for more details: Working with Cell Ranges.