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 =>'Sheet1'});
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,
});
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.
holli, /regexed 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
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 =>'Sheet1'});
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:)