0

I'm stuck on this problem, let me explain. I've a Excel sheet that has 3 columns like so:

y       x       z
10      5       0
10      5       4.25
10      5       0
30      2       0
30      2       1.85
30      2       0

How can I add the values of col.x for the similar values of col.y?

The desired result is:

y=10, x=15
y=30, x=6

Is it possible to achieve this using PHPExcel (if not then what's the best approach)?

I tried this but its returning associative array:

$objPHPExcel = PHPExcel_IOFactory::load($file_name);
$array = $objPHPExcel->getActiveSheet();
$highestRow = $array->getHighestRow();

$data = $array->rangeToArray("A2:C$highestRow");

$data returns associative array, but that's not what I want.

Any help is greatly appreciated.

Rakesh
  • 706
  • 6
  • 10
  • Instead of using it as a excel file try using csv file. It stores data as multidimensional array. Then search for position of different variables and add them up. That is pretty easy!!! – Venkat Sai Dec 17 '17 at 07:51
  • thanks but I need to do it using excel(.xlsx) only – Rakesh Dec 17 '17 at 07:54

1 Answers1

2

You need walk the array data and sum the items manually

$objPHPExcel = PHPExcel_IOFactory::load($file_name);
$array = $objPHPExcel->getActiveSheet();
$highestRow = $array->getHighestRow();

$data = $array->rangeToArray("A2:C$highestRow");

$sumData = [];
foreach ($data as $row){
    $key = $row[0];
    if(!isset($sumData[$key])){
        $sumData[$key] = [0, 0];
    }
    $sumData[$key][0] += $row[1];
    $sumData[$key][1] += $row[2];
}

print_r($sumData);

Result:

Array
(
    [10] => Array
        (
            [0] => 15
            [1] => 4.25
        )

    [30] => Array
        (
            [0] => 6
            [1] => 1.85
        )

)
Mauricio Florez
  • 1,112
  • 8
  • 14