项目后台使用Thinkphp6+layuiAdmin，经常用到导入导出excel的功能，记录一下。

1.导入

html模板部分

  //执行实例
            var uploadInst = upload.render({
                elem: '#put' //绑定元素
                //将excel上传接口到服务器拿到地址 /public/excel/32424324.xls
                ,url: '{:url("Base/uploadFile")}?type=excel'
                ,accept:'file'
                ,done: function(res){
                    layer.msg('上传中，请稍等', {icon: 6, time: 1000}, function () {
                        if(res.code==200) {
                            path = res.data.path;
                            //window.location.href = "{:url('two.Sch/fromExcel')}?filePath=" + path;//注释掉直接跳转形式，使用下面的json走接口形式
                            $.post('{:url("two.Sch/fromExcel")}', {path:path}, function (r) {
                                if (r.code == 200) {
                                    layer.msg(r.msg, {icon: 6, time: 1000}, function () {
                                        parent.location.reload();
                                    })
                                } else {
                                    layer.msg(r.msg)
                                }
                            }, 'json');

                        }else{
                            layer.msg(res.msg)
                        }
                    })
                    //上传完毕回调
                }
                ,error: function(){
                    //请求异常回调

                }
            });

代码部分

1.上传文件

 public function uploadFile(Request $request)
    {
        $type = $request->param('type','others');
        $file = $request->file("file");

        if($type=='excel'){
            if($file->extension()!='xlsx'){
                output_error('请使用模板导入');
            }
        }

        $path = "/public/storage/";
        $save_name = \think\facade\Filesystem::disk('public')->putFile('file/'.$type, $file);
        return output_data(["path" => $path . $save_name]);
    }

2.导入文件

    public function fromExcel(){
        //output_success('保存成功');
        $path = $this->request->param('path','public/excel/导入模板.xlsx');
        $base=[
            ['category_name','类型'],
            ['name','店铺名'],
            ['business_license','营业执照'],
            ['management_id','经营许可证'],
        ];
        $data = $this->baseFromExcel($base,$path,['flag'=>'序号','mustField'=>'A']);//
        SchModel::saveAll($data);
        output_success('导入成功');

    }

3.excel解析类

    /**
     * @param $filePath
     * @return array
     * @date 2022/3/2 11:29
     * @author 
     * @desc 导入excel之读取excel
     * params['mustField']行的某列为空则跳过
     * params['flag']开始工作的行（excel没有大标题，则该参数没用）
     */
    public function baseFromExcel($base,$path,$params=[]){
        $abc=[
            'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
            'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ'
        ];
        $baseNew=[];
        foreach($base as $k => $v){
            $baseNew[$abc[$k]]=$v;
        }
        $base=$baseNew;

        $path = ltrim($path,'/');
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path);
        $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        $data=[];
        //array_pop($sheetData);//去掉最后一个空的

        //$flag=0;
        foreach($sheetData as $k => $v){
            //if(!$flag) {
                //if ($v['A'] == $params['flag']) {
                    //$flag = 1;
                    //continue;
                //}
            //}
            //if(!$flag) continue;
            if(empty($v[$params['mustField']])) continue;

            $temp=[];
            foreach($v as $k2 => $v2){
                if(!isset($base[$k2][0])) continue;
                $temp[$base[$k2][0]]=$v2;
            }
            $data[]=$temp;
        }
        return $data;
    }

2.导出

1.导出文件

    public function toExcel()
    {
        $post = $this->request->param();
        //通过筛选条件拿到一个二维数组，格式[{"id":1,"name":"哈哈"},{"id":2,"name":"嘻嘻"}](json格式只是为演示)
        $data = $this->getList($post, []);
        $base = [
            ['category_name','类型'],
            ['name','店铺名'],
            ['business_license','营业执照'],
            ['management_id','经营许可证'],
            ['sanitation_permit','卫生许可证号'],
            ['address','经营地址'],
            ['sheet','归属路'],
        ];
        foreach($data['data'] as &$v){
            if($v['status']==1) $v['update_time']='';
        }
        $this->excelBase($base, $data['data']);
    }

2.excel解析类

    /**
     * @param $header
     * @param $body
     * @param $controller
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     * @date 2022/6/7 11:23
     * @author xuke
     * @desc 导出excel
     */
    public function excelBase($header, $body, $controller, $params = [])
    {
        $abc = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
            'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ'
        ];

        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        # 获取活动工作薄
        $sheet = $spreadsheet->getActiveSheet();
        // 设置个表格宽度
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(30);


        $sheet->mergeCells('A1:' . $abc[count($header) - 1] . '1');
        $sheet->setCellValue('A' . '1', $title)->getStyle('A1')->getFont()->setBold(true)->setSize(15);
        $spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('center');
        //

        //参数名
        $order = 0;
        $j = [];
        foreach ($header as $v) {
            $sheet->setCellValue($abc[$order] . '2', $v)->getStyle($abc[$order] . '2')->getFont()->setBold(true)->setSize(15);;
            //if(strstr($v,'手机')){//增加宽度防止变16进制
            $spreadsheet->getActiveSheet()->getColumnDimension($abc[$order])->setWidth(16);
            //}
            $j[] = [$abc[$order] . '1', $v];
            $order++;
        }

        //主体数据
        $orderMain = 3;
        if (isset($body['list'])) $body = $body['list'];
        foreach ($body as $value) {
            $order = 0;
            foreach ($header as $k => $v) {

                if (!strstr($k, '#')) {
                    $valueResult = $value[$k];
                } else {//数组
                    $temp = explode('#', $k);
                    $valueResult = $value[$temp[0]][$temp[1]] ?? '';
                }
                $sheet->setCellValue($abc[$order] . $orderMain, $valueResult);
                $j[] = [$abc[$order] . $orderMain, $valueResult];
                $order++;
            }
            $orderMain++;
        }
        # Xlsx类 将电子表格保存到文件
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $filename = '文件名' . '列表' . time() . '.xlsx';
        $path = '/public/excel/' . $filename;
        $writer->save(root_path() . $path);

        //保存到download表
        $//data = [
            //'filename' => $filename,
            //'username' => $this->request->user['name'],
            //'times' => 0,
            //'type' => $controller,
            //'path' => $path,
            //'source' => $source,
        //];
        //DownloadLogModel::create($data);
        //
		
        output_success('', ['path' => $path]);
    }