in reply to Re: Win32::OLE::Const 'Microsoft Excel'
in thread Win32::OLE::Const 'Microsoft Excel'


maa,
Thanks for your post. Although I'm now able to format the color-context of the Chart, I'm facing a new problem now.

I have a worksheet that has data dynamically entered to it, in the form of tables (2 columns and 'n' rows). 25 - 35 such tables are possible in a single sheet.

A part of my code: 
$column=1;
    foreach my $dept (sort keys %every_dept)
    {    
        $row=1;
        $Sheet->Cells($row++,$column)->{Value} = $dept;
        foreach (@{$every_dept{$dept}})
        {
            $Sheet->Cells($row,$column)->{Value} = $_;
            $Sheet->Cells($row,$column+1)->{Value} = <some-val>;      
+      
            $row++;
            
            #my $range = 'b'.$y;
            #$Sheet->Range($range)->Interior->{ColorIndex} =$y;
            #$Sheet->Range($range)->{Value} = $y;            
        }
        $column+=2;
    }

[download]
How do I draw separate charts for each table? I tried various loops, but I get charts which are totally irrelevant to the data.

Any ideas/solutions?


Thanks,
Rupesh.

Replies are listed 'Best First'.
Re^3: Win32::OLE::Const 'Microsoft Excel'
by maa (Pilgrim) on Dec 06, 2004 at 14:28 UTC

    If you can figure out how to build your charts you can iterate over them using:

    #my $ChartObjects=$Sheet->ChartObjects();
foreach my $c (in $Sheet->ChartObjects() ) {
    #match the $c->Charttitle.Text property against some list?
    #There is a $c->{CodeName} property too... 
    #Store $Chart->{Name} at creation and check against that?
}

    [download]

    To make it easier you could try storing your tables in a format such as: 

    COLA  | COLB | COLC | COLD | COLE | COLF | COLG | ...
Label | Data |      | Label| Data |      |...
item  | item |      | item | item |      |...
item  | item |      | item | item |      |...
item  | item |      | item | item |      |...
item  | item |      | item | item |      |...
      |      |      |      |      |      | <blank row>
Title | txt  |      |      
Name  | $Chart->{Name}
Blah  |      |      |

    Bad diagram... so you have all your tables separated by a column... under your data table is another table with metadata (name, title, xaxis text, y axis text... (cold just be a column)

    You know your chart regions start in A1, C1, E1, etc so you select that cell when building and do a Range("E1")->CurrentRegion->Rows->Countto return the number of rows... +2 takes you to the row where your meta data is... you can then either use RANGE("E9") (or whatever) to assign the properties to the chart making it easy to update (not dynamically though) or save info about the chart.

    I confused myself there - perhaps it'll help you though.

    - Mark
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom