Just some information on your reference to the "real" format of the cell.

Excel maintains two formats for a cell that has conditional formatting. The first is the format of the cell as if no conditional formatting were applied. The second is the format to be overlaid on the first format if the condition is met. I'd suspect that your code is returning the first format. Is that the case? I don't know how the second format is accessed in VBA.

--
John.

When Excel uses the word "Format", it can have at least two meanings. The commonest meaning is more strictly defined as "NumberFormat", i.e. whether a number appears as "1" or "1.00". You seem to be looking for the other meaning, but you may need to be aware of the difference, as the NumberFormat cannot be set by conditional formatting.

Second, there is a lot of history to conditional formatting that your code needs to respect. Conditional formatting came in with Excel 97 (I think), so in the unlikely event that you have such old versions lying around your user base, they will throw errors. 2007 increased the number of conditions permitted from 3 to (I think) unlimited, so if you try to process a file written in 2007/2010 with 2003, conditions will be dropped and the appearance may not be what you expect. However, getting around this limit in 1997-2003 is a FAQ, with this showing the answer.

Third, conditional formatting behaves strangely. See John Walkenbach's issues, especially his comment that "There is no direct way for your VBA code to determine if a particular cell's conditional formatting has been "triggered."". This means that there is no way for Perl to do it, unless you want to write a parser.

If you do want to write a parser, you're a braver man than I am. Bear in mind that you won't know the number of conditions, so you should start looking at $rng->FormatConditions->Count. You will then have to parse each condition, bearing in mind that the operators will not be returned as operators but as integers. You will have to research all versions of Excel you are going to meet to be sure that you can handle all possible options. I would guess that, in writing your own Perl parser, you would obviate the need for the R1C1 conversion, but that is something that you would have to test.

It's possible that your case will be sufficiently restricted that you can write a parser. The code you show looks only at the pattern, so perhaps you aren't interested in anything else, but even so, parsing the conditions is going to be a pig. You might consider going back to the OzGrid reference I gave earlier and replacing conditional formatting with some VBA that changes the primary format rather than using conditional formatting.

Your code starts by taking control of an assumed existing instance of Excel. See Re: Manipulating open excel sheet for some reasons not to do this.

Good luck. I think you're going to need it.

Regards,

John Davies

Thank you for your replies, I really appreciate. From what you wrote, I understand that it is not possible to access that overlay from perl, neither VBA. When you say "writing a parser", does it mean I will have to parse each condition and decide the format in my perl script. Am I right? Fred.

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.