Uncaught exception "Detail Calc!"... spreadsheet too complicated?

Topics: Developer Forum, User Forum
Aug 3, 2012 at 5:35 PM

Hi! We have a fantastically complicated spreadsheet we're working with that I am able to read values out of when loading the file with PHPExcel, but if I attempt to change any of the values and output the file as a download or otherwise this causes a very peculiar error, which I will paste below.

 

~~~~~~~~~~~~~~~~~~~~~

Fatal error: Uncaught exception 'Exception' with message 'Detail Calc!D7 -> Detail Calc!H61 -> Detail Calc!D61 -> Detail Calc!H60 -> Detail Calc!D60 -> Detail Calc!H59 -> Detail Calc!D59 -> Detail Calc!H58 -> Detail Calc!D58 -> Detail Calc!H57 -> Detail Calc!D57 -> Detail Calc!H56 -> Detail Calc!D56 -> Detail Calc!H55 -> Detail Calc!D55 -> Detail Calc!H54 -> Detail Calc!D54 -> Detail Calc!H53 -> Detail Calc!D53 -> Detail Calc!H52 -> Detail Calc!D52 -> Detail Calc!H51 -> Detail Calc!D51 -> Detail Calc!H50 -> Detail Calc!D50 -> Detail Calc!H49 -> Detail Calc!D49 -> Detail Calc!H48 -> Detail Calc!D48 -> Detail Calc!H47 -> Detail Calc!D47 -> Detail Calc!H46 -> Detail Calc!D46 -> Detail Calc!H45 -> Detail Calc!D45 -> Detail Calc!H44 -> Detail Calc!D44 -> Detail Calc!H43 -> Detail Calc!D43 -> Detail Calc!H42 -> Detail Calc!D42 -> Detail Calc!H41 -> Detail Calc!D41 -> Detail Calc!H40 -> Detail Calc!D40 -> Detail Calc!H39 -> Detail Calc!D39 -> Detail Calc!H38 -> Detail Calc!D38 -> Detail Calc!H37 -> Detail Calc!D37 -> Detail Calc in /opt/local/apache2/htdocs/passwordtrustee/web/PHPExcel/PHPExcel/Cell.php on line 293

~~~~~~~~~~~~~~~~~~~~~~

 

I am running PHP 5.3.12 on OS X with the gd2, zip, xml extensions and xdebug is disabled. An example of the code that I am using can be found here: https://gist.github.com/8d8e379218bf3e966833

... but the important parts are these:

require_once dirname(__FILE__) . '/PHPExcel/PHPExcel.php';
$objPHPExcel = new PHPExcel();

$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load( dirname(__FILE__) . '/test.xls' );
$assumpSheet = $objPHPExcel->setActiveSheetIndexByName('Assump');
$formData = $_POST['assumptions'];
$assumpSheet->getCell('F10')->setValue($formData['spending']);
$assumpSheet->getCell('F36')->setValue($formData['spendingIncreaseConstant']);
$assumpSheet->getCell('E62')->setValue($formData['portfolioCost']);
$assumpSheet->getCell('D42')->setValue($formData['portfolioExistingTaxable']);
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save('test.xls');

Any ideas on how to fix this exception or am I doomed to being able to read from this sheet but not actually make changes and output them?

Coordinator
Aug 3, 2012 at 9:47 PM
Try setting precalculate formulas to false before the save:
 
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->setPreCalculateFormulas(FALSE);
$objWriter->save('test.xls');
 
This stops PHPExcel from precalculating formulae, forcing MS Excel to do it when the newly saved file is first loaded.
 
If it's possible to send me a copy of the workbook I'd appreciate it. I'd like to run some tests to see what is triggering this problem.
Aug 6, 2012 at 4:15 PM

Hey Mark,

That helped me surmount this particular problem, thank you! As it turns out however, because the excel file has macros, and despite the fact that I dont actually want PHPExcel to use the macros but instead just update a couple of field values (and then let a user download/run the macros in their copy of excel)... I won't be able to use PHPExcel to serve my needs for this particular task. Thanks anyway!