Formulas on multiple work sheets

Topics: Developer Forum, User Forum
Oct 22, 2009 at 5:34 PM

I have a really complicated spreadsheet (which I think PHPExcel should be fine with). Formulas reference cells in other worksheets within the same excel file. I have two questions:

1) Will PHPExcel support formulas referencing cells in other worksheets?
2) Please help with the following code, I don't get the actual values in the cells.

<?php
    require_once("../phpexcel/Classes/PHPExcel.php");
    require_once('../phpexcel/Classes/PHPExcel/IOFactory.php');
 
    $objPHPExcel = PHPExcel_IOFactory::load("quote.xls");
    $objPHPExcel->setActiveSheetIndex(1);

    echo "Calculated Values<BR>";    
    echo $objPHPExcel->getSheetByName('Resident Paysht')->getCell('F27')->getCalculatedValue()."<BR>";
    echo $objPHPExcel->getSheetByName('Resident Paysht')->getCell('F21')->getCalculatedValue()."<BR>";
    echo "Done <BR>";

    echo "<BR>Formulas<BR>";
    echo $objPHPExcel->getSheetByName('Resident Paysht')->getCell('F27')->getValue()."<BR>";
    echo $objPHPExcel->getSheetByName('Resident Paysht')->getCell('F21')->getValue()."<BR>";
    echo "Done <BR>";
?>

 

Output:

Calculated Values
#N/A
#N/A
Done 

Formulas
=+F25+F21+F11
=F18-F19-F20
Done 

Expected:

Calculated Values
300
200
Done 

Formulas
=+F25+F21+F11
=F18-F19-F20
Done 

Please note the 300 and 200 being shown. These are actual values that are set by default on load of the spreadsheet and calculated. Please help. I am a newbie with PHPExcel. I previously used COM based solution which works. I am moving to this and I am stuck!

 

 

    $objPHPExcel = PHPExcel_IOFactory::load("quote.xls");
    $objPHPExcel->setActiveSheetIndex(1);
    
    echo $objPHPExcel->getSheetByName('Intro.')->getCell('C7')->getCalculatedValue();
    echo "<BR>Inserting....<BR>";
    //$objPHPExcel->getActiveSheet()->setCellValue("C7",30);
    //$objPHPExcel->setActiveSheetIndex(7);    
    echo $objPHPExcel->getSheetByName('Resident Paysht')->getCell('F27')->getCalculatedValue();
    echo $objPHPExcel->getSheetByName('Resident Paysht')->getCell('F21')->getCalculatedValue();
    echo $objPHPExcel->getSheetByName('Resident Paysht')->getCell('I27')->getCalculatedValue();    
    echo "DoneI am
Developer
Oct 22, 2009 at 6:24 PM

1) Will PHPExcel support formulas referencing cells in other worksheets?

It is supposed to support this already. If not, it could be a bug? Can you try to test using latest source code:

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx


2) Please help with the following code, I don't get the actual values in the cells.

It could be a bug. Can you try to upload the file quote.xls so we can check. You can upload the file here:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10749

Oct 23, 2009 at 11:04 AM

Thanks Koyama for your input. I think I found a bug. As you can see in my code above. My sheet has a space. I dont think PHPExcel likes spaces in Sheet Names. I have uploaded a sample Excel file to the link and also put the code here.

 

<?php

    require_once("../phpexcel/Classes/PHPExcel.php");
    require_once('../phpexcel/Classes/PHPExcel/IOFactory.php');

    $objPHPExcel = PHPExcel_IOFactory::load("quoteroh.xls");
    $objPHPExcel->setActiveSheetIndex(1);

    $objPHPExcel->getSheetByName('Sheet 1')->setCellValue('B1','30');

    echo "Calculated Values<BR>";    
    echo $objPHPExcel->getSheetByName('Sheet 2')->getCell('B1')->getCalculatedValue()."<BR>";
    echo $objPHPExcel->getSheetByName('Sheet 2')->getCell('B2')->getCalculatedValue()."<BR>";
    echo "Done <BR>";

    echo "<BR>Formulas<BR>";
    echo $objPHPExcel->getSheetByName('Sheet 2')->getCell('B1')->getValue()."<BR>";
    echo $objPHPExcel->getSheetByName('Sheet 2')->getCell('B2')->getValue()."<BR>";
    echo "Done <BR>";      
        
?>

The output is 
Calculated Values
#VALUE!
#VALUE!
Done 

Formulas
='Sheet 1'!B1*10
='Sheet 1'!B1*20
Done

If I take out the space, it works fine. Eg: 'Sheet1' works fine but 'Sheet 1' does not. Let me know if you have a work around.

 

Developer
Oct 23, 2009 at 10:40 PM

I have tested your script, and you are right that there is such bug in PHPExcel 1.7.0. However, with latest source code the error does not appear.

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Can you verify that it is working with that source code?