Can't take value of a cell from another worksheet in the same workbook

Feb 9, 2010 at 5:20 AM
Edited Feb 9, 2010 at 5:32 AM

Hi all
i have a problem on my php 5.2.12, WinXP SP3, PHPExcel 1.7.2:

$objPHPExcel = new PHPExcel();

$worksheet2 = $objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);

$worksheet2 = $objPHPExcel->getActiveSheet();

$worksheet2->setCellValue('A1','=Worksheet.D2');


header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="uprtabel_pe.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

 i have an error message in my uprtabel_pe.xls:

Fatal error: Uncaught exception 'Exception' with message 'Formula Error: An unexpected error occured' in C:\Inetpub\wwwroot\repsys\Classes\PHPExcel\Calculation.php:3067 Stack trace: #0
Why phpexcel cant write formula on Worksheet2? When i do it manually directly in Excel it works fine

Coordinator
Feb 9, 2010 at 8:04 AM

Based on your code above, =Worksheet.D2 isn't a a valid formula. What are you actually trying to do with this?

Feb 10, 2010 at 5:04 AM
Edited Feb 10, 2010 at 6:27 AM

I solved a problem.

formula "=Worksheet.D2" works fine in my Excel (in case when im typing manually ), it represents a value of a cell D2 from worksheet Worksheet

I changed formula to "='Worksheet'!D2"

but i get same error with formula "=IF(K39>0;ROUND(K39*C39/F39;0);0)"

solved

 

 

Coordinator
Feb 10, 2010 at 8:20 AM
tzi0 wrote:

I solved a problem.

formula "=Worksheet.D2" works fine in my Excel (in case when im typing manually ), it represents a value of a cell D2 from worksheet Worksheet

I changed formula to "='Worksheet'!D2"

but i get same error with formula "=IF(K39>0;ROUND(K39*C39/F39;0);0)" 

 What locale are you from tzi0? What country/language are you using for Excel?

PHPExcel 1.7.2 only supports formulae using English formatting, i.e

  • ·         Decimal separator is '.' (period)
  • ·         Function argument separator is ',' (comma)
  • ·         Matrix row separator is ';' (semicolon)
  • ·         Always use English function names

Your formula is using semi-colon as the argument separator

The latest SVN code does support some localisation for a number of languages currently Czech (Čeština), Danish (Dansk), German (Deutsch), Spanish (Español), Finnish (Suomi), French (Français), Hungarian (Magyar), Italian (Italiano), Dutch (Nederlands), Norwegian (Norsk), Polish (Język polski), Portuguese (Português), Brazilian Portuguese (Português Brasileiro), Russian and Swedish (Svenska)  specifically local language function names and argument separator. Over the next few months, I'll be extending that and looking at how we can handle decimal separators and other locale formatting, I wasn't aware that worksheet/cell references could be formatted differently (e.g. Worksheet.D2 rather than Worksheet!D2, using a dot rathe rthan an exclamation mark) in different locales, but I can try to incorporate that as well, as long as I know what the formatting is for different locales.

Feb 10, 2010 at 8:37 AM

I'm from Russia, thank you alot. I'm beginner in using PHPExcel

Now i trying to use English formating everywhere.

Thanks for your great work ;)