Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

As an extension to the Excel Graph *issue* and after several attempts (of failures), I'm stuck in a situation where I can go no further.

I need to have multiple graphs/charts in the same sheet as that of the corresponding data, and, particularly, display each bar in every chart with a different color. Colors are always unique in one chart, but could be redundant across many.

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


Cheers,
Rupesh.

In reply to Win32::OLE::Const 'Microsoft Excel' by rupesh

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and all is quiet...

    How do I use this? | Other CB clients
    Other Users?
    Others surveying the Monastery: (3)
    As of 2018-02-18 05:29 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?
      When it is dark outside I am happiest to see ...














      Results (250 votes). Check out past polls.

      Notices?