composer require maatwebsite/excelphp artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"'providers' => [
....
Maatwebsite\Excel\ExcelServiceProvider::class,
],
'aliases' => [
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class,
],
Last updated
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class NewClass extends Model
{
public $table = 'newclass';
public $primaryKey = 'id';
public $incrementing = true;
public $timestamps = false;
}<html lang="en">
<head>
<title>Import & Export</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" >
</head>
<body>
<br/>
<br/>
<div class="container">
<div class="panel panel-primary">
<div class="panel-heading">
<h3 class="panel-title" style="padding:12px 0px;font-size:25px;"><strong>PHP+MySQL+Laravel</strong></h3>
</div>
<div class="panel-body">
@if ($message = Session::get('success'))
<div class="alert alert-success" role="alert">
{{ Session::get('success') }}
</div>
@endif
@if ($message = Session::get('error'))
<div class="alert alert-danger" role="alert">
{{ Session::get('error') }}
</div>
@endif
<h3>導入資料到指定table:</h3>
<form style="border: 4px solid #a1a1a1;margin-top: 15px;padding: 20px;" action="{{ URL::to('importExcel') }}" class="form-horizontal" method="post" enctype="multipart/form-data">
<input type="file" name="import_file" />
{{ csrf_field() }}
<br/>
<button class="btn btn-primary">Import</button>
<p>只能導入CSV或Excel類型的檔案</p>
</form>
<br/>
<h3>導出指定table的所有資料:</h3>
<div style="border: 4px solid #a1a1a1;margin-top: 15px;padding: 20px;">
<p>可導出三種格式的檔案</p>
<a href="{{ url('downloadExcel/xls') }}"><button class="btn btn-success btn-lg">Excel xls</button></a>
<a href="{{ url('downloadExcel/xlsx') }}"><button class="btn btn-success btn-lg">Excel xlsx</button></a>
<a href="{{ url('downloadExcel/csv') }}"><button class="btn btn-success btn-lg">CSV</button></a>
</div>
</div>
</div>
</div>
</body>
</html><?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\NewClass;
use Excel;
class MaatwebsiteDemoController extends Controller
{
protected $model;
public function __construct(NewClass $model) {
$this->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','導入失敗，請檢查你導入的檔案');
}
}Route::get('importExport', 'MaatwebsiteDemoController@importExport');
Route::get('downloadExcel/{type}', 'MaatwebsiteDemoController@downloadExcel');
Route::post('importExcel', 'MaatwebsiteDemoController@importExcel');Excel::create('Filename', function($excel) {
// Excel code
})->export('xls');
// or
->download('xls');