Place a column at the end of a collection in laravel excel

Question

I have an excel file that has a query to get the neighborhoods, and the total record for each neighborhood. But how can I put at the end, when all the data of the query is placed in the excel a custom column that is the sum or the total of the neighborhoods. This is my code

class NeighborhoodExport implements FromCollection,WithHeadings, ShouldAutoSize, WithEvents,WithTitle
{
    /* *
    * @return \Illuminate\Support\Collection
    */

    protected $start;
    protected $end;
    function __construct($start,$end) {
        $this->start = $start;
        $this->end = $end;
    }
    /**
    * @return \Illuminate\Support\Collection
    */
    public function title(): string
    {
        return 'Neighborhood';
    }
    public function collection()
    {
        return DB::table('users')->select("neighborhood"),DB::raw("COUNT(neighborhoods) as count"))
        ->whereBetween('created_at', [$this->start, $this->end])
        ->groupBy('neighborhood')->get();

    }

    public function headings(): array
    {
        return [
        'Neighborhood',
        'Count',
        ];
    }
    public function registerEvents(): array
    {
        return [
            AfterSheet::class    => function(AfterSheet $event) {
                $cellRange = 'A1:B1'; // All headers
                $styleArray = [
                    'font' => [
                        'name'      =>  'Calibri',
                        'size'      =>  11,
                        'bold'      =>  false,
                        'color' => ['argb' => 'FFFFFF'],
                    ],
                      //Set background style
                      'fill' => [
                        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                        'startColor' => [
                            'rgb' => '000000',
                         ]
                    ],
                ];
                $event->sheet->getDelegate()->getStyle($cellRange)->applyFromArray($styleArray);
              },
        ];

    }
}

There is a example of the column total in the excel. Just like this

did you read the documentation of the package you are using? It's pretty explicative — Giacomo M, Dec 15 '20 at 21:04

score 0 · Accepted Answer · answered Dec 15 '20 at 21:51

I found in the documentaion the append event on the function registerEvents. This is how my code looks now

 public function registerEvents(): array
    {
        return [
            AfterSheet::class    => function(AfterSheet $event) {
                $cellRange = 'A1:B1'; // All headers
                $styleArray = [
                    'font' => [
                        'name'      =>  'Calibri',
                        'size'      =>  11,
                        'bold'      =>  false,
                        'color' => ['argb' => 'FFFFFF'],
                    ],
                      //Set background style
                      'fill' => [
                        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                        'startColor' => [
                            'rgb' => '000000',
                         ]
                    ],
                ];
 $total = User::select('neighborhood')->whereBetween('created_at', [$this->start, $this->end])->count();
    $event->sheet->appendRows(array(
                    array('Total', $total),
                    //....
                ), $event);
                $event->sheet->getDelegate()->getStyle($cellRange)->applyFromArray($styleArray);
              },
        ];

https://docs.laravel-excel.com/2.1/export/rows.html#append-row

Place a column at the end of a collection in laravel excel

1 Answers1