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

首先的配置:
composer require maatwebsite/excel在config/app.php里面添加
'providers' => [ /* * Package Service Providers... */ Maatwebsite\Excel\ExcelServiceProvider::class,'aliases' => [ ... 'Excel' => Maatwebsite\Excel\Facades\Excel::class,最后运行发布命令就会在config文件下生成excel.php:
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config可以通过make:export来创建一个文件,比如想导出订单列表的一些数据,--model后面可以写上关联Order,就是关联订单表相关的模型
php artisan make:export OrderExport --model=Order创建完成文件就会在app\Exports下面生成一个OrderExport文件类,是处理数据的类,实现的是FromView接口,代码如下:
<?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文件,就是和普通的视图文件渲染数据一样:
<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的方法像下面这样写:
/**
* 导出
*/
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);
}路由是://Excel导出===========================================================
下面的是通过Collection方式设置数据并且导出excel表格:
OrderExport.php代码如下,个别参数说明和上面一样,就是一个接口实现类的不同,这个实现的是FromCollection和WithHeading:
<?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代码如下:
/**
* 导出表格
* @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);最后再生成路由文件给前端访问就好了~
路由是://Excel导出至此结束,不足之处欢迎指正~
