Simplest way to use the calculation engine?

Topics: User Forum
Jan 17, 2010 at 2:01 AM

Have read every thread (I think) on this, am brand new to PhpExcel, and my mind is reeling.  I have a spreadsheet with exhaustive formulas, and would like to like to know the cleanest way of loading the spreadsheet, changing a couple of values, and get the calculated result.  It seems I can do this with the Excel2007 Reader, but want to know if I'm assuming correctly before i embark on the learning curve of using this beautiful code that you've provided.

Apologies if this is an eyeroller - I HAVE been reading up on it, but am feeling,well, a bit overwhelmed with the endless possibilities.

Thanks in advance,

Jan 17, 2010 at 2:31 AM

This may get you started

require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';

// read some workbook
$excel = PHPExcel_IOFactory::load('read.xlsx');

$worksheet = $excel->getActiveSheet();

// do stuff, e.g.

// retrieve some calculated value and dump it, e.g.

// save the workbook
$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');


Jan 17, 2010 at 2:48 AM

Wow, and thanks for that quick reply!. 

Mind is reeling now with possibilities!  Pondering ways of efficiently and completely replacing all our pricing code by loading a single spreadsheet! 

Thanks again! :)

Jan 17, 2010 at 11:54 PM

I'm working on doing this very thing for an online quoting application right now.  I don't want to scare you off as this solution has a lot of potential and no other competitive options (short of .net and office on a windows server), but I have to warn you that it might not be as easy as it seems.  Depending on how the pricing spreadsheet is organized it might work great, or you might have to make some revisions. 

The pricing sheets that I received from the client had a lot of Hlookup and Vlookups that were defined pretty inefficiently (referencing a whole block of cells rather than just the necessary lookup and result fields) which caused a massive explosion of calculations object creation.  Many of the PHPExcel calculation functions are not yet optimized and will calculate cells that don't necessarily need to be calculated (e.g. calculating all hlookup/vlookup referenced cells, calculating both the true and false answers before checking whether the conditional is true or false, etc).

You will want to use the following line to disabled any calculation caching or many calculated values requests will come back wrong.


The following two functions are useful for debugging:

     * Create an HTML table representation of a PHP Excel worksheet
     * @param PHPExcel_Worksheet $objWorksheet
     * @return string An HTML table representation of the worksheet
    public static function dumpPHPExcelSheetToHTML(PHPExcel_Worksheet $objWorksheet) {
        $output.='<table border="1">' . "\n";
        foreach ($objWorksheet->getRowIterator() as $row) {
            $output.= '<tr>' . "\n";

            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false); // This loops all cells,
            // even if it is not set.
            // By default, only cells
            // that are set will be
            // iterated.
            foreach ($cellIterator as $cell) {
                $output.= '<td>' . $cell->getValue() . '</td>' . "\n";

            $output.= '</tr>' . "\n";
        $output.= '</table>' . "\n";
        return $output;

     * Dumps the cell value, calculated value and processing
     * stack for a PHPExcel cell.
     * @param PHPExcel_Worksheet $sheet
     * @param string $cell   e.g. 'A1', 'C3', etc.
    public static function debugPHPExcelCell(PHPExcel_Worksheet $sheet, $cell) {
        //$sheet = $objPHPExcel->getActiveSheet();
        PHPExcel_Calculation::getInstance()->writeDebugLog = true;

        $cellValue = $sheet->getCell($cell)->getValue();
        echo '<b>'.$cell.' Value is </b>'.$cellValue."<br />\n";

        echo '<b>Parser Stack :-</b><pre>';
        $tokens = PHPExcel_Calculation::getInstance()->parseFormula($cellValue);
        echo '</pre>';

        $cellValue = $sheet->getCell($cell)->getCalculatedValue();
        echo '<b>'.$cell.' Calculated Value is </b>'.$cellValue."<br />\n";

        echo '<h3>Evaluation Log:</h3><pre>';
        echo '</pre>';