Yii2.0 Excel导入 批量插入数据

上传类:

common\models\UploadForm.php

namespace common\models;

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

/**
 * UploadForm is the model behind the upload form.
 */
class UploadForm extends Model
{
/**
 * @var UploadedFile file attribute
 */
public $file;

/**
 * @return array the validation rules.
 */
public function rules()
{
    return [
        [['file'], 'file'],
    ];
}

/**
 * @inheritdoc
 */
public function attributeLabels()
{
    return [
        'file' => '请选择文件',

    ];
}
}

视图调用:

<div>
    <h4>用户信息导入:</h4>
    <?php
        $model =  new UploadForm;
        $form = ActiveForm::begin(['action'=>['inport'],'options' => ['enctype' => 'multipart/form-data']]);
    ?>

    <?= $form->field($model, 'file')->fileInput() ?>

    <button class="btn btn-success">上传导入</button>

    <?php ActiveForm::end() ?>
    <br>

</div>
<!-- 错误弹框 -->
<div>
    <?php
        if( Yii::$app->getSession()->hasFlash('error') ) {
        echo Alert::widget([
            'options' => [
                'class' => 'alert-danger',
            ],
            'body' => Yii::$app->getSession()->getFlash('error'),
        ]);
    }
    ?>
</div>

控制器:

// 导入数据
public function actionInport ()
{
    // 判断是不是excel文件
    if($_FILES['UploadForm']['error']['file'] > 0){
        $data = array('error'=>'1','msg'=>'文件上传失败,请重新上传..','info'=>'');
    }
    set_time_limit(0);
    // ini_set('memory_limit','256M');

    // 事务开始
    $transaction = Yii::$app->db->beginTransaction();

    try{
        // 读取新的excel
        $model = new Excel;
        $model->readExcel($_FILES['UploadForm']['tmp_name']['file']);

        //获取总行 列
        $excel = $model->getobjPhpexcel();
        //多少行
        $total_line   = $excel->getActiveSheet()->getHighestRow();
        // echo "行".$total_line."<br>";
        //多少列
        $total_column = $excel->getActiveSheet()->getHighestColumn();
        $total_column = $model->getNum($total_column);
        // echo "列".$total_column."<br>";

		// 插入的表名称
        $tableName = User::tableName();
        // 要插入的字段
        $field = [
            'username','server_num','duty','department','classes',
            'parent_name','add_man','job_status','mobile','email',
            'education','major','identity_num',
        ];

        $attributes = array();
        $cols = array();
        // 逐行读取数据
        for ($n=2; $n <= $total_line; $n++)//行
        {
            $flag =0;

            for ($i=0 ; $i < $total_column; $i++)//列
            {
               $string = $model->getCellValue($i,  $n);
               $cols[$flag] = $string;
               $flag++;
            }
            //保存数据
            $yyb = new User();
            $yyb->username     = $cols[0];
            $yyb->server_num   = $cols[1];
            $yyb->duty         = $cols[2];
            $yyb->department   = $cols[3];
            $yyb->classes      = $cols[4];
            $yyb->parent_name  = $cols[5];
            $yyb->add_man      = $cols[6];
            $yyb->job_status   = $cols[7];
            $yyb->mobile       = $cols[8];
            $yyb->email        = $cols[9];
            $yyb->education    = $cols[10];
            $yyb->major        = $cols[11];
            $yyb->identity_num = $cols[12];

            $attributes[] = $yyb;
        }

        // 检验数据合法性
        $rows = [];
        foreach ($attributes as $k => $model) {
            if (!$model->validate()) {
                    $eor = $model->errors;
                    // print_r($eor);
                    // exit;
                    foreach ($eor as $v)
                    {
                        $ee = implode(',',$v);
                    }
                    // 抛出异常错误
                    $message = "Excel数据 第".($k+1)."行 出错!"."<br><br>错误: <b>".$ee."</b>";
                    throw new \yii\db\Exception($message);
            }
            $rows[] = [
                       'username'         => $model->username,
                       'server_num'       => $model->server_num,
                       'duty'             => $model->duty,
                       'department'       => $model->department,
                       'classes'          => $model->classes,
                       'parent_name'      => $model->parent_name,
                       'add_man'          => $model->add_man,
                       'job_status'       => $model->job_status,
                       'mobile'           => $model->mobile,
                       'email'            => $model->email,
                       'education'        => $model->education,
                       'major'            => $model->major,
                       'identity_num'     => $model->identity_num,
            ];
        }

        $totalnum = Yii::$app->db->createCommand()->batchInsert($tableName,$field,$rows)->execute();
        $transaction->commit();
    }
    catch (\Exception $e) {

        $transaction->rollBack();

        \Yii::$app->getSession()->setFlash('error',$e->getMessage());
    }

    $this->redirect(['user/index']);

}



导航