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

[subdesign]

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]

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 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');
});

[DarrenChowles]

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.

[bumerang07]

@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]

@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]

@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]

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;
        }

    }

[Tushar0012]

I am getting this issue on excel file import on web server. But this is working fine on local server.

Maatwebsite \ Excel \ Exceptions \ NoTypeDetectedException No ReaderType or WriterType could be detected. Make sure you either pass a valid extension to the filename or pass an explicit type.

Please help to resolve this issue.

[GlennM]

I am getting this issue on excel file import on web server. But this is working fine on local server.

Maatwebsite \ Excel \ Exceptions \ NoTypeDetectedException No ReaderType or WriterType could be detected. Make sure you either pass a valid extension to the filename or pass an explicit type.

Please help to resolve this issue.

Please open a new issue for this and fill in the issue template. Thanks!

[shubh-511]

Thanks Bro!!...I stucked for 1 week you solved my problem within minutes...Thanks a lot!!

[raphjutras]

Thanks @subdesign && @DarrenChowles that really helped!

[AdamReece-WebBox]

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

You're spot on. For some reason we now have to do a significantly higher amount of work to achieve the same output. This is not an upgrade.

What I did on 2.1:

Excel::create($filename, function($excel) use($data) {

    $excel->sheet("Export_Data", function($sheet) use($data) {

        $sheet
            ->setColumnFormat([
                "L"     => "d/m/y",
                "M"     => "d/m/y",
                "N"     => "h:mm",
                "O"     => "h:mm",
                "AN"    => "d/m/y",
                "AO"    => "d/m/y",
                "AP"    => "d/m/y",
                "AQ"    => "d/m/y",
            ])
            ->fromArray($data, null, "A1", true)
            ->freezeFirstRow()
        ;

    });

})->store($extension, storage_path("app/downloads"));

Suddenly this package becomes excessive compared to using PHPSpreadsheet directly, for example:

$spreadsheet = new Spreadsheet();

$worksheet = $spreadsheet->getActiveSheet();
$worksheet
    ->setTitle("Export_Data")
    ->fromArray(array_keys(current($data)), null, "A1", true)
    ->fromArray($data, null, "A2", true)
    ->freezePane("A2")
;

foreach ([
    "L"     => "d/m/y",
    "M"     => "d/m/y",
    "N"     => "h:mm",
    "O"     => "h:mm",
    "AN"    => "d/m/y",
    "AO"    => "d/m/y",
    "AP"    => "d/m/y",
    "AQ"    => "d/m/y",
] as $c => $format) {
    $worksheet
        ->getStyle(sprintf("%s2:%s%d", $c, $c, $worksheet->getHighestRow($c)))
        ->getNumberFormat()
        ->setFormatCode($format)
    ;
}

(SpreadsheetIOFactory::createWriter($spreadsheet, ucfirst($extension)))
    ->save(sprintf("%s/%s.%s", storage_path("app/downloads"), $filename, $extension))
;

[patrickbrouwers]

Opinions differ, for all of my use cases the amount of code needed to create my exports greatly reduced. However this package is still 100% optional, if you don't like it, you can use any other package that does fit your needs.