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