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();?> |
in which folder .. where to create import.php file
You should create the import.php inside the view folder corresponds to your controller. For example if you create the public function actionImport in TestController.php , then you should put import.php inside view/test folder.
Hi, I already implement your codes and it’s running flawlessly 😀 the table filled in with data from excel file called main_received.xlsx and this excel file is always updated by the end of the day. and what i want to ask you is there any way to update the table database from new data from updated excel file without erase previous data ?
Yes handy, I think it would also possible to update the excel file without erasing the previous data. All you need to do is modify the controller, you need to read the excel file and find the last row, then insert the data with baseRow = lastRow+1. Hope this helps 🙂
Hi, i already solved my previous issue using your codes in comment section 😀
but sorry for ask, is there any kind of way to filter event from calendar based on select option ? It would be very generous if you can give a sample codes. I really hope you can help.
how to get data in merge cell?
Hi, how to import images from a table? Help please, thank you very much!
Never tried it before, however, seems like we need to fetch the image link information first from the cell, then extract it afterwards.
Help please, thank you very much!
hello, I can’t run this code in my code. The result after I import not showing result in database
May I know the error message?
Same Problem here. There is no error i did var_dump(sheetData) and it throws the data which is in sheet file but the loop is not executing the save() function may be .??
Done you are Roczz,Thank you So Much…..
Class ‘PHPExcel_IOFactory’ not found how to solve the error
Sudah diinstall melalui composer?
Please help me, I can upload file .xls but I can’t upload file .xlsx
https://imgur.com/Cip8p9R
Class ‘PHPExcel_IOFactory’ not found
Related to PHPExcel version
the file was importing successfully but not updated in database, i check properly.
But not fix.