Skip to content
This repository has been archived by the owner on Jan 2, 2019. It is now read-only.

Case-sensitivity with string equalities #31

Closed
alexgann opened this issue Aug 16, 2012 · 5 comments
Closed

Case-sensitivity with string equalities #31

alexgann opened this issue Aug 16, 2012 · 5 comments
Milestone

Comments

@alexgann
Copy link
Contributor

In Excel "Cool" = "COOL"
In PHPExcel "Cool" <> "COOL"

As such, customer had formula in workbook: =IF(A2="NONE",True,False)
In Excel, when A2 = "None" the formula returns True
In PHPExcel the calculated value for the formula cell returns False

I did a cursory scan to try and find the culprit lines in the codebase but the Calculation engine is pretty dense so I figured I'd just toss this one over the wall. Maybe it is this way on purpose due to behavior of other spreadsheet apps - maybe it needs to be a condition just for COMPATIBILITY_EXCEL?

Easy enough to work around (made the customer match case in the workbook values), but I would think a guiding goal for PHPExcel would be to have consistency with Excel results.

Thanks!

@PowerKiKi
Copy link
Member

I confirm the issue still exists and can be summed up like so:

Formula Excel 2010 PHPExcel
="a" = "a" TRUE TRUE
="a" = "A" TRUE FALSE
="A" = "A" TRUE TRUE
="A" = "a" TRUE FALSE

Interestingly LibreOffice 4.0 seems to implement both modes. When creating a new document, it will be case sensitive (default behavior), but if you save it as .xlsx and re-open it, it will switch to case insensitive (Excel compatible behavior). The documentation does not mention anything though: https://help.libreoffice.org/Calc/Operators_in_Calc

My opinion is PHPExcel is not called PHPLibreOffice for a reason, and as much as it pains me to say it, I would implement Excel behavior as closely as possible.

I will have a look at PHPExcel source code and see if I can submit a PR...

@PowerKiKi
Copy link
Member

I should add that Google Drive spreadsheets, behave the same way as Excel. One more argument in favor of Excel behavior for what it is worth...

PowerKiKi added a commit to PowerKiKi/PHPExcel that referenced this issue Nov 20, 2013
This is consistent with Excel behavior (tested on Excel 2010).

Fixes PHPOffice#31
PowerKiKi added a commit to PowerKiKi/PHPExcel that referenced this issue Nov 21, 2013
Excel, Gnumeric and Google Spreadsheet are case insensitive, so the
default behavior of PHPExcel is modified accordingly.

However OpenOffice is case sensitive and is also supported via the
compatibility mode of PHPExcel.

Fixes PHPOffice#31
PowerKiKi added a commit to PowerKiKi/PHPExcel that referenced this issue Nov 21, 2013
Excel, Gnumeric and Google Spreadsheet are case insensitive, so the
default behavior of PHPExcel is modified accordingly.

However OpenOffice is case sensitive and is also supported via the
compatibility mode of PHPExcel.

Fixes PHPOffice#31
PowerKiKi added a commit to PowerKiKi/PHPExcel that referenced this issue Nov 21, 2013
Excel, Gnumeric and Google Spreadsheet are case insensitive, so the
default behavior of PHPExcel is modified accordingly.

However OpenOffice is case sensitive and is also supported via the
compatibility mode of PHPExcel.

Fixes PHPOffice#31
@PowerKiKi
Copy link
Member

I updated my original Pull Request, and it now properly support compatibility mode setting to use case sensitive for OpenOffice and case insensitive for everything else.

On a side note, I noticed PHPExcel unit tests would need a lot of love to fix them all. If I ever happen to do some of that work, on which branch should it be ? develop or develop_2.0.0 ?

MarkBaker pushed a commit that referenced this issue Nov 23, 2013
@MarkBaker
Copy link
Member

The unit tests certainly need a lot of love and attention, and have been largely neglected for a long time, and I'm ore than happy to see some new work in that area. The develop_2.0.0 branch is dead for the moment; I've refactored a lot of the code to use a an object factory, and moved some of the core components to a repository common for all PHPOffice applications; and am rewriting the calculation engine significantly, and those changes won't merge cleanly with any existing branch. So any additions should be against the develop branch.

@PowerKiKi
Copy link
Member

Thank you for the clarification. I was indeed wondering how you planned to
merge those very heavy changes.

@Progi1984 Progi1984 added this to the 1.8.0 milestone Aug 14, 2014
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants