使用PHPExcel读取Excel文件时忽略计算值 [英] Ignoring calculated values when reading in Excel file with PHPExcel

问题描述

我正在读取带有setReadDataOnly(true)的XLS文件.读取的对象再次保存为新的Excel文件.不幸的是，某些单元格的值计算不正确(这与具有小计公式的单元格的计算错误有关).如果我理解正确，则XLS文件中的每个单元格都包含一个预先计算的值以及公式.如果我可以使PHPExcel在读取文件时不尝试计算公式(而是按原样使用预先计算的值)，则可以解决此问题.我以为setReadDataOnly(true)或setPreCalculateFormulas(false)可以完成此操作，但事实并非如此.

I am reading an XLS file with setReadDataOnly(true). The read object is saved again as a new Excel file. Unfortunately, some cell values are calculated incorrectly (this has to do with a calculation bug over cells with a subtotal formula). If I understand correctly, each cell in an XLS file contains a pre-calculated value along with the formula. If I can get PHPExcel to not attempt to calculate the formulas when reading the file in (instead just use the precalculated values as is) I can get around this issue. I thought that setReadDataOnly(true) or setPreCalculateFormulas(false) might accomplish this, but it does not.

其他信息

由于Mark的解释，我研究了getCalculatedValue()和getOldCalculatedValue()之间的区别.我使用以下代码读取文件，然后再次将其写出:

Thanks to Mark's explanation, I investigated the difference between getCalculatedValue() and getOldCalculatedValue() in my case. I use the following code to read the file in and then write it out again:

$excel_reader = PHPExcel_IOFactory::createReaderForFile($file);
$excel_reader->setReadDataOnly(true);
$excel_obj_temp = $excel_reader->load($file);

// Test one of the values in question
$excel_obj_temp->setActiveSheetIndexByName("Form 11");
error_log("val:".$excel_obj_temp->getActiveSheet()->getCell("E36")->getCalculatedValue());
error_log("old_val:".$excel_obj_temp->getActiveSheet()->getCell("E36")->getOldCalculatedValue());

$new_file = "new_generated_name";

$excel_writer = new PHPExcel_Writer_Excel5($excel_obj_temp);
$excel_writer->setPreCalculateFormulas(false);
$excel_writer->save($unprotected_file);

读取文件时，使用getOldCalculatedValue()显示正确的值.如果随后我无保存文件setPreCalculateFormulas(false)并再次读取该文件，则getCalculatedValue()和getOldCalculatedValue()都返回相同(错误)的结果.这与Mark的解释一致，即如果不设置setPreCalculateFormulas(false)

When Reading the the file in, it shows the correct value with getOldCalculatedValue(). If I then save the file without setPreCalculateFormulas(false) and read the file in again, both getCalculatedValue() and getOldCalculatedValue() returns the same (incorrect) result. This is inline with Mark's explanation that the values will be recalculated on save if you do not set setPreCalculateFormulas(false)

但是，如果我改用 保存文件setPreCalculateFormulas(false)(这似乎是正确的方法)并再次读取文件，则getCalculatedValue()返回错误的结果，而getOldCalculatedValue()返回0，这是错误的.

However, if I instead save the file with setPreCalculateFormulas(false) (which seems to be the correct way) and read the file again, getCalculatedValue() returns the incorrect result and getOldCalculatedValue() returns 0, which is wrong.

为什么保存后清除缓存的值?还有其他一些需要与setPreCalculateFormulas(false)一起应用的设置吗?

Why is the cached values cleared after saving? Is there some other setting I need to apply along with setPreCalculateFormulas(false)?

推荐答案

在加载电子表格文件时，PHPExcel不会不计算任何值.仅当您显式调用单元格的getCalculatedValue()或getFormattedValue()方法，或者默认情况下在保存时(除非使用Writer的setPreCalculateFormulas(false))，它才会计算单元格值，尽管使用autofit列会强制为保存重新计算不论其他设置如何，这些列中的所有单元格都可以.

PHPExcel does not calculate any values when you load a spreadsheet file. It will only calculate cell values if you explicitly call the cell's getCalculatedValue() or getFormattedValue() methods, or by default when you save (unless you use the Writer's setPreCalculateFormulas(false))... though using autofit columns forces a recalculation on save for any cells in those columns regardless of any other settings.

MS Excel通常会在电子表格中保存所有公式单元格的计算值(除非明确禁用了此值)，并且可以使用单元格的getOldCalculatedValue()方法在PHPExcel中读取该值.

MS Excel will normally save a calculated value for all formula cells in a spreadsheet (unless this is explicitly disabled), and this value can be read in PHPExcel using the cell's getOldCalculatedValue() method.

这篇关于使用PHPExcel读取Excel文件时忽略计算值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！