Graph generation using MS Excel

[Nalina]

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

I want to reduce the X-axis value font

I tried

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

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

Regards

Nalina

[~~David~~]

I could be wrong, but I think it is

$AgeChart->Axes(xlCategory, xlPrimary)->TickLabels->Font->{Size} = 6;

[Nalina]

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;

but got an error saying

Win32::OLE(0.1701) error 0x80020003: "Member not found"
    in PROPERTYPUT "ShowSeriesName" at graph.pl line 88

Thanks & Regards

Nalina

[tachyon]

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.

[Nalina]

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

[tachyon]

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.....