Laravel – 用 Laravel-Excel 匯出資料

最近在用 Laravel 重建公司的網站後台，很久沒來寫筆記，發現之前沒寫到匯出、匯入，趁這次剛好作到匯出匯入 EXCEL 功能，順便寫下。還沒用框架之前資料匯出都是用 PHPOffice/PhpSpreadsheet ，現在改用 Laravel 建網站則使用 Laravel Excel ，實際上 Laravel Excel 也是用 PHPOffice/PhpSpreadsheet 為核心，讓匯出匯入更簡單方便。

安裝

composer require maatwebsite/excel

設定

# 建立設定檔
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config

# 在 config.app 加入
'providers' => [
    ....
    Maatwebsite\Excel\ExcelServiceProvider::class,
]

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

匯出

第一步先在路由建立路徑並使用 artisan 建立匯出的 Class 。

# Laravel 8 路由寫法
Route::post('orders/export', [OrdersController::class, 'export'])->name('export');

# Laravel 6 路由寫法
Route::post('orders/export','OrdersController@export')->name('export');

# 控制器
....
use App\Exports\OrderExport;
use Maatwebsite\Excel\Facades\Excel;
....

class OrdersController extends Controller
{
    ....
    public function export()
    {
        $param['id'] = [1,2,3,4,5];
        return Excel::download(new OrderExport($param), '訂單資料.xlsx');
    }
    ....
}

php artisan make:export OrdersExport --model=App\Models\Order

執行完成後，會在 App 目錄下建立出一個 Exports 目錄，並產生 OrdersExport.php 檔案。方便統一管理匯出的檔案。這時候已經可以將整個Order資料庫匯出到Excel了。當然這不是我要的。

<?php

namespace App\Exports;

use App\Models\Order;
use Maatwebsite\Excel\Concerns\FromCollection;

class OrdersExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return Order::all();
    }
}

下面的範例可以看到是一個檔案裏面有兩個Sheet，這兩個Sheet分別放在 App\Exports\Sheets 目錄下，實際建立的方式與上面指令一樣即可建立出來，差別在於放置的位置不同而已。

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;
use Maatwebsite\Excel\Concerns\WithProperties;

use App\Exports\Sheets\OrderDetailSheet;       //訂單內容
use App\Exports\Sheets\OrderDetailItemSheet;   //商品列表

class OrderExport implements WithMultipleSheets, WithProperties
{
    protected $param;

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

    /**
     * @return array
     */
    public function sheets(): array
    {

        $sheets = [
            new OrderDetailSheet($this->param),
            new OrderDetailItemSheet($this->param),
        ];

        return $sheets;
    }

    public function properties(): array
    {
        return [
            'creator'        => '系統管理員',
            'lastModifiedBy' => '系統管理員',
            'title'          => '後台管理-訂單資料匯出',
            'description'    => '後台管理-訂單資料匯出',
            'subject'        => '後台管理-訂單資料匯出',
            'keywords'       => '',
            'category'       => '',
            'manager'        => '系統管理員',
            'company'        => 'CodingLab',
        ];
    }
}

App\Exports\Sheets\OrderDetailSheet

<?php

namespace App\Exports\Sheets;

use App\Models\Order as OrderDB;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Concerns\WithTitle;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class OrderDetailSheet implements FromCollection,WithStrictNullComparison, WithHeadings,WithStyles,WithTitle
{
    protected $param;

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

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        $param = $this->param;
        $orders = OrderDB::whereIn('id',$param['id'])->orderBy('created_at','desc')->get();
        foreach($orders as $order){
            $data[] = [
                $order->order_number,
                $order->partner_order_number,
                $order->user_name,
                $order->receiver_name,
                $order->receiver_address,
                $order->receiver_name,
                $order->receiver_tel,
                $order->receiver_email,
                $order->pay_time,
                $order->amount,
                $order->shipping_fee,
            ];
        }
        return collect($data);
    }

    public function styles(Worksheet $sheet)
    {
        $sheet->getStyle('A')->getNumberFormat()->setFormatCode('#');
        $sheet->getStyle('H')->getNumberFormat()->setFormatCode('#');
        $sheet->getColumnDimension('A')->setAutoSize(true);
        $sheet->getColumnDimension('B')->setAutoSize(true);
        $sheet->getColumnDimension('C')->setAutoSize(true);
        $sheet->getColumnDimension('D')->setAutoSize(true);
        $sheet->getColumnDimension('E')->setAutoSize(true);
        $sheet->getColumnDimension('F')->setAutoSize(true);
        $sheet->getColumnDimension('G')->setAutoSize(true);
        $sheet->getColumnDimension('H')->setAutoSize(true);
        $sheet->getColumnDimension('I')->setAutoSize(true);
        $sheet->getColumnDimension('K')->setAutoSize(true);
        //參數參考連結
        //https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#styles
    }

    public function title(): string
    {
        return '訂單資料';
    }

    public function headings(): array
    {
        return [
            '訂單編號',
            '訂購者ID',
            '訂購者',
            '寄送地址',
            '收件人',
            '收件人電話',
            '收件人email',
            '付款時間',
            '訂單金額',
            '運費',
        ];
    }
}

App\Exports\Sheets\OrderDetailItemSheet

<?php

namespace App\Exports\Sheets;

use App\Models\OrderItem as OrderItemDB;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Concerns\WithTitle;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class OrderDetailItemSheet implements FromCollection,WithStrictNullComparison, WithHeadings,WithStyles,WithTitle
{
    protected $param;

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

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        $param = $this->param;
        $orderItems = OrderItemDB::with('user')->whereIn('order_id',$param['id'])->orderBy('created_at','desc')->get();
        foreach($orderItems->items as $orderItem){
            $data[] = [
                $order->order_number,
                $order->user['name'],
                $orderItem->product_name,
                $orderItem->quantity,
                $orderItem->price,
                $orderItem->quantity * $orderItem->price,
            ];
        }
        return collect($data);
    }

    public function styles(Worksheet $sheet)
    {
        $sheet->getStyle('A')->getNumberFormat()->setFormatCode('#');
        $sheet->getColumnDimension('A')->setAutoSize(true);
        $sheet->getColumnDimension('B')->setAutoSize(true);
        $sheet->getColumnDimension('C')->setAutoSize(true);
        $sheet->getColumnDimension('D')->setAutoSize(true);
        $sheet->getColumnDimension('E')->setAutoSize(true);
        $sheet->getColumnDimension('F')->setAutoSize(true);
        //參數參考連結
        //https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#styles
    }

    public function title(): string
    {
        return '商品資料';
    }

    public function headings(): array
    {
        return [
            '訂單編號',
            '訂購者',
            '商品名稱',
            '數量',
            '單價',
            '總價',
        ];
    }
}

更詳細的使用方法請參考 Laravel-Excel 官方網站，另外 Style 設定請參考 https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#styles