如何在Laravel實作Excel匯入匯出功能

如何在Laravel實作Excel匯入匯出功能

透過 Laravel Excel 套件來實作 Excel 處理的功能

需求&安裝

需求

  • PHP: 7.2以上版本
  • Laravel: 5.8以上版本
  • PhpSpreadsheet: ^1.15
  • PHP extension php_zip enabled
  • PHP extension php_xml enabled
  • PHP extension php_gd2 enabled
  • PHP extension php_iconv enabled
  • PHP extension php_simplexml enabled
  • PHP extension php_xmlreader enabled
  • PHP extension php_zlib enabled

安裝

開啟Terminal,輸入以下指令:

composer require maatwebsite/excel

如需發布設定檔到專案內,請輸入以下指令:

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config

匯出

快速開始

Step 1.建立匯出類別

可透過以下指令在 app/Exports 路徑內新增匯出類別,比如 UserExport

php artisan make:export UsersExport --model=User

生成檔案位置如下圖

檔案內容如下:

//App/Exports/UsersExport.php

<?php

namespace App\Exports;

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

class UsersExport implements FromCollection
{
    public function collection()
    {
        return User::all();
    }
}

collection() 定義要匯出的資料內容,可為 Eloquent 集合

如果資料並非 Eloquent 集合,也可以自定義你的集合,參考以下範例:

//App/Exports/UsersExport.php

public function collection()
{
    return new Collection([
        [1, 2, 3],
        [4, 5, 6]
    ]);
}

Step 2.在控制器方法中使用匯出類別

透過 Excel Facade 的 download() 來進行 Excel 下載,如下範例:

//App/Http/Controllers/UsersController.php

<?php

namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

download() 的第一參數為匯出類別物件,第二參數為匯出的Excel檔名

如果不是要下載到本機而是存放於伺服器內,可改用 store() ,參考以下程式碼:

//App/Http/Controllers/UsersController.php

public function storeExcel() 
{
    return Excel::store(new UsersExport, 'users.xlsx', 's3');
}

Step 3.建立匯出路由

開啟 routes/web.php ,加入以下路由:

//routes/web.php

Route::get('users/export/', '[email protected]');

傳入參數

如需從控制器傳入參數,可透過建構子來達成,如下範例

//app/Exports/UsersExport.php

namespace App\Exports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\FromArray;

class UsersExport implements FromArray
{
    protected $invoices;

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

    public function array(): array
    {
        return $this->users;
    }
}

在控制器方法端,在建立匯出類別物件時傳入參數

$export = new InvoicesExport([
    [1, 2, 3],
    [4, 5, 6]
]);

return Excel::download($export, 'invoices.xlsx');

自定義匯出內容

如果你有必要調整 Excel 內容的樣式,比如欄位順序,第一行要加上header等等。最簡單的作法就是透過 Blade 視圖來完成所有的自定義,可透過實作 FromView 來達成,請參考以下範例:

//app/Exports/UsersExport.php

namespace App\Exports;

use App\Models\User;
use Illuminate\Contracts\View\View;
use Maatwebsite\Excel\Concerns\FromView;

class UsersExport implements FromView
{
    public function view(): View
    {
        return view('exports.users', [
            'users' => User::all()
        ]);
    }
}

這將會把視圖的 Table 結構轉成 Excel 格式。視圖內容如下範例:

//resources/views/exports/users.blade.php

<table>
    <thead>
    <tr>
        <th>名稱</th>
        <th>Email</th>
    </tr>
    </thead>
    <tbody>
    @foreach($users as $user)
        <tr>
            <td>{{ $user->name }}</td>
            <td>{{ $user->email }}</td>
        </tr>
    @endforeach
    </tbody>
</table>

匯出大量資料

當需要匯出的資料過多時,就有必要透過 Laravel 的 chunks 來分段取出資料,以提升效能。要達成這個目的,請改實作 FromQuery,如下面範例

//app/Exports/UsersExport.php

namespace App\Exports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\Exportable;

class UsersExport implements FromQuery
{
    use Exportable;

    public function query()
    {
        //取出users表格所有資料
        return User::query();
    }
}

如需傳入參數,你可以透過 setter 的方式來達成,如下面範例

public function forYear(int $year)
{
    $this->year = $year;

    return $this;
}

public function query()
{
    return User::query()->whereYear('created_at', $this->year);
}

改成上面寫法之後,就能夠透過以下方式來傳入參數

return (new InvoicesExport)->forYear(2018)->download('invoices.xlsx');

自定義資料

資料並非集合而是陣列

  • 改成實作 FromArray
  • 實作 array()
//app/Exports/UsersExport.php

namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\FromArray;

class UsersExport implements FromArray
{
    public function array(): array
    {
        return [
            [1, 2, 3],
            [4, 5, 6]
        ];
    }
}

自定義欄位寬度

如希望欄位寬度能根據內容而進行調整,可實作 ShouldAutoSize,如下範例

namespace App\Exports;

use Maatwebsite\Excel\Concerns\ShouldAutoSize;

class UsersExport implements ShouldAutoSize
{
    ...
}

如果你想要手動的定義各欄位寬度,可實作WithColumnWidths,請參考以下範例

//app/Exports/UsersExport.php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithColumnWidths;

class UsersExport implements WithColumnWidths
{
    public function columnWidths(): array
    {
        return [
            'A' => 55,
            'B' => 45,            
        ];
    }
}

這個技巧可搭配上面的 ShouldAutoSize ,任何欄位如果沒有手動指定寬度就會被自動調整

匯出的 Excel 欄位有超連結

程式碼內的 AR 請替換成你的超連結欄位

<?php

namespace App\Exports;

...
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Cell\Hyperlink;
use Maatwebsite\Excel\Concerns\WithEvents;
...

class YourExport implements ..., WithEvents
{
    ...

    public function registerEvents(): array
    {
        return [
            AfterSheet::class    => function (AfterSheet $event) {
                /** @var Worksheet $sheet */
                foreach ($event->sheet->getColumnIterator('AR') as $row) {
                    foreach ($row->getCellIterator() as $cell) {
                        if (str_contains($cell->getValue(), '://')) {
                            $cell->setHyperlink(new Hyperlink($cell->getValue(), 'Read'));

                            // Upd: Link styling added
                            $event->sheet->getStyle($cell->getCoordinate())->applyFromArray([
                                'font' => [
                                    'color' => ['rgb' => '0000FF'],
                                    'underline' => 'single'
                                ]
                            ]);
                        }
                    }
                }
            },
        ];
    }
}

精簡程式碼

這個技巧非必須,只有在你常常需要撰寫匯出程式時可以讓你少寫一點程式碼,並把所有匯出設定封裝於匯出類別內

//app/Exports/UsersExport.php

namespace App\Exports;

use App\Models\User;
use Maatwebsite\Excel\Excel;
use Illuminate\Contracts\Support\Responsable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\Exportable;

class UsersExport implements FromCollection, Responsable
{
    use Exportable;

    /**
    * 匯出檔名,必須要有
    */
    private $fileName = 'users.xlsx';

    /**
    * 寫入類型,非必須。其他類型有CSV.TSV.ODS.XLS.HTML.MPDF.DOMPDF.TCPDF
    */
    private $writerType = Excel::XLSX;

    /**
    * 標頭,非必須
    */
    private $headers = [
        'Content-Type' => 'text/csv',
    ];

    public function collection()
    {
        return Invoice::all();
    }
}

如需匯出pdf,需要安裝pdf渲染函式庫

匯入

快速開始

Step 1.建立匯入類別

開啟 Terminal ,輸入以下指令來生成匯入類別

php artisan make:import UsersImport --model=User

生成的匯入類別內容如下:

//app/Imports/UsersImport.php

<?php

namespace App\Imports;

use App\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;

class UsersImport implements ToModel
{
    /**
     * @param array $row
     *
     * @return User|null
     */
    public function model(array $row)
    {
        return new User([
           'name'     => $row[0],
           'email'    => $row[1], 
           'password' => Hash::make($row[2]),
        ]);
    }
}

Step 2.在控制器方法內使用匯入類別

使用範例如下所示,透過 Excel Facade 的 import() 來匯入

//app/Http/Controllers/UsersController.php

use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;
use App\Http\Controllers\Controller;

class UsersController extends Controller 
{
    public function import() 
    {
        Excel::import(new UsersImport, 'users.xlsx');

        return redirect('/')->with('success', 'All good!');
    }
}

如果有多個Sheet,只需要匯入某個Sheet...

Step 1.在Import類別導入類別並實作

<?php

namespace App\Imports;

...
use Maatwebsite\Excel\Concerns\WithMultipleSheets;
...

class UserImport implements ToModel,WithMultipleSheets

Step 2.在Import類別加入以下函式

下列程式表示只匯入第一個sheet

public function sheets(): array
{
    return [
        0 => $this,
    ];
}

指定從Sheet的第幾行開始讀

Step 1.在Import類別導入類別並實作

<?php

namespace App\Imports;

...
use Maatwebsite\Excel\Concerns\WithStartRow;
...

class UserImport implements ToModel,WithStartRow

Step 2.在Import類別加入以下程式碼

public function startRow(): int
{
    return 4; //從第4行開始讀
}

匯入時進行欄位驗證

Step 1.在Import類別導入類別並實作

<?php

namespace App\Imports;

...
use Maatwebsite\Excel\Concerns\WithValidation;
...

class UserImport implements ToModel,WithValidation

Step 2.在Import類別加入以下程式碼

//匯入時的驗證規則
public function rules(): array
{
    return [
            '0' => 'required|string',
            '1' => 'required|email',

    ];
}
//驗證訊息使用的欄位名稱
public function customValidationAttributes()
{
    return [
        '0' => '姓名',
        '1' => 'Email',
    ];
}

參考資料

Laravel Excel 套件官方網站


分享這篇文章:

關聯文章: