How can I add title and sum value of column in laravel excel version 3?

Question

I get reference from here : https://laravel-excel.maatwebsite.nl/docs/3.0/getting-started/basics

So I use version 3

My controller like this :

public function exportToExcel(Request $request)
{
    $data = $request->all();
    $exporter = app()->makeWith(SummaryExport::class, compact('data'));   
    return $exporter->download('Summary.xlsx');
}

My script export to excel like this :

namespace App\Exports;
use App\Repositories\ItemRepository;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithHeadings;
class SummaryExport implements FromCollection, WithHeadings {
    use Exportable;
    protected $itemRepository;
    protected $data;
    public function __construct(ItemRepository $itemRepository, $data) {
        $this->itemRepository = $itemRepository;
        $this->data = $data;
    }
    public function collection()
    {
        $items = $this->itemRepository->getSummary($this->data);
        return $items;
    }
    public function headings(): array
    {
        return [
            'No',
            'Item Number',
            'Sold Quantity',
            'Profit'
        ];
    }
}

If the script executed, the result like this :

I want to add some description or title above the table and I want to sum sold quantity column and profit column

So I want the result like this :

I had read the documentation and search in the google, but I don't find the solution

Is there anyone who can help?

Update

From this reference : https://laravel-excel.maatwebsite.nl/docs/3.0/export/extending

I try add :

....
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\BeforeExport;
use Maatwebsite\Excel\Events\BeforeWriting;
use Maatwebsite\Excel\Events\BeforeSheet;
use Maatwebsite\Excel\Events\AfterSheet;
class SummaryExport implements FromCollection, WithHeadings, WithColumnFormatting, ShouldAutoSize, WithEvents
{
    ...
    public function registerEvents(): array
    {
        return [
            BeforeExport::class  => function(BeforeExport $event) {
                $event->writer->setCreator('Patrick');
            },
            AfterSheet::class    => function(AfterSheet $event) {
                $event->sheet->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);

                $event->sheet->styleCells(
                    'B2:G8',
                    [
                        'borders' => [
                            'outline' => [
                                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
                                'color' => ['argb' => 'FFFF0000'],
                            ],
                        ]
                    ]
                );
            },
        ];
    }
}

In my script above

But there exist error like this :

Method Maatwebsite\Excel\Sheet::styleCells does not exist
Method Maatwebsite\Excel\Sheet::setOrientation does not exist.
Method Maatwebsite\Excel\Writer::setCreator does not exist.

How can I solve the error?

You can try to use `append` and `prepend` row functions to add your desired extra descriptions along with the extra profit total value: https://laravel-excel.maatwebsite.nl/docs/2.1/export/rows — user3574492, Aug 04 '18 at 13:45
In that case you're going to use events to trigger off something after the sheet is ready: https://laravel-excel.maatwebsite.nl/docs/3.0/export/extending — user3574492, Aug 04 '18 at 14:08
@user3574492 Okay thanks. I will try it. But please answer this question with the script. So I can accept your answer — moses toh, Aug 04 '18 at 14:15
Give it a go and then we'll take it from there. I can't do it for you. — user3574492, Aug 04 '18 at 14:18
@user3574492 I had try it. There exist error : `Method Maatwebsite\Excel\Writer::setCreator does not exist` — moses toh, Aug 04 '18 at 14:28
Try https://laravel-excel.maatwebsite.nl/docs/3.0/export/from-view — Mahbub, Aug 04 '18 at 17:36
@user3574492 Seems you can help me again. Look at this : https://stackoverflow.com/questions/51733202/how-can-i-set-text-align-right-in-the-column-on-the-laravel-excel-maatwebsite — moses toh, Aug 08 '18 at 23:42
any solution bro? i want to get sum like this. (stock + order Qty+ goods on the way Qty)/ Qty per day = estimated selling — M Amir Shahzad, May 02 '19 at 12:27

score 2 · Answer 1 · answered Mar 14 '19 at 21:58

You gotta instantiate an array like this:-

$export = [];

Then you have to push your data in it using array_push:-

array_push($export, [
                    'No' => '',
                    'item'=>'',
                    'sold' =>'',
                    'profit' => ''
                ]);

and then you can append your calculations like this:-

array_push($export,[' ','Items Count:', '=COUNTA(C2:C'.$Count.')' ,' ','Profit Sum:', '=SUM(D2:D'.$Count.')']);

while the $count = count($array+1) if you add any headings.

and you can use your normal cells functions.

score 0 · Answer 2 · answered Mar 09 '22 at 11:03

In 2022 with latest version 3.1, this can be done in a very transparent and straight forward way.

1. For title/header row with column titles, you can use the official method

public function headings():array{
    return [
        'Column 1',
        'Column 2'
    ];
}

For this to work, add WithHeadings to class implements section.

Official docs: https://docs.laravel-excel.com/3.1/exports/mapping.html#adding-a-heading-row

2. To add a "summary" row, or any other rows after the end of your dataset, you can add such rows in prepareRows($rows) function

public function prepareRows($rows){
  $sum = 0;
  foreach($rows as $row)$sum+=$row->column_2;
  $rows[]=[
    'is_summary'=>true,
    'sum_column_1'=>$sum
  ]
}

Note: $rows parameter is an array.

then in the map($row) function add logic to differentiate between normal data row and summary row:

public function map($row){
  if(isset($row['is_summary']) && $row['is_summary']===true){
    //Return a summary row
    return [
      'Total sum:',
      $row['sum_column_1']
    ];
  }else{
    //Return a normal data row
    return [
      $row->column_1,
      $row->column_2
    ];
  }
}

For map function to work, add WithMapping to the implements section of the class.

Official docs:

Apart from my heading row, this just returns an empty excel sheet for me. I am using `FromArray` — warmwhisky, Jan 13 '23 at 07:48

score 0 · Answer 3 · answered Dec 14 '22 at 18:18

While the previous suggestion of using prepareRows might fit your use case, it comes with the drawback that styling that footer row is not possible.

Instead, you could do the following:

public function collection()
{
    return collect([
        ...$this->itemRepository->getSummary($this->data),
        $this->itemRepository->getTotal($this->data)
    ]);
}

How can I add title and sum value of column in laravel excel version 3?

3 Answers3

Linked