PHPExcel-从单元格引用另一个单元格的值未正确获得 [英] PHPExcel - Value from a Cell referencing to another Cell Did Not Obtained Properly
问题描述
当我尝试从excel文件中提取信息时遇到了这个问题.这是我的情况，我有34个Excel文件，这些文件是我从各种用户那里收到的.
I'm having this problem when I tried to extract information from excel files. Here's my situation, I have 34 Excel files which I received from my various users.
我正在使用PHP版本5从Excel文件中提取.我的脚本将为每个文件循环，然后根据工作表名称再次循环，最后根据单元格地址再次循环.
I'm using PHP version 5 to extract from the Excel files. My script will loop for every files, and looping again according to sheet name, and lastly looping again according to cell addresses.
当用户进入例如= + A1，表示用户将单元格值引用到另一个单元格，因为它与单元格A1具有相同的值.
The problem arised when the users had entered into a cell for e.g. =+A1 which means the users referencing the cell value to another cell due to it has the same value with cell A1.
当我签入mysql(因为我将它们保存以备将来使用)时，我从特定单元格的记录中发现与从同一单元格但在不同excel文件中获得的另一条记录相同.我的意思是，由于我的php脚本将从一个文件循环到另一个文件，因此，PHPExcel第一次读取具有某些值USD3,000.00的单元格C3，例如下一个文件，PHPExcel可能会转到同一单元格C3，但这一次C3单元格包含一个引用单元格A1的公式("= + A1"公式)，值USD5,000.00.
When I checked in mysql (as I saved those for future use) I found from the record for a particular cell is identical with another record obtained from the same cell but in different excel file. What I meant is that, as my php script will loop from one file to another file, the first time PHPExcel read for e.g cell C3 which has some value USD3,000.00 the next files the PHPExcel may go to the same cell C3 but this time the C3 cell contain a formula that referencing to cell A1 ("=+A1" formula)which has value USD5,000.00.
PHP脚本假设以5,000.00美元的价格在mysql中记录，但没有.我怀疑PHPExcel脚本在第一轮没有清除变量.我尝试过unset($ objPHPExcel)并销毁该变量，但它仍在发生.
PHP script suppose to record in mysql for USD5,000.00 but it didn't. I suspect that the PHPExcel script did not clear the variable at first round. I've tried unset($objPHPExcel) and destroy the variable but it still happening.
我的编码很简单，如下所示:
My coding is simple as follows:
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('Y-m-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 that you can help me out here..
先谢谢您
推荐答案
PHPExcel还拥有一个计算缓存，并且在您取消设置工作簿时不会清除该缓存:必须使用以下命令手动清除它:
PHPExcel holds a calculation cache as well, and this is not cleared when you unset a workbook: it has to be cleared manually using:
PHPExcel_Calculation::flushInstance();
或
PHPExcel_Calculation::getInstance()->clearCalculationCache();
您还可以使用以下方法完全禁用计算缓存(尽管如果您有很多引用包含其他公式的单元格的公式，这可能会使速度降低)
You can also disable calculation caching completely (although this may slow things down if you have a lot of formulae that reference cells containing other formulae) using:
PHPExcel_Calculation::getInstance()->setCalculationCacheEnabled(FALSE);
开始处理文件之前
这是因为当前PHPExcel使用单例作为计算引擎.计划在今年晚些时候切换到使用multiton模式，这将有效地为每个工作簿维护一个单独的缓存，从而减轻此问题.
This is because currently PHPExcel uses a singleton for the calculation engine. It is in the roadmap to switch to using a multiton pattern later this year, which will effectively maintain a separate cache for each workbook, alleviating this problem.
编辑
请注意，仅取消设置$ objPHPExcel无效.您需要之前分离工作表，然后取消设置$ objPHPExcel.
Note that simply unsetting $objPHPExcel does not work. You need to detach the worksheets before unsetting $objPHPExcel.
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);
，如《开发人员文档》第4.3节所述.这是您还应该添加PHPExcel_Calculation :: flushInstance();
as described in section 4.3 of the Developer Documentation. And this is the point where you should also add the PHPExcel_Calculation::flushInstance();
这篇关于PHPExcel-从单元格引用另一个单元格的值未正确获得的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
