Generating an Excel chart

rara has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks: I'm trying to generate a chart in Excel 2007, and am able to replicate the example at <http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-2.37/lib/Spreadsheet/WriteExcel/Chart.pm>

$chart->add_series(
        name       => 'Sample 1',
        categories => '=Sheet1!$A$2:$A$7',
        values     => '=Sheet1!$B$2:$B$7',
    );
[download]

Now, if I'm to use a variable in the cell name, say $A$rowbegin, how do I make sure $rowbegin gets evaluated? I tried the following in vain:

categories => "\'=Sheet1!$A$rowbegin:$A$rowend\'",
[download]

. Thanks!

Comment on Generating an Excel chart Select or Download Code

Replies are listed 'Best First'.
Re: Generating an Excel chart by moritz (Cardinal) on Mar 12, 2012 at 06:46 UTC
Print out the argument to `categories` and see if it matches what you want: `print "\'=Sheet1!$A$rowbegin:$A$rowend\'", "\n";` [download] You probably want to not interpolate $A, and the ' in there are wrong too. So something like `"Sheet1!\$A$rowbegin:\$A$rowend"` And remember to always `use strict; use warnings;` Perl 6 - second systems done right	[reply] [d/l] [select]
Re: Generating an Excel chart by jmcnamara (Monsignor) on Mar 12, 2012 at 09:01 UTC
Spreadsheet::WriteExcel provides a utility function called `xl_range_formula()`to deal with this. See the Working with cell ranges section of the chart documentation: `$chart->add_series( categories => xl_range_formula( 'Sheet1', 1, 9, 0, 0 ), values => xl_range_formula( 'Sheet1', 1, 9, 1, 1 ), );` [download] Note, if you are creating an Excel 2007 workbook and chart then you could consider using Excel::Writer::XLSX. It has a slightly easier interface for this case: `$chart->add_series( categories => '=Sheet1!$A$2:$A$7' ); # Sa +me as ... $chart->add_series( categories => [ 'Sheet1', 1, 6, 0, 0 ] ); # Ze +ro-indexed.` [download] -- John.	[reply] [d/l] [select]