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);
  }

本作品采用《CC 协议》，转载必须注明作者和本文链接