CODEWITHSUNDEEP
phpexcellaravelmaatwebsite-excel
FullStackOfPancakes
FullStackOfPancakes

Reputation: 1381

Set column cell value as formula in dynamic range Maatwebsite/Excel 2.1.0

I'm stumped here and hoping someone out there has an answer or can point me in the right direction!

Using: Laravel 5.7 | Maatwebsite/Excel 2.1.0

What I'm trying to accomplish:

I want to insert a basic formula into a column across a dynamic number of rows when exporting. I know I can use

$sheet->setCellValue('H2','=SUM(G2*F2)');

to set a specific cell value. I'm assuming the answer lies somewhere here

$sheet->cells('H2:H'.$numRows, function($cells) {

    // manipulate the range of cells

});

but we all know what happens when you assume something. Does anyone have any insight on this at all? Thank you in advance!

Upvotes: 0

Views: 8208

Answers (2)

brwa ata
brwa ata

Reputation: 61

This may help you

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;


Class ExportData implements FromCollection, WithHeadings, ShouldAutoSize, WithEvents

public function registerEvents(): array
{
    return [
        AfterSheet::class => function(AfterSheet $event) {
            $event->sheet->setCellValue('E'. ($event->sheet->getHighestRow()+1), '=SUM(E2:E'.$event->sheet->getHighestRow().')');
        }
    ];
}

...
"Your code to export"
...

"Note:
  1.in my case the data I want to get the SUM is in "E" Column you can change it as you want

  2.You only need to implement "WithEvents" and  use "AfterSheet" other things that I implemented is for other purposes
"

Upvotes: 4

nakov
nakov

Reputation: 14288

You can iterate over each cell in the range that you have found and set it's value to the formula that you want.

$sheet->cells('H2:H'.$numRows, function($cells) {

     foreach($cells as $cell) {
         $cell->setValue('=SUM(G2*F2)');
     }

});

Or if you have custom formula for some cells in the range based on the documentation for PhpSpreadSheet which Maatwebsite uses, you can see how to set range of cells here from an array.

So for your case you can build a 1-d array and place it as columns like this:

$rowArray = [];
for($i = 0; $i <= $numOfRows; $i++) {
    $rowArray[] = '=SUM(G2*F2)'; // customize the formula if needed for specific row
}

$columnArray = array_chunk($rowArray, 1);
$sheet->fromArray(
        $columnArray,   // The data to set
        NULL,           // Array values with this value will not be set
        'H2'            // Top left coordinate of the worksheet range where
                        // we want to set these values (default is A1)
);

Upvotes: 2

Related Questions