Formula problem

Topics: Developer Forum, User Forum
Mar 3, 2011 at 10:40 AM

Hi!

I have a problem with this formula:

 

$locale = "hu";
$validLocale = PHPExcel_Settings::setLocale($locale);
if (!$validLocale) {
  echo 'Unable to set locale to '.$locale." - reverting to en_us<br />\n";
}

.

.

.

 

$worksheet->setCellValueExplicitByColumnAndRow(2, $row, '=SZUMHA(K:K;"' . $line["felelossegi_kor"] . '";E:E)', PHPExcel_Cell_DataType::TYPE_FORMULA);

 

The result is here:

<br />
<font size='1'><table dir='ltr' border='1' cellspacing='0' cellpadding='1'>
<tr><th align='left' bgcolor='#f57900' colspan="5"><span style='background-color: #cc0000; color: #fce94f; font-size: x-large;'>( ! )</span> Exception: M�rcius!B5 -> Formula Error: Unexpected operator ':' in D:\Common\miki\xampp\htdocs\infopult\lib\phpexcel\Classes\PHPExcel\Cell.php on line <i>288</i></th></tr>
<tr><th align='left' bgcolor='#e9b96e' colspan='5'>Call Stack</th></tr>
<tr><th align='center' bgcolor='#eeeeec'>#</th><th align='left' bgcolor='#eeeeec'>Time</th><th align='left' bgcolor='#eeeeec'>Memory</th><th align='left' bgcolor='#eeeeec'>Function</th><th align='left' bgcolor='#eeeeec'>Location</th></tr>
<tr><td bgcolor='#eeeeec' align='center'>1</td><td bgcolor='#eeeeec' align='center'>0.0066</td><td bgcolor='#eeeeec' align='right'>262144</td><td bgcolor='#eeeeec'>{main}(  )</td><td title='D:\Common\miki\xampp\htdocs\infopult\tv\tv_statisztika.php' bgcolor='#eeeeec'>..\tv_statisztika.php<b>:</b>0</td></tr>
<tr><td bgcolor='#eeeeec' align='center'>2</td><td bgcolor='#eeeeec' align='center'>0.1344</td><td bgcolor='#eeeeec' align='right'>5767168</td><td bgcolor='#eeeeec'>PHPExcel_Writer_Excel5->save( ??? )</td><td title='D:\Common\miki\xampp\htdocs\infopult\tv\tv_statisztika.php' bgcolor='#eeeeec'>..\tv_statisztika.php<b>:</b>54</td></tr>
<tr><td bgcolor='#eeeeec' align='center'>3</td><td bgcolor='#eeeeec' align='center'>0.1680</td><td bgcolor='#eeeeec' align='right'>7602176</td><td bgcolor='#eeeeec'>PHPExcel_Writer_Excel5_Worksheet->close(  )</td><td title='D:\Common\miki\xampp\htdocs\infopult\lib\phpexcel\Classes\PHPExcel\Writer\Excel5.php' bgcolor='#eeeeec'>..\Excel5.php<b>:</b>171</td></tr>
<tr><td bgcolor='#eeeeec' align='center'>4</td><td bgcolor='#eeeeec' align='center'>0.1740</td><td bgcolor='#eeeeec' align='right'>7864320</td><td bgcolor='#eeeeec'>PHPExcel_Cell->getCalculatedValue( ??? )</td><td title='D:\Common\miki\xampp\htdocs\infopult\lib\phpexcel\Classes\PHPExcel\Writer\Excel5\Worksheet.php' bgcolor='#eeeeec'>..\Worksheet.php<b>:</b>441</td></tr>
</table></font>
Coordinator
Mar 3, 2011 at 1:18 PM

To help diagnose a calculation problem, it's useful if you can use the following code snippet:

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";


	try {
		$tokens = PHPExcel_Calculation::getInstance()->parseFormula($formulaValue,$sheet->getCell($cell));

		echo '<b>Parser Stack :-</b><pre>';
		print_r($tokens);
		echo '</pre>';
	} catch (Exception $e) {
		echo "CALCULATION PARSER ERROR: ".$e->getMessage()."<br />\n";

		echo '<b>Parser Stack :-</b><pre>';
		print_r($tokens);
		echo '</pre>';
	}

	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>';
	}
}

PHPExcel_Calculation::getInstance()->writeDebugLog = true; 
testFormula($objPHPExcel->getActiveSheet(),'A1');
Substituting in the relevant sheet and cell reference as appropriate.
If you could do this, then the output would be incredibly helpful in trying to diagnose the cause of your problem
Mar 3, 2011 at 2:05 PM

This is the reason:

<b>B6 Value is </b>=DARABTELI(K:K; "M�sorszolg�ltat�")<br />
<b>B6 Expected Value is </b><br />
CALCULATION PARSER ERROR: Formula Error: Unexpected operator ':'<br />
<b>Parser Stack :-</b><pre></pre>CALCULATION ENGINE ERROR: M�rcius!B6 -> Formula Error: Unexpected operator ':'<br />
<h3>Evaluation Log:</h3><pre>Array
(
)
</pre><b>B7 Value is </b>=DARABTELI(K:K; "Vis Major")<br />
<b>B7 Expected Value is </b><br />
CALCULATION PARSER ERROR: Formula Error: Unexpected operator ':'<br />
<b>Parser Stack :-</b><pre></pre>CALCULATION ENGINE ERROR: M�rcius!B7 -> Formula Error: Unexpected operator ':'<br />
<h3>Evaluation Log:</h3><pre>Array
(
)
</pre><br /

Mar 3, 2011 at 2:08 PM

It is the same with the original function

<b>C6 Value is </b>=SZUMHA(K:K;"M�sorszolg�ltat�";E:E)<br />
<b>C6 Expected Value is </b><br />
CALCULATION PARSER ERROR: Formula Error: Unexpected operator ':'<br />
<b>Parser Stack :-</b><pre></pre><b>C6 Calculated Value is </b>=SZUMHA(K:K;"M�sorszolg�ltat�";E:E)<br />
<h3>Evaluation Log:</h3><pre>Array
(
)
</pre><b>C7 Value is </b>=SZUMHA(K:K;"Vis Major";E:E)<br />
<b>C7 Expected Value is </b><br />
CALCULATION PARSER ERROR: Formula Error: Unexpected operator ':'<br />
<b>Parser Stack :-</b><pre></pre><b>C7 Calculated Value is </b>=SZUMHA(K:K;"Vis Major";E:E)<br />
<h3>Evaluation Log:</h3><pre>Array
(
)
</pre>
Coordinator
Mar 3, 2011 at 2:38 PM

The problem is that you're storing the hungarian formula directly in the cell.

You can work with hungarian formulae, but the formula stored in a cell and referenced by the calculation engine, and the writers must be English, so you need to 'translate' before storing.

 

$hungarianFormula = '=SZUMHA(K:K;"' . $line["felelossegi_kor"] . '";E:E)';
$englishFormula = PHPExcel_Calculation::getInstance()->translateFormulaToEnglish($hungarianFormula);
$worksheet->setCellValueExplicitByColumnAndRow(2, $row, $englishFormula, PHPExcel_Cell_DataType::TYPE_FORMULA);
This is described in section 4.6.5 Locale Settings for Formulae of the developer documentation
Mar 3, 2011 at 3:34 PM

Thanks a lot!

I have changed to this:

$hungarianSZUMHA = '=SZUMHA(K:K;"' . $line["felelossegi_kor"] . '";E:E)';
$englishSZUMHA = PHPExcel_Calculation::getInstance()->_translateFormulaToEnglish($hungarianSZUMHA);
$worksheet->setCellValueExplicitByColumnAndRow(2, $row, $englishSZUMHA, PHPExcel_Cell_DataType::TYPE_FORMULA);

But unfortunately the cell (C5 for example) is empty! There isn't any formula in this cell!

Here is the log:

<b>C5 Value is </b>=SUMIF(K:K,"Magyar Telekom",E:E)<br />
<b>C5 Expected Value is </b><br />
<b>Parser Stack :-</b><pre>Array
(
    [0] => Array
        (
            [type] => Value
            [value] => K1
            [reference] => 
        )

    [1] => Array
        (
            [type] => Value
            [value] => K1048576
            [reference] => 
        )

    [2] => Array
        (
            [type] => Binary Operator
            [value] => :
            [reference] => 
        )

    [3] => Array
        (
            [type] => Value
            [value] => "Magyar Telekom"
            [reference] => 
        )

    [4] => Array
        (
            [type] => Value
            [value] => E1
            [reference] => 
        )

    [5] => Array
        (
            [type] => Value
            [value] => E1048576
            [reference] => 
        )

    [6] => Array
        (
            [type] => Binary Operator
            [value] => :
            [reference] => 
        )

    [7] => Array
        (
            [type] => Operand Count for Function SZUMHA()
            [value] => 3
            [reference] => 
        )

    [8] => Array
        (
            [type] => Function
            [value] => SUMIF(
            [reference] => 
            [localeValue] => SZUMHA(
        )

)
</pre><b>C5 Calculated Value is </b>0<br />
<h3>Evaluation Log:</h3><pre>Array
(
    [0] => M�rcius!C5 -> Evaluating Cell K1 in current worksheet
    [1] => M�rcius!C5 -> Evaluation Result for cell K1 is a null value
    [2] => M�rcius!C5 -> Evaluating Cell K5 in current worksheet
    [3] => M�rcius!C5 -> Evaluation Result for cell K5 is a null value
    [4] => M�rcius!C5 -> Evaluating Range K1:K5
    [5] => M�rcius!C5 -> Evaluating Cell E1 in current worksheet
    [6] => M�rcius!C5 -> Evaluation Result for cell E1 is a null value
    [7] => M�rcius!C5 -> Evaluating Cell E5 in current worksheet
    [8] => M�rcius!C5 -> Evaluation Result for cell E5 is a null value
    [9] => M�rcius!C5 -> Evaluating Range E1:E5
    [10] => M�rcius!C5 -> Evaluating Function SZUMHA() with 3 arguments
    [11] => M�rcius!C5 -> Evaluating SZUMHA( { ; ; ; ;  }; "Magyar Telekom"; { ; ; ; ;  } )
    [12] => M�rcius!C5 -> &#65533;Wrk! -> Evaluating "" = "MAGYAR TELEKOM"
    [13] => M�rcius!C5 -> &#65533;Wrk! -> Evaluation Result is a boolean with a value of HAMIS
    [14] => M�rcius!C5 -> &#65533;Wrk! -> Evaluating "" = "MAGYAR TELEKOM"
    [15] => M�rcius!C5 -> &#65533;Wrk! -> Evaluation Result is a boolean with a value of HAMIS
    [16] => M�rcius!C5 -> &#65533;Wrk! -> Evaluating "" = "MAGYAR TELEKOM"
    [17] => M�rcius!C5 -> &#65533;Wrk! -> Evaluation Result is a boolean with a value of HAMIS
    [18] => M�rcius!C5 -> &#65533;Wrk! -> Evaluating "" = "MAGYAR TELEKOM"
    [19] => M�rcius!C5 -> &#65533;Wrk! -> Evaluation Result is a boolean with a value of HAMIS
    [20] => M�rcius!C5 -> &#65533;Wrk! -> Evaluating "" = "MAGYAR TELEKOM"
    [21] => M�rcius!C5 -> &#65533;Wrk! -> Evaluation Result is a boolean with a value of HAMIS
    [22] => M�rcius!C5 -> Evaluation Result for SZUMHA() function call is an integer number with a value of 0
)
</pre>

Mar 3, 2011 at 3:35 PM

This simple one is OK:

$worksheet->setCellValueExplicitByColumnAndRow(3, $row, '=$C5/$C$8', PHPExcel_Cell_DataType::TYPE_FORMULA);

Coordinator
Mar 3, 2011 at 10:31 PM

The calculation log tells us that the calc engine is parsing and evaluating the formula correctly, but that columns K and E both contain 5 empty cells. The question is, why are K1 to K5 and E1 to E5 empty? Without knowing how you've populated the rest of the worksheet, that's difficult to answer.