Implementation of Excel export with laravel6 and maatsite / Excel

Time:2020-10-26

Compared with import, there are more export scenarios in the project. It is estimated that there are more than ten exports now. The import was written before, and the export will be added.

As mentioned before installation, here is the configuration. Although there is a default configuration, there are still scenarios to modify the configuration. Therefore, it is recommended to generate a configuration file.

to configure

//Generate config/ excel.php
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

Only one configuration is mentioned. Other comments are very detailed,

'csv' => [
    'delimiter'              => ',',
    'enclosure'              => '"',
    'line_ending'            => PHP_EOL,
    //To export the Chinese garbled code of CSV, use_ Set BOM to true
    'use_bom'                => true,
    'include_separator_line' => false,
    'excel_compatibility'    => false,
],

Next, let’s complete some common points in an exported demo.

DEMO

php artisan make:export MultiExport

The generated files are as follows:

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;

class MultiExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        //
    }
}
  • Custom sheet, add withtitle
  • Custom column name, add withheaders
  • Do not want to use collection, use fromarray instead of fromcollection
  • Multiple sheets, using withmultiplesheets instead of fromcollection

After transformation:

<?php
/**
 *Multiple export
 */
namespace App\Exports;

use App\Exports\MultiExportA;
use App\Exports\MultiExportB;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class MultiExport implements WithMultipleSheets
{
    private $date;

    public function __construct($date)
    {
        $this->date = $date;
    }


    public function sheets(): array
    {
        $sheets = [];

        $sheets[] = new MultiExportA($this->date);
        $sheets[] = new MultiExportB($this->date);

        return $sheets;
    }
}

---
//Multiexporta and multiexportb can be compared
<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithTitle;
use App\Models\ExportA;

class MultiExportA implements FromArray, WithTitle, WithHeadings
{
    private $date;

    public function __construct($date, $cityId)
    {
        $this->date = $date;
    }

    public function headings(): array
    {
        return [
            'ID',
            'name',
            'price',
            'cell phone'
        ];
    }


    /**
    * @return array
    */
    public function array() : array
    {
        $data = ExportA::where('date', $this->date)
            ->get()
            ->toArray();

        $ret = [];
        foreach ($data as $val) {
            //A magic code calculates the price
            $price = ...;
            $ret[] = [
                'id' => $val['id']."\t",
                'name' => $val['name'],
                'price' => $price,
                //Converted to text, coded excel uses scientific counting method
                'mobile' => $val['mobile']."\t",
            ];
        }
        return $ret;
    }

    /**
     * @return string
     */
    public function title(): string
    {
        Return 'form a';
    }
}

use

//Preservation
$obj = new MultiExport($date);
Excel::store($obj, 'MultiExport'.$date.'.xlsx');

//Download CSV
Excel::download($obj, 'MultiExport'.$date.'.csv', \Maatwebsite\Excel\Excel::CSV, ['Content-Type' => 'text/csv']);

Problem thinking

When the amount of data is too large, the memory may overflow when exporting. Suggestions:

  • Use other high-performance components, or use native code to stream output to the browser, or write directly in other languages (such as go)
  • If the file is too large, it is difficult for Excel to open a large amount of data files, which is easy to get stuck or even crash. Try to export the file by file, such as 1W
  • Part of the export process may have calculations, which can be calculated in advance. When exporting, read the table directly and use lazycollection,Using lazy collections to improve the performance of larravel excel reading (easily supporting millions of data)