Ignoring calculated values when reading in Excel file with PHPExcel

Question

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.

Additional info

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

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)

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.

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

Answer 1

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 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.