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?

Answer 1

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.

Answer 2

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:

• https://docs.laravel-excel.com/3.1/exports/mapping.html#mapping-rows
• https://docs.laravel-excel.com/3.1/exports/mapping.html#prepare-rows