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

Hi, Is there any module in perl which will prepare excel chart on dynamic data? We have the code for the fixed rows and columns' data. But if the number of rows and columns are dynamic then how we are going to handle that?

$chart->add_series( categories => '=Sheet1!$B$1:$E$1', values => '=Sheet1!$B$2:$E$2' );

The above code is for fixed rows and columns.I am looking for something like the below code where the row no is a variable, give me pointer if you have one, thanks

$chart->add_series( categories => '=Sheet1!$B$1:$E$1', values => '=Sheet1!$B$variable:$E$variable' );

Replies are listed 'Best First'.
Re: Excel chart for dynamic data
by GrandFather (Saint) on Mar 23, 2012 at 00:24 UTC

    You can do exactly what you want so long as you appropriately quote $ characters. For your example you could:

    use strict;
use warnings;

my $chart;
my $variable;

$chart->add_series(
    categories => '=Sheet1!$B$1:$E$1',
    values     => "=Sheet1!\$B\$$variable:\$E\$$variable"
    );

    [download]
    True laziness is hard work
[reply]
[d/l]
Re: Excel chart for dynamic data
by jmcnamara (Monsignor) on Mar 23, 2012 at 08:53 UTC

    If you are using Spreadsheet::WriteExcel there is 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]

    If you are using Excel::Writer::XLSX you can use the same utility function or pass an arrayref instead: 

        $chart->add_series( categories => '=Sheet1!$A$2:$A$7'      ); # Sa
+me as ...
    $chart->add_series( categories => [ 'Sheet1', 1, 6, 0, 0 ] ); # Ze
+ro-index.

    [download]

    --
    John.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom