Laravel Excel 基本 Export/Import 實作

@ver 0.1.1 @date 2019-05-04 06:28:14 (星期六)

excel 是很偉大的資料集中介溝通檔案, 好好的來運用它吧😇

Laravel Excel Supercharged Excel exports and imports in Laravel

  1. excel 匯入匯出

    官網

    github

    繼續上一個專案 testAuth

    安裝

    命令

    # 安裝
    composer require maatwebsite/excel
    
    # 產生設定檔
    php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

    設定檔暫時不用作任何修改. 安裝完成後 laravel 5.5 以後版本會自動 Auto Discovery providers 跟 aliases, 加入到對應的程式裡. 如果沒有, 請手動修改 config/app.php 加入此套件的程式

    php artisan package:discover 可以查看現有已被 Auto Discovery 的套件

    config/app.php 程式碼

    'providers' => [
        ...(前略)
        /*
         * Package Service Providers...
         */
        Maatwebsite\Excel\ExcelServiceProvider::class,
    ],
    
    'aliases' => [
        ...(前略)
        'Excel' => Maatwebsite\Excel\Facades\Excel::class,
    ],
  2. 簡單的匯出

    基本匯出範例

    我們使用前幾次實作 users 資料表 的內容, 產生直接下載的 excel xlsx 檔案

    mysql 的 users 資料表內容

    命令及執行結果

    mysql> select id, name, email from users;
    +----+---------------+---------+
    | id | name          | email   |
    +----+---------------+---------+
    |  1 | 測試帳號1     | a@b.com |
    |  2 | 測試帳號2     | b@c.com |
    |  3 | 測試帳號3     | c@d.com |
    |  4 | 測試帳號4     | d@e.com |
    |  5 | 測試帳號5     | e@f.com |
    +----+---------------+---------+
    5 rows in set (0.00 sec)
    
    mysql>

    產生 UsersExport class

    使用此套件內建命令, 產生一支匯出 User Model 專用的 UsersExport class , 它會放在 app/Exports/ 目錄裡, 請開啟 UsersExport.php 此檔案.

    命令

    php artisan make:export UsersExport --model=User

    執行結果

    [alex@nvda ~/jobs/testAuth ]$ php artisan make:export UsersExport --model=User
    Export created successfully.
    [alex@nvda ~/jobs/testAuth ]$

    查看 app/Export/UsersExport.php 程式碼

    ...(前略)
    /**
     * @return \Illuminate\Support\Collection
     */
    public function collection()
    {
        return User::all();
    }
    ...(後略)

    重點在 return 這裡, 當 controller 呼叫執行 User::all(); 會取出整個 users 資料表 的所有資料回傳給此套件, 透過程式轉成 xlsx 格式. 但我們還得要先把 rotue 及 controller 跟此 UsersExport 連結, 才動得起來.

    在 routes/web.php 最後加入以下測試用的 route 程式碼

    routes/web.php 程式碼

    ...(前略)
    // 測試 Laravel Excel Export
    Route::get('/test2e', 'HomeController@testExcelExport')->name('excel.export');

    在 app/Http/Controllers/HomeController.php 加入 route 呼叫用的程式碼

    app/Http/Controllers/HomeController.php 程式碼

    ...(前略)
    // 加入引用
    use App\Exports\UsersExport;
    use Maatwebsite\Excel\Facades\Excel;
    ...(中略)
    /**
     * 測試 Laravel Excel Export 為 xlsx
     */
    public function testExcelExport()
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
    ...(後略)

    進入上述 rotue 設定的 瀏覽器 test2e 測試位置, 此刻可能直接轉到 login 畫面. 別擔心, 因為我們先前的實作有加入 Auth 認證, 它會在程式載入 HomeController 時, 執行 public function __construct() 裡的 $this->middleware('auth');

    暫時將此行程式碼, 加上 // 註解讓它不執行, 所以修改為 // $this->middleware('auth');

    再一次進入 瀏覽器 test2e 測試位置, 瀏覽器直接回應 下載存檔 的畫面, 算是成功了, 選擇位置後存檔.

    請開啟 users.xlsx 檔案, 登!登!登! 看到跟 users 資料表 一模一樣的內容. 但是?

    但是, 為什麼少了 password, remember_token 兩個欄位的資料呢?

    查看 app/User.php 程式碼

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password', 'remember_token',
    ];

    看到了嗎, 因為 $hidden 這個陣列, 在 app/Export/UsersExport.php 執行 User::all(); 時, 設為隱藏.

    這只是最基本的, 連結裡的範例還有許多不同功能搭配各式需求, 請小心服用.

  3. 大量匯入

    基本匯入範例

    大量匯入是系統很重要的功能, 現在實作一個 excel xlsx 檔, 使用最前三欄, 任意編輯數個使用者的 name, email, password 不作任何檢查, 把它匯入到 users 資料表 成為新的使用者.

    注意 : users 資料表 有指定 $table->string('email')->unique(); 所以若有相同, 會有錯誤發生.

    產生 UsersImport class

    使用此套件內建命令, 產生一支匯入 User Model 專用的 UsersImport class , 它會放在 app/Imports/ 目錄裡, 請開啟 UsersImport.php 此檔案.

    命令

    php artisan make:import UsersImport --model=User

    執行結果

    [alex@nvda ~/jobs/testAuth ]$ php artisan make:import UsersImport --model=User
    Import created successfully.
    [alex@nvda ~/jobs/testAuth ]$

    查看 app/Export/UsersImport.php 程式碼

    ...(前略)
    public function model(array $row)
    {
        return new User([
            //
        ]);
    }
    ...(後略)

    model(...) 函數裡 return new User([ ]); 陣列在最初時是空白, 我們要加上一些程式, 讓它匯入時, excel 欄位會對應到 users 資料表欄位

    修改 app/Import/UsersImport.php model() 函數的程式碼

    ...(前略)
    public function model(array $row)
    {
        return new User([
           'name'     => $row[0],
           'email'    => $row[1], 
           'password' => Hash::make($row[2]),
        ]);
    }
    ...(後略)

    現在在 User([ ... ]); 裡面多了三行程式碼, 都是 '欄位名' => $row[?], . 很明確可以看出, 陣列前面 keyModel 或資料表的欄位名; 後面 value 則對應要匯入 excel 的每一行資料的 第 ? 欄 之值. Hash::make() 是用來把密碼欄位加密編碼, 它需要 use Illuminate\Support\Facades\Hash;

    但我們還得要先把 rotue 及 controller 跟此 UsersExport 連結, 才動得起來.

    在 routes/web.php 最後加入以下測試用的 route 程式碼

    routes/web.php 程式碼

    ...(前略)
    // 測試 Laravel Excel Import
    Route::get('/test2i', 'HomeController@testExcelImport')->name('excel.import');

    在 app/Http/Controllers/HomeController.php 加入 route 呼叫用的程式碼

    app/Http/Controllers/HomeController.php 程式碼

    ...(前略)
    // 加入引用
    use App\Imports\UsersImport;
    use Maatwebsite\Excel\Facades\Excel;
    ...(中略)
    /**
     * 測試 Laravel Excel 從 xlsx Import
     * @date 2019-05-07 14:42:14 (星期二)
     */
    public function testExcelImport()
    {
        $r = Excel::import(new UsersImport, 'users2import.xlsx');
        //$r = Excel::import(new UsersImport, 'public/users2import.xlsx');
        //$r = Excel::import(new UsersImport, storage_path('my/users2import.xlsx'));
        //dd($r);
    
        return redirect('/')->with('success', 'All good!');
    }
    ...(後略)

    程式到此為止已完工. 但是有沒有人發現 users2import.xlsx 檔案要放那裡? 程式才能抓到? 它就丟在第二個參數, 難道還要寫支程式上傳嗎? 呼叫 google 找達人解答. 原來所有的輸入, 輸出, 在本地端若沒指定, 都預設在 storage/app/ 這個子目錄裡.

    上面的第一行程式碼, 檔案從預設路徑找起, 定向到 storage/app/users2import.xlsx 註解的第二行程式碼, 則是指到 storage/app/public/users2import.xlsx 註解的第三行程式碼, 加了 storage_path() 所以沒有去找預設值, 而會變成 storage/my/users2import.xlsx

    補充, 關於路徑位置的設定, 可參考 config/filesystems.php

    ...(前略)
    'disks' => [
    
        'local' => [
            'driver' => 'local',
            'root' => storage_path('app'),
        ],
    ...(後略)

    進入上述 rotue 設定的 瀏覽器 test2i 測試位置, 可能直接轉到 login 畫面. 別擔心, 因為我們先前的實作有加入 Auth 認證, 它會在程式載入 HomeController 時, 執行 public function __construct() 裡的 $this->middleware('auth');

    暫時將此行程式碼, 加上 // 註解讓它不執行, 所以修改為 // $this->middleware('auth');

    再次進入 瀏覽器 test2i 測試位置, 若沒意外, 瀏覽器上的頁面是下列的錯誤訊息

    Illuminate \ Contracts \ Filesystem \ FileNotFoundException
    File not found at path: users2import.xlsx

    當然會有錯誤, 在路徑裡找不到檔案 users2import.xlsx.

    users2import.xlsx 的內容如下

    大家好 c1@q1.com   password
    Helen Ho    helen@ho.com    password
    Grace 李 pick@pp.com password
    陳大明 ionchen@qe.com  password

    存檔 users2import.xlsx 完成後, 請把它複製到 storage/app/ 目錄下. 再次進入 瀏覽器 test2i 測試位置, 完成後, 無聲無息的回到首頁, 這沒問題, 因為在 HomeController@testExcelImport 此函數裡, 匯入 xlsx 完成後 return redirect('/');

    我們直接進 mysql 看看結果.

    匯入 xlsx 後 mysql 的 users

    命令及執行結果

    mysql> select id, name, email from users;
    +----+---------------+----------------+
    | id | name          | email          |
    +----+---------------+----------------+
    |  1 | 測試帳號1     | a@b.com        |
    |  2 | 測試帳號2     | b@c.com        |
    |  3 | 測試帳號3     | c@d.com        |
    |  4 | 測試帳號4     | d@e.com        |
    |  5 | 測試帳號5     | e@f.com        |
    |  6 | 大家好        | c1@q1.com      |
    |  7 | Helen Ho      | helen@ho.com   |
    |  8 | Grace 李      | pick@pp.com    |
    |  9 | 陳大明        | ionchen@qe.com |
    +----+---------------+----------------+
    9 rows in set (0.01 sec)
    
    mysql>

    pick@pp.com 進來 瀏覽器 login 登入試試.

    Dashboard
    You are logged in!

    成功, 放鞭炮 🧨 🧨 🧨 !!!

    git

    git s
    git a .
    git cm 'Laravel Excel 基本 Export/Import 實作'
  4. 本章總結

    本章使用 laravel 命令總結

    composer require maatwebsite/excel                  # 安裝 Laravel Excel
    php artisan package:discover                        # 查看現有已被 Auto Discovery 的套件
    php artisan make:export UsersExport --model=User    # 產生 UsersExport class
    php artisan make:import UsersImport --model=User    # 產生 UsersImport class