Laravel / Advanced / Export to Excel

Export to excel

1. Package required


                    composer require maatwebsite/excel

2. Configure the Package

config/app.php


                    'providers' => [
                        // ...
                        Maatwebsite\Excel\ExcelServiceProvider::class,
                    ],

                    'aliases' => [
                        // ...
                        'Excel' => Maatwebsite\Excel\Facades\Excel::class,
                    ],

Next, publish the package configuration file by running the following command:


                        php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

3. Define export class

app/Exports/BookingExport.php


                    namespace App\Exports;
 
                    use App\Models\Booking;

                    use Maatwebsite\Excel\Concerns\FromCollection;

                      
                    class BookingExport implements FromCollection 
                    {
                        
                        public function __construct($data)
                        {
                            $this->data=$data;
                        }

                    
                        public function collection()
                        {
                            return $this->data;
                        }

                    }

4. in controller


                      
                      use Maatwebsite\Excel\Facades\Excel;
                      use App\Exports\BookingExport;
                      use App\Models\Booking;

                      $this->data['gridData']=Booking::get(); 
                      return Excel::download(new BookingExport($this->data['gridData']), 'bookings.xlsx');

2. Formatting (Optional)

1. Excel Heading


                    public function headings():array{
                        return[
                            'BookingID',
                            'Booking Date' ,            
                            'AdvRegNo',
                            'Name',
                            'Father Name',
                            'House Name',
                            'Gender',
                            'Age',
                            'Education',
                            'Job',
                            'Parish Name',
                            'Diocese Name',
                            'Pincode',
                            'Mobile',
                            'Email',
                            'Religion',
                            'Denomination',
                            'RsPaid',
                            'EntryBy',
                            'Remarks',  
                            'Retreat',            
                            'Retreat Place',
                            'Retreat Start' ,
                            'Retreat End' ,           
                            'Batch',
                            'Cancellation Status',
                            'Cancellation Amount'
                            

                        ];
                    }

Required fields from the collection



                    public function map($row): array
                    {
                        $data=[
                            $row->adv_reg_no,
                            date("d-m-Y",strtotime($row->book_date)),
                            $row->receipt_no,
                            $row->candidate_name,
                            $row->father_name,
                            $row->house_name,
                            $row->sex,
                            $row->age,
                            $row->education,
                            $row->job,
                            $row->other_parish!='' ? $row->other_parish : $row->parish_name,
                            $row->other_diocese!='' ? $row->other_diocese : $row->diocese_name,
                            $row->pincode,
                            $row->candidate_mobile_no,
                            $row->candidate_email,
                            $row->religions_name!='' ? $row->religions_name : '',
                            $row->classification_name!='' ? $row->classification_name : $row->other_diocese,
                            $row->paid_amount,
                            $row->entry_name,
                            $row->remarks,
                            $row->course_name,
                            $row->center,
                            date("d-m-Y",strtotime($row->start_date)),
                            date("d-m-Y",strtotime($row->end_date)),
                            $row->batch_name,
                            $row->cancel_booking,
                            $row->cancel_booking_amount

                        ];
                        return $data; 
                }

complete code



              namespace App\Exports;
 
use App\Models\Booking;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithMapping;
 
class BookingExport implements FromCollection,WithHeadings, WithMapping
{
    /**
    * @return  \Illuminate\Support\Collection
    */ 
    public function __construct($data)
    {
        $this->data=$data;
    }
    public function headings():array{
        return[
            'BookingID',
            'Booking Date' ,            
            'AdvRegNo',
            'Name',
            'Father Name',
            'House Name',
            'Gender',
            'Age',
            'Education',
            'Job',
            'Parish Name',
            'Diocese Name',
            'Pincode',
            'Mobile',
            'Email',
            'Religion',
            'Denomination',
            'RsPaid',
            'EntryBy',
            'Remarks',  
            'Retreat',            
            'Retreat Place',
            'Retreat Start' ,
            'Retreat End' ,           
            'Batch',
            'Cancellation Status',
            'Cancellation Amount'
            

        ];
    } 

    public function map($row): array
        {
            $data=[
                $row->adv_reg_no,
                date("d-m-Y",strtotime($row->book_date)),
                $row->receipt_no,
                $row->candidate_name,
                $row->father_name,
                $row->house_name,
                $row->sex,
                $row->age,
                $row->education,
                $row->job,
                $row->other_parish!='' ? $row->other_parish : $row->parish_name,
                $row->other_diocese!='' ? $row->other_diocese : $row->diocese_name,
                $row->pincode,
                $row->candidate_mobile_no,
                $row->candidate_email,
                $row->religions_name!='' ? $row->religions_name : '',
                $row->classification_name!='' ? $row->classification_name : $row->other_diocese,
                $row->paid_amount,
                $row->entry_name,
                $row->remarks,
                $row->course_name,
                $row->center,
                date("d-m-Y",strtotime($row->start_date)),
                date("d-m-Y",strtotime($row->end_date)),
                $row->batch_name,
                $row->cancel_booking,
                $row->cancel_booking_amount

            ];
            return $data; 
    }


    public function collection()
    {
        return $this->data;
    }
}

3. Cell Formatting (Optional)



              public function registerEvents(): array
        {
                return [
                    AfterSheet::class    => function(AfterSheet $event) {


                        //main heading
                        $cellRange = 'A1:H1'; // All headers


                        $event->sheet->getDelegate()->mergeCells($cellRange);

                        $event->sheet->getDelegate()
                            ->getStyle('A1')
                            ->getAlignment()
                            ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

                       $event->sheet->getDelegate()->setCellValue('A1', 'QUOTATION');


                        $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(20);
        
                        $event->sheet->getDelegate()->getStyle($cellRange)
                                ->getFill()
                                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                                ->getStartColor()
                                ->setARGB('ffffff');

                                $event->sheet->getStyle($cellRange)->applyFromArray([
                                    'font'=>[
                                        'bold'=>true,
                                        'color' => ['argb' => '0195da'],
                                    ],                
                                ]);
                   

                    
        
                    },
                ];
        }

Complete code



      namespace App\Exports;

//use App\Models\Booking;

use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;

class ProposalExport implements FromArray, WithHeadings, WithEvents{
    private $data;
    public function __construct($data) 
    {
        $this->data = $data;
    }

    public function headings():array{
        return[

            'Item Description',
            'Unit Rate',
            'Quantity',
            'Unit',
            'Total',
            'GST%',
            'GST Amount',
            'Line Total',
           

        ];
    } 

    public function map($row): array
    {
        $data=[
            $row['item_name'],
            $row['s_price'],
            $row['quantity'],
            $row['unit'],
            $row['sp_excluding_tax'],
            $row['tax_rate'],
            $row['sp_tax_amount'],
            $row['sp_including_tax']
            

        ];
        return $data; 
    }

    public function array(): array
    {

       // $this->data['response']=array();

        $this->data['response'][]=[
            'item_name'=> 'Item Description',
            's_price'=> 'Price',
            'quantity' =>'Quantity',
            'unit'=>'Unit',
            'sp_excluding_tax'=>'Total',
            'tax_rate'=>'Tax Rate',
            'sp_tax_amount'=>'Tax Amount',
            'sp_including_tax'=>'Line Total'
            
         ];

        $categories=\App\Models\LeadProposalCategory::join('lead_proposals', 'lead_proposals.id', 'lead_proposal_categories.proposal_id')
            ->join('boqcategories','boqcategories.id', 'lead_proposal_categories.category_id')                
            ->select('lead_proposal_categories.*', 'boqcategories.name as product_title')   
            ->where('lead_proposal_categories.proposal_id', $this->data['proposal_id'])
            ->get()->toArray();


            
            if(isset($categories)){
                foreach( $categories as $key=>$cRow){
                    
                    
                    $this->data['response'][]=[
                       'item_name'=> $cRow['product_title'],
                       'type'=> 'category'
                    ];

                    $item=\App\Models\LeadProposalItem::join('products', 'products.id', 'lead_proposal_items.sku_id')
                    ->where('lead_proposal_items.proposal_category_id', $cRow['id'])
                    ->select('lead_proposal_items.*','products.name as product_title')
                    ->get()->toArray();

                    foreach($item as $iRow){
                          
                            
                        $subItems=[
                            
                            'item_name'=>$iRow['product_title'],
                            's_price' =>isset($iRow['s_price']) ? $iRow['s_price'] : '',
                            'quantity'=>$iRow['quantity'],
                            'unit'=>$iRow['unit'],
                            'sp_excluding_tax'=>isset($iRow['sp_excluding_tax']) ? $iRow['sp_excluding_tax'] : '',
                            // 'description'=>$iRow['description'],
                            // 'g_price' =>isset($iRow['g_price']) ? $iRow['g_price'] : '',
                            // 'vendor_id' =>isset($iRow['vendor_id']) ? $iRow['vendor_id'] : '',
                            'tax_rate' =>isset($iRow['tax_rate']) ? $iRow['tax_rate'] : '',
                            // 'gp_including_tax' =>isset($iRow['gp_including_tax']) ? $iRow['gp_including_tax'] : '',
                            // 'margin_rate' =>isset($iRow['margin_rate']) ? $iRow['margin_rate'] : '',
                           
                            'sp_tax_amount' =>isset($iRow['sp_tax_amount']) ? $iRow['sp_tax_amount'] : '',
                            'sp_including_tax' =>isset($iRow['sp_including_tax']) ? $iRow['sp_including_tax'] : '',
                           

                        ];

                        $this->data['response'][]=$subItems;
                    }


                }
            }


            

         
        return  $this->data['response'];
    }

    

    public function registerEvents(): array
    {
            return [
                AfterSheet::class    => function(AfterSheet $event) {


                    //main heading
                    $cellRange = 'A1:H1'; // All headers


                    $event->sheet->getDelegate()->mergeCells($cellRange);

                    $event->sheet->getDelegate()
                        ->getStyle('A1')
                        ->getAlignment()
                        ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

                   $event->sheet->getDelegate()->setCellValue('A1', 'QUOTATION');


                    $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(20);
    
                    $event->sheet->getDelegate()->getStyle($cellRange)
                            ->getFill()
                            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                            ->getStartColor()
                            ->setARGB('ffffff');

                            $event->sheet->getStyle($cellRange)->applyFromArray([
                                'font'=>[
                                    'bold'=>true,
                                    'color' => ['argb' => '0195da'],
                                ],                
                            ]);





                   
                   


                   foreach( $this->data['response'] as $key=>$row){
                        $index=$key+2;
                       
                        if(isset($row['type']) && $row['type']=='category'){
                        $cellRange = 'A'.$index.':H'.$index; // All headers


                        $event->sheet->getDelegate()->mergeCells($cellRange);

                        $event->sheet->getDelegate()
                            ->getStyle('A'.$index)
                            ->getAlignment()
                            ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);

                       // $event->sheet->getDelegate()->setCellValue('A', 'STUDENT TERMINATION REPORT');


                        $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(12);
        
                        $event->sheet->getDelegate()->getStyle($cellRange)
                                ->getFill()
                                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                                ->getStartColor()
                                ->setARGB('0195da');

                                $event->sheet->getStyle($cellRange)->applyFromArray([
                                    'font'=>[
                                        'bold'=>true,
                                        'color' => ['argb' => 'ffffff'],
                                    ],                
                                ]);


                                // $event->sheet->getDelegate()->getStyle('A1:C1')
                                // ->getAlignment()
                                // ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

                                 $event ->sheet-> getDelegate()->getColumnDimension('A')->setWidth(100);

                        }          
                    
                }

                   // $cellRange = 'A1:E1'; // All headers

                
    
                },
            ];
    }


}

BGCOLOR


public function registerEvents(): array
    {
        return [
            AfterSheet::class    => function(AfterSheet $event) {
  
                $event->sheet->getDelegate()->getStyle('A1:C1')
                        ->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()
                        ->setARGB('DD4B39');
  
            },
        ];
    }

    

    public function registerEvents(): array
        {
            return [
                AfterSheet::class    => function(AfterSheet $event) {
      
                    $event->sheet->getDelegate()->getStyle('A1:H1')
                            ->getFill()
                            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                            ->getStartColor()
                            ->setARGB('0b2652');

                            $event->sheet->getStyle('A1:H1')->applyFromArray([
                                'font'=>[
                                    'bold'=>true,
                                    'color' => ['argb' => 'ffffff'],
                                ],                
                            ]);

                            $event ->sheet-> getDelegate()->getColumnDimension('A')->setWidth(25);
                            $event ->sheet-> getDelegate()->getColumnDimension('B')->setWidth(25);
                            $event ->sheet-> getDelegate()->getColumnDimension('C')->setWidth(25);
                            $event ->sheet-> getDelegate()->getColumnDimension('D')->setWidth(25);
                            $event ->sheet-> getDelegate()->getColumnDimension('E')->setWidth(25);
                            $event ->sheet-> getDelegate()->getColumnDimension('F')->setWidth(25);
                            $event ->sheet-> getDelegate()->getColumnDimension('G')->setWidth(25);
                            $event ->sheet-> getDelegate()->getColumnDimension('H')->setWidth(25);
      
                },
            ];
        }