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: laravel-excel.maatwebsite.nl/docs/2.1/export/rows — user3574492
– user3574492, Commented Aug 4, 2018 at 13:45
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 — user3574492
– user3574492, Commented Aug 4, 2018 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
– moses toh, Commented Aug 4, 2018 at 14:15

Mohamed Gabr · Accepted Answer · 2019-03-14 21:58:17Z

2

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.

answered Mar 14, 2019 at 21:58

Mohamed Gabr

7167 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ak93 · Accepted Answer · 2022-03-09 11:03:58Z

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

naghal · Accepted Answer · 2022-12-14 18:18:15Z

0

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

answered Dec 14, 2022 at 18:18

naghal

7266 silver badges24 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related