COUNTIF() does not properly parse comparison operators

[billblume]

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?

COUNTIF() should properly parse comparison expressions in the selection criteria parameter.

What is the current behavior?

PhpSpreadsheet is generating the error Formula Error: Unexpected operator '<' when it tries to evaluate the function:

COUNTIF(F12,"<>< Please Select >")

The underlying issue is that method Calculations\Functions::ifCondition() is not properly parsing the conditional operator in the front of the selection parameter. For example, it thinks that the selection parameter above means:

 <>< "PLEASE SELECT >"

when it should mean

<> "< PLEASE SELECT >"

This problem is due to an overly aggressive regular expression that matches any length of '<', '>', and '=' characters, when it should be limited to just '=', '<', '>', '<>', '<=', or '>=', (see section 18.17.2.2 in ECMA 376 5th Edition Part 1 for all allowable comparison operators in Open Office Spreadsheet XML formulas.)

Other statistical functions that use ifCondition() also probably suffer from this issue. The MAXIF(), MINIF(), and AVERAGEIF() functions use ifCondition() as well.

What are the steps to reproduce?

Calling Calculations\Functions::ifCondition() on formula COUNTIF(F12,"<>< Please Select >") will generate an exception with message Formula Error: Unexpected operator '<'

Which versions of PhpSpreadsheet and PHP are affected?

This occurs in 1.2.1 of PhpSpreadsheet. I haven't checked earlier versions. I did not see this in PhpExcel.

[billblume]

FYI. I am working on a PR for this.

[LucasBonafe]

This command line is returning several errors, even though I have already reviewed in Excel:
->setCellValue($F[2][1],"=COUNTIFS($F[3][0];\"<\"&$F[1][1];$F[3][0];\">\"&$F[1][0])")

The result should be:
=COUNTIFS(Y7:Y99;"<"&AL7;Y7:Y99;">"&AJ7)

If someone can help me,
the error is on line 546: xlsx.php

Thanks in advance.

[billblume]

I don’t think your bug is related to my recent bug fix. I fixed a problem in COUNTIF() This fix does not affect COUNTIFS(), which is a different function. Looking at the PhpSpreadsheet documentation, I don’t think they have support for COUNTIFS(). See: https://phpspreadsheet.readthedocs.io/en/develop/references/function-list-by-category/ In general, I have found PhpSpreadheet to have a lot of issues with the calculation of complicated formulae. As a workaround, you can turn off formula recalculation on saves. This can be done by calling $writer->setPreCalculateFormulas(false); before your save. This is what I had to do for my own code. If you use this workaround, I also recommend that you include pull request (#515) in your copy of PhpSpreadsheet. The pull request patches PhpSpreadsheet to force a formula recalculation in Excel when you open the document. Note that I am not a maintainer of PhpSpreadsheet. I am just a user that has provided some bug fixes. I am by no means an expert.

…

-Bill Blume From: Lucas Bonafé <[email protected]> Reply-To: PHPOffice/PhpSpreadsheet <[email protected]> Date: Wednesday, June 6, 2018 at 2:43 PM To: PHPOffice/PhpSpreadsheet <[email protected]> Cc: Bill Blume <[email protected]>, Author <[email protected]> Subject: Re: [PHPOffice/PhpSpreadsheet] COUNTIF() does not properly parse comparison operators (#526) This command line is returning several errors, even though I have already reviewed in Excel: ->setCellValue($F[2][1],"=COUNTIFS($F[3][0];\"<\"&$F[1][1];$F[3][0];\">\"&$F[1][0])") The result should be: =COUNTIFS(Y7:Y99;"<"&AL7;Y7:Y99;">"&AJ7) If someone can help me, the error is on line 546: xlsx.php<https://github.com/LucasBonafe/Sabesp/blob/master/xlsx.php> Thanks in advance. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#526 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AB-PNARiblAnT-pW8i7k872krPyr5wI3ks5t6EzlgaJpZM4UXeVI>.