Problem with workbook and worksheet.

Topics: Developer Forum, Project Management Forum, User Forum
Feb 1, 2011 at 7:28 PM

Hi, I'm with a problem I can not solve.
I have an excel file that works well.
This file load on other cells in other booksbooks and works ok.
But the problem is when I try to put on a development in php. The shooting error is:
Fatal error: Uncaught exception 'Exception' with message 'Cotizador!AM8 -> undefined variable ','' in C:\wamp\www\cotizador\Classes\PHPExcel\Cell.php:284 Stack trace: #0 C:\wamp\www\cotizador\index.php(29): PHPExcel_Cell->getCalculatedValue() #1 {main} thrown in C:\wamp\www\cotizador\Classes\PHPExcel\Cell.php on line 284

Now, if I do not use booksbook and do it all on the same worksheet, it works fine.
Someone can help me?

Thanks.
Ariel.

Coordinator
Feb 1, 2011 at 8:19 PM

Can you please try this using the latest daily download, accessible from the "Source Code" tab.

Feb 2, 2011 at 11:46 AM

I tried with the latest version and the problem continues.

I think I found the problem:

My excel sheet has references to other worksheets and use the function "IF" that is where I have the problem.
In Spanish the function "IF" is "SI".

The function is: =(SI(Cotizador!AG8="TEST";Roseta!A10;Roseta!A11))      The error that appears is: Fatal error: Uncaught exception 'Exception' with message 'Cotizador!AM8 -> undefined variable ','' in C:\wamp\www\cotizador\Classes\PHPExcel\Cell.php:284 Stack trace: #0 C:\wamp\www\cotizador\index.php(29): PHPExcel_Cell->getCalculatedValue() #1 {main} thrown in C:\wamp\www\cotizador\Classes\PHPExcel\Cell.php on line 284

Now if instead I refer to two cells within the same page works fine: =(SI(Cotizador!AG8="TEST";A1;A2))

Thank you very much for the help.

Ariel.

Coordinator
Feb 2, 2011 at 4:24 PM
Edited Feb 2, 2011 at 4:30 PM

There is a complete section of the manual devoted to setting the locale for the calculation engine.

Internally, the calculation engine uses English function names, in the same way as Excel itself. It also uses a comma (,) as the argument separator rather than a semi-colon (;). However, you can use the internal translation methods to convert between English and Spanish function names, and the semi-colon (;) as an argument separator, as long as you set the locale to Spanish.

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

Once you have set the locale, you can set a cell to contain a formula using:

$formula = '=SI(Cotizador!AG8="TEST";Roseta!A10;Roseta!A11)';
$internalFormula = PHPExcel_Calculation::getInstance()->translateFormulaToEnglish($formula);
$objPHPExcel->getActiveSheet()->setCellValue('B8',$internalFormula);

To read a formula from a cell (where it is stored in English) and translate it to the locale version:

$formula = $objPHPExcel->getActiveSheet()->getCell('B8')->getValue();
$translatedFormula =PHPExcel_Calculation::getInstance()->_translateFormulaToLocale($formula);

This only translates function names... it will not translate text strings like "TEST", nor worksheet names like "Roseta", etc, although it will allow the use of VERDADERO and FALSO for booleans TRUE and FALSE.

 

 

 

 

Feb 2, 2011 at 5:26 PM

Dear Mark:

My code is part of

.

.

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

.

.

.

$formula = '=SI(Cotizador!AG8="TEST";Roseta!A10;Roseta!A11)';
$internalFormula = PHPExcel_Calculation::getInstance()->_translateFormulaToEnglish($formula);
$objPHPExcel->getActiveSheet()->setCellValue('AM8',$internalFormula);

$total= $objPHPExcel->getActiveSheet()->getCell('AM8')->getCalculatedValue();

 

...and still the problem: Fatal error: Uncaught exception 'Exception' with message 'Cotizador!AM8 -> undefined variable ','' in C:\wamp\www\cotizador\Classes\PHPExcel\Cell.php:284 Stack trace: #0 C:\wamp\www\cotizador\index.php(45): PHPExcel_Cell->getCalculatedValue() #1 {main} thrown in C:\wamp\www\cotizador\Classes\PHPExcel\Cell.php on line 284

What I need is to get the cell value AM8.

 

Thank you.

Coordinator
Feb 2, 2011 at 5:59 PM

If this really is the case, then there's a major flaw in the Calculation Engine; although I'd have expected it to have appeared before now for such a simple formula. Is it possible to email me a copy of your workbook/code, or attach it to Work Item 10749

Feb 2, 2011 at 7:26 PM
Edited Feb 2, 2011 at 7:45 PM

to send your mail?

Coordinator
Feb 2, 2011 at 9:33 PM

I don't think the mail facility here on codeplex allows file attachments, so send to

define('XX','.');
define('YY','@');
echo strrev(str_rot13('xh').XX.str_rot13('bp').XX.str_rot13('abzrq').XX.str_rot13('rtany').YY.str_rot13('xenz'));
Feb 3, 2011 at 4:58 PM

This morning I sent the mail. Arrived?

Coordinator
Feb 3, 2011 at 7:14 PM
On 03/02/2011 17:58, averbner wrote:

From: averbner

This morning I sent the mail. Arrived?

Received and looking at it.

---
Mark Baker
Feb 4, 2011 at 1:59 PM

Definitely the problem is in the arguments of the function =IF. If I make references found on the same sheet, without referring to another (nombre!) works well.

Coordinator
Feb 8, 2011 at 8:24 PM

The problem is actually in cell 'Configuracion'!C53 which is used within the calculation. The formula for that cell is ='[!CONTROL MANAGER.xls]DOT'!$I$7 which is a reference to an external workbook.

The PHPExcel the calculation engine does not support references to external workbooks. There are currently two work items open for this issue: Work Items 15046 and 15184.

While I know (in theory) how to do this, it's a lot of work, and a big performance overhead whenever it encounters an external workbook reference, and the code still has to trap for situations where the external workbook is not available. What I will try to do sooner is to perform a trap for external workbook references and throw an exception when one is encountered, so that it can be handled cleanly. This change won't be in time for the next release, but I'm hoping to rework the structure of the calculation engine as part of the 1.7.7 release, and will target it as part of that change.

In the interim, if you need to read the calculated value from the cell (as stored when the workbook was saved in MS Excel), you can use:

$objPHPExcel->getSheetByName('Cotizador')->getCell('AM8')->getOldCalculatedValue();
Feb 9, 2011 at 2:03 PM

Mark, thank you very much for the help and good predisposition. You have been very helpful.