Import & Export

# Import - Export [參考工具](https://github.com/Maatwebsite/Laravel-Excel) 也就是我想將從資料庫抓取的資料導出來並下載，從json檔轉成excel檔，在laravel這邊就有一個套件可以滿足，讓你很快的上手。接下來這篇就是來介紹這個套件，從安裝到最後的範例。 ## 安裝首先我們要先在你的專案資料夾底下開啟命令提示字元，然後輸入以下的字串，即可安裝： ``` composer require maatwebsite/excel ``` 然後創建一個excel.php檔在config裡面，方便更改設定 ``` php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" ``` ## 設定再來我們要去`config/app.php`裡面的`providers`和`aliases`宣告一下，如下： ```php 'providers' => [ .... Maatwebsite\Excel\ExcelServiceProvider::class, ], 'aliases' => [ .... 'Excel' => Maatwebsite\Excel\Facades\Excel::class, ], ``` ## 建立要導入跟導出當然需要有table然後還有跟table連接的model囉，我的table建立了一個有(id、f\_id、name、pp\_id)，如下： ![](/files/-LxPa0OglfFRF376-Xjk) 然後model： ```php Import & Export

PHP+MySQL+Laravel

@if ($message = Session::get('success'))

@endif @if ($message = Session::get('error'))

@endif

導入資料到指定table:

導出指定table的所有資料:

可導出三種格式的檔案

``` view長的樣子如下： ![](/files/-LxPa0Oi4yAYxRm8mJW3) 再來就是重頭戲controller：我們需要的功能，ㄧ個就是需要導向一個頁面，然後需要導入檔案，需要導出資料，大概就是三個功能囉！ ```php model = $model; } /** * 指定前端頁面 * * @var array */ public function importExport() { return view('importExport'); } /** * 導出並下載檔案 * * @var array */ public function downloadExcel(Request $request, $type) { $data = $this->model->get()->toArray(); return Excel::create('testClass', function($excel) use ($data) { $excel->sheet('mySheet', function($sheet) use ($data) { $sheet->fromArray($data); }); })->download($type); } /** * 導入資料到table * * @var array */ public function importExcel(Request $request) { if($request->hasFile('import_file')){ $path = $request->file('import_file')->getRealPath(); $data = Excel::load($path, function($reader) {})->get(); if(!empty($data) && $data->count()){ foreach ($data as $qqq) { if(!empty($qqq)){ // 這邊就是你資料庫裡面所有的欄位設定 $insert[] = ['f_id' => $qqq['f_id'], 'name' => $qqq['name'],'pp_id'=> $qqq['pp_id']]; } } if(!empty($insert)){ // 將上面編排好的資料存到資料庫中 $this->model->insert($insert); return back()->with('success','成功導入！'); } } } return back()->with('error','導入失敗，請檢查你導入的檔案'); } } ``` routes方面的設定： ```php Route::get('importExport', 'MaatwebsiteDemoController@importExport'); Route::get('downloadExcel/{type}', 'MaatwebsiteDemoController@downloadExcel'); Route::post('importExcel', 'MaatwebsiteDemoController@importExcel'); ``` ## 導出方式有兩個`->export($ext)`和`->download($ext)`，如下： ```php Excel::create('Filename', function($excel) { // Excel code })->export('xls'); // or ->download('xls'); ``` 檔案類型可以是：Excel5 (`xls`)、Excel2007 (`xlsx`)、CSV (`csv`)。 --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://tobyisme.gitbook.io/laravel/import-export.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.