2

I am comparing the values of two columns cell by cell and then in the 3rd column writing the value if there was a change then change else no Change.

enter image description here

I am able to do that perfectly.

But my problem arises when i try to fetch the formulated value. It gives me 0 as output for all the values.

My Code to fetch is as follows:

<?php
/** Error reporting */
error_reporting(E_ALL);
include 'PHPExcel.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel1 = new PHPExcel();

require_once 'PHPExcel/IOFactory.php';

$directory = 'C:\Users\user\Desktop\Google-Competitors\\';   
$target_file_path = $directory . "domain-page-size.xlsx";
$change_size_url = $directory . "url-list-with-change-in-page-size.xlsx";

$objPHPExcel = PHPExcel_IOFactory::load($target_file_path);     //Load file
$objWorksheet = $objPHPExcel->getActiveSheet();                 //get active sheet

$highestRow = $objWorksheet->getHighestRow();                   //get total rows
$highestColumn = $objWorksheet->getHighestColumn();             //get total columns

$objPHPExcel1 = PHPExcel_IOFactory::load($change_size_url);
$objWorksheet1 = $objPHPExcel1->getActiveSheet();   
$highestColumn = $objWorksheet1->getHighestColumn();    
$timestamp = date('M Y');
$objPHPExcel1->setActiveSheetIndex(0)->setTitle($timestamp);
$i= 1;
for ($row = 2; $row <= 547; ++$row)
{
    $cell = "E".$row;
    // $val = $objWorksheet->getCell($cell)->getCalculatedValue();
    $val = $objWorksheet->getCell($cell)->getOldCalculatedValue();
    echo $val;
    if($val=="Change")
    {
        $url_cell = "B".$row;
        $url_val = $objWorksheet->getCell($url_cell);
        $strvar = (string) $url_val;
        $objPHPExcel1->setActiveSheetIndex()->setCellValue('A'.$i, $strvar);
        $i++;
    }
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel1, 'Excel2007');
$objWriter->save($change_size_url);
?>

and in echo i get output which is meant as No Change i guess

0
0
0
0
0
0
0
0
0
0

I referred enter link description here and enter link description here

Can please any one tell me where am i going wrong in the code

Thanks

EDIT

After using formula Debugging tool i got output as follows

11:34:13 Create new PHPExcel object
11:34:13 Add some data
11:34:14 Rename worksheet
Formula Value is=IF(ABS(C6-D6)>10,"Change", "No Change")
Expected Value is 0
Parser Stack :-
Array
(
    [0] => Array
        (
            [type] => Cell Reference
            [value] => C6
            [reference] => C6
        )

    [1] => Array
        (
            [type] => Cell Reference
            [value] => D6
            [reference] => D6
        )

    [2] => Array
        (
            [type] => Binary Operator
            [value] => -
            [reference] => 
        )

    [3] => Array
        (
            [type] => Operand Count for Function ABS()
            [value] => 1
            [reference] => 
        )

    [4] => Array
        (
            [type] => Function
            [value] => ABS(
            [reference] => 
        )

    [5] => Array
        (
            [type] => Value
            [value] => 10
            [reference] => 
        )

    [6] => Array
        (
            [type] => Binary Operator
            [value] => >
            [reference] => 
        )

    [7] => Array
        (
            [type] => Value
            [value] => "Change"
            [reference] => 
        )

    [8] => Array
        (
            [type] => Value
            [value] => "No Change"
            [reference] => 
        )

    [9] => Array
        (
            [type] => Operand Count for Function IF()
            [value] => 3
            [reference] => 
        )

    [10] => Array
        (
            [type] => Function
            [value] => IF(
            [reference] => 
        )

)

Calculated Value is No Change
Evaluation Log:
Array
(
    [0] => Testing cache value for cell Simple!E6
    [1] => Simple!E6 => Evaluating Cell C6 in current worksheet
    [2] => Simple!E6 => Evaluation Result for cell Simple!C6 is a floating point number with a value of 34738
    [3] => Simple!E6 => Evaluating Cell D6 in current worksheet
    [4] => Simple!E6 => Evaluation Result for cell Simple!D6 is a floating point number with a value of 34742
    [5] => Simple!E6 => Evaluating 34738 - 34742
    [6] => Simple!E6 => Evaluation Result is a floating point number with a value of -4
    [7] => Simple!E6 => Evaluating Function ABS() with 1 argument
    [8] => Simple!E6 => Evaluating ABS( -4 )
    [9] => Simple!E6 => Evaluation Result for ABS() function call is a floating point number with a value of 4
    [10] => Simple!E6 => Evaluating 4 > 10
    [11] => Simple!E6 => Evaluation Result is a boolean with a value of FALSE
    [12] => Simple!E6 => Evaluating Function IF() with 3 arguments
    [13] => Simple!E6 => Evaluating IF( FALSE, "Change", "No Change" )
    [14] => Simple!E6 => Evaluation Result for IF() function call is a string with a value of "No Change"
)
Community
  • 1
  • 1
Penny
  • 824
  • 1
  • 14
  • 31
  • I've recreated the snippet of worksheet that you've posted above, and tested PHPExcel using that, and it correctly evaluates all the formulae using `getCalculatedValue()` - can you try using the formula debugging tools as shown in [this gist](https://gist.github.com/MarkBaker/5908357) – Mark Baker Jan 13 '15 at 21:16
  • Thanks Mark for awesome library. But i am not getting how to debug :( . Could you please explain more in detail? – Penny Jan 16 '15 at 10:53
  • Have you read the gist that I linked? Basically you're just passing details of the worksheet and cell address that you want to debug to the `testFormula()` function that's defined in the gist code sample, and it will then display a stream of output showing exactly what steps it is taking to evaluate the formula, with details of the values involved and the results at every stage – Mark Baker Jan 16 '15 at 11:12
  • ok i just implemented..I am adding the output in my question edit – Penny Jan 16 '15 at 11:27
  • I sent `$cell = 'E4';` – Penny Jan 16 '15 at 11:31
  • And the log shows that the result for a getCalculatedValue() call on that cell is showing as "Change" – Mark Baker Jan 16 '15 at 11:32
  • ok now editing for `$cell = 'E6';` – Penny Jan 16 '15 at 11:35
  • and the ans is no change ....i agree.... but when i echo this cell i get o/p `0` – Penny Jan 16 '15 at 11:36
  • And the evaluated result of the formula for cell E6 is "No Change" – Mark Baker Jan 16 '15 at 11:36
  • Double check that you're calling `getCalculatedValue()`.... you can see that is exactly what the `testFormula()` function is doing.... note that the `Expected Value` shown in the debugger output is the result of a `getOldCalculatedValue()` call, that can't be relied on – Mark Baker Jan 16 '15 at 11:37
  • yes sir i have also added my code `$cell = "E".$row; $val = $objWorksheet->getCell($cell)->getOldCalculatedValue();` – Penny Jan 16 '15 at 11:38
  • No, __don't__ use `getOldCalculatedValue()`, use `getCalculatedValue()` – Mark Baker Jan 16 '15 at 11:39
  • ohh omg ! oh yes and i get ans as expected... Thankyou so much – Penny Jan 16 '15 at 11:41

0 Answers0