Multiple Charts in Excel

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

Hi, I am trying to put multiple Charts in a single Excel WorkSheet. I have now:

foreach $curr_graph (sort  keys %range_struct) {
    $Range = $Sheet->Range("$range_struct{$curr_graph}");
    $Chart = $Excel->Charts->Add;
    $Chart->{Name} = "$curr_graph";
    $Chart->{ChartType} = xlXYColumn;
    $Chart->Location(xlLocationAsObject, $Sheet->{Name});    
    $Chart = $Excel->ActiveChart;  
    $Chart->SetSourceData({Source => $Range, PlotBy => xlColumns});
    $Chart->{HasTitle} = 1;
    $Chart->ChartTitle->{Text} = "COMPARISON CHART ".$curr_graph;

    }
}
[download]

The problem is that I have total of 20-30 charts to display, and all of them are over each other in the SpreadSheet. How can I position them, so that they will not overlap each other?

Comment on Multiple Charts in Excel Download Code

Replies are listed 'Best First'.
Re: Multiple Charts in Excel by Grygonos (Chaplain) on Jun 23, 2003 at 16:21 UTC
The "ChartObject" object has properties left and top which will move the chart. ChartObject from MSDN	[reply]
Re: Re: Multiple Charts in Excel by vitaliy (Initiate) on Jun 23, 2003 at 16:29 UTC
Could you please be more specific how to use those properties. I tried: `$Chart->{Name} = "$curr_graph"; $Chart->{ChartType} = xlXYColumn; $Chart->{Top}=10; $Chart->{Left}=0;` [download] But it didn't work. THanks, Vitaliy	[reply] [d/l]
Re: Re: Re: Multiple Charts in Excel by Grygonos (Chaplain) on Jun 23, 2003 at 17:54 UTC
The object hierarchy that OLE uses is odd, but it makes sense once you understand it. Code has been tested and does* work.* #!/Perl/bin/perl use Win32::OLE #Make new object my $excel = Win32::OLE->new('Excel.Application') or die "$!"; #Open the file $excel->Workbooks->Open("c:\\test.xls") or die "$!"; #Get the chart collection..the Item method returns the nth #item in the worksheets collection $charts = $excel->Worksheets->Item(1)->ChartObjects(); #find out how many you have $chart_count = $charts->{Count}; #loop and chage their left and top properties #again the item property returns the nth item in the #charts list for(my $i=1; $i =< $chart_count; $i++) { $charts->Item($i)->{Left}=XXXX; $charts->Item($i)->{Top}=XXXX } [download]	[reply] [d/l]