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

本文实例讲述了yii2.0框架实现上传excel文件后导入到数据库的方法。分享给大家供大家参考，具体如下：

model模型

<?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 '验证失败';
    }
  }

}

views视图

<?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() ?>

controller控制器

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