yii2.0框架实现上传excel文件后导入到数据库的方法示例

<?php
/**
 * 描述...
 * @author zcy
 * @date 2019/8/13
 */

namespace app\models;

use yii\base\Model;
use yii\db\ActiveRecord;
use yii\web\UploadedFile;

class uploadForm extends ActiveRecord
{
  public $file;

  public function rules()
  {
    return [
      [['file'],'file', 'skipOnEmpty' =  false,'extensions' =  'xls,xlsx'],
    ];
  }

  public function attributeLabels()
  {
    return [
      'file'=  '上传文件'
    ];
  }

  public function upload()
  {
    $file = UploadedFile::getInstance($this, 'file');

    if ($this- rules()) {
      $tmp_file = $file- baseName . '.' . $file- extension;
      $path = 'upload/' . 'Files/';
      if (is_dir($path)) {
        $file- saveAs($path . $tmp_file);
      } else {
        mkdir($path, 0777, true);
      }
      $file- saveAs($path . $tmp_file);
      return true;
    } else {
      return '验证失败';
    }
  }

}

<?php

use yii\widgets\ActiveForm;

$model = new app\models\uploadForm();
$form = ActiveForm::begin([
  'id' =  'upload',
  'options' =  ['enctype' =  'multipart/form-data'],
])
? 

<?= $form- field($model,'file')- fileInput(['multiple'= 'multiple']) ? 

  <button 上传</button 
<?php ActiveForm::end() ? 

<?php
/**
 * 描述...
 * @author zcy
 * @date 2019/8/16
 */

namespace app\controllers;

use app\models\uploadForm;
use Yii;
use yii\web\Controller;
use yii\web\UploadedFile;

class UploadController extends Controller
{
  /**
   * 导入
   * @author zcy
   * @date 2019/8/16
   */
  public function actionImport()
  {
    $model = new uploadForm();

    if (Yii::$app- request- isPost) {
      $model- file = UploadedFile::getInstance($model,'file');
//      if ($model- upload()) {
//        print <<<EOT
// <script alert('上传成功')</script 
//EOT;
//      } else {
//        print <<<EOT
// <script alert('上传失败')</script 
//EOT;
//      }
      if (!$model- upload()) {
        print <<<EOT
 <script alert('上传失败')</script 
EOT;
      }
    }

    $ok = 0;
    if ($model- load(Yii::$app- request- post())) {
      $file = UploadedFile::getInstance($model,'file');

      if ($file) {
        $filename = 'upload/Files/' . $file- name;
        $file- saveAs($filename);

        if (in_array($file- extension,array('xls','xlsx'))) {
          $fileType = \PHPExcel_IOFactory::identify($filename);//文件名自动判断类型
          $excelReader = \PHPExcel_IOFactory::createReader($fileType);

          $phpexcel = $excelReader- load($filename)- getSheet(0);//载入文件并获取第一个sheet
          $total_line = $phpexcel- getHighestRow();//总行数
          $total_column = $phpexcel- getHighestColumn();//总列数

          if (1 < $total_line) {
            for ($row = 2;$row <= $total_line;$row++) {
              $data = [];
              for ($column = 'A';$column <= $total_column;$column++) {
                $data[] = trim($phpexcel- getCell($column.$row));
              }

              $info = Yii::$app- db- createCommand()
- insert('{{%shop_info}}',['shop_name' =  $data[0],'shop_type' =  $data[1]])
- execute();

              if ($info) {
                $ok = 1;
              }
            }
          }

          if ($ok == 1) {
            echo "<script alert('导入成功');window.history.back();</script ";
          } else {
            echo "<script alert('操作失败');window.history.back();</script ";
          }
        }
      }
    } else {
      return $this- render('import',['model' =  $model]);
    }
  }
}