PHPExcel formula error

Question

I am trying to read a workbook but I am getting following errors:

Fatal error: Uncaught exception 'PHPExcel_Calculation_Exception' with message 'Sheet (abc)!F6 -> ID!F3 -> Formula Error: Unexpected ')'' in C:\webserver\Apache\htdocs\ExcelSheetReader\PHPExcel_1.7.9_new buss req\Classes\PHPExcel\Cell.php:298 Stack trace: #0 C:\webserver\Apache\htdocs\ExcelSheetReader\PHPExcel_1.7.9_new buss req\Classes\PHPExcel\Worksheet.php(2432): PHPExcel_Cell->getCalculatedValue() #1 C:\webserver\Apache\htdocs\ExcelSheetReader\PHPExcel_1.7.9_new buss req\Classes\PHPExcel\Worksheet.php(2508): PHPExcel_Worksheet->rangeToArray('A1:N260', NULL, true, true, true) #2 C:\webserver\Apache\htdocs\ExcelSheetReader\PHPExcel_1.7.9_new buss req\Workbook\read_credits_v3_revised.php(36): PHPExcel_Worksheet->toArray(NULL, true, true, true) #3 {main} thrown in C:\webserver\Apache\htdocs\ExcelSheetReader\PHPExcel_1.7.9_new buss req\Classes\PHPExcel\Cell.php on line 298

Formula in sheet (abc) cell F6 is =ID!F3, and In sheet ID cell F3, formula is =SUM(IDc1.2Y,IDc1.3Y,IDc1.4Y,IDc1.5Y,IDc1.6Y,IDc2.3Y,IDc3.1Y,IDc3.2Y,IDc3.3Y,IDc3.4Y) Please guide me to find the issue in sheet as I dont think there is any issue with the formula.

I also get this: Uncaught exception 'PHPExcel_Calculation_Exception' with message 'LL!F3 -> Formula Error: Unexpected ')'' Formula in sheet LL cell F3 is =MAX(LLc1Y, MIN(10,SUM(LLc2Y,MAX(LLc3.1Y,LLc3.2Y),LLc3.3Y,LLc4Y,LLc5.1Y,LLc5.2Y,LLc5.3Y,LLc6Y))) which also seems good to me but still gets the error.

Kindly help me out in this error if possible. Also is there any way by which I can just read the data from sheets by ignoring any formula issue ?

Are IDc1.2Y, LLc2Y, LLc3.1Y, etc named ranges? Or what? Because I get a #NAME! error if I simply try to paste that formula into MS Excel — Mark Baker
IDc1.2Y,IDc1.3Y,IDc1.4Y-- these cells exists in the sheets and they contain drop down values. IDc1.2Y is in ID sheet, LLc2Y is in LL sheet and so on. — Yogesh Asthana
IDc1.2Y isn't a valid cell reference (based on any MS Excel documentation that I've read) I'd at least have expected a separator between the worksheet name and the cell id like ID!c1.... and if the dropdown is a form element, then PHPExcel can't handle it because it doesn't work with form elements — Mark Baker
I removed all decimal cell refrences but I don't know why I am getting: Uncaught exception 'PHPExcel_Calculation_Exception' with message 'Scoresheet (Test)!L3 -> Formula Error: Unexpected operator '=" . Formula at Scoresheet (Test)!J3 is =IF(IDp11V="Y","Verified","Not Verified"). — Yogesh Asthana
So what is IDp11V? My clairvoyance hasn't worked for many years — Mark Baker

Mark Baker Mark Baker · Accepted Answer · 2014-12-09T12:48:13

You're calling the toArray() method with the following arguments.

PHPExcel_Worksheet->toArray(NULL, true, true, true)

Look at the documentation for the actual arguments that can be passed to the toArray() method that you're using:

* @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
* @param boolean $calculateFormulas Should formulas be calculated?
* @param boolean $formatData  Should formatting be applied to cell values?
* @param boolean $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
*                               True - Return rows and columns indexed by their actual row and column IDs

In particular, look at the second argument (you're passing a true). If you don't want to calculate formulae, then pass a false, but remember that the result you get back for those cells will be the actual formula itself, not the calculated value

PHPExcel formula error

1 Answers