Formatting exported excel file

You can add the following at the start of your class to simplify event binding:

use RegistersEventListeners;

Then add a method like this:

public static function afterSheet(AfterSheet $event)
    {
        $event->getSheet()->getDelegate()->getStyle('A1:G1')->getFont()->setName('Calibri')->setSize(14);
    }

For the example you can do the following in the same method:

$styleArray = [/* your style definition */];
$event->getSheet()->getDelegate()->getStyle('A1:G1')->applyFromArray($styleArray);

Hi SKAUK.

Thank you very much for your points,

I finally applied the following solution, since I needed to format some intermediate rows.

I still need to add all border style to all the cell of the excel. How can I edit $styleArray to create the border I need?

<?php

namespace App\Exports;

use App\Member;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings; 
use Maatwebsite\Excel\Concerns\ShouldAutoSize;

use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;

class MembersExport implements FromCollection, WithHeadings, ShouldAutoSize, WithEvents
{
    
    public function collection()
    {
        return Member::all();
    }
    
    public function headings(): array
    {
        return 
        [
            'head1',
            'head2',
            'head3',
            'head4',
            'head5',
            'head6',
            'head7',
        ];
    }
    
     public function registerEvents(): array
    {
        
        $styleArray = [
        'font' => [
        'bold' => true,
        ]
        ];
            
        
        
        return [
            AfterSheet::class    => function(AfterSheet $event) use ($styleArray)
            {
                $cellRange = 'A1:G1'; // All headers
                //$event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setName('Calibri');
                $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(14);
                $event->sheet->getStyle($cellRange)->ApplyFromArray($styleArray);
                $event->sheet->getStyle('A9:G9')->ApplyFromArray($styleArray);
                $event->sheet->getStyle('A12:G12')->ApplyFromArray($styleArray);
                $event->sheet->getStyle('A19:G19')->ApplyFromArray($styleArray);
                $event->sheet->getStyle('A27:G27')->ApplyFromArray($styleArray);
                $event->sheet->getStyle('A31:G31')->ApplyFromArray($styleArray);
                $event->sheet->getStyle('A35:G35')->ApplyFromArray($styleArray);
                $event->sheet->getStyle('A36:G36')->ApplyFromArray($styleArray);
                $event->sheet->getStyle('A42:G42')->ApplyFromArray($styleArray);
                $event->sheet->getStyle('A43:G43')->ApplyFromArray($styleArray);
            },
        ];
    }
    
    
    
}

I guess something like this:

$event->getSheet()->getDelegate()->getStyle('A1:G1')->applyFromArray(
    array(
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array('rgb' => '000000')
            )
        )
    )
);

Hi SKAUK,

I finished with the project. You helped me a lot! Thanks again!

@konrms I got the same problem. can you help me how to fix this problem? I just wanna add borders to the table. I'm trying like this but no luck

    return [
            AfterSheet::class    => function(AfterSheet $event)
            {
                $event->getSheet()->getDelegate()->getStyle('A1:G1')->applyFromArray(
                    array(
                        'borders' => array(
                            'allborders' => array(
                                'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                                'color' => array('rgb' => '000000')
                            )
                        )
                    )
                );
            }
        ];

any help that would be great. Thanks!

Hi @deepu07

I have declared my border style as below:

//border style
		$styleArray = [
				'borders' => [
					'outline' => [
						'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
					//'color' => ['argb' => 'FFFF0000'],
						],
					],
				];	

This is later used like this:

 return [
            AfterSheet::class => function(AfterSheet $event) use ($styleArray, $styleArray1, $styleArray2, 
            $styleArray3, $styleArray4 , $styleArray5)

To help you more, I have attached the solution I applied on the whole, rearding the styling I have used. I have formatted headings, added borders and fixed width. Each type of format I have used ($styleArray1, $styleArray2, ...) is well declared.

I hope I have helped!

<?php

namespace App\Exports;

use App\Member;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings; 
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;

class MembersExport implements FromCollection, WithHeadings, WithEvents//, ShouldAutoSize  
{
    public function collection()
    {
        return Member::all();
    }
	
	public function headings(): array
    {
        return 
		[
            'HEAD1',
            'HEAD2',
            'HEAD3',
            'HEAD4',
            'HEAD5',
	    'HEAD6',
	    'HEAD7',
        ];
    }
	
	 public function registerEvents(): array
    {

		//border style
		$styleArray = [
				'borders' => [
					'outline' => [
						'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
					//'color' => ['argb' => 'FFFF0000'],
						],
					],
				];	
		
		//font style	
		$styleArray1 = [
						'font' => [
							'bold' => true,
							]
						];
		
		//column  text alignment
		$styleArray2 = array(
			'alignment' => array(
				'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
				 )
		);				
		
		//$styleArray3 used for vertical alignment 
		$styleArray3 = array(
			'alignment' => array(
				'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
				 )
		);
		

		$styleArray4 = array(
						'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
        'startColor' => [
        'argb' => 'FFA0A0A0',
        ],
        'endColor' => [
            'argb' => 'FFFFFFFF',
        ]]
					);
		
		$styleArray5 = array(
						'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        
        'startColor' => [
            'argb' => 'E0E0E0',
        ]]);
		
		
			
				
        return [
            AfterSheet::class => function(AfterSheet $event) use ($styleArray, $styleArray1, $styleArray2, 
            $styleArray3, $styleArray4 , $styleArray5)
			{
                		$cellRange = 'A1:G1'; // All headers
                		$event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(13);
				$event->sheet->getStyle($cellRange)->ApplyFromArray($styleArray);
				$event->sheet->getStyle('A9:G9')->ApplyFromArray($styleArray);
				$event->sheet->getStyle('A12:G12')->ApplyFromArray($styleArray);
				$event->sheet->getStyle('A19:G19')->ApplyFromArray($styleArray);
				$event->sheet->getStyle('A27:G27')->ApplyFromArray($styleArray);
				$event->sheet->getStyle('A31:G31')->ApplyFromArray($styleArray);
				$event->sheet->getStyle('A35:G35')->ApplyFromArray($styleArray);
				$event->sheet->getStyle('A36:G36')->ApplyFromArray($styleArray);
				$event->sheet->getStyle('A42:G42')->ApplyFromArray($styleArray);
				$event->sheet->getStyle('A43:G43')->ApplyFromArray($styleArray);
				
							
//Heading formatting...
			 	$event->getSheet()->getDelegate()->getStyle('A1:G1')->applyFromArray($styleArray);						
				$event->getSheet()->getDelegate()->getStyle('A1:G1')->applyFromArray($styleArray1);
							
							
							//used for making bold
				$event->getSheet()->getDelegate()->getStyle('A9:G9')->applyFromArray($styleArray1);
				$event->getSheet()->getDelegate()->getStyle('A12:G12')->applyFromArray($styleArray1);
				$event->getSheet()->getDelegate()->getStyle('A19:G19')->applyFromArray($styleArray1);
				$event->getSheet()->getDelegate()->getStyle('A27:G27')->applyFromArray($styleArray1);
				$event->getSheet()->getDelegate()->getStyle('A31:G31')->applyFromArray($styleArray1);
				$event->getSheet()->getDelegate()->getStyle('A35:G35')->applyFromArray($styleArray1);
				$event->getSheet()->getDelegate()->getStyle('A36:G36')->applyFromArray($styleArray1);
				$event->getSheet()->getDelegate()->getStyle('A42:G42')->applyFromArray($styleArray1);
				$event->getSheet()->getDelegate()->getStyle('A43:G43')->applyFromArray($styleArray1);
							
							
							//column width set							
				$event ->sheet-> getDelegate()->getColumnDimension('A')->setWidth(65);
				$event ->sheet-> getDelegate()->getColumnDimension('B')->setWidth(64);
				$event ->sheet-> getDelegate()->getColumnDimension('C')->setWidth(13);
				$event ->sheet-> getDelegate()->getColumnDimension('F')->setWidth(12);
				$event ->sheet-> getDelegate()->getColumnDimension('G')->setWidth(11);
							
							
							//D & E column width set to 17
				$columns = ['D', 'E'];
					foreach ($columns as $column) {
						$event ->sheet-> getDelegate()->getColumnDimension($column)->setWidth(17);	
							}
							
							//D1 & E1 text wrapping...
				$event ->sheet->getStyle('D1')->getAlignment()->setWrapText(true);	
				$event ->sheet->getStyle('E1')->getAlignment()->setWrapText(true);	
							
							//text center columns...
				$event ->sheet->getStyle('C1:C43')->applyFromArray($styleArray2);
				$event ->sheet->getStyle('D1:D43')->applyFromArray($styleArray2);
				$event ->sheet->getStyle('E1:E43')->applyFromArray($styleArray2);
				$event ->sheet->getStyle('F1:F43')->applyFromArray($styleArray2);
				$event ->sheet->getStyle('G1:G43')->applyFromArray($styleArray2);
							
							
			$event ->sheet->getStyle('A1:G1')->applyFromArray($styleArray3);  //headings vertical alignment 
							
							
							
							//sums color formatting...
				$event ->sheet->getStyle('A1:G1')->applyFromArray($styleArray4);
				$event ->sheet->getStyle('A9:G9')->applyFromArray($styleArray5);
				$event ->sheet->getStyle('A12:G12')->applyFromArray($styleArray5);
			        $event ->sheet->getStyle('A19:G19')->applyFromArray($styleArray5);
				$event ->sheet->getStyle('A27:G27')->applyFromArray($styleArray5);
				$event ->sheet->getStyle('A31:G31')->applyFromArray($styleArray5);
				$event ->sheet->getStyle('A35:G35')->applyFromArray($styleArray5);
				$event ->sheet->getStyle('A36:G36')->applyFromArray($styleArray5);
				$event ->sheet->getStyle('A42:G42')->applyFromArray($styleArray5);
				$event ->sheet->getStyle('A43:G43')->applyFromArray($styleArray5);
					//$row->setBackground('#CCCCCC'); 
							

		    },
        ];
    }
	
	
	
}

Hi @deepu07 ,

This is a case-sensitive issue. Please read below.

$styleBorder = [
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                            'color' => ['rgb' => '000000'],
                        ],
                    ],
                ];