getCalculatedValue returns 0

Topics: Developer Forum, Project Management Forum, User Forum
Aug 5, 2008 at 3:25 AM
I have a PHP application which collects responses to a set of questions from my client's clients.
My client wants the results presented in an Excel workbook (Excel 97) with a worksheet for each clients set of responses and a comparison of some of the results in the front page.
My script creates the responses worksheets exactly as desired. Before I write to file I interate  thought the each of the response worksheets to collect data for the comparison page.
When I use $thisSheet->getCell('C21')->getValue() the formula (=C19/C20) is returned however when I use $thisSheet->getCell('C21')->getCalculatedValue() it returns 0. Can someboby advise what I may be doing in correctly or is this a problem with PHPExcel?
Aug 5, 2008 at 4:10 AM
Hmm.. There is not enough information for me to reconstruct the error. Simple division should be working. Can you please provide a more detailed example?
Aug 5, 2008 at 6:33 AM
This is my actual code.
$questions is an array of items to which the participants responded they are grouped into 5 groups (but could be any number of questions and groups)
$frontPage is worksheet[0] & has had some headings written to it earlier on
$fpRowIndex is an array of row numbers for the front page
$groupResponseLine is an array of rows in the response worksheets worksheet[1...10] from which the data for the front page is to be taken.
As I said in the original post if I use getValue() instead of getCalculatedValue() the formulas are returned.

foreach ($questions as $question) {
        if ($question->name != $groupName) {
            $groupName = $question->name;
            for ($n=1;$n<=$partCount;$n++) {
                $thisSheet = $workbook->getActiveSheet();
                $frontPage->setCellValue("A$fpRowIndex[$groupIndex]","Resp $n");
I hope this is enough information.
Thanks for your response.

Aug 5, 2008 at 12:33 PM
Well I don't see anything suspicious there... This may sound silly, but have you checked what's in C19 and C20?

Aug 5, 2008 at 10:06 PM
Edited Aug 5, 2008 at 11:14 PM
Could this be because the value of C19 is actually a formula, 'SUM(C7:C18)', if so any suggestions on how to accomplish what I need to?
All the correct values are written to the individual worksheets. It is when I attempt to collect data from each of the sheets for the front comparison worksheet.
Aug 5, 2008 at 11:24 PM
No, that should be ok to my knowledge. Just to check, please post what you get when you run this:

Aug 6, 2008 at 9:00 AM
This is what is weird.
  • var_dump($thisSheet->getCell('C19')->getCalculatedValue());   displays int(0)
  • var_dump($thisSheet->getCell('C19')->getValue());   displays string(12) "=SUM(C7:C18)
  • var_dump($thisSheet->getCell('C20')->getCalculatedValue());  displays int(30) which is the correct value.
Aug 6, 2008 at 10:26 AM
Are the values in C7..C18 actual numbers, or strings containing a numeric value?
Aug 6, 2008 at 10:10 PM
Originally I was writing to cells C7..C18 using this code:
$workbook->getActiveSheet()->setCellValue("C$rowIndex",($r->policy_response == 0 ? 'n/a':$r->policy_response));
var_dump() reports these as string, in the resultant worksheets these cells return TRUE to ISNUMBER

After Mark's post I changed it to:

if ($r->policy_response == 0) {
} else {
var_dump still reports these cells as strings????????

Aug 7, 2008 at 1:49 AM
Well, as long as

var_dump($thisSheet->getCell('C19')->getCalculatedValue());   displays int(0)

then the result of the division

C21 = C19 / C20 = 0

should be zero so there is no problem. I figure that you didn't expect C19 to be 0 so your real question is why C19 is zero.

Can you please post exactly what you get when you run this:

Aug 7, 2008 at 8:53 AM
doesn't actually change the PHP datatype to a numeric (though it probably should)

Try explicitly typecasting using
        $workbook->getActiveSheet()->setCellValue("C$rowIndex",($r->policy_response == 0 ? 'n/a':(integer)$r->policy_response));
Aug 8, 2008 at 8:33 AM
I'm not absolutely sure what has fixed this issue but it is working. Thanks Mark & Koyama