Yii2 Tips 27 : Import Data from Excel
1. Install yii2 export
|
1 |
composer require --prefer-dist hscstudio/yii2-export "1.0.0" |
2. Excel file , i.e (take note on the baseRow and baseColumn) : 3. Create table and the model
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
<?php namespace common\models; use Yii; /** * This is the model class for table "category". * * @property integer $id * @property string $title * @property string $description */ class Category extends \yii\db\ActiveRecord { /** * @inheritdoc */ public static function tableName() { return 'category'; } /** * @inheritdoc */ public function rules() { return [ [['description'], 'string'], [['title'], 'string', 'max' => 255], ]; } /** * @inheritdoc */ public function attributeLabels() { return [ 'id' => Yii::t('app', 'ID'), 'title' => Yii::t('app', 'Title'), 'description' => Yii::t('app', 'Description'), ]; } } |
4. Put actionImport (or whatever the name is), to your controller
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
public function actionImport(){ $modelImport = new \yii\base\DynamicModel([ 'fileImport'=>'File Import', ]); $modelImport->addRule(['fileImport'],'required'); $modelImport->addRule(['fileImport'],'file',['extensions'=>'ods,xls,xlsx'],['maxSize'=>1024*1024]); if(Yii::$app->request->post()){ $modelImport->fileImport = \yii\web\UploadedFile::getInstance($modelImport,'fileImport'); if($modelImport->fileImport && $modelImport->validate()){ $inputFileType = \PHPExcel_IOFactory::identify($modelImport->fileImport->tempName); $objReader = \PHPExcel_IOFactory::createReader($inputFileType); $objPHPExcel = $objReader->load($modelImport->fileImport->tempName); $sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true); $baseRow = 3; while(!empty($sheetData[$baseRow]['B'])){ $model = new \common\models\Category; $model->title = (string)$sheetData[$baseRow]['B']; $model->description = (string)$sheetData[$baseRow]['C']; $model->save(); $baseRow++; } Yii::$app->getSession()->setFlash('success','Success'); }else{ Yii::$app->getSession()->setFlash('error','Error'); } } return $this->render('import',[ 'modelImport' => $modelImport, ]); } |
5. Create import.php
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<?php use yii\widgets\ActiveForm; use yii\helpers\Html; ?> <h1>Import Data</h1> <?php $form = ActiveForm::begin(['options'=>['enctype'=>'multipart/form-data']]);?> <?= $form->field($modelImport,'fileImport')->fileInput() ?> <?= Html::submitButton('Import',['class'=>'btn btn-primary']);?> <?php ActiveForm::end();?> |