Conditional Styles methods only work for single-cell styles

[MarkBaker]

This is:

- [X] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

If a cell has conditional styling, then Worksheet methods like conditionalStylesExists() and getConditionalStyles() - which require a cell reference argument - should return true, and the list of conditions/styles respectively.

What is the current behavior?

If the conditional is applied to just a single cell, then the correct behaviour applies; but if the conditional applies to a range of cells, then false and an empty array will be returned, even when the specified cell falls within the conditional range.

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';

$inputFileType = 'Xlsx';
$inputFileName = __DIR__ . '/Conditional.xlsx';

$callStartTime = microtime(true);

$reader = IOFactory::createReader($inputFileType);
$spreadsheet = $reader->load($inputFileName);

$callEndTime = microtime(true);
$loadCallTime = $callEndTime - $callStartTime;

$conditionalsForA1 = $spreadsheet->getActiveSheet()->conditionalStylesExists('A1');
$conditionalsForF1 = $spreadsheet->getActiveSheet()->conditionalStylesExists('F1');
var_dump($conditionalsForA1, $conditionalsForF1);

Should return true and true for cells A1 and F1; but returns false and true

Which versions of PhpSpreadsheet and PHP are affected?

All versions

Sample file has a single cell conditional for cell F1, and a cell range conditional for cells A1:D3
Conditional.xlsx

[oleibman]

@MarkBaker Welcome back!

[MarkBaker]

@oleibman Dank je wel! It's been a difficult tail end of the year for me, and things still aren't going well; but I hope that I'll be able to do more work here again than I have been doing over the last few months.... although half the things that I want to do entail major bc breaks, or making the testing/release process incredibly complicated... but it's good to see just how much you've achieved while I've been absent

[oleibman]

I am sorry things are not going well for you. I hope the new year will be a better one.

[marcomoscardini]

Good morning Mark,
I seam to have a problem related to this issue:

$cellRange = 'E3:'.$colIdx.'1024' ;
$conditionalStyles = [];
$wizardFactory = new Wizard($cellRange);
/** @var Wizard\Expression $expressionWizard */
$expressionWizard = $wizardFactory->newRule(Wizard::EXPRESSION);
$expressionWizard->expression('(0=COUNTIF({"saas";"licence"},$E3))')->setStyle($redStyle);
$conditionalStyles[] = $expressionWizard->getConditional();
$sheet->getStyle($cellRange)->setConditionalStyles($conditionalStyles);

produces a file where the formule is altered. I get a
(0=COUNTIF({"saas";"licence"}, l5))
after adding dumps all around I see the prop $expressionWizard->expression is what I set it to, but when I check the return of:
$expressionWizard->getConditional()
I find the same formule showed in excel/libreoffice/gnumeric. I have tried with $E3 et E3 in the expression with no success.

[MarkBaker]

I assume that the expression is supposed to style the cell if the cell value is neither saas or license.

Looking at your expression, should (0=COUNTIF({"saas";"licence"},$E3)) really be referencing E3 when the conditional range is E3:'.$colIdx.'1024? And where we document that A1 should be used to reference the top-left cell of the range within the formula?

I'll need to experiment further (I'm still trying to get that expression working in MS Excel itself) but a couple of other possibilities spring to mind:

• There is a known issue with the use of braces around an expression in the calculation engine, and you're wrapping the whole expression in braces.
• You're using an array constant for the two values that you're checking against, I'm currently assessing array handling within the calculation engine to identify what does and doesn't work correctly.

However, I'm struggling to get MS Excel to accept the COUNTIF() formula, with or without an array constant

While perhaps not as efficient, you could try rewriting your formula as =NOT(OR(A1="saas",A1="license"))

[marcomoscardini]

Thank you Mark for your insight, it was very very useful.I will try tomorrow without brackets (i thing not problematic), but i think your proposition (alternative to countif) will do it.
I will keep you posted and thank you for your help.

[marcomoscardini]

Good morning Mark,
As discussed, I tried without the brackets and the result in Excel has not changed. The referenced cell changes to some 'odd' cell (E5 when the range is E3:E1024):
0=COUNTIF({"saas";"licence"},$E3) is translated to : 0=COUNTIF({"saas";"licence"};$E5)
I tried to inverse the 0 and stick it to the end rather at the start of the formule: COUNTIF({"saas";"licence"},$E3)=0 returns COUNTIF({"saas";"licence"};$E5)=0

My hopes were not too high on this solution anyway, I will carry on with the Boolean operators and keep my fingers crossed ;-)

[MarkBaker]

The reference cell will change when you call getConditional():
On the basis that A1 in the wizard formula (0=COUNTIF({"saas";"licence"},$E3)) will be adjusted to match the range unless column and/or row is pinned. It's not odd if you consider that row 1 in the Wizard formula will be adjusted to match the first row in the conditional range, so it's adding two rows which for $E1 would give $E3; but for $E3 adding 2 rows will give $E5.

I've been unable to use your formula in MS Excel, even as a general formula in a cell. While the documentation might suggest otherwise, MS Excel doesn't seem to accept an array constant as the first argument for COUNTIF(); only as the second argument, which then returns an array rather than a single value. In theory, I should then be able to do =SUM(COUNTIF($A1, {"saas","license"}))=0; but I actually get a meaningful error message when I try to use that formula:

So it would seem that Excel has some interesting restrictions on the formula expressions that can be used for Conditional Formatting... note that this includes array constants like {"saas";"licence"}.

[marcomoscardini]

Thanks you Mark for your support, I confirm your second proposition ( NOT(OR(A1="saas",A1="license")) ) works like a charm, as you said not ideal, but not too bad either ;-)

thanks again. I will carry on diving into REGEX/MATCH to validate date and standard inputs.

[marcomoscardini]

Good afternoon Mark,

as anticipated I have attacked REGEX and I think I have an other issue... that might me related... I create it ant you will relate it if you think useful.