New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[PROPOSAL] How I migrated from 2.x to 3.x #1799

Closed
subdesign opened this Issue Sep 20, 2018 · 7 comments

Comments

Projects
None yet
5 participants
@subdesign
Copy link

subdesign commented Sep 20, 2018

I see lot of people complaining about the new version, "everything changed", so I share my story of migration to the new version.

Here's my old 2.x code:
I have a ReportController with more than 20 different reports. So my report 1 example:

Route:

/admin/reports/{report1}/{xls}

Controller:

public function export1(Company $company, $type)
{
    $result = $company->whereHas('years', function ($q) {
        $q->where('year_id', currentYearId());
        $q->where('year_company.status', 1);
    })
    ->with(['company_datas', 'stands', 'co_companies' => function ($q) {
        $q->with('co_company_datas');
    }])
    ->where('canceled', 0)
    ->get();

    $data = [];

    foreach ($result as $r) {

        $pending = ($r->first_step == 1) ? 'No' : 'Yes';

        if (strlen(trim($r->company_datas[0]->flyer)) > 0) {
            $flyer = $r->company_datas[0]->flyer;
        } else {
            $flyer = '';
        }

        if (isset($r->stands[0]) && strlen($r->stands[0]->code) > 0) {
            $code = $r->stands[0]->code;
        } else {
            $code = '';
        }

        $data[] = [$r->name, $flyer, 'N', $code, $r->company_datas[0]->online_invitation_name, $pending];

        if (isset($r->co_companies) && ! $r->co_companies->isEmpty()) {
            foreach ($r->co_companies as $cco) {
                if (strlen(trim($cco->co_company_datas[0]->flyer)) > 0) {
                    $flyer = $cco->co_company_datas[0]->flyer;
                } else {
                    $flyer = '';
                }

                $data[] = [$cco->name, $flyer, 'Y', $code, '', ''];
            }
        }
    }

    $captions = ['Company name', 'Flyer name', 'Co Company', 'Stand', 'Online invitation', 'Pending'];

    $this->export($data, 'flyer_data', $captions, $type);
}

Then I have a private function which generates the output

private function export($data, $filename, $captions, $type)
{
    if (! in_array($type, ['xls', 'csv'])) {
        $type = 'csv';
    }
    
    $fn = $filename.'-'.date('Y-m-d_H-i-s');
    
    Excel::create($fn, function ($excel) use ($data, $captions) {

        $excel->sheet('SHEET NAME', function ($sheet) use ($data, $captions) {
                       
            $sheet->fromArray($data, null, 'A1', false, false);
            $sheet->prependRow(1, $captions);
            $sheet->freezeFirstRow();

        });

    })->export($type);
}

For the 3.x version I moved some code to a new Export class. I'll comment it in the source code
(app/Exports/Report1Export.php)


namespace App\Exports;

use App\Models\Company;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Events\AfterSheet;

class Report1Export implements FromCollection, WithHeadings, WithEvents {

    // set the headings
    public function headings(): array
    {
        return [
            'Company name', 'Flyer name', 'Co Company', 'Stand', 'Online invitation', 'Pending'
        ];
    }

    // freeze the first row with headings
    public function registerEvents(): array
    {
        return [            
            AfterSheet::class => function(AfterSheet $event) {
                $event->sheet->freezePane('A2', 'A2');
            },
        ];
    }

    public function collection()
    {
        // the above code is the same as in 2.x was ..
        $result = Company::whereHas('years', function ($q) {
            $q->where('year_id', currentYearId());
            $q->where('year_company.status', 1);
        })
        ->with(['company_datas', 'stands', 'co_companies' => function ($q) {
            $q->with('co_company_datas');
        }])     
        ->where('canceled', 0)
        ->get();

        $data = [];
        
        foreach ($result as $r) {

            $pending = ($r->first_step == 1) ? 'No' : 'Yes';

            if (strlen(trim($r->company_datas[0]->flyer)) > 0) {
                $flyer = $r->company_datas[0]->flyer;
            } else {
                $flyer = '';
            }

            if (isset($r->stands[0]) && strlen($r->stands[0]->code) > 0) {
                $code = $r->stands[0]->code;
            } else {
                $code = '';
            }

            $data[] = [$r->name, $flyer, 'N', $code, $r->company_datas[0]->online_invitation_name, $pending];

            if (isset($r->co_companies) && ! $r->co_companies->isEmpty()) {
                foreach ($r->co_companies as $cco) {
                    if (strlen(trim($cco->co_company_datas[0]->flyer)) > 0) {
                        $flyer = $cco->co_company_datas[0]->flyer;
                    } else {
                        $flyer = '';
                    }

                    $data[] = [$cco->name, $flyer, 'Y', $code, '', ''];
                }
            }
        }

        // ..but I return a collection from the built array data
        return collect($data);
    }
}

Added the freezePane macro to a service provider boot method (eg.: AppServiceProvider, or better make a specific one like ExportServiceProvider):

Sheet::macro('freezePane', function (Sheet $sheet, $pane) {
    $sheet->getDelegate()->getActiveSheet()->freezePane($pane);  // <-- https://stackoverflow.com/questions/49678273/setting-active-cell-for-excel-generated-by-phpspreadsheet
});

My ReportController method is simpler now:

public function export1($type)
{
    // now I return the result
    return $this->export('App\Exports\Report1Export', 'flyer_data', $type);
}

And the export() method looks linke this:

use App\Exports\Report1Export;

...

private function export($class, $filename, $type)
{
    if (! in_array($type, ['xls', 'csv'])) {
        $type = 'csv';
    }

    $fn = $filename.'-'.date('Y-m-d_H-i-s');

    return Excel::download(new $class, $fn.'.'.$type);
}

And that's it. I have fully accomplished the same result as it was in the 2.x version.
I hope it helped to someone..

@patrickbrouwers

This comment has been minimized.

Copy link
Member

patrickbrouwers commented Sep 20, 2018

Thanks @subdesign for providing your experience. I might add a simplified version of your example to the docs soon. I'd plan to write a blog post with a migration example in the future.

@DarrenChowles

This comment has been minimized.

Copy link

DarrenChowles commented Oct 16, 2018

This is really helpful. I used 2.x regularly in the past.

How can you run native PhpSpreadsheet methods via Laravel Excel? In 2.x I was able to construct the sheet row by row, allowing me to keep count, etc. but I see no quick way of doing this now, unless I use PhpSpreadsheet directly. See my old 2.x code below.

I cycle through all invoice items (keeping count of the items) and add them as rows. At the end I add totals as follows:

// Cycle through all invoice items here, incrementing $counter with each row... 
// ...

// Grand total
$counter++;
$sheet->row($counter, [
	'', '', '', '', '', '', '', '', '', $grand_totals['vat'], $grand_totals['excl'], $grand_totals['incl']
]);
$sheet->setColumnFormat(['J' . $counter => '[$£]#,##0.00_-']);
$sheet->setColumnFormat(['K' . $counter => '[$£]#,##0.00_-']);
$sheet->setColumnFormat(['L' . $counter => '[$£]#,##0.00_-']);
$sheet->cells('J' . $counter, function($cells) {
	$cells->setBorder(['top' => ['style' => 'double']]);
	$cells->setFontWeight('bold');
});
$sheet->cells('K' . $counter, function($cells) {
	$cells->setBorder(['top' => ['style' => 'double']]);
	$cells->setFontWeight('bold');
});
$sheet->cells('L' . $counter, function($cells) {
	$cells->setBorder(['top' => ['style' => 'double']]);
	$cells->setFontWeight('bold');
});

screenshot_1

@DarrenChowles

This comment has been minimized.

Copy link

DarrenChowles commented Oct 17, 2018

Figured it out, simply set a property in the export class. Cycle through the results in the collection() method (and increment the property). Finally, apply relevant styles in the registerEvents() method.

Very different from 2.x, but much better structure. Great job.

@Maatwebsite Maatwebsite deleted a comment from liumenglei Oct 31, 2018

@bumerang07

This comment has been minimized.

Copy link

bumerang07 commented Nov 16, 2018

@DarrenChowles can You please provide some code of your export class?
I'm interested in making cell by cell table because of complex data that I want to show.

@DarrenChowles

This comment has been minimized.

Copy link

DarrenChowles commented Nov 16, 2018

@bumerang07 here is one below that uses a collection. The main formatting you simply do in the AfterSheet event:

<?php

namespace App\Exports;

use App\Helpers\DateHelper;
use Maatwebsite\Excel\Events\AfterSheet;
use Maatwebsite\Excel\Events\BeforeExport;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\FromCollection;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;

class TimeTrackingExport implements FromCollection, WithMapping, WithHeadings, ShouldAutoSize, WithColumnFormatting, WithEvents
{
    public function __construct($results, $member, $date)
    {
        $this->date = $date;
        $this->member = $member;
        $this->results = $results;
    }

    public function headings(): array
    {
        return [
            'Date', 'Task', 'Time'
        ];
    }

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

    public function map($entry): array
    {
        return [
            $entry->date_at->format('Y-m-d'),
            $entry->description,
            DateHelper::friendlyDuration($entry->mins, 2),
        ];
    }

    public function columnFormats(): array
    {
        return [
            //'A' => NumberFormat::FORMAT_DATE_DDMMYYYY
        ];
    }

    public function registerEvents(): array
    {
        return [
            BeforeExport::class => function(BeforeExport $event) {
                $event->writer->getProperties()->setCreator($this->member->name . ' ' . $this->member->surname);
            },
            AfterSheet::class => function(AfterSheet $event) {
                $styleArray = [
                    'font' => [
                        'bold' => true,
                    ],
                ];

                $event->sheet->getDelegate()->setTitle($this->date)->getStyle('A1:C1')->applyFromArray($styleArray);
                $event->sheet->getDelegate()->getStyle('A1'); // Set cell A1 as selected
            },
        ];
    }
}

For something a little more custom, maybe do FromArray instead, and build your sheet using that? E.g.

class CustomExport implements FromArray, ShouldAutoSize, WithColumnFormatting, WithEvents
{
    public function __construct($member)
    {
        $this->member = $member;
    }

    public function array(): array
    {
        return [
            ['Title', $this->member->title],
            ['First Name', $this->member->name],
            ['Middle Names', $this->member->middle_names],
            ['Last Name', $this->member->surname],
            ['Email', $this->member->email],
            ['DOB', $this->member->dob],
        ];
    }

    public function columnFormats(): array
    {
        return [
            'A' => NumberFormat::FORMAT_TEXT,
            'B' => NumberFormat::FORMAT_TEXT,
        ];
    }

    public function registerEvents(): array
    {
        return [
            BeforeExport::class => function(BeforeExport $event) {
                $event->writer->getProperties()->setCreator(config('app.name'));
            },
            AfterSheet::class => function(AfterSheet $event) {
                $styleArray = [
                    'font' => [
                        'bold' => true,
                    ],
                ];

                $event->sheet->getDelegate()->setTitle('Info')->getStyle('A1:A100')->applyFromArray($styleArray);
                $event->sheet->getDelegate()->getStyle('A1'); // Set cell A1 as selected
            },
        ];
    }
}
@bumerang07

This comment has been minimized.

Copy link

bumerang07 commented Nov 16, 2018

@DarrenChowles thank You for your code.
I will publish my solution after I will finish, because I'm using multi dimensional array with data shows in rows instead columns.
My array contains data for multiple shops and each shop is represented as separated table with headers , so it wont be so simple.
It would be nice to use array() to return array with data, and styling / formatting for each cell :)
But I also need some calculations made for each cell during inserting them to excel.

@bumerang07

This comment has been minimized.

Copy link

bumerang07 commented Nov 21, 2018

My way to create cell by cell table is to create at least two classes: standardExport, viewGenerator.
First class has to implements WithEvents, the only Event I need for now is afterSheet. From that I call another static function (now I see I don't realy need it) generate with my $sheet passed. This function call another class witch will generate the sheet.

    namespace App\Exports;

    use App\Products;
    use Maatwebsite\Excel\Concerns\WithEvents;
    use Maatwebsite\Excel\Concerns\Exportable;
    use Maatwebsite\Excel\Events\AfterSheet;
    use Maatwebsite\Excel\Concerns\RegistersEventListeners;

    class StatisticsRingsExport implements  WithEvents
    {
        use Exportable, RegistersEventListeners;
        static $stats;

	    public function __construct($statistics = [])
            {
		$this->statistics = $statistics;
                self::$stats = $statistics;
	    }

        public static function generate($sheet)
        {
            $generator = new RingsSheetGenerator($sheet, self::$stats);
        }

        public static function afterSheet(AfterSheet $event)
        {
            $sheet = $event->sheet;
            self::generate($sheet);
        }

    }

Second class RingsSheetGenerator looks like:

    namespace App\Exports;

    use \PhpOffice\PhpSpreadsheet\Cell;


    class RingsSheetGenerator
    {
        var $row = 1; // this will hold current row position
        var $col = 1; // this will hold current column position
        var $sold_rows = []; // some helpers
        var $stock_rows = [];
        var $gray_rows = [];
        var $cols_chars = [];
        var $headers_rows_styles = [ // I have skipped the rest of arrays with styles
            'font' => [
                'bold' => true,
            ]
        ];

        public function __construct(&$sheet, $statistics) // Sheet ought to be passed as alias
        {
            $this->statistics = $statistics;
            $this->generateColsChars();
            $this->generate($sheet, $statistics); // is my function that calls other functions that generates sheet
        }

        public function generateColsChars() // this function is very extremly helpfull for generating cells rows
        {
            $cols_chars_1 = range('A', 'Z');
            $cols_chars_2 = range('A', 'Z');
            $this->cols_chars = $cols_chars_1;
            array_unshift($this->cols_chars, '');

            foreach ($cols_chars_1 as $char) {
                foreach($cols_chars_2 as $c)
                    $this->cols_chars[] = $char.$c;
            }
        }

        public function cell($col, $row) // this will return ie: M22 - very usefull for formulas
        {
            return $this->cols_chars[$col].$row;
        }
    
    }
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment