Yii2 基于 layui 的 Excel 上传并导入数据(含分页)

安装yii2的phpexcel扩展
  • 安装命令:composer require phpoffice/phpexcel
    引入layui包,我这里用的是2.4.5的版本,请自行下载对应版本
  • layui前台页面
    导入文件
    <div class="layui-card" id="ImportList" style="display: none">
        <div class="layui-card-header">卡号信息</div>
        <div class="layui-card-body">
            <table id="ImportExcel" lay-filter="ImportExcel"></table>
        </div>
    </div>
    <script>
    layui.use(['form','element','upload','table'], function(){
        var element = layui.element;
        var upload = layui.upload;
        var table = layui.table;
        upload.render({
            elem:'#file',
            url:'<?=Url::to(["sales/re-charge-upload"])?>',
            accept: 'file',
            size: 5120,
            exts:'xlsx',
            //上传前的回调
            before: function(obj){
                layer.load(2);
            },
            //上传成功的回调
            done: function(res, index, upload){
                layer.closeAll('loading');
                //执行渲染
                $('#ImportList').show();
                table.render({
                    elem: '#ImportExcel' //指定原始表格元素选择器(推荐id选择器)
                    ,cols: [[
                        {field: 'agentId', title: '分销商编号',width:100,unresize:true},
                        {field: 'vcOrderId', title: '销售单号',width:120,unresize:true},
                        {field: 'productName', title: '产品名称',width:100,unresize:true},
                        {field: 'vcUnitName', title: '产品单位',width:100,unresize:true},
                        {field: 'nFXRate', title: '产品汇率',width:100,unresize:true},
                        {field: 'cardId', title: '卡号',width:'',unresize:true},
                        {field: 'initBalance', title: '初始值',width:100,unresize:true},
                        {field: 'cardBalance', title: '当前余额',width:100,unresize:true},
                        {field: 'cardStatus', title: '卡状态',width:80,unresize:true}
                    ]] //设置表头
                    ,data:res.data
                    ,page:true
                    ,limit:10
                    ,skin:'line'
                    ,text:'导入数据异常,请重新导入'
                });
                table.render();
            },
            //上传错误的回调
            error: function(res,index, upload){
                layer.closeAll('loading');
            }
        });
    });
    </script>
  • Yii2 后台代码

     //导入excel文件并解析数据
    public function actionReChargeUpload(){
      $request = Yii::$app->request;
     if ($request->isAjax){
      $params = $_FILES['file'];
     if (!empty($params)) {
      $file_name = $params['name'];
      $temp_name = $params['tmp_name'];
      $error = $params['error'];
      $arr = pathinfo($file_name);
      $ext_suffix = $arr['extension'];
      $allow_suffix = ['xlsx'];
     if (!in_array($ext_suffix, $allow_suffix)) {
          return $this->asJson(['msg' => '上传的文件类型只能是xlsx', 'code' => 400]);
      }
      if (!file_exists('uploads')) {
         mkdir('uploads');
      }
      $new_filename = date('YmdHis', time()) . rand(100, 1000) . '.' . $ext_suffix;
     if (move_uploaded_file($temp_name, 'uploads/' . $new_filename)) {
      $excelData = json_decode($this->actionImportExcel('uploads/' . $new_filename),true);
     if (!empty($excelData)) {
      return $this->asJson(['msg' => '导入成功', 'data' => $excelData, 'code' => 200]);
      }else{
      return $this->asJson(['msg' => '导入失败', 'data' => [], 'code' => 200]);
      }
     } else {
      return $this->asJson(['msg' => '导入失败,错误码:' . $error, 'code' => 400]);
      }
     }else{
      return $this->asJson(['msg' => '文件导入失败,没有找到数据流', 'code' => 400]);
      }
     }else{
      return $this->asJson(['请求错误','code'=>400]);
      }
    }
    
    //解析excel文件数据
    public function actionImportExcel($filename='uploads/20190920105001627.xlsx'){
      $dataArray = [];
      $Sheets = \PHPExcel_IOFactory::load($filename);
      $dataArray = $Sheets->getSheet(0)->toArray();
      array_shift($dataArray);
      $excel_array = [];
     foreach($dataArray as $k=>$v) {
      $excel_array[$k]['cardId'] = $v[0];
      $excel_array[$k]['cardStatus'] = $v[1];
      $excel_array[$k]['initBalance'] = $v[2];
      $excel_array[$k]['cardBalance'] = $v[3];
      $excel_array[$k]['agentId'] = $v[4];
      $excel_array[$k]['cardNo'] = $v[5];
      $excel_array[$k]['vcOrderId'] = '-';
      $excel_array[$k]['nFXRate'] = '-';
      $excel_array[$k]['vcUnitName'] = '-';
      $excel_array[$k]['productName'] = '-';
      }
      return json_encode($excel_array);
    }

qiaoshuai_job

qiaoshuai_job
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!