how to use excel sum formula in php phpspreadsheet?

Question

I am using PhpSpreasdheet php library. I have done almost everything i want to sum the particular column and want to show total of that column.. See My output is coming below:-

My expected output like below:-

I have tried below code:-

$spreadsheet = new Spreadsheet();
$Excel_writer = new Xlsx($spreadsheet);
$spreadsheet->setActiveSheetIndex(0);
$activeSheet = $spreadsheet->getActiveSheet();

$activeSheet->setCellValue('A1', 'Location');
$activeSheet->setCellValue('B1', 'Media Vehicle');
$activeSheet->setCellValue('C1', 'Dimension');
$activeSheet->setCellValue('D1', 'Amount');

$spreadsheet->getActiveSheet()->setAutoFilter('A1:D1');
    $locations = DB::table('locations')->get();
    $locations = json_decode(json_encode($locations),true);
    $i = 2;
    foreach($locations as $location){
        $activeSheet->setCellValue('A'.$i , $location['location']);
        $activeSheet->setCellValue('B'.$i , $location['media_vehicle']);
        $activeSheet->setCellValue('C'.$i , $location['dimension']);
        $activeSheet->setCellValue('D'.$i , $location['amount']);
        $i++;
    }

    $samplepath = storage_path('/excels/sampleExcel'.str_random(5).'.xlsx');
    $Excel_writer->save($samplepath);
    echo 'saved'; die;

I want the total of amount column. I want to make dynamic. if in future it will be 10 rows so it will calculate 10 rows of amount column count.

score 8 · Answer 1 · answered Feb 20 '20 at 08:30

8

In phpspreadsheet you can use Excel formulas. All you need is the range of numbers you want to sum.

$SUMRANGE = 'D2:D'.$i;
$activeSheet->setCellValue('D'.$i , '=SUM($SUMRANGE)');

answered Feb 20 '20 at 08:30

Sarah Trees

822
12
28

Ashu · Answer 2 · 2019-03-01T07:13:32.597

1

you have to take total 1st :

1st solution:

 $total = 0;
 foreach($locations as $location){
    $activeSheet->setCellValue('A'.$i , $location['location']);
    $activeSheet->setCellValue('B'.$i , $location['media_vehicle']);
    $activeSheet->setCellValue('C'.$i , $location['dimension']);
    $activeSheet->setCellValue('D'.$i , $location['amount']);
    $total = $total+$location['amount'];
    $i++;
}
//here your $i val already incremented in foreach() loop
$activeSheet->setCellValue('C'.$i , "Total");
$activeSheet->setCellValue('D'.$i , $total);

2nd solution:

$activeSheet->setCellValue('C'.$i , "Total");
$spreadsheet->getActiveSheet()->getCell('D'.$i)->getCalculatedValue();

I was refer : https://phpspreadsheet.readthedocs.io/en/stable/topics/calculation-engine/

edited Mar 01 '19 at 07:13

answered Mar 01 '19 at 07:00

Ashu

1,320
2
10
24

i know this is alternative but i want to use excel function Sum in this – kunal Mar 01 '19 at 07:02
You can ref: https://phpspreadsheet.readthedocs.io/en/stable/topics/calculation-engine/ – Ashu Mar 01 '19 at 07:05
second one gives error Call to undefined method PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::getActiveSheet() – kunal Mar 01 '19 at 07:10
yes i also tried but total amount columns get empty – kunal Mar 01 '19 at 07:14
we have just calcuate the value we need to insert the value also – kunal Mar 01 '19 at 07:20
Try this : $activeSheet->setCellValue('D'.$i , $spreadsheet->getActiveSheet()->getCell('D'.$i)->getCalculatedValue()); – Ashu Mar 01 '19 at 08:27
not working its still coming same results nothing is printing – kunal Mar 01 '19 at 10:01
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/189250/discussion-between-ashu-and-kunal). – Ashu Mar 01 '19 at 10:37

score 1 · Answer 3 · answered Feb 28 '21 at 13:30

It will help;

$i = 2;
$first_i = $i;
foreach($locations as $location){
    $activeSheet->setCellValue('A'.$i , $location['location']);
    $activeSheet->setCellValue('B'.$i , $location['media_vehicle']);
    $activeSheet->setCellValue('C'.$i , $location['dimension']);
    $activeSheet->setCellValue('D'.$i , $location['amount']);
    $i++;
}
$last_i = $i - 1;
$sumrange = 'D' . $first_i . ':D' . $last_i;
$activeSheet->setCellValue('C' . $i, 'Total:');
$activeSheet->setCellValue('D' . $i, '=SUM(' . $sumrange . ')');

how to use excel sum formula in php phpspreadsheet?

3 Answers3