Layui导入导出

Layui导入导出

1. 导入

   <a class="layui-btn layui-btn-danger layui-btn-sm" id="importBtn">导入</a>
   <a class="layui-btn layui-btn-checked layui-btn-sm" download href="/travel.xlsx" id="downloadBtn">下载导入模版</a>
   
    var uploadInst = upload.render({
        elem: '#importBtn', //绑定元素
        url: '/travel/import', // 上传接口，实际使用时改成您自己的上传接口即可。
        accept: 'file', // 限制上传类型
        size: 50*1024,
        exts: 'xls|xlsx',
        done: function (res) {
            if (res && res.code === 200) {
                layer.msg(res.msg);
                table.reload('travelTableReload');
            } else {
                layer.msg(res.msg);
            }
        },
        error: function () {
            //请求异常回调
            layer.msg('导入失败');
        }
    });
   		
    use Dcat\EasyExcel\Excel;
    public function import(Request $request)
    {
        $file = $request->file('file');
        try {
            validate([
                'file' => [
                    'fileSize' => 20 * 1024 * 1024,
                    'fileExt' => 'xls,xlsx',
                    'fileMime' => 'application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                ]
            ])->check(['file' => $file]);
            $path = Filesystem::disk('local')->putFile('travel', $file);
            $full = Filesystem::disk('local')->path($path);
            $headings = ['name', 'destination', 'start_time', 'end_time', 'duration', 'price', 'sales', 'outlink'];
            // 导入xlsx
            $travelSheets = Excel::import($full)->headings($headings)->sheet(0)->toArray();
            $allTravelData = $this->formatSingleSheetData($travelSheets);
            if (empty($allTravelData)) {
                return $this->showError('导入数据为空');
            }
            $travelData=[];
            foreach ($allTravelData as &$item) {
                $travelId=Db::name('travel')->where(['name'=>$item['name'],'destination'=>$item['destination']])->value('id');
                if($travelId){
                        continue;
                }
                $item['thumb'] = config('app.web_url') . 'static/avatar.png';
                $item['cover_image'] = config('app.web_url') . 'static/avatar.png';
                $item['describe'] = $item['destination'] . $item['name'];
                $item['content'] = $item['name'];
                $item['status'] = 1;
                $item['create_time'] = date('Y-m-d H:i:s');
                $item['update_time'] = date('Y-m-d H:i:s');
                $travelData[]=$item;
            }
            if(empty($travelData)){
                return $this->showError('导入数据经过滤后为空');
            }
            $res = Db::name('travel')->insertAll($travelData);
            if (empty($res)) {
                return $this->showError('导入失败');
            }
            return $this->showSuccess([], '导入成功');
        } catch (ValidateException $e) {
            return json(['code' => 0, 'msg' => $e->getMessage()]);
        } catch (\Throwable $e) {
            return $this->showError('导入失败：' . $e->getMessage());
        }
    }
   

2. 导出

    <a class="layui-btn layui-btn-warm layui-btn-sm export_btn" data-type="export" id="export">导出</a>
   
    $('#export').on('click', function () {
        window.location.href = '/order/export?key=' + $('#keyword').val() + '&expiry_status=' + $('#expiry_status').val() + '&fee_status=' +
        $('#fee_status').val() + '&time_range=' + $('#time_range').val();
    });
   	
    use Dcat\EasyExcel\Excel;
    public function export(Request $request): \think\response\Json
    {
        $where = [];
        $key = $request->param('key');
        if ($key != '') {
            $where[] = ['holderName|holderPhone|insuredName', 'like', "%$key%"];
        }
   
        $timeRange = trim((string) $request->param('time_range', ''));
        if (!empty($timeRange)) {
            $times = explode(' - ', $timeRange);
            $where[] = ['o.create_time', 'between time', [$times[0] . ' 00:00:00', $times[1] . ' 23:59:59']];
        }
   
        $lists = Db::name('order_info')
            ->alias('o')
            ->leftJoin('prod_sales p', 'o.productId = p.id')
            ->where($where)
            ->field('o.*,p.title as prod_title')
            ->order('o.create_time', 'desc')
            ->select()
            ->toArray();
        $headings = [
            'id' => 'ID',
            'policyNo' => '保单号',
            'prod_title' => '产品名称',
            'holderName' => '投保人',
            'insuredName' => '被保人',
            'startDate' => '开始日期',
            'endDate' => '结束日期',
            'update_time' => '支付时间',
            'create_time' => '创建时间'
        ];
   
        // xlsx
        $excel = Excel::export($lists)->headings($headings)->download('order.xlsx');
        return json(['code' => 0, 'msg' => '', 'excel' => $excel]);
    }