IF statement not working

Topics: User Forum
Sep 22, 2010 at 11:53 AM

Hi there,

I've started to use PHPExcel and so far it rocks. Problem: I need to insert an IF-formula. A simple sum ('=A1+A2+A3') works like a charm, but the if-statement fails for some reason. Code:


/** Error reporting */
require_once './Classes/PHPExcel.php';

$objPHPExcel = new PHPExcel();
			->setCellValue('A1', 5)
			->setCellValue('B1', '=if(A1="";"empty";"full")');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

This results in the following error:

PHP Fatal error:  Uncaught exception 'Exception' with message 'Worksheet!B1 -> Formula Error: An unexpected error occured' in /var/www/excel/Classes/PHPExcel/Cell.php:272
Stack trace:
#0 /var/www/excel/Classes/PHPExcel/Writer/Excel2007/Worksheet.php(969): PHPExcel_Cell->getCalculatedValue()
#1 /var/www/excel/Classes/PHPExcel/Writer/Excel2007/Worksheet.php(910): PHPExcel_Writer_Excel2007_Worksheet->_writeCell(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), 'B1', Array, Array)
#2 /var/www/excel/Classes/PHPExcel/Writer/Excel2007/Worksheet.php(82): PHPExcel_Writer_Excel2007_Worksheet->_writeSheetData(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), Array)
#3 /var/www/excel/Classes/PHPExcel/Writer/Excel2007.php(260): PHPExcel_Writer_Excel2007_Worksheet->writeWorksheet(Object(PHPExcel_Worksheet), Array)
#4 /var/www/excel/formularTest.php(13): PHPExcel_Writer_Excel2007->save('output.xlsx')
#5 {main}
  thrown in /var/www/excel/Classes/PHPExcel/Cell.php on line 272

Same happens when I set the CellValue to formula explicitly:

->setCellValueExplicit('B1', '=IF(A1="";"empty";"full")', PHPExcel_Cell_DataType::TYPE_FORMULA);
Any ideas why?

Sep 22, 2010 at 12:11 PM

Unless you've enabled locale-specific settings using PHPExcel_Settings::setLocale($locale) then the default argument separator for functions is the American/English comma (,) rather than a semi-colon (;)

            ->setCellValue('A1', 5)
            ->setCellValue('B1', '=if(A1="", "empty", "full")');
Sep 22, 2010 at 3:46 PM

Hi Mark,

that explains it - thanks a lot.

Oct 6, 2010 at 10:27 PM

Can you explain me how to do that? because im having the same problem, i was going to open a post about this...


I have a template that have this type of formulas, but when i run the script, the columns that have this formulas didnt bring anything. even the columm came without the formula.

Oct 6, 2010 at 10:58 PM


I try to help where I can, but I really need something more specific to work with. What is your formula? What does PHPExcel return if you do a getValue() on the cell containing the formula? If you enable formula logging using PHPExcel_Calculation::getInstance()->writeDebugLog = true; then do a getCalculatedValue() on the cell, what does a PHPExcel_Calculation::getInstance()->debugLog return?

Oct 7, 2010 at 3:59 PM

this is my formula that i have in my template....


When i run my script the columm where i have this formula appear on white...

Im gonna give you more information in a while.

Oct 11, 2010 at 10:08 PM

There should be no problems with a simple formula such as that.

Can you please try running the following:

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

$formulaValue = $sheet->getCell($cellID)->getValue();
echo '<b>'.$cellID.' Value is </b>'.$formulaValue."<br />\n";
echo '<b>Parser Stack :-</b><pre>';
$tokens = PHPExcel_Calculation::getInstance()->parseFormula($formulaValue,$sheet->getCell($cellID));
echo '</pre>';

try {
   $cellValue = $sheet->getCell($cellID)->getCalculatedValue();
   echo '<b>'.$cellID.' Calculated Value is </b>'.$cellValue."<br />\n";
   echo '<h3>Evaluation Log:</h3><pre>';
   echo '</pre>';
} catch (Exception $e) {
   echo "CALCULATION ENGINE ERROR: ".$e->getMessage()."<br />\n";
   echo '<h3>Evaluation Log:</h3><pre>';
   echo '</pre>';

Where $cellID is the reference of the cell containing your formula. This gives a detailed log of how PHPExcel is trying to evaluate the formula.