laravel及layui 导出

框架 :

前端 : layui 后端 : laravel

需求 :

导出商品列表(6000多条数据),渠道列表(100条数据)

方法一 :

使用layui自带的table.exportFile(id, data, type)语法,在前端直接导出数据

过程 :

代码 :

<script type="text/html" id="toolbar">
    <div class="layui-btn-container">
      <button class="layui-btn layui-btn-sm" lay-event="export">导出</button>
    </div>
  </script>

// 头工具栏事件
table.on('toolbar(test)', function (obj) {
  if (obj.event == 'export') {
    $.ajax({
      url : '{{ route('order_build.export') }}',
      type: 'get',
      data: {//可查询的数据
        goods_name    : $('input[name="goods_name"]').val(),
        order_id      : $('input[name="order_id"]').val()
      }
    }).done(function (data) {
      table.exportFile(tableIns.config.id, data, 'csv');
    });
  }
});

...
public function export(Request $request, OrderBuild $order_build)
{
    if ($request->filled('goods_name')) {
        $order_build = $order_build->where(
            'order_build.goods_name',
            'like',
            '%' . $request->input('goods_name') . '%'
        );
    }
   
    if ($request->filled('order_id')) {
        $order_build = $order_build->where('order_build.order_id', $request->input('order_id'));
    }
    // 排序
    $order_build = $order_build->orderBy('order_build.create_time', 'desc');
    $order_build = $order_build->get();
	//格式化导出数据
    foreach ($order_build as $key=>$value) {
        $order_build[$key]['order_id'] = $value['order_id']."\t";
        $order_build[$key]['subcustomer_id'] = $value['subcustomer_id']."\t";
        $order_build[$key]['linkmanphone'] = $value['linkmanphone']."\t";
    }

    return response()->json($order_build);
}
...

结果 :

1. 需要配合头工具栏事件使用,否则会出现可以导出全部数据,不能导出查询数据的问题
2. 可以解决渠道列表的导出问题,不能解决商品列表的导出问题,数据太多,会出现超时问题

方法二 :

使用Laravel-Excel 3 的导出功能,后端实现导出功能

过程 :

HTML
<a href="javascript:;" class="layui-btn layui-btn-sm" onclick="exporturl()" id="export">导出</a>

jQuery
function exporturl () {
    var goods_id      = $('input[name="goods_id"]').val();
    var supplier_name = $('input[name="supplier_name"]').val();
    var goods_name    = $('input[name="goods_name"]').val();
    $('#export').attr('href', '/goods/export' +
      '?goods_id=' + goods_id
      + '&supplier_name=' + supplier_name
      + '&goods_name=' + goods_name);
  }

//控制器
namespace Inspur\Goods\Controller;

use Maatwebsite\Excel\Facades\Excel;

...
public function export(Request $request)
{
    return Excel::download(new GoodsExport(
        $request->input('goods_id'),
        $request->input('goods_name'),
        $request->input('supplier_name')
    ), 'goods.csv');
}
...

//GoodsExport文件
<?php

namespace Inspur\Goods\Exports;
use Inspur\Goods\Models\Goods;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
class GoodsExport implements FromQuery, WithHeadings, WithMapping
{
    use Exportable;

    public function __construct($goods_id, $goods_name, $supplier_name)
    {
        $this->goods_id = $goods_id;
        $this->goods_name = $goods_name;
        $this->supplier_name = $supplier_name;
    }
	//添加表头
    public function headings(): array
    {
        return [
            '商品 ID',
            '服务商名称',
            '商品名称'
        ];
    }
	
	//通过添加  WithMapping, 可以控制每一列的数据,格式化列
    public function map($goods): array
    {
        return [
            $goods->goods_id . "\t",
            $goods->supplier_name,
            $goods->goods_name
        ];
    }
	//查询导出数据
    public function query()
    {
        $goods = new Goods();
        $goods = $goods->query()
            ->selectRaw('*')
            ->leftJoin('goods_supplier as s', 'goods_info.supplier_id', '=', 's.supplier_id');
        if ($this->goods_id) {
            $goods = $goods->where(
                'goods_info.goods_id',
                'like',
                '%' . $this->goods_id . '%'
            );
        }

        if ($this->goods_name) {
            $goods = $goods->where(
                'goods_name',
                'like',
                '%' . $this->goods_name . '%'
            );
        }

        if ($this->supplier_name) {
            $goods = $goods->where(
                'supplier_name',
                'like',
                '%' . $this->supplier_name . '%'
            );
        }

        $goods->orderBy('goods_info.create_time', 'desc');

        return $goods;
    }
}

结果 :

1. 要限制导出的列的数量,可导出10列,6000行数据,不能导出全表数据
2. 基本解决了商品列表的导出问题