TP5+PHPexcel导入xls,xlsx文件读取数据

首先:在extend里面引入PHPexcel文件,直接根目录导入进去

html创建上传按钮

1<form  class="layui-form" method="post" action="">
2
3
4
5                        <div class="layui-form-item" >
6                            <label class="layui-form-label">上传excel</label>
7                            <div class="layui-input-inline">
8                                <div class="layui-upload">
9                                    <button type="button" name="myfile" class="layui-btn" id="myfile"><i class="layui-icon"></i>上传文件</button>
10                                </div>
11                            </div>
12                        </div>
13
14                        <div class="layui-form-item" style="padding-left: 35%;">
15                            <div class="layui-input-inline"  >
16                                <button class="layui-btn" lay-submit lay-filter="formsub">立即提交</button>
17                                <button type="reset" class="layui-btn layui-btn-primary">重置</button>
18                            </div>
19                        </div>
20
21                    </form>
22
23
24<script type="text/javascript">
25    layui.use(['form','upload'],function(){
26
27        var form=layui.form;
28        var upload=layui.upload;
29
30        upload.render({ //允许上传的文件后缀
31            elem: '#myfile'
32            ,url: "{:url('sale/do_upload')}"
33            ,accept: 'file' //普通文件
34            ,exts: 'xls|excel|xlsx' //只允许上传压缩文件
35            ,done: function(res){
36                if(res.code==1){
37                    layer.msg('上传成功,已解析数据',{icon:6});
38                }else{
39                    layer.msg('解析失败',{icon:5});
40                }
41            }
42        });
43
44        form.on('submit(formsub)',function(data){
45            layer.msg('导入数据具体详情未协商确认,待确认后处理');
46            return false;
47        })
48        
49
50    })
51
52</script>

在上传后的sale/do_upload中去进行解析上传的excel

1public function do_upload(){
2
3
4
5        //引入文件
6        \think\Loader::import('PHPExcel.PHPExcel');
7        $objPHPExcel = new \PHPExcel();
8
9        //获取表单上传文件
10        $file = request()->file('file');
11        $info = $file->validate(['ext' => 'xlsx,xls'])->move(ROOT_PATH . 'public' . DS . 'uploads');
12
13        //数据为空返回错误
14        if(empty($info)){
15            $output['status'] = false;
16            $output['info'] = '导入数据失败~';
17            $this->ajaxReturn($output);
18        }
19
20        //获取文件名
21        $exclePath = $info->getSaveName();
22        //上传文件的地址
23        $filename = ROOT_PATH . 'public' . DS . 'uploads'.DS . $exclePath;
24
25
26        $extension = strtolower( pathinfo($filename, PATHINFO_EXTENSION) );
27
28        \think\Loader::import('PHPExcel.IOFactory.PHPExcel_IOFactory');
29        if ($extension =='xlsx') {
30            $objReader = new \PHPExcel_Reader_Excel2007();
31            $objExcel = $objReader ->load($filename);
32
33        } else if ($extension =='xls') {
34
35            $objReader = new \PHPExcel_Reader_Excel5();
36            $objExcel = $objReader->load($filename);
37
38
39        }
40
41
42        $excel_array=$objExcel->getsheet(0)->toArray();   //转换为数组格式
43        array_shift($excel_array);  //删除第一个数组(标题);
44        array_shift($excel_array);  //删除th
45
46        $data=[];
47        foreach ($excel_array as $k=>$v){
48            $data[$k]["danhao"]=$v[0];//单号
49            $data[$k]["type_name"]=$v[1];//类型名称
50            $data[$k]["name"]=$v[2];
51            $data[$k]["number"]=$v[3];
52            $data[$k]["price"]=$v[4];
53            $data[$k]["danwei"]=$v[0];
54            $data[$k]["create_user"]=$v[5];
55            $data[$k]["create_time"]=$v[6];
56            $data[$k]["remark"]=$v[7];
57        }
58
59        $msg=[
60            'code'=>1,
61            'msg'=>'已获取信息',
62        ];
63        $msg['data']['src']=$filename;
64        $msg['data']['data']=$data;
65
66        return json_encode($msg);
67
68
69    }