TP5.1导入CSV|xls|xlsx等表格文件进数据库

和导出一样的使用phpoffice全家桶

composer require phpoffice/phpspreadsheet

Server层

    //支持CSV/xls/xlsx格式
    public function blog_info()
    {
        $objPHPExecl = new \PHPExcel();
        if (request()->isPost()) {
            $file = \request()->file('file');
            $path    =  './uploads';
            // dump($path);die();
            if (!file_exists($path)) {
                mkdir($path);
            }
            $info = $file->move($path);
            if (empty($info)) {
                return $this->json(400, '数据存储失败', '');
            }
            $excelPath = $info->getSaveName();
            $file_name = $path .'/'. $excelPath;
            //获取后缀
            $extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));
                $objReader = \PHPExcel_IOFactory::createReader('Excel5'); 

             //载入excel文件
        $excel = $objReader->load($file_name, $encode = 'utf-8');
        //读取第一张表
        $sheet = $excel->getSheet(0);
        //获取总行数
        // array_shift($sheet);  //删除第一个数组(标题);
        $row_num = $sheet->getHighestRow();
        //获取总列数
        $col_num = $sheet->getHighestColumn();

        $import_data = []; //数组形式获取表格数据
        for ($i = 2; $i <= $row_num; $i++) {
            $import_data[$i]['ip']  = $sheet->getCell("A" . $i)->getValue();
            $import_data[$i]['address']  = $sheet->getCell("B" . $i)->getValue();
            $import_data[$i]['llq']  = $sheet->getCell("C" . $i)->getValue();
            $import_data[$i]['phoneorweb']  = $sheet->getCell("D" . $i)->getValue();
            $import_data[$i]['formtime']  = $sheet->getCell("E" . $i)->getValue();
            $import_data[$i]['form_blog']  = $sheet->getCell("F" . $i)->getValue();
        }

            $success = Db::name('wp_form')->insertAll($import_data);
            if ($success) {
                return json(['msg'=>'success','code'=>'200']);
            }
            return false;
        }
    }

数据库字段与for循环字段对应就好

前端显示层

//hidden等待layeropen弹出显示
<div id="in_file" hidden style="text-align:center">
<fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;">
  <legend>上传Word文件</legend>
</fieldset>

<div class="layui-upload">
  <button type="button" class="layui-btn layui-btn-normal" id="up">选择文件</button>
  <button type="button" class="layui-btn" id="do_up">开始上传</button>
</div>
        </div>
<div class="demoTable" style="text-align:center;">
     <div class="layui-btn" data-type="out_daixs">
         <i class="iconfont"></i>导出数据</div>
                       
     div class="layui-btn" data-type="in_daixs">
         <i class="iconfont"></i>导入数据  </div>
            </div>
//导出按钮 data-type="out_daixs"绑定layui点击事件
     out_daixs: function () {
          //搜索后执行重载
       let download = confirm("确定导出文件吗?");
       if(download == true){
         location.href = 'http://count.php1314.cn/api';  
            if(location.href){layer.msg("导出成功", { icon: 6 });}
    }else{
           if(location.href){layer.msg("取消导出", { icon: 6 });}
     }

 }
//点击弹出上传html页面
    in_daixs: function () {
       layer.open({
            type: 1,
            title: "导入数据",
             area: ['420px', '330px'],
             content: $("#in_file"),
                });
        },
//上传文件js
layui.use('upload', function(){
              var $ = layui.jquery
              ,upload = layui.upload;
                  upload.render({
                    elem: '#up'
                    ,url: 'http://count.php13' //改成您自己的上传接口
                    ,accept:'file'
                    ,auto: false
                    ,multiple: true
                    ,exts:'xlsx|csv|xls'
                    ,bindAction: '#do_up'
                    ,done: function(res){
                        if(res.code == 200){
                            layer.msg('导入Success!',{icon:6,shade:[0.3,'393D49']});
                            location.reload();
                        }

                    }
                  });
              });

为您推荐

评论已关闭