Yes, you are right. You will have to evaluate each condition successively against the value of the cell, although $cell->{Value} will normally be enough. There will be complications if you are using dates and I dread to think what might happen if you are using array formulae. One bit of good news, however, is that Excel evaluates conditions serially, so that if the first matches, the others are ignored.

Regards,

John Davies

Massive update!

Depending on how your spreadsheets are created, there is a trick using names that allows you to continue to use conditional formulae but enables you to evaluate the conditions very easily. It's got other massive advantages, too. Here is some code to demonstrate it:

use strict;
use warnings;
use diagnostics;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 1;
my $wb = $xl->Workbooks->Add();
for (2..$wb->Sheets->{Count}) {
    $wb->Sheets(2)->Delete;
}

$wb->Names->Add({Name=>"Fmt", RefersToR1C1=>"=RC>0"});         #Commen
+t 1
my $A1 = $wb->Sheets(1)->Cells(1, 1);
my $A2 = $wb->Sheets(1)->Cells(2, 1);
$A1->FormatConditions->Add({Type=>2, Formula1=>"=Fmt"});    #Comment 2
$A1->FormatConditions(1)->Interior->{Color} = 255;
$A1->Copy($A2);
PrintStatus();
$A1->{Value} = 1;
PrintStatus();
$A2->{Value} = 1;
PrintStatus();
$A1->{Value} = -1;
PrintStatus();

sub PrintStatus {
    my $seln = $xl->Selection;
    $A1->Select;                                            #Comment 3
    print $xl->Evaluate("Fmt") . " ";
    $A2->Select;
    print $xl->Evaluate("Fmt") . "\n";
    $seln->Select;
}

[download]

There is a common misconception in dealing with Excel. People refer to "named ranges". This was correct in 123, but Excel uses named formulae. Any time you create a name, the "RefersTo" property will start with an equals sign. They can also be as complex as any other formula.

Comment 1

This is where the name is created. A named formula called "Fmt" is created. It isn't necessary to use R1C1 notation, but it's clearer from this than from A1 notation that we are using an offset of no rows and no columns from some reference point (more on this later). The formula asks if the target cell is greater than zero, so it will return a boolean to Excel or VBA and 1 or 0 to Perl.

Comment 2

There are two main types of condition in a conditional format, namely "Cell value" and "Formula". If "Cell value" gets used, you have to write a parser. If "Formula" is used, you don't if you can replicate the formula. The formula we are entering, "=Fmt", could hardly be simpler (therefore, hardly easier to replicate). When we enter it as the condition for cell A1, that cell is used as the reference point for the offset, so it becomes the target cell itself. It is here that the difference between A1 and R1C1 format is useful. A user entering a name would not be likely to distinguish between a reference to A1 and one to $A$1. However, the difference is huge. The latter, which is Excel's default, will always point to the same cell. This is not what we want. When we copy, two lines later, we want the reference point and the target cell to change, too. Provided you are aware of this point, it doesn't matter which you use.

Comment 3

In Excel’s Select and Activate considered harmful, I rail against the use of Select. Your problem has led me to a very rare situation where it is necessary. To evaluate "Fmt", we have to have a reference point to get to the target cell. That is easy in the conditional format, as Excel takes the cell to be formatted as the reference point. But VBA and Perl need a different approach, which unfortunately is to use Select.

Other advantages

You don't repeat yourself as much. If you want to change the condition for formatting cells, you have to change it once only, in the named formula. You don't have to remember which cells have conditional formats and copy to them (I always get this wrong; don't you?). If you want to add the condition to subsequent cells that already have different formulae meaning that you can't copy, you have far less typing and far less risk of error.

In reply to Re^3: Conditional Formating with OLE Excel by davies
in thread Conditional Formating with OLE Excel by fredo2906

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, details, 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, summary, 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.