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?

Kowloon

You can try to use append and prepend row functions to add your desired extra descriptions along with the extra profit total value: laravel-excel.maatwebsite.nl/docs/2.1/export/rows – Mook

@Mook I use version 3. No version 2 – Kowloon

In that case you're going to use events to trigger off something after the sheet is ready: laravel-excel.maatwebsite.nl/docs/3.0/export/extending – Mook

@Mook Okay thanks. I will try it. But please answer this question with the script. So I can accept your answer – Kowloon

Give it a go and then we'll take it from there. I can't do it for you. – Mook

@Mook Okay. No problem – Kowloon

@Mook I had try it. There exist error : Method Maatwebsite\Excel\Writer::setCreator does not exist – Kowloon

Post all of your code and show us what you've tried – Mook

Try laravel-excel.maatwebsite.nl/docs/3.0/export/from-view – Benediction

@Mook I had update my question – Kowloon

@Benediction Great solution. Thanks – Kowloon

@Mook Seems you can help me again. Look at this : #51733702 – Kowloon

any solution bro? i want to get sum like this. (stock + order Qty+ goods on the way Qty)/ Qty per day = estimated selling – Ri

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.

Marta

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

Reboant

Apart from my heading row, this just returns an empty excel sheet for me. I am using FromArray – Acaulescent

Answer 3

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)
    ]);
}

Lolly