Calculations work and don't work on same document

Topics: User Forum
Jun 11, 2009 at 7:22 PM

I have a large Excel document, about 1.5 MB with about 7 worksheets.  It is being used to encapsulate the business logic of a web application.

The application has 5 screens.  Screens 2 and 3 take in user inputs, screen 4 takes in user input and shows results, and screen 5 only shows results.  Screen 4 shows the results, which are from calculations based in input from screens 2 and 3.  However, screen 5 shows values which are dummy data, not the actual value calculated by the formula with the inputs from screens 2-4.

All calculated data is retrieved like this: $excelDoc->getActiveSheet()->getCell('A1')->getCalculatedValue(); 

The calculated values shown in screen 4 are based on the inputs in screens 2-3, and the cells have sample data, yet screen 4 shows the calculated value and not the sample data, while screen 5 only shows the sample data, not the calculated value, despite using the getCalculatedValue() method.

How can this be resolved?  Also, I read somewhere a week or so ago that when reading in the Excel document, there is a method which prevents the calculations running at load time, if there is such a function would it help with my issue?

Also, on the FAQ (http://phpexcel.codeplex.com/Wiki/View.aspx?title=FAQ&referringTitle=Home) in the section "Formulas don’t seem to be calculated in Excel2003 using compatibility pack?" it says that recalculation can be forced, yet I have not seen anywhere how this can be done.  Can anyone shed on light on the matter?

Thank you!

Developer
Jun 12, 2009 at 2:36 AM

It sounds like you are doing everything right, but not getting results as expected. Would it be possible to illustrate the problem with some code snippets?

Coordinator
Jun 13, 2009 at 11:50 AM

>>Also, I read somewhere a week or so ago that when reading in the Excel document, there is a method which prevents the calculations running at load time, if there is such a function would it help with my issue?

This option is available from within Excel under Tools -->Options-->Calculation

Calculation settinsg are held at the application level rather than for each workbook.

confusingly these application level settings are saved in each workbook, but only the settings in the first workbook opened are actioned: the settings in subsequent workbooks are ignored.

>>Also, on the FAQ (http://phpexcel.codeplex.com/Wiki/View.aspx?title=FAQ&referringTitle=Home) in the section "Formulas don’t seem to be calculated in Excel2003 using compatibility pack?" it says that recalculation can be forced, yet I have not seen anywhere how this can be done.  Can anyone shed on light on the matter?

Re-calculation can be forced when viewing a worksheet in Excel by pressing SHIFT+F9 for the current worksheet, or CTRL+ALT+SHIFT+F9  for the entire workbook (from Excel 2002)

Jun 16, 2009 at 4:20 PM

What I am doing is this:

Page 2 and 3 on submit:

include('PHPExcel.php');

$excelObj = $_SESSION['excel'];

$var = $_POST['var'];

$excelObj->setActiveSheetIndex(1);

$excelObg->getActiveSheet()->setCellValueExplicit('C73',$var,PHPExcel_Cell_DataType::TYPE_NUMERIC); # PHPExcel craps out if I don't use setCellValueExplicit() as if it can't detect the data type

$_SESSION['excel'];

 

Page 4:

include('PHPExcel.php');

$excelObj = $_SESSION['excel'];

$excelObj->setActiveSheetIndex(1);

$someValueForForm = $excelObj->getActiveSheet->getCell('D1')->getCalculatedValue(); # works! different results for different inputs

 

Page 4 submit:

include('PHPExcel.php');

$excelObj = $_SESSION['excel'];

$excelObj->setActiveSheetIndex(1);

$excelObg->getActiveSheet()->setCellValueExplicit('D2',$var,PHPExcel_Cell_DataType::TYPE_NUMERIC); # PHPExcel craps out if I don't use setCellValueExplicit() as if it can't detect the data type

$_SESSION['excel'];

 

Page 5:

include('PHPExcel.php');

$excelObj = $_SESSION['excel'];

$excelObj->setActiveSheetIndex(1);

$someValueForForm = $excelObj->getActiveSheet->getCell('D1')->getCalculatedValue(); # does not work, same value for any inputs

 

Page 5 shows the same values for all inputs, yet the values are not crazy, they would be valid values, except that the value is the same regardless of the output.  If I put in the same inputs into Excel itself, then I get the proper results, so PHPExcel is not reading the spreadsheet properly.  That or the calculations are already done for Page 5 at the beginning and it won't update the values after the user's input from Pages 2-4 (if that makes any sense). The calculations in page 5 are summations mostly, nothing too crazy.

Thank you.

Developer
Jun 16, 2009 at 10:53 PM

I can't spot any errors in your code. Maybe Mark can? Mark, is there any caching involved in the calculation engine?

rjzak, I know it may not be easy, but would it be possible to set up a simple example we are able to run showing the error?

Coordinator
Jun 17, 2009 at 8:25 AM

>> is there any caching involved in the calculation engine?

The Calculation engine does cache results by default

Caching can be diabled by

PHPExcel_Calculation::getInstance->setCalculationCacheEnabled(False)

Jun 17, 2009 at 4:53 PM

Mark- Disabling the cache seems to have no effect.

Koyama- I am working to get the site running in a publicly available fashion.

Jun 30, 2009 at 6:56 PM

Is there a list of Excel functions and operations that are not supported by PHPExcel?