Laravel Excel(maatwebsite/excel)导出xlsx表格===Laravel8版本

话不多说，坑事真多，泪也是真多~其实这个架子也是对PhpSpreadsheet的封装，所以就需要这个扩展
我用的是laravel8，插件是用的3.1版本，好像是最新的~
安装需要这些个扩展

首先的配置：

1	composer require maatwebsite/excel

在config/app.php里面添加

1
2
3
4
5
6

'providers' => [
/*
* Package Service Providers...
*/
Maatwebsite\Excel\ExcelServiceProvider::class,
]

1
2
3
4

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

最后运行发布命令就会在config文件下生成excel.php:

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

可以通过make:export来创建一个文件，比如想导出订单列表的一些数据，–model后面可以写上关联Order，就是关联订单表相关的模型

1	php artisan make:export OrderExport --model=Order

创建完成文件就会在app\Exports下面生成一个OrderExport文件类，是处理数据的类，实现的是FromView接口，代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

<?php
namespace App\Exports;
use App\Models\Clients;
use App\Models\Orders;
use Illuminate\Contracts\View\View;
use Maatwebsite\Excel\Concerns\FromCollection;
use App\Models\OrdersMaterials;
use Maatwebsite\Excel\Concerns\FromView;
class OrderExport implements FromView
{
/**
* @return \Illuminate\Support\Collection
* 这个构造方法可以自己根据需求修改，我这里直接把订单搜索的数据直接写在里面（省代码，纯粹偷懒），
* 注意最后数据放在了变量里
*/
public function __construct(array $where)
{
$this->where = $where;
$query = Orders::query();
$wheres = $this->where;
$orders = $query->with(['OrdersMaterials','Designers'])
->when(!empty($this->where['between']),function ($query)use ($wheres){
$timer = explode('-',$wheres['between']);
$query->whereBetween('created_at',[date('Y-m-d',strtotime($timer[0])),date('Y-m-d',strtotime($timer[1]))]);
})
->when(!empty($this->where['exts']),function ($query)use ($wheres){
$query->where($wheres['exts']);
})->orderBy('id', 'desc')
->get();
foreach($orders as $k=>$v){
$design = Designer::query()->where(['id'=>$v->designers_id])->first();
$v->designers_id = $design['name'];
$client = Clients::query()->where(['id'=>$v->client_id])->first();
$v->client_id = $client['name'];
$v->is_invoice = $v->is_invoice==0?'否':'是';
}
$this->orders = $orders;
}

public $where = [];
public $orders = [];

//这里是采用的数据输出到视图的方法实现导出，后面写基于collection导出
public function view(): View
{
return view('export.order', ['data' => $this->orders]);
}
}

在resources/views下面创建视图文件export/order.blade.php文件，就是和普通的视图文件渲染数据一样：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

<table>
<tr>
<td>订单号</td>
<td>用户信息</td>
<td>总价</td>
<td>订单备注</td>
<td>所属设计师</td>
<td>开票状态</td>
<td>创建时间</td>
<td>修改时间</td>
</tr>
@foreach($data as $vv)
<tr>
<td>{{$vv->id}}</td>
<td>{{$vv->client_id}}</td>
<td>{{$vv->total_price}}</td>
<td>{{$vv->desc}}</td>
<td>{{$vv->designers_id}}</td>
<td>{{$vv->is_invoice}}</td>
<td>{{$vv->created_at}}</td>
<td>{{$vv->updated_at}}</td>
</tr>
@endforeach
</table>

最后在你访问OrderController.php的方法像下面这样写：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

/**
* 导出
*/
public function exportOrder(Request $request)
{
$where = [];
$param = $request->all();
if(!empty($param['data'])){
$where['between'] = $param['data'];
}
if(!empty($param['real_name'])){
$where['exts'][] = ['id', '=', $param['real_name']];
}
$name = 'order.xlsx';
//以下三个header作用自行百度，主要是设置导出格式，设置导出时间限制
header("Content-Type:text/html;charset=utf-8");
header('Content-Disposition: attachment; filename='.$name);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet/vnd.ms-excel');
ini_set("memory_limit", "128M");
set_time_limit(0);
//ini_set('max_execution_time', '0'); //数值 0 表示没有执行时间的限制。
//这里的$where条件就是前端访问这个导出接口传过来的参数，
//通过这里传到OrderExport.php文件里面，在通过__construct()接收参处理
return Excel::download(new OrderExport($where), $name);
}

1
2
3

路由是：
//Excel导出
Route::get('Order/exportOrder', 'OrderController@exportOrder');

===========================================================
下面的是通过Collection方式设置数据并且导出excel表格：
OrderExport.php代码如下，个别参数说明和上面一样，就是一个接口实现类的不同，这个实现的是FromCollection和WithHeading：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90

<?php
namespace App\Exports;
use App\Models\Order;
use Maatwebsite\Excel\Concerns\FromCollection;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Concerns\WithHeadings;
class OrderExport implements FromCollection, WithHeadings
{
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
$wheres = $this->where;
$data = Order::select('id', 'order_sn', 'name','mobile','contract_id','total',
'pay_id', 'emp_id', 'emp_name', 'pay_status',
'loan_time', 'is_share', 'transaction_id', 'created_at', 'updated_at'
)
->when(!empty($this->where['exts']), function ($query) use ($wheres){
$query->where($wheres['exts']);
})
->when(!empty($this->where['between']), function ($query) use ($wheres) {
$query->whereBetween('loan_time', $wheres['between']);
})
->get()
->map(function ($item){
$result=[
'id'=>$item['id'],
'order_sn'=>$item['order_sn'],
'name'=>$item['name'],
'mobile'=>$item['mobile'],
'contract_id'=>$item['contract_id'],
'total'=>$item['total'] / 100,
'pay_id' => $this->payId[$item->pay_id],
'emp_id'=>$item['emp_id'],
'emp_name'=>$item['emp_name'],
'pay_status' => $this->payStatus[$item->pay_status],
'loan_time'=>$item['loan_time'],
'is_share' => $this->isShare[$item->is_share],
'transaction_id'=>$item['transaction_id'],
'created_at'=>$item['created_at'],
'updated_at'=>$item['updated_at'],
];
return $result;
});
return $data;
}

public $where = [];

protected $payId = [
1=>'微信支付',
2=>'支付宝支付',
];
protected $payStatus = [
1=>'待支付',
2=>'已支付',
];

protected $isShare = [
1=>'否',
2=>'是',
];

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

public function headings(): array
{
return [
'序号',
'订单号',
'客户姓名',
'客户手机',
'合同编号',
'合同金额',
'支付方式',
'所属员工ID',
'所属员工姓名',
'支付状态',
'放款时间',
'是否分账',
'微信支付订单号',
'创建时间',
'更新时间'
];
}
}

OrderController.php代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

/**
* 导出表格
* @param Request $request
* @return \Symfony\Component\HttpFoundation\BinaryFileResponse
*/
public function exportData(Request $request)
{
$where = [];
$param = $request->all();
if(!empty($param['name'])){
$where['exts'][] = ['name', '=', $param['name']];
}
if(!empty($param['order_sn'])){
$where['exts'][] = ['order_sn', '=', $param['order_sn']];
}
if(!empty($param['mobile'])){
$where['exts'][] = ['mobile', '=', $param['mobile']];
}
if(!empty($param['contract_id'])){
$where['exts'][] = ['contract_id', '=', $param['contract_id']];
}
if(!empty($param['pay_status'])){
$where['exts'][] = ['pay_status', '=', $param['pay_status']];
}
if(!empty($param['is_share'])){
$where['exts'][] = ['is_share', '=', $param['is_share']];
}
if(!empty($param['loan_time'])){
list($st, $et) = explode(',', $param['loan_time']);
$where['between'] = [$st, $et];
}
$name = 'order.xlsx';
//这里的参数上面有说明
header('Content-Disposition: attachment; filename='.$name);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet/vnd.ms-excel');
ini_set("memory_limit", "-1");
set_time_limit(0);
return Excel::download(new OrderExport($where), $name);
}

最后再生成路由文件给前端访问就好了~

1
2
3

路由是：
//Excel导出
Route::get('Order/exportData', 'OrderController@exportData');

至此结束，不足之处欢迎指正~