Generate chart in Excel

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]

Comment on Generate chart in Excel Select or Download Code

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]
Re^2: Generate chart in Excel by Anonymous Monk on May 05, 2006 at 14:55 UTC
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]


Problems? Is your data what you think it is?
	PerlMonks