Promblem with formulas and excel templates...

Topics: Developer Forum
Apr 30, 2013 at 10:56 AM
I have a problem when I want to enter a formula in a cell in my templates using PHPexcel.

my code:
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');
// Include PHPExcel
require_once '../Classes/PHPExcel.php';
require_once 'functions.php'; //my functions for data
$excel2 = PHPExcel_IOFactory::createReader('Excel2007'); // ('Excel2007');
$excel2 = $excel2->load('sablon1.xlsx'); // Empty Sheet!!
$excel2->setActiveSheetIndex(0);
$excel2->getActiveSheet(0)->setCellValue('D5', 'NAME')->setCellValue('D6', '=VLOOKUP($D$5,GENERAL!J:N,2,0)');
$objWriter = PHPExcel_IOFactory::createWriter($excel2, 'Excel2007');
$objWriter->save('Nimit New.xlsx');
and I get error
Fatal error: Uncaught exception 'Exception' with message 'Obrazac!D6 -> Invalid cell coordinate J' in C:\xamp\xampp\htdocs\mljekara\phpexcel\Classes\PHPExcel\Cell.php:288 Stack trace: #0 C:\xamp\xampp\htdocs\mljekara\phpexcel\Classes\PHPExcel\Writer\Excel2007\Worksheet.php(965): PHPExcel_Cell->getCalculatedValue() #1 C:\xamp\xampp\htdocs\mljekara\phpexcel\Classes\PHPExcel\Writer\Excel2007\Worksheet.php(911): PHPExcel_Writer_Excel2007_Worksheet->_writeCell(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), 'D6', Array, Array) #2 C:\xamp\xampp\htdocs\mljekara\phpexcel\Classes\PHPExcel\Writer\Excel2007\Worksheet.php(82): PHPExcel_Writer_Excel2007_Worksheet->_writeSheetData(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), Array) #3 C:\xamp\xampp\htdocs\mljekara\phpexcel\Classes\PHPExcel\Writer\Excel2007.php(266): PHPExcel_Writer_Excel2007_Worksheet->writeWorksheet(Object(PHPExcel_Worksheet), Array) #4 C:\xamp\xampp\htdocs\mljekara\phpexcel\Tests\mojtest.php(70): PHPExcel_Writer_Excel2007->save('Nimit in C:\xamp\xampp\htdocs\mljekara\phpexcel\Classes\PHPExcel\Cell.php on line 288
Can anybody help me?

Best regards
Tesic
Coordinator
Apr 30, 2013 at 12:30 PM
Formulas do not yet work with row or column references.

either rewrite your formula to use a range reference,
   '=VLOOKUP($D$5,GENERAL!J1:N512,2,0)
or disable PreCalculated formulas on write
$objWriter->setPreCalculateFormulas(FALSE);
May 4, 2013 at 2:03 PM
Thank you Mark...now working fine ;)