Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Excel and Win32::OLE chart problems

by the_hawk_1 (Scribe)
on Oct 29, 2007 at 21:33 UTC ( #647936=perlquestion: print w/ replies, xml ) Need Help??
the_hawk_1 has asked for the wisdom of the Perl Monks concerning the following question:

Hi esteemed monks!

I seek your enlightment on a really obscured trouble. To make it worst, it's regarding a Microsoft product: Excel.

I'm trying to creat a 3d chart with a bunch of data, but unfortunately, I can't do anything else than a regular 2D!

Here's the perl code:

# Create the chart # $chart = $sheet->ChartObjects->Add(1, 175, 457, 300); $chart->ChartType} = xl3DColumnClustered; $chart->Chart->ChartWizard({Source =>$sheet->Range("D5:D12")}); $chart->Chart->SeriesCollection(1)->{Values}=$sheet->Range("D5:D12"); $chart->Chart->SeriesCollection(1)->{XValues}=$sheet->Range("B5:B12"); $chart->Chart->SeriesCollection(1)->{Values}=$sheet->Range("D5:D12"); $chart->Chart->SeriesCollection(1)->{Name}=$sheet->Range("B5:B12"); $chart->Chart->{HasLegend} = $vtfalse; $chart->Chart->{HasDataTable} = $vtfalse; $chart->Chart->SeriesCollection(1)->{HasDataLabels} = 1;
Don't worry, the data are in the cells from D5 to D12, and the name are in B5:B12.

Can someone help me?

Comment on Excel and Win32::OLE chart problems
Download Code
Replies are listed 'Best First'.
Re: Excel and Win32::OLE chart problems
by andyford (Curate) on Oct 29, 2007 at 23:34 UTC

    I'm not a Win32::OLE user, but that loose curly bracket looks suspect:

    $chart->ChartType} = xl3DColumnClustered;
    I tried to throw your code into a file like this:
    use strict; use warnings; use Win32::OLE; my ($vtfalse,$sheet); # Create the chart # my $chart = $sheet->ChartObjects->Add(1, 175, 457, 300); $chart->ChartType = 'xl3DColumnClustered'; $chart->Chart->ChartWizard({Source =>$sheet->Range("D5:D12")}); $chart->Chart->SeriesCollection(1)->{Values}=$sheet->Range("D5:D12"); $chart->Chart->SeriesCollection(1)->{XValues}=$sheet->Range("B5:B12"); $chart->Chart->SeriesCollection(1)->{Values}=$sheet->Range("D5:D12"); $chart->Chart->SeriesCollection(1)->{Name}=$sheet->Range("B5:B12"); $chart->Chart->{HasLegend} = $vtfalse; $chart->Chart->{HasDataTable} = $vtfalse; $chart->Chart->SeriesCollection(1)->{HasDataLabels} = 1;
    but I still get errors.
    Can't call method "ChartObjects" on an undefined value at K:\Desktop\3 +d-chart.pl line 7.
    Guess I'm missing something else. Can you post some minimal complete code that illustrates your problem?

      Thanks for taking time to test it.

      Here is a part of code that might help:

      use Win32::OLE qw(in with); use Win32::OLE::Variant; use Win32::OLE::NLS qw(:LOCALE :DATE); use Win32::OLE::Const 'Microsoft Office .* Object Library'; use Win32::OLE::Const 'Microsoft Excel'; use Win32::OLE::Const "Microsoft Graph 9.0 Object Library"; my $vtfalse = Variant(VT_BOOL, 0); my $vttrue = Variant(VT_BOOL, 1); my $vtpagesw = Variant(VT_I4, 1); my $vtpagest = Variant(VT_I4, 999); eval {$ex = Win32::OLE->GetActiveObject('Excel.Application','Quit' +)}; die "Excel not installed" if $@; unless (defined $ex) { $ex = Win32::OLE->new('Excel.Application', sub {$_[0]- +>Quit;}) or die "Oops, cannot start Excel"; }
      I've correct the missing braket, but it didn't change anything. I've also tried to use the $chart->ChartType = 'xl3DColumnClustered';, but I ended with a Can't modify non-lvalue subroutine call at Z:\ScriptPerl\SAC\Sommaire-VL.pl line 232.

      I'll continue trying to make it work today and I'll update later with my conclusions... if any.

Re: Excel and Win32::OLE chart problems
by bmann (Priest) on Oct 30, 2007 at 19:04 UTC
    The object $chart is an Excel ChartObject, which contains a Chart object, which has a property ChartType. In Excel, that'd be chart.Chart.ChartType In perl,

    $chart->ChartType} = xl3DColumnClustered;
    should be
    $chart->Chart->{ChartType} = ...
      Thanks!

      That fix the "style" of the graph!

      Unfortunately, I still didn't get the XAxis Values.

      Did you still get enough time to help me again?

      Here's my *new* code

      # Create the chart # $chart = $sheet->ChartObjects->Add(1, 175, 457, 300); $chart->Chart->{ChartType} = xl3DColumnClustered; $chart->Chart->ChartWizard({Source =>$sheet->Range("D5:D12")}); $chart->Chart->SeriesCollection(1)->{XValues}=$sheet->Range("D5:D12"); $chart->Chart->SeriesCollection(1)->{HasDataLabels} = 1; $chart->Chart->SeriesCollection(1)->Interior->{ColorIndex} = 36; $chart->Chart->{HasLegend} = $vtfalse; $chart->Chart->{HasDataTable} = $vtfalse; with ($chart->Chart, 'Elevation' => 15, 'Perspective' => 0, 'Rotation' => 20, 'RightAngleAxes' => $vttrue, 'HeightPercent' => 100, 'AutoScaling' => $vttrue); with ($chart->Chart->Axes(xlCategory), 'TickLabelSpacing' => 1, 'TickMarkSpacing' => 1);
      Thanks again!
        If I understand your question correctly, the values in B5-B12 should be the XAxis labels, right?

        If so, use that range as the source data for the chart, ie

        $chart->Chart->ChartWizard({Source => $sheet->Range("B5:B12,D5:D12")};
        instead of just D5:D12. I believe that the two SeriesCollection lines are the defaults, so they shouldn't be necessary. Keep the ColorIndex line though.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (6)
As of 2015-07-29 06:52 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (260 votes), past polls