Excel 2007 Protected View and Calculated Values

Topics: Developer Forum
Jun 14, 2013 at 5:47 AM
I have a spreadsheet that is very basic and only contains a single formula.

When viewing the spreadsheet in "protected view" this calculation comes back with 46.8:
            $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Range #1')
                                          ->setCellValue('B2', (double)$rows[0]['data'][2]['data'])
                                          ->setCellValue('B3', 0.7)
                                          ->setCellValue('B4', 13.1)
                                          ->setCellValue('B5', '=SUM(B2:B4)');
The $row array reference = 16.

As soon as I click "Enable Editing" in Excel, the values are correctly calculated and the total shows 29.8 as it should.

Why is the "Protected View" showing a different calculation based on the same values?

Screenshot of protected view: http://cl.ly/Pdf0
Screenshot after clicking "Enable Editing": http://cl.ly/PcKx

Jun 14, 2013 at 12:37 PM
Are you setting PreCalculateFormulas to FALSE when you save the file?
Jun 14, 2013 at 12:45 PM
No, I am not setting PreCalculatedFormulas at all. So my understanding is that it should be TRUE by default?
Jun 14, 2013 at 1:11 PM
It should be true by default... I'm trying to recreate it at the moment, without success so far.

Couple of additional questions:
  • Have you loaded a template workbook, or are you creating a new workbook?
  • Have you executed any data changes to values in these cell ranges prior to the write?
    • Or done any calls to getCalculatedValue() for any of those cells?
    • ie... could the result of a previous cell value be in the calculation cache?
Jun 14, 2013 at 1:32 PM
When I try to recreate the problem without using variable data form the database, I cannot recreate the problem. For example, this code below has the same exact dataset that I am using in the array, but the data is hard-coded instead of pulled from the database. It works fine, when hard-coded.

require_once './PHPExcel.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Sum:');
$row = array
'data' => array
        0 => Array
                'data' => "Senior 2 Int'l Rush",
                'colspan' => 2,
                'style' => "background-color:#2880d7;color:white;font-weight:bold;"
        1 => Array
                'data' => 0.6,
                'style' => "background-color:#2880d7;color:white;font-weight:bold;text-align:right;"
        2 => Array
                'data' => 33,
                'style' => "background-color:#2880d7;color:white;font-weight:bold;text-align:right;"
        3 => Array
                'data' => 19.8,
                'style' => "background-color:#2880d7;color:white;font-weight:bold;text-align:right;"
        4 => Array
                'data' => 16.1,
                'style' => "background-color:#2880d7;color:white;font-weight:bold;text-align:right;"
'class' => 'myearningslevel',
'level' => "Senior 2 Int'l Rush"
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Range #1')
                          ->setCellValue('B2', 3)
                          ->setCellValue('B3', $row['data'][2]['data'])
                          ->setCellValue('B4', 13)
                          ->setCellValue('B5', '=SUM(B2:B4)');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', FILE));
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(str_replace('.php', '.xls', FILE));
print '<a href="formulas.xls">formulas.xls</a><br />';
print '<a href="formulas.xlsx">formulas.xlsx</a><br />';

I am creating the workbooking using PHPExcel.

Is there a way to force recalculation before saving or somehow to clear the calculation cache?
Jun 14, 2013 at 1:50 PM
Recalculation before saving is automatic, unless explicitly suppressed using setPreCalculatedFormulas(FALSE).

You can flush any calculation cache before saving by calling:
in version 1.7.9

in versions prior to 1.7.9
Jun 14, 2013 at 1:55 PM
PHPExcel_Calculation::getInstance($objPHPExcel)->clearCalculationCache() did the trick.