CODEWITHSUNDEEP
phpexcel-formulaphpexcel
Pooshonk
Pooshonk

Reputation: 1324

PHPExcel - Fatal error: Uncaught exception 'Exception' with message

I am getting an error everytime I try to export an excel spreadsheet. The error is as follows:

Fatal error: Uncaught exception 'Exception' with message 'Worksheet!J6 -> Worksheet!J6 -> Cyclic Reference in Formula' in /classes/PHPExcel/Cell.php:293 Stack trace: 
    #0 /classes/PHPExcel/Writer/Excel2007/Worksheet.php(997): PHPExcel_Cell->getCalculatedValue() 
    #1 /classes/PHPExcel/Writer/Excel2007/Worksheet.php(943): PHPExcel_Writer_Excel2007_Worksheet->_writeCell(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), 'J6', Array, Array) 
    #2 /classes/PHPExcel/Writer/Excel2007/Worksheet.php(83): PHPExcel_Writer_Excel2007_Worksheet->_writeSheetData(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), Array) 
    #3 /classes/PHPExcel/Writer/Excel2007.php(282): PHPExcel_Writer_Excel2007_Worksheet->writeWorksheet(Object(PHPExcel_Worksheet), Array, false) 
    #4 /includes/reports_funcs.php(620): PHPExcel_Writer_Excel2007->save('reports/risk-bo...') #5 /createXlsxReport. in /classes/PHPExcel/Cell.php on line 293

I tried to look for a solution but so far no luck. I found another stackoverflow question which suggested adding $objWriter->setPreCalculateFormulas(FALSE); before the file save, but this gives me an empty spreadsheet.

It has only started happening recently so I am really confused as to why this is happening. Any help would be greatly appreciated, cheers

SAMPLE CODE - EDIT

$objPHPExcel->getActiveSheet(0)
    ->setCellValueByColumnAndRow(0, $row, $pol_ref . 'e' . $end_numb)
    ->setCellValueByColumnAndRow(1, $row, $insured_name)
    ->setCellValueByColumnAndRow(2, $row, $vessel_name)
    ->setCellValueByColumnAndRow(3, $row, $insurance_type . ' - ' . $end_type)
    ->setCellValueByColumnAndRow(4, $row, $insured_domicile)
    ->setCellValueByColumnAndRow(5, $row, gmdate("d-m-Y",$date_logged))
    ->setCellValueByColumnAndRow(6, $row, $inception_date)
    ->setCellValueByColumnAndRow(7, $row, $expiry_date)
    ->setCellValueByColumnAndRow(8, $row, ($policy_limit == 0.00?$loh_limit:$policy_limit))
    ->setCellValueByColumnAndRow(9, $row, $premium)
    ->setCellValueByColumnAndRow(10, $row, "=sum(J".($row)."*".($seacurus_percent/100).")")
    ->setCellValueByColumnAndRow(11, $row, "=sum(J".($row)."*".($placing_percent/100).")")
    ->setCellValueByColumnAndRow(12, $row, "=sum(J".($row)."*".($rebate_percent/100).")")
    ->setCellValueByColumnAndRow(13, $row, "=sum(J".($row)."*".($iptpercent/100).")")
    ->setCellValueByColumnAndRow(14, $row, "=sum(".($binder_id==6?"(J".$row." - (P".$row."+L".$row."+O".$row."+P".$row."))":"J".($row))."*".$bgn_percent.")")
    ->setCellValueByColumnAndRow(15, $row, "=sum(J".($row)."*".$ghc_percent.")")
    ->setCellValueByColumnAndRow(16, $row, "=sum(J".($row)."*".($commission_percentage_sum/100).")");
$row++;

Upvotes: 1

Views: 2562

Answers (1)

Shivam
Shivam

Reputation: 2443

If your formulae contain cyclic references, then you need to choose how to deal with them, as you do in MS Excel itself. The default behaviour is to throw an Exception like this, which is similar to the way MS Excel will behave by default. The alternative is to tell PHPExcel to follow the cycle for a fixed number of iterations by setting the calculation engine to:

PHPExcel_Calculation::getInstance()->cyclicFormulaCount = 1;

or to any value > =1 indicating the number of iterations to cycle

PHPExcel_Calculation::getInstance()->cyclicFormulaCount = 10;

// 10 iteration

Upvotes: 1

Related Questions