Graph generation using MS Excel

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

Hi Monks,

I am using MS Excel application to draw graph and then exporting the graph to an html page.

Part of my script which is generating Graph is as follows.

my $Range = $Sheet->Range("B1:D$row");
    
     
      my $Chart = $Sheet->ChartObjects;
      my $AgeChartContainer = $Chart->Add(200,30,500,300);
             my $AgeChart = $AgeChartContainer->Chart;
                  
      $AgeChart->{ChartType} = $Constant->{xlLineMarkers};
        $AgeChart->Location($Constant->{xlLocationAsObject}, $Sheet->{
+Name});
       $AgeChart = $Excel->ActiveChart;
      $AgeChart->{ChartType} = xlLineMarkers;
      $AgeChart->SetSourceData({Source => $Range, PlotBy => xlColumns}
+);
      $AgeChart->SeriesCollection(1)->{XValues}= "=Sheet1!R1C1:R"."$ro
+w"."C1";
      $AgeChart->{HasTitle} = 1;
      $AgeChart->{HasLegend} = 1;
      $AgeChart->Legend->{Position} = xlLegendPositionTop;
      $AgeChart->SeriesCollection(1)->{Name} = "max";
    $AgeChart->SeriesCollection(2)->{Name} = "min";
        $AgeChart->SeriesCollection(3)->{Name} = "avg";
        $AgeChart->SeriesCollection(2)->{MarkerStyle} = xlDiamond;
        $AgeChart->SeriesCollection(3)->{MarkerStyle} = xlDiamond;
        $AgeChart->SeriesCollection(3)->{MarkerForegroundColorIndex} =
+ 10;
        $AgeChart->SeriesCollection(3)->{MarkerBackgroundColorIndex} =
+ 10;
        $AgeChart->SeriesCollection(3)->Border->{ColorIndex} = 10;
                                         
      $AgeChart->ChartTitle->{Text} = "Total Memory Usage - 192.168.21
+8.135(MAPLE)      $Date";
      $AgeChart->Axes(xlCategory, xlPrimary)->{HasTitle} = 1;
      $AgeChart->Axes(xlCategory, xlPrimary)->AxisTitle->{Text} = "Dat
+e\nMax = $Max  Min = $Min  Avg = $Avg ";
      $AgeChart->Axes(xlValue, xlPrimary)->{HasMajorGridlines} = 1;
      $AgeChart->Axes(xlCategory, xlPrimary)->{HasMajorGridlines} = 1;
      $AgeChart->Axes(xlValue, xlPrimary)->MajorGridlines->Border->{Co
+lorIndex} = 15;
      $AgeChart->Axes(xlCategory, xlPrimary)->MajorGridlines->Border->
+{ColorIndex} = 15;        
      $AgeChart->Axes(xlValue, xlPrimary)->{HasTitle} = 1;
      $AgeChart->Axes(xlValue, xlPrimary)->AxisTitle->{Text} = "Total 
+Memory Used(%)";
      $AgeChart->PlotArea->Border->{ColorIndex} = 16;
      $AgeChart->PlotArea->Interior->{ColorIndex}   = 2;
                  
      $Book->SaveAs('C:\\Graph.xls');
     $Book->Close({SaveChanges=>0});
[download]

I want to reduce the X-axis value font

I tried

$AgeChart->TickLabels->Font->{Size}=6;
[download]

but didn't work. How do I do it?

Regards

Nalina

Comment on Graph generation using MS Excel Select or Download Code

Replies are listed 'Best First'.
Re: Graph generation using MS Excel by ~~David~~ (Hermit) on Jul 02, 2004 at 15:06 UTC
I could be wrong, but I think it is `$AgeChart->Axes(xlCategory, xlPrimary)->TickLabels->Font->{Size} = 6;` [download]	[reply] [d/l]
Re^2: Graph generation using MS Excel by Nalina (Monk) on Jul 06, 2004 at 05:17 UTC
Thanks for ur reply. I have one more problem. I want the series name to be vissible at the last data point. I tried `AgeChart->SeriesCollection(1)->Points(30)->{ShowSeriesName} = True;` [download] but got an error saying `Win32::OLE(0.1701) error 0x80020003: "Member not found" in PROPERTYPUT "ShowSeriesName" at graph.pl line 88` [download] Thanks & Regards Nalina	[reply] [d/l] [select]
Re: Graph generation using MS Excel by tachyon (Chancellor) on Jul 03, 2004 at 05:49 UTC
If the desired endpoint is a webpage GD::Graph is an easy and fast way to generate png graphs. You can use GD::Graph::Map to map hotpoints and you can get the GD binary as a ppm from ActiveState. Using GD::Grpah is way simpler as well as being a lot faster. cheers tachyon	[reply]
Re^2: Graph generation using MS Excel by Nalina (Monk) on Jul 06, 2004 at 05:31 UTC
Hi, Thanks for the reply. I could not install GD::Graph & GD::Graph::Map on windows machine. Can I install it using ppm. If so please give me the url to do it. Thanks & Regards Nalina	[reply]
Re^3: Graph generation using MS Excel by tachyon (Chancellor) on Jul 06, 2004 at 07:51 UTC
Unlike GD itself (which need compilation) these modules are pure perl so will install without any issues on Win32. See A Guide to Installing Modules, get nmake, get the tarballs from CPAN, perl Makefile.PL && nmake && nmake test && nmake install. There is more to life that what comes as a PPM. This is some of it..... cheers tachyon	[reply]
Re^4: Graph generation using MS Excel by Nalina (Monk) on Jul 07, 2004 at 05:06 UTC
Re^5: Graph generation using MS Excel by tachyon (Chancellor) on Jul 07, 2004 at 07:34 UTC