Can't use "getCalculatedValue", always a comma error!

Topics: User Forum
Apr 27, 2011 at 2:16 PM

Hi all a a big thumb up for your work, this tool is A M A Z I N G

but i'm not able to get working the getCalculatedValue method, i can only read simple values from cell.

I always get this error:

Fatal error: Uncaught exception 'Exception' with message 'Matrix!U2 -> Formula Error: Unexpected ,' in \Classes\PHPExcel\Cell.php:284 Stack trace: #0 \updatedb.php(86): PHPExcel_Cell->getCalculatedValue() #1 {main} thrown in C:\Inetpub\wwwroot\RO\Classes\PHPExcel\Cell.php on line 284

I'm using version 1.7.6 production

i thought that the problem could be for the vlookup function i used in the cell, but even with simple formulas the error is the same

am i missing something? in documentation i can't find anything useful (obviously I am the problem :))

Thank you so much

bye

Coordinator
Apr 29, 2011 at 11:38 AM
Edited Apr 29, 2011 at 11:40 AM

Without knowing any details, this is almost impossible to answer. While the calculation engine isn't perfect, it can handle most formulae.

When debugging formulae, there's a simple function that I use to display the parser stack, and a detailed log of every step within the evaluation.

$sheet = $objPHPExcel->getActiveSheet();
PHPExcel_Calculation::getInstance()->writeDebugLog = true;

function testFormula($sheet,$cell) {
   $formulaValue = $sheet->getCell($cell)->getValue();
   echo '<b>'.$cell.' Value is </b>'.$formulaValue."<br />\n";
   $expectedValue = $sheet->getCell($cell)->getOldCalculatedValue();
   echo '<b>'.$cell.' Expected Value is </b>'.$expectedValue."<br />\n";

   $calculate = false;
   try {
      $tokens = PHPExcel_Calculation::getInstance()->parseFormula($formulaValue,$sheet->getCell($cell));
      echo '<b>Parser Stack :-</b><pre>';
      print_r($tokens);
      echo '</pre>';
      $calculate = true;
   } catch (Exception $e) {
      echo "PARSER ERROR: ".$e->getMessage()."<br />\n";
      echo '<b>Parser Stack :-</b><pre>';
      print_r($tokens);
      echo '</pre>';
   }
   if ($calculate) {
      try {
         $cellValue = $sheet->getCell($cell)->getCalculatedValue();
         echo '<b>'.$cell.' Calculated Value is </b>'.$cellValue."<br />\n";
         echo '<h3>Evaluation Log:</h3><pre>';
         print_r(PHPExcel_Calculation::getInstance()->debugLog);
         echo '</pre>';
      } catch (Exception $e) {
         echo "CALCULATION ENGINE ERROR: ".$e->getMessage()."<br />\n";
         echo '<h3>Evaluation Log:</h3><pre>';
         print_r(PHPExcel_Calculation::getInstance()->debugLog);
         echo '</pre>';
      }
   }
}

testFormula($sheet,'A5');

to test the formula in cell A5 of $sheet.

 The diagnostics returned from this might help identify what your problem is.

Jul 17, 2012 at 6:10 PM
Edited Jul 17, 2012 at 6:11 PM
or you can try this one too ;)

	function getCellValue($sheet, $cell)
		{
		$getCellType = $sheet->getCellByColumnAndRow($cell[0], $cell[1])->getDataType();

		if($getCellType == PHPExcel_Cell_DataType::TYPE_FORMULA)
			{
			try
				{
				$value = $sheet->getCellByColumnAndRow($cell[0], $cell[1])->getOldCalculatedValue();
				}
			catch(Exception $e)
				{
				$value = '';
				}
			}
		else
			{
			$value = $sheet->getCellByColumnAndRow($cell[0], $cell[1])->getValue();
			}
			
		$value = trim($value); // Strip whitespace (or other characters) from the beginning and end of a string
		$value = addslashes($value); // Quote string with slashes
		$value = iconv("UTF-8", "ISO-8859-1", $value); // Performs a character set conversion on the string from UTF-8 to ISO-8859-1
		$value = preg_replace('/[^(\x20-\x7F)]*/','', $value); // Remove non ASCII characters from a string
		$value = preg_replace('/\s+/', ' ', $value); // Replace all sequences of two or more spaces, tabs, and/or line breaks with a single space
		
		return $value;
		}