PHPExcel - a value from a cell that references another cell was not received properly

Question

I had this problem when I was trying to extract information from excel files. Here is my situation, I have 34 Excel files that I received from different users.

I am using PHP 5 to extract from Excel files. My script will loop on each file and loop through the sheet name, and finally loop through the cell addresses.

The problem arose when users entered the cell, for example. = + A1, which means that users who refer to the value of a cell in another cell, because of this, have the same value with cell A1.

When I checked in mysql (since I saved them for future use), I found from the record that for a particular cell it’s identical to another record obtained from the same cell, but in a different excel file. I meant that since my php script will loop from one file to another file, the first PHPExcel reads, for example, for cell C3, which has some value of $ 3000, the following PHPExcel files can go to the same cell C3 but this time, cell C3 contains a formula that refers to the formula of cell A1 ("= + A1"), which has a value of USD5,000.00.

PHP script suppose mysql entry for USD5,000.00, but it is not. I suspect that the PHPExcel script did not clear the variable in the first round. I tried to disable ($ objPHPExcel) and destroy the variable, but it still happens.

My encoding is simple:

if(file_exists($inputFileName)) { $inputFileType = PHPExcel_IOFactory::identify($inputFileName); $objReader = PHPExcel_IOFactory::createReader($inputFileType); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($inputFileName); //to obtain date from FILE and store in DB for future comparison $validating_date_reporting = $objPHPExcel->getSheet(0)->getCell('C10')->getValue(); $validating_date_reporting = PHPExcel_Style_NumberFormat::toFormattedString($validating_date_reporting,"YYYY-MMM-DD"); $validating_date_reporting = date('Ym-d',strtotime($validating_date_reporting)); //first entry $entry = mysql_query('INSERT INTO `'.$table.'`(`broker_code`, `date`, `date_from_submission`) VALUES("'.$broker_code.'","'.$reporting_date.'","'.$reporting_date.'")') or die(mysql_error()); foreach($cells_array as $caRef=>$sName) { foreach($sName as $sNameRef=>$cells) { $wksht_page = array_search($caRef, $sheetNameArray); $cell_column = $wksht_page.'_'.$cells; echo $inputFileName.' '.$caRef.' '.$cell_column.'<br>'; $value = $objPHPExcel->setActiveSheetIndexByName($caRef)->getCell($cells)->getCalculatedValue(); echo $value.'<br>'; if($value) { $isdPortal->LoginDB($db_periodic_submission); $record = mysql_query('UPDATE `'.$table.'` SET `'.$cell_column.'` = "'.$value.'" WHERE broker_code = "'.$broker_code.'" AND date_from_submission = "'.$validating_date_reporting.'"') or die(mysql_error()); } } } } 

I really hope you can help me here.

thank you in advance.

Answer 1

PHPExcel also contains a calculation cache, and it does not clear when you delete a workbook: you need to clear it manually using:

 PHPExcel_Calculation::flushInstance(); 

or

 PHPExcel_Calculation::getInstance()->clearCalculationCache(); 

You can also completely disable computational caching (although this can slow down if you have many formulas that reference cells containing other formulas) using:

 PHPExcel_Calculation::getInstance()->setCalculationCacheEnabled(FALSE); 

before starting file processing

This is because PHPExcel currently uses singleton for the calculation engine. This year’s roadmap, you can switch to using a multi-tone template that will effectively support a separate cache for each workbook, alleviating this problem.

EDIT

Note that just disabling $ objPHPExcel does not work. You need to separate worksheets before by canceling $ objPHPExcel.

 $objPHPExcel->disconnectWorksheets(); unset($objPHPExcel); 

as described in section 4.3 of the Developer Documentation. And this is the moment when you must also add PHPExcel_Calculation :: flushInstance ();