excel文件导入，大家一起来指点分享 [ 新手入门 ]

在网上找的方法，简单整理了一下分享给大家

public function actionSow()
{
    $model = new PigData();
    $ok = "";
    if ($model->load(Yii::$app->request->post())) {
        //1.获取上传的文件实例
        $file = UploadedFile::getInstance($model, 'id' );  //获取上传的文件实例

    if ($file) {
        //2.设置文件名称

        $filename = date('Y-m-d',time()).'_'.rand(1,9999).".". $file->extension;

        //3.保存文件
        $file->saveAs('file/'.$filename);   //保存文件

        $format = $file->extension;


        if(in_array($file->extension,array('xls','xlsx'))){

        //4.获取文件路径
            $excelFile = Yii::getAlias('@frontend/web/file/'.$filename.'');//获取文件名

                // $phpexcel=new \PHPExcel();
                // if ($format == "xls") {
                //    $excelReader = \PHPExcel_IOFactory::createReader('Excel5');
                // } else {
                //    $excelReader = \PHPExcel_IOFactory::createReader('Excel2007');
                // }

        //5.判断文件类型
                $fileType   = \PHPExcel_IOFactory::identify($excelFile); //文件名自动判断文件类型

                $excelReader  = \PHPExcel_IOFactory::createReader($fileType);

        //6.载入文件并获取第一个sheet
                $phpexcel    = $excelReader->load($excelFile)->getSheet(0);//载入文件并获取第一个sheet

        //7.获取总行数
                $total_line  = $phpexcel->getHighestRow();//总行数

        //8.获取最大列数
                $total_column= $phpexcel->getHighestColumn();//总列数

                // $highestColumn = $objWorksheet->getHighestColumn();//最大列数 为数字
                // $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn); //将字母变为数字

        //9.开始事务
            $transaction = Yii::$app->db->beginTransaction();   //事务开始
            try{
                if($total_line > 1){
                    for($row = 2;$row <= $total_line; $row++){
                        $data = '';
                        for($column = 'A'; $column <= $total_column; $column++){
                            $data[] = trim($phpexcel->getCell($column.$row)->getValue());

                        }

                        //10.一行行的插入数据库操作
                        $_model = new Homes();
                        $_model->homes_name = $data[0];
                        $_model->create_time = time();
                        if(!$_model->save())
                        {
                            $error=array_values($_model->getFirstErrors())[0];
                            throw new Exception($error);//抛出异常
                        }

                    }
                }
                $transaction->commit();
                $this->redirect(['index']);
            }catch (yii\db\Exception $e)
            {
                $transaction->rollBack();
                echo "<script>alert('操作失败');window.history.back();</script>";
            }


        }
    }

    } else {
        return $this->render('create-kong', [
            'model' => $model,
        ]);
    }
}