Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Generate chart in Excel

by Anonymous Monk
on May 04, 2006 at 12:04 UTC ( #547396=perlquestion: print w/ replies, xml ) Need Help??
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'});

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, });

Comment on Generate chart in Excel
Select or Download Code
Re: Generate chart in Excel
by Fletch (Chancellor) 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.

Re: Generate chart in Excel
by holli (Monsignor) 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/
Re: Generate chart in Excel
by marto (Chancellor) 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
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'});

      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:)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://547396]
Approved by Tanalis
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (5)
As of 2014-09-22 05:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (180 votes), past polls