Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Excel Graph *issue*

by rupesh (Hermit)
on Nov 18, 2004 at 17:09 UTC ( [id://408810]=perlquestion: print w/replies, xml ) Need Help??

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


Firstly, I would like to thank cacharbe for his wonderful tutorial, to get me going way long back.
Now, what I need in an excel graph is this:-

I have an excel sheet full of little tables of 2 columns * [2-7] rows each. Most of them are redundant, except that the table headers change. Each table is for one department and the table for one department looks something like this:
Name Hours loy 13 toy 12 mccoy 05 roy 47 loy 24 toy 05 joy 24
What I want is to create a graph (Bar/XYColumn) for each of the tables, and, here comes the tricky part, have a color attached to one bar of the chart based on the values of the 'hour', so that I get, say differrent colors for 'hours' ranging from 0-10, 11-20 and so on...

I want some clues on how to get the color for each column.

Many thanks!
Rupesh.

Edited by Chady -- escaped [...] to prevent linking

Replies are listed 'Best First'.
Re: Excel Graph *issue*
by Grygonos (Chaplain) on Nov 18, 2004 at 20:23 UTC

    Have you attempted to read the OLE doc? The answer is a smidge obfu'd but I would like to think you at least tried. However, have a look at the Color propery of the Interior object. A Series contains an Interior property. You can set it there

Re: Excel Graph *issue*
by teabag (Pilgrim) on Nov 19, 2004 at 13:46 UTC
Update: Excel Graph - Code so far
by rupesh (Hermit) on Nov 23, 2004 at 09:17 UTC

    After quite a few tries (and backfires...), I've come till this, thanks to a number of "supporters".
    I know this code can be made shorter, and multiple lines of code can be singled out, and all that. All my focus is on the Excel Graph, and nothing else matters right now.
    use strict; use Win32::OLE; use Win32::OLE::Const 'Microsoft Excel'; # Start Excel and create new workbook with a single sheet use Win32::OLE qw(in valof with); use Win32::OLE::Const 'Microsoft Excel'; use Win32::OLE::NLS qw(:DEFAULT :LANG :SUBLANG); my (@Names); $Win32::OLE::Warn = 3; push @Names, ['loy', 13]; push @Names, ['toy', 12]; push @Names, ['mccoy', 05]; push @Names, ['roy', 47]; push @Names, ['loy', 24]; push @Names, ['toy', 05]; push @Names, ['joy', 24]; print "Start Excel\n"; my $Excel = Win32::OLE->new('Excel.Application', 'Quit'); $Excel->{Visible} = 1; $Excel->{SheetsInNewWorkbook} = 1; my $Book = $Excel->Workbooks->Add; my $Sheet = $Book->Worksheets(1); $Sheet->{Name} = 'DepOne'; my $Range = $Sheet->Range("A1:B1"); $Range->{Value} = [qw(Name Hours)]; $Range->Font->{Bold} = 1; print "Add data\n"; $Range = $Sheet->Range(sprintf "A2:B%d", 2+$#Names); $Range->{Value} = \@Names; print "Create chart\n"; $Sheet->Range("A:B")->Select; my $Chart = $Book->Charts->Add; $Chart->Chart->ChartWizard({Source =>$Sheet->Cells(15)}); $Chart->{ChartType} = xlColumn; $Chart->Location(xlLocationAsObject, $Sheet->{Name}); # Excel bug: old $Chart has become invalid now! $Chart = $Excel->ActiveChart; # Add title, remove legend with($Chart, HasLegend => 0, HasTitle => 1); $Chart->ChartTitle->Characters->{Text} = "Department One"; # Fat candles with only 5% gaps $Chart->ChartGroups(1)->{GapWidth} = 5; sub RGB { my ($red,$green,$blue) = @_; return $red | ($green<<8) | ($blue<<16); } # White background with a solid border $Chart->PlotArea->Border->{LineStyle} = xlContinuous; $Chart->PlotArea->Border->{Color} = RGB(0,0,0); $Chart->PlotArea->Interior->{Color} = RGB(255,255,255); # Add 1 hour moving average of the Close series my $MovAvg = $Chart->SeriesCollection(4)->Trendlines ->Add({Type => xlMovingAvg, Period => 4}); $MovAvg->Border->{Color} = RGB(255,0,0); $Book->SaveAs('somefile.xls'); $Book->Close;
    Items Pending:

    1. Fill each column bar in a chart with a particular color
    2. Put this chart in the same sheet along with the data in it

    All thoughts are welcome
    Rupesh.

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others chanting in the Monastery: (3)
As of 2024-04-24 06:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found