2

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 ?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Yogesh Asthana
  • 99
  • 2
  • 9
  • 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 Dec 09 '14 at 12:56
  • 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 Dec 09 '14 at 13:08
  • `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 Dec 09 '14 at 13:26
  • 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 Dec 10 '14 at 16:54
  • So what is `IDp11V`? My clairvoyance hasn't worked for many years – Mark Baker Dec 10 '14 at 17:04
  • IDp11V is a cell in ID sheet which is basically a dropdown having two values(Y and N), and initially it is blank. I hope it helps. I had similar sheet having dropdowns which worked well. – Yogesh Asthana Dec 10 '14 at 17:11
  • But `IDp11V` doesn't match a standard MS Excel cell reference..... how dows this cell naming system work? It isn't standard A1 format, or even the older R1C1 format, and I don't recall ever seeing anything like it anywhere in the MS documentation on Excel.... so there is no way that PHPExcel can understand what it means if I don't understand what it means.... can you point me to any Microsoft documentation that explains it – Mark Baker Dec 10 '14 at 17:40

1 Answers1

2

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

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • yes, but I was not able to read the sheet due to errors in it. Also I tried to use $objPHPExcel->getActiveSheet()->getCell("F34")->getOldCalculatedValue(), by this errors were ignored but I think I won't be able to dump the whole sheet into array easily as i have to loop through the whole sheet. Need Guidance – Yogesh Asthana Dec 09 '14 at 13:11