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

I am using the below code to generate graph and it is working fine.

use strict;
use warnings;
use Win32::OLE;
use Win32::OLE::Const "Microsoft Excel";

my $xls = Win32::OLE->new('Excel.Application');
$xls->Workbooks->Add();
$xls->{Visible} = 1;
$xls->Sheets("Sheet1")->Range('A1')->{Value} = 'Left';
$xls->Sheets("Sheet1")->Range('B1')->{Value} = 'Right';
$xls->Sheets("Sheet1")->Range('C1')->{Value} = 'Center';

for (2 .. 5) {
    $xls->Sheets("Sheet1")->Range("A$_")->{Value} = $_;
    $xls->Sheets("Sheet1")->Range("B$_")->{Value} = $_ * 2;
    $xls->Sheets("Sheet1")->Range("c$_")->{Value} = $_ * 3;    
}

$xls->Charts->Add();
$xls->ActiveChart->SetSourceData({ 
              Source =>$xls->Sheets('Sheet1')->Range("A1:C5"), 
              PlotBy =>xlColumns,
         });
$xls->ActiveChart->Location({ Where => xlLocationAsObject, Name =>'She
+et1'});

[download]

But what i want to do is, i want give the values through some variable, ie. the chart should not be generated from the excel data, rather i want to create by giving the values through variables or datastructure. for example, i want to create for the below data. I tried as shown below. How can i achieve that?

my @a = (1, 2, 3);
my @b = (5, 10, 15);
my $a = \@a;
my $b = \@b;
$xls->ActiveChart->SetSourceData({ 
              Source =>$xls->Sheets('Sheet1')->Range("$a:$b"), 
              PlotBy =>xlColumns,
         });

[download]

Replies are listed 'Best First'.
Re: Generate chart in Excel
by marto (Cardinal) on May 04, 2006 at 12:29 UTC
    Anonymouse Monk,

    "i want give the values through some variable, ie. the chart should not be generated from the excel data, rather i want to create by giving the values through variables or datastructure"

    Perhaps it is just me, but what you are looking to do is populate cells in excel with your data, then create a chart? Otherwise what is the point generating a chart using Excel? I would have thought that doing this would be obvious. If you need an example check out How do I make a chart in Microsoft Excel? from perlwin32faq12 - Using OLE with Perl.

    Martin
[reply]
Re: Generate chart in Excel
by Fletch (Bishop) on May 04, 2006 at 12:26 UTC

    Excel graphs data from the worksheet. I sincerely doubt you're going to be able to point it at transient data in your perl and have it work. A better approach might be to make a new worksheet tab and stick the data there (so it's not visible on your "main" sheet, but it's available to Excel in the file somewhere), then tell Excel to use that data on the other tab to make the chart.

[reply]
Re: Generate chart in Excel
by holli (Abbot) on May 04, 2006 at 12:29 UTC
    You cannot do it that way, imho. What's wrong with writing the data into the sheet and generate the chart from there?


    holli, /regexed monk/
[reply]
[d/l]
Re: Generate chart in Excel
by Ponky (Curate) on May 04, 2006 at 23:46 UTC
    It is actually possible: 
    use strict;
use warnings;
use Win32::OLE;
use Win32::OLE::Const "Microsoft Excel";

my $xls = Win32::OLE->new('Excel.Application');
$xls->Workbooks->Add();
$xls->{Visible} = 1;

my @series_a = (1,2,3,4,5,6);
my $series_string_a = join(',',@series_a);
my @series_b = (6,5,4,3,2,1);
my $series_string_b = join(',',@series_b);

$xls->Charts->Add();
$xls->ActiveChart->SeriesCollection->NewSeries();
$xls->ActiveChart->SeriesCollection(1)->{Values} = "={".$series_string
+_a."}";
$xls->ActiveChart->SeriesCollection->NewSeries();
$xls->ActiveChart->SeriesCollection(2)->{Values} = "={".$series_string
+_b."}";

$xls->ActiveChart->Location({ Where => xlLocationAsObject, Name =>'She
+et1'});

    [download]
[reply]
[d/l]

      Ponky, thats an excellent reply :-). I saw that you are writing a writeup after one year. Thank you very much. I thought that it is not possible after seeing the above replies, but atlast i got an wonderful reply which i didnt expected. Thats Perl Monks:)

[reply]

Back to Seekers of Perl Wisdom