Configuring a Conditional for string testing

[cwolcott]

What is the appropriate why to configure conditional formatting to test a cell for string comparison. I tried the following:

        $fpCondHits = new Conditional();
        $fpCondHits->setConditionType(Conditional::CONDITION_CELLIS);
        $fpCondHits->setOperatorType(Conditional::OPERATOR_BEGINSWITH);
        $fpCondHits->addCondition("Y");
        $fpCondHits->getStyle()->applyFromArray(Reports::$conditionalFormatGreen);
        $fpConditionalStyles[] = $fpCondHits;

        // Apply the conditional formatting
        $resultWS->getStyle('M3:M'.$rMaxRow)->setConditionalStyles($fpConditionalStyles);

Getting an error when opening the spredsheet.

I have performed a lot of conditional formatting for integers without a problem.

[oleibman]

Sample ConditionalFormatting/02_Text_Comparisons does it as follows:

use PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\Wizard;

// Set rules for Literal Value Begins With
$cellRange = 'A2:B4';
$conditionalStyles = [];
$wizardFactory = new Wizard($cellRange);
/** @var Wizard\TextValue $textWizard */
$textWizard = $wizardFactory->newRule(Wizard::TEXT_VALUE);

$textWizard->beginsWith('H')
    ->setStyle($yellowStyle);
$conditionalStyles[] = $textWizard->getConditional();

$spreadsheet->getActiveSheet()
    ->getStyle($textWizard->getCellRange())
    ->setConditionalStyles($conditionalStyles);

[oleibman]

You can also get the result you want without the wizard by changing 2 statements in your code:

        //$fpCondHits->setConditionType(Conditional::CONDITION_CELLIS);
        $fpCondHits->setConditionType(Conditional::CONDITION_BEGINSWITH);
        ...
        //$fpCondHits->addCondition("Y");
        $fpCondHits->setText("Y");

[cwolcott]

That is exactly what I was looking for. I forgot about the setText instead of addCondition.

[cwolcott]

I will try to writeup a test case, but have you seen text conditional formatting not evaluating?

I have conifugred the condition and when I open the spreadsheet it has not applied, but if I look at the range the conditional formatting is defined and then edit the rule and just say OK, the rule is applied just fine.

All of the number conditional formatting is applied just fine.

Using v2.0.0

[cwolcott]

A couple of fun images

1. Opening the spreadsheet for the first time

2. Managing the rules (You can see the first rule is suppose to look for '-', apply the style and then stop processing other rules

3. The text rule

4. All I do is say OK to this rule and then OK to the managed rules and the spreadsheet looks like I was hoping. Cells with '-' have no format.

[cwolcott]

I changed my code from using '-' to 'ns' just case the '-' was causing an issue. Not affect the rule is still not being processed when the spreadsheet initially opens.

[oleibman]

Unable to duplicate your problem. You will need to upload code and/or spreadsheet. The following code works for me exactly as desired - the first condition (contains minus sign) matches, applies no formatting, and stops if true. The second condition again tests for contains minus sign, so is useless except as a test, which is exactly what we're doing; this demonstrates that 'stop if true' was effective (otherwise you'd see a red background). The third condition (yellow fill for items under 50) and the fourth (green fill for items over 150) work as desired.

        $spreadsheet = new Spreadsheet();
        $resultWS = $spreadsheet->getActiveSheet();
        $resultWS->fromArray([
            [102.54, 103.94, '-', 12],
            [115, 150, 47, '-'],
            [83, 162, 31, 29],
        ]);
        $fpConditionalStyles = [];

        $fpCondHits = new Conditional();
        $fpCondHits->setConditionType(Conditional::CONDITION_CONTAINSTEXT);
        $fpCondHits->setOperatorType(Conditional::OPERATOR_CONTAINSTEXT);
        $fpCondHits->setText('-');
        $fpCondHits->setStopIfTrue(true);
        $fpConditionalStyles[] = $fpCondHits;

        $fpCondHits = new Conditional();
        $fpCondHits->setConditionType(Conditional::CONDITION_CONTAINSTEXT);
        $fpCondHits->setOperatorType(Conditional::OPERATOR_CONTAINSTEXT);
        $fpCondHits->setText('-');
        $fpCondHits->getStyle()->getFill()->setFillType(Fill::FILL_SOLID);
        $fpCondHits->getStyle()->getFill()->getEndColor()->setARGB(Color::COLOR_RED);
        $fpConditionalStyles[] = $fpCondHits;

        $fpCondHits = new Conditional();
        $fpCondHits->setConditionType(Conditional::CONDITION_CELLIS);
        $fpCondHits->setOperatorType(Conditional::OPERATOR_LESSTHAN);
        $fpCondHits->addCondition(50);
        $fpCondHits->getStyle()->getFill()->setFillType(Fill::FILL_SOLID);
        $fpCondHits->getStyle()->getFill()->getEndColor()->setARGB(Color::COLOR_YELLOW);
        $fpConditionalStyles[] = $fpCondHits;

        $fpCondHits = new Conditional();
        $fpCondHits->setConditionType(Conditional::CONDITION_CELLIS);
        $fpCondHits->setOperatorType(Conditional::OPERATOR_GREATERTHAN);
        $fpCondHits->addCondition(150);
        $fpCondHits->getStyle()->getFill()->setFillType(Fill::FILL_SOLID);
        $fpCondHits->getStyle()->getFill()->getEndColor()->setARGB(Color::COLOR_GREEN);
        $fpConditionalStyles[] = $fpCondHits;

        // Apply the conditional formatting
        $resultWS->getStyle('A1:D3')->setConditionalStyles($fpConditionalStyles);
        $outfile = 'issue.3880a.generated.xlsx';
        $writer = new XlsxWriter($spreadsheet);
        $writer->save($outfile);

[cwolcott]

Ok, I slapped the code into a function and just called it from my code and it looks perfect. So I now need to start commenting out parts of my report to see what is causing the issue. I will report back what I find.

[cwolcott]

Found the issue.
I was missing the :
$fpCondHits->setOperatorType(Conditional::OPERATOR_CONTAINSTEXT);
Thank you for your help. The scientist love these spreadsheets generated from the website based on their experiment results.

[cwolcott]

I have been a fan of this library for many years. Thank you for the time you spend developing.

[oleibman]

Glad to be able to help. And always grateful for a kind word - thanks.