Excel reader in YII2

PHPExcel extension is used to experting and importing Excel to PHP. In this example we are reading Excel data by using this extension and inserting the data in given tables accordingly.

PHPExcel extension is used to experting and importing Excel to PHP. In this example we are reading Excel data by using this extension and inserting the data in given tables accordingly.

Step 1: Installation : Download the extension from https://github.com/PHPOffice/PHPExcel and put it in vendor directory. Then update your composer or add the following code in your composer.jason file under require array().
code: “phpoffice/phpexcel”: “dev-develop” Download the extension from https://github.com/PHPOffice/PHPExcel and put it in vendor directory. Then update your composer or add the following code in your composer.jason file under require array().

code: "phpoffice/phpexcel": "dev-develop"

step 2: In your model :

public $files;
    public static function tableName()
    {
        return 'tbl';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [            
            [['files'], 'file', 'skipOnEmpty' => true, 'extensions' => 'csv, xlsx,xls', 'maxFiles' => 1],            
        ];
    }

Step 3: In your controller

	public function actionImport()
    {
		$objPHPExcel = new \PHPExcel();
		if (Yii::$app->request->isPost) {        
			$excel_files = UploadedFile::getInstance($model, 'files'); 
			$objPHPExcel = \PHPExcel_IOFactory::load($excel_files->tempName);  
			$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true); 
			 if(isset($sheetData)){
				foreach($sheetData as $row){ 
					/*Your code*/
				}
			 }
		}
	}

Step 3: In your View

<?php $form = ActiveForm::begin(['options' => ['enctype' => 'multipart/form-data']]) ?>
		<?= $form->field($model, 'files')->fileInput() ?>
	 <?php  echo Html::submitButton($model->isNewRecord ? 'Upload' : 'Update', [
		'class'=>$model->isNewRecord ? 'btn btn-success' : 'btn btn-primary']
	);?>
	<?php ActiveForm::end() ?>

Pankaj Singh Sugara