Jul 14, 2020

Level 9

Advanced Format of Laravel Excel Maatwebsites for Export

I am using Maatwebsites to Export Employee profile report to Excel using Laravel-5.8

class ManagerMidYearCompleteExport implements FromCollection, ShouldAutoSize, WithHeadings, WithEvents
{
    private $headings = [
        'Staff ID', 
        'Name',
        'Gender',
        'Official Email',
        'Department',
        'HOD',
        'HRBP'
    ];

    public function collection()
    {
        $userCompany = Auth::user()->company_id;
        $userEmployee = Auth::user()->employee_id;
        $userId = Auth::user()->id;
        $employeecode = Auth::user()->employee_code;

        $identities = DB::table('appraisal_identity')->select('id')->where('company_id', $userCompany)->where('is_current', 1)->pluck('id');

    $employee_completed_goals = DB::table('hr_employees')
                 ->join('appraisal_goals', 'hr_employees.employee_code', '=', 'appraisal_goals.employee_code')
                 ->select('hr_employees.employee_code')
                 ->where('hr_employees.company_id', $userCompany)
                 ->where('hr_employees.hr_status',0)
                 ->where('appraisal_goals.is_mid_year_published', '=', '1')
                 ->where('appraisal_goals.line_manager_mid_year_approved', '=', '3')
                 ->where('appraisal_goals.appraisal_identity_id', $identities)
                 ->groupBy('hr_employees.employee_code')
                 ->get();   


        $published_goals = DB::table('hr_employees AS e')
                    ->join('hr_departments AS d','e.department_id','=','d.id')
                    ->join('hr_employees AS eh','eh.employee_code','=','d.dept_head')
                    ->join('hr_employees AS eb','eb.employee_code','=','d.hr_business_partner_id')                
                     ->select(
                        'e.employee_code as staff_id',
                        DB::raw('CONCAT(e.first_name, " ", e.last_name) AS full_name'),
                        DB::raw('IF(e.gender_code = 0, "Female", "Male") AS gender'),      
                        'e.email as official_email',
                        'd.dept_name',                                                     
                        DB::raw('CONCAT(eh.first_name, " ", eh.last_name) AS hod_name'),
                        DB::raw('CONCAT(eb.first_name, " ", eb.last_name) AS hrbp_name')
                 )                
                ->whereIn('e.employee_code', $employee_completed_goals->pluck('employee_code'))
                ->where('e.company_id', $userCompany)
                ->where('e.hr_status', 0)
                ->distinct()
                ->get();


            $published_goals = $published_goals->unique('staff_id');

                return collect($published_goals);
        } 

    public function headings() : array
    {
                            return $this->headings;	//english
    }    

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

               $cellRange = 'A1:G1'; 

                $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(14);

        },
    ];
}

}

How do I format my Export Report to something Like this:

Thank you

Brian Kidd

5 years ago

Level 27

If you have all of the correct data from your database, as much as I like Laravel Excel, it doesn't provide a way to use a template. What you should do is create an xlsx file with all of the formatting and use a library that lets you open the file, write to it, and then save as a new file.

I think you can do this with PhpSpreadsheet and there are probably others. Last thing I read from Laravel Excel was that using a template is on the roadmap but they don't have a date as to when this will happen.

Good luck.

MarianoMoreyra

5 years ago

Level 25

As @briankidd said, I think you won't be able to achieve the upper informational format that you want.

As far as the headings format with auto filter, you can do something like this:

public function registerEvents() : array
{
    return [
        AfterSheet::class    => function(AfterSheet $event) {
            $cellRange = 'A1:L1'; // All headers
            $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(14);
            $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->getColor()
                   ->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
            $event->sheet->getDelegate()->getStyle($cellRange)->getFill()
                   ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                   ->getStartColor()->setARGB('FF17a2b8');
            $event->sheet->setAutoFilter($cellRange);
        },
    ];
}

noblemfd

5 years ago

Level 9

@marianomoreyra - Please I have one more clarification.

What about if I want to have:

Title Page (e.g. Employees Report) as A1:L1
Current Date (Current Date Label as A2:B2, Current Date & Time as C2:D2) - Align left and Backcolor Yellow
Printed By: ...... K2:L2 --- Align Right
The initial All Headers that was A1:L1 to be A3:L3

Thanks

MarianoMoreyra

5 years ago

Best Answer

Level 25

@noblemfd there is no easy way of doing that.

Although it can be done, once again right at the AfterSheet event show in my previous answer and by using the underlying PhpSpreadsheet API (Laravel Excel is based on that package).

For example:

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

            // Set cell A1 with Your Title
            $event->sheet->setCellValue('A1', 'Your Title Here');

            // Set cells A2:B2 with current date
            $event->sheet->setCellValue('A2', 'Report Date:');
            $event->sheet->setCellValue('B2', now());

            $cellRange = 'B5:K5'; // All headers
            $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(14);
            $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->getColor()
                        ->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
            $event->sheet->getDelegate()->getStyle($cellRange)->getFill()
                        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                        ->getStartColor()->setARGB('FF17a2b8');
            $event->sheet->setAutoFilter($cellRange);
        },
    ];
}

You will have to change the start of the data table to a custom one. For that, you'll have to implement the WithCustomStartCell concern. (In my example, I've set it to start at B5)

You can get the idea from that snippet. For the rest, you can check the PhpSpreadsheet documentation here: https://phpspreadsheet.readthedocs.io/en/latest/topics/accessing-cells/

Hope this helps!

1 like

MarianoMoreyra

5 years ago

Level 25

Hey @noblemfd

I see that my solution worked for you.

Please, mark this as answered so others can reach this solution too.

Thanks!

1 like

noblemfd

5 years ago

Level 9

@marianomoreyra This one did not work

        // Set cell A1 with Your Title
        $event->sheet->setCellValue('A1', 'Your Title Here');

        // Set cells A2:B2 with current date
        $event->sheet->setCellValue('A2', 'Report Date:');
        $event->sheet->setCellValue('B2', now());

But only this worked

        $cellRange = 'B5:K5'; // All headers
        $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(14);
        $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->getColor()
                    ->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
        $event->sheet->getDelegate()->getStyle($cellRange)->getFill()
                    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                    ->getStartColor()->setARGB('FF17a2b8');
        $event->sheet->setAutoFilter($cellRange);

MarianoMoreyra

5 years ago

Level 25

@noblemfd what was the error with the section that didn't work?

I used exactly the same code in one of my apps before submitting the solution to you and it worked.

Maybe adding the getDelegate() method like in the rest?

        // Set cell A1 with Your Title
        $event->sheet->getDelegate()->setCellValue('A1', 'Your Title Here');

        // Set cells A2:B2 with current date
        $event->sheet->getDelegate()->setCellValue('A2', 'Report Date:');
        $event->sheet->getDelegate()->setCellValue('B2', now());

noblemfd

5 years ago

Level 9

@marianomoreyra - When I added it this way:

    // Set cell A1 with Your Title
    $event->sheet->getDelegate()->setCellValue('A1', 'Your Title Here');

    // Set cells A2:B2 with current date
    $event->sheet->getDelegate()->setCellValue('A2', 'Report Date:');
    $event->sheet->getDelegate()->setCellValue('B2', now());

    $cellRange = 'B5:K5'; // All headers
    $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(14);
    $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->getColor()
                ->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
    $event->sheet->getDelegate()->getStyle($cellRange)->getFill()
                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()->setARGB('FF17a2b8');
    $event->sheet->setAutoFilter($cellRange);

The initial formatting for:

    $cellRange = 'B5:K5'; // All headers

Did not take effect again

Please or to participate in this conversation.