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

Question

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++;

What version of PHPExcel?

Mark Baker
– Mark Baker

2014-02-26 21:09:34 +00:00
Commented Feb 26, 2014 at 21:09 — Mark Baker
– Mark Baker, Commented Feb 26, 2014 at 21:09

Shivam · Accepted Answer · 2014-02-26 16:48:56Z

1

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

answered Feb 26, 2014 at 16:48

Shivam

2,44319 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Pooshonk Over a year ago

And where would this be placed? Before the file is exported?

Pooshonk Over a year ago

Doesn't work unfortunately. When I add it i get a memory error. When I fix the memory error it doesn't put the data into the spreadsheet

Shivam Over a year ago

you should use it before any formula evaluation. so it can handle it before occur

Shivam Over a year ago

try before setting values in cell

Mark Baker Over a year ago

If the OP is running PHPExcel 1.7.9 or above, then each PHPExcel object has its own calculation engine, and you need to identify this when calling PHPExcel_Calculation::getInstance() using PHPExcel_Calculation::getInstance($objPHPExcel)->cyclicFormulaCount = 1;

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related