import excel and save into database

I have THREE tables

CREATE TABLE building (

building_id int(23) NOT NULL AUTO_INCREMENT PRIMARY KEY,

city_id int(12) NOT NULL,

cat_id int(12) NOT NULL

building_name varchar(30) NOT NULL,

corporation_name varchar(25) NOT NULL,

supply_rating varchar(20) NOT NULL,

last_inspection date DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE cities (

city_id int(12) NOT NULL AUTO_INCREMENT PRIMARY KEY,

city_name char(254) NOT NULL,

city_location text NOT NULL,

state_id int(12) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE building_category (

cat_id int(12) NOT NULL AUTO_INCREMENT PRIMARY KEY,

cat_name varchar(85) NOT NULL,

parent_id int(12) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tHE MAIN TABLE IS BUILDING.

I have an excel file that i want to import into the application, and the save it to the database.

7032

building.PNG

I want it that as its doing the import or upload, it will check the tables of the foreign keys. I f the values have not been created, it will create it. Am try to use the code below, but dont know how to really go about it. Please any other solution?

Controller




<?php


/**

 *

 * @package modules.consumers.controllers

 */


namespace app\modules\consumers\controllers;


use Yii;

use arogachev\excel\import\basic\Importer;

use yii\web\UploadedFile;

use PHPExcel;

use PHPExcel_IOFactory;

use yii\helpers\Html;

use app\modules\consumers\models\Building;

use app\modules\consumers\models\BuildingManager;

use app\modules\consumers\models\BuildingCategory;

use app\models\AuthAssignment;

use app\models\City;

use app\models\State;

use app\models\Zone;

use app\models\User;


class BuildingImportController extends \yii\web\Controller

{

    public function actionIndex()

    {

		$model = new Building();		

		$model->scenario = 'import-building';

		$importResults = [];


		if ($model->load(Yii::$app->request->post())) {			

			$model->importFile = UploadedFile::getInstance($model, 'importFile');

			if($model->saveImportFile()) {

				$importResults = $this->importBuildingData($model);

				//print_r($importResults); exit;

			}			

		}


        return $this->render('index', [

			'model' => $model,

			'importResults' => $importResults,

		]);

    }




	public function importBuildingData($model)

	{

		$dispResults = []; 

		$totalSuccess = 0;

		

		$objPHPExcel = PHPExcel_IOFactory::load($model->importFilePath.$model->importFile);

		$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);

		

		//print_r($sheetData); exit;

		unset($sheetData[1]);


		//start import building row by row

		foreach($sheetData as $k => $line){

			//print_r($line); exit;

			if(!array_filter($line))

				continue;

				 

			$line = array_map('trim', $line);

			$line = array_map(function($value) { return empty($value) ? NULL : $value; }, $line);

			

			$buildingMaster = new Building();

			$buildingMaster->scenario = 'import-building';


			//set building info attributes

			$buildingMaster->last_inspection = Yii::$app->dateformatter->getDateFormat($line['A']); //Last Inspection Date

			$buildingMaster->building_name = $line['B']; //Building Name 

//			$buildingMaster->corporation_name = $line['C']; //Corporation Name

			$buildingMaster->address = $line['C']; //Address

			$buildingMaster->status = $this->valueReplace($line['D'], $buildingMaster->getBuildingStatus()); // Building Status

			$buildingMaster->corporation_type = $this->valueReplace($line['E'], $buildingMaster->getCorporationTypes()); // Corporation Types

			$buildingMaster->supply_rating = $line['F']; //Supply Rating




			

			//set building master attribute

			$buildingMaster->city = $this->valueReplace($line['L'], City::getAllCity()); //City

			$buildingMaster->cat_id = $this->valueReplace($line['M'], BuildingCategory::getBuildingCategoryId()); //Building Category

			$buildingMaster->manager_id = $this->valueReplace($line['N'], BuildingManager::getBuildingManager()); //Building Manager


			if($buildingMaster->save())


			//set building info attribute

			$buildingManager->corporation_name = $line['O']; //Corporation Name

		return ['dispResults' => $dispResults, 'totalSuccess' => $totalSuccess];

	}


	protected function valueReplace($value, $arrayData)

    {

    	if(empty($value) || empty($arrayData)) {

    		return null;

    	} else {

    		$key = array_search(strtolower($value), array_map('strtolower', $arrayData));

    		return ($key) ? $key : NULL;

    	}    

    }

	

	public function actionDownloadFile($id)

    {

    	$path = null;

		

    	if($id=='SSF') {

    			$path = Yii::getAlias('@webroot').'/data/import_files/building_import_en.xlsx';

    	}

    

		if(file_exists($path)) {

			return \Yii::$app->response->sendFile($path);

		}

			throw new NotFoundHttpException('The requested file does not exist.');	

    }

}




VIEW




<?php

use yii\helpers\Html;

use yii\widgets\Breadcrumbs;

use yii\grid\GridView;


use yii\helpers\Url;

use yii\helpers\ArrayHelper;

use yii\widgets\ActiveForm;

use yii\widgets\Pjax;

fedemotta\datatables\DataTablesBootstrapAsset::register($this);


/* @var $this yii\web\View */


$this->title = Yii::t('consumers', 'Import Building');

$this->params['breadcrumbs'][] = ['label' => Yii::t('consumers', 'Building Profile Master'), 'url'=>['default/index']];

$this->params['breadcrumbs'][] = $this->title;

?>


<?php $form = ActiveForm::begin([

	'id' => 'import-building',

	'enableAjaxValidation' => false,

	'options' => ['enctype' => 'multipart/form-data'],


]); ?>


<div class="box box-primary">

	<div class="box-header with-border">

		<h3 class="box-title"><i class="fa fa-file-excel-o"></i> <?php echo Yii::t('consumers', 'Select File'); ?></h3>

	</div><!--./box-header-->

	<div class="box-body">

		<div class="row">

			<div class="col-sm-12 col-xs-12">

				<?= $form->field($model, 'importFile')->fileInput(['title' => Yii::t('consumers', 'Browse Excel File')])->label(false) ?>

			</div>

		</div>

		<div class="row">

			<div class="col-sm-12 col-xs-12">

				<div class="callout callout-info">

					<h4><?php echo Yii::t('consumers', 'You must have to follow the following instruction at the time of importing data'); ?></h4>

					<ol>

						<li><b><?php echo Yii::t('consumers', 'The field with red color are the required field cannot be blank.'); ?></b></li>

						<li><?php echo Yii::t('consumers', 'Building ID is auto generated.'); ?></li>

					</ol>

					<h5><?php echo Yii::t('consumers', 'Download the sample format of Excel sheet.'); ?> <b><?= Html::a(Yii::t('consumers', 'Download'), ['download-file', 'id' => 'SSF']) ?></b></h5>

				</div><!--./callout-->

			</div><!--./col-->

		</div><!--./row-->

	</div><!--./box-body-->

	<div class="box-footer">

		<?= Html::submitButton('<i class="fa fa-upload"></i>'.Yii::t('consumers', ' Import'), ['class' => 'btn btn-primary']) ?>

	</div>

</div><!--./box-->

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


<?php if(!empty($importResults)) : ?>

<!--Start Import Summary Results Block-->

<div class="box box-info">

	<div class="box-header">

		<h3 class="box-title"><i class="fa fa-list-ul"></i> <?php echo Yii::t('consumers', 'Buildings Import Results'); ?></h3>

	</div>

	<div class="box-body">

		<div class="row">

			<div class="col-sm-12">

				<?php $totalError = (count($importResults['dispResults'])-$importResults['totalSuccess']); ?>

				<?php $headerTr = $content = ''; $i = 1; ?>

				

			</div>

		</div>

	</div><!--./box-body-->

</div><!--./box-->

<?php endif; ?>


<?php

$ckBoxCheckScript = <<< JS

    var table = $('#fixHeader').DataTable( {

        scrollY:        "450px",

        scrollX:        true,

        scrollCollapse: false,

        paging:         false,

        bSort: 			false,

        bInfo: 			false,

    } );  

JS;

$this->registerJs($ckBoxCheckScript, yii\web\View::POS_READY);

?>




Please help me. Any other solution

If you want help, you might have to ask more specific questions.

You can’t expect others to write your code for you (unless you pay them of course).

Sorry, I didnt say they should my code for me. What I want is any other idea.

Thanks

The basic idea is OK.

What you have to do is create a static method that returns the id of a city from its name. When it is already registered, just return its id. When it’s not registered, register it and return its newly created id.




class City extends ActiveRecord {

...

    public static function getRegisteredId($name)

    {

        $city = City::findOne(['name' => $name]);

        if (empty($city)) {

            $city = new City(['name' => $name]);

            $city->save();

        }

        return $city->id;

    }



Then importing can be written like:




    $buildingMaster->city = City::getRegisteredId($line['L']);



The same strategy applies to building’s category.

And, slightly OT, I would not write importing methods in the controller. They should be written in Building model, IMO.

Thanks a lot. You have really solved my problem.

But there is one thing that you omitted. In the city table,

there is foreign key called state_id, it is not in the excel but it’s linked to a table called state

CREATE TABLE cities (

city_id int(12) NOT NULL AUTO_INCREMENT PRIMARY KEY,

city_name char(254) NOT NULL,

city_location text NOT NULL,

state_id int(12) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE state (

state_id int(12) NOT NULL AUTO_INCREMENT PRIMARY KEY,

state_name char(254) NOT NULL,

zone_id int(12) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Then you can not create a new entry in the city table on the fly while registering buildings.

Consider using a two-step approach.

In the 1st step, just check the city names in the excel sheet and list all the missing cities yet to be registered.

And let the user register the missing cities manually.

And in the 2nd step, import all the buildings.

Sorry if my question sounds child (I started using Yii not quite long). How do I go about this. Just a brief hint. Please

I would do something like the following (pseudo code):




open uploaded excel file

$errors = [];

db transaction start

foreach ($rows as $row) {

    check if $row->city_name is already registered

    if yes

        register building

    else

        $errors[$row->city_name] = $row->city_name;

}

if count($errors) === 0 {

    db transaction commit;

    echo "Importing completed";

} else {

    db transaction rollback;

    echo "Importing failed";

    echo "Register the following cities before you import buildings."

    foreach ($errors as $error)

        echo $error;

}



This will work as step 2 as well as step 1. The user must register the cities according to the error report that this page produces.

Thanks a lot. You are great. The work is done

Dear Sir,
I am working with yii2 on import excel.
Please tell me how to display excel data in browser before user click save.
Thanks inadvance!

I would use a temporary table to hold the raw data from an excel sheet. Then it would be easy to display them in a grid view.

below is the controller

public function actionUpload()
    {
        $model = new UploadForm();

        if (Yii::$app->request->isPost) {
            $model->file = UploadedFile::getInstance($model, 'file');
            if ($model->upload()) {

                $file = $model->file;

                try {
                    $inputFileType = IOFactory::identify($file);
                    $objReader = IOFactory::createReader($inputFileType);
                    $objPHPExcel = $objReader->load($file);
                } catch (Exception $e) {
                    print_r($e->getMessage() . $e->getFile());
                    exit();
                }

                $transaction = Yii::$app->db->beginTransaction();
                $num = $objPHPExcel->getSheetCount();//bao nhieu sheets
                $sheetnames = $objPHPExcel->getSheetNames();//ten sheets
                for ($i = 0; $i < $num; $i++) {
                    $sheet = $objPHPExcel->getSheet($i);

                    $highestRow = $sheet->getHighestRow();
                    $highestColumn = $sheet->getHighestColumn();
                    $rowHead = $sheet->rangeToArray('A1:' . $highestColumn . '1', NULL, TRUE, FALSE);
                    $rowData = $sheet->rangeToArray('A3:' . $highestColumn . $highestRow, NULL, TRUE, FALSE);
                    $rowHead = $rowHead[0];

                    foreach ($rowData as $row) {
                        $a = [];
                        foreach ($row as $index => $value) {
                            $a[$rowHead[$index]] = $value;
                        }
                        $model_cabenh = new Cabenh();
//                        if (!$model_cabenh->save()) {
//                            $dataProvider = new ArrayDataProvider([
//                                'allModels' => $a,
//                                'pagination' => [
//                                    'pageSize' => 10,
//                                ],
//                            ]);
//                            return $this->render('display', ['model' => $model, 'dataProvider' => $dataProvider,]);
//                        }
                        try {
                            $b = [$model_cabenh->formName() => $a];
                            //                dd($b);
                            $model_cabenh->load($b);
                            $model_cabenh->delete = 1;
                            $model_cabenh->save();
                        } catch (InvalidConfigException $e) {
                        }
                    }
                }
                try {
                    $transaction->commit();
                    echo "<script>alert('Import thành công ');</script>";
                } catch (\yii\db\Exception $e) {
                    $transaction->rollBack();
                    die('Xem lại');
                }
                unlink($file);
            } else {
                $message = "Import thất bại";
                echo "<script type='text/javascript'>alert('$message'); window.history.back ()</script>";
            }
        }

        return $this->render('upload', ['model' => $model]);
    }

public function actionDisplay()
    {

        $model = new UploadForm();

        $model->file = UploadedFile::getInstance($model, 'file');
        if ($model->upload()) {

            $file = $model->file;

            try {
                $inputFileType = IOFactory::identify($file);
                $objReader = IOFactory::createReader($inputFileType);
                $objPHPExcel = $objReader->load($file);
            } catch (Exception $e) {
                print_r($e->getMessage() . $e->getFile());
                exit();
            }

            $num = $objPHPExcel->getSheetCount();//bao nhieu sheets
            $sheetnames = $objPHPExcel->getSheetNames();//ten sheets
            for ($i = 0; $i < $num; $i++) {
                $sheet = $objPHPExcel->getSheet($i);

                $highestRow = $sheet->getHighestRow();
                $highestColumn = $sheet->getHighestColumn();
                $rowHead = $sheet->rangeToArray('A1:' . $highestColumn . '1', NULL, TRUE, FALSE);
                $rowData = $sheet->rangeToArray('A3:' . $highestColumn . $highestRow, NULL, TRUE, FALSE);
                $rowHead = $rowHead[0];

                foreach ($rowData as $row) {
                    $a = [];
                    foreach ($row as $index => $value) {
                        $a[$rowHead[$index]] = $value;
                    }
                }
            }
        }

        $dataProvider = new ArrayDataProvider([
            'allModels' => $a,
            'pagination' => [
                'pageSize' => 10,
            ],
        ]);
        unlink($file);
        return $this->render('display', ['dataProvider' => $dataProvider]);
    }

below is view

<?php

use app\widgets\gridview\GridView;
use yii\bootstrap4\Modal;

/* @var $dataProvider yii\data\ActiveDataProvider */

?>

<div class="cabenh-display">
    <div>
        <?=$this->widget([
            'dataProvider' => $dataProvider,
            'pjax'=>true,
            'columns' => require(__DIR__.'/_columns.php'),
//            'toolbar'=> [
//                ['content'=>
//                    Html::a('<i class="icon-folder-plus2"></i> Thêm mới', ['create'],
//                    ['data-pjax'=>0,'title'=> 'Thêm mới ','class'=>'btn btn-success'])
//                ],
//            ],
            'striped' => true,
            'condensed' => true,
            'responsive' => true,
            'hover'=>true,
            'panel' => [
                'heading' => '<i class="fas fa-book"></i>  Import ca bệnh',
                'type' => 'success'
            ]
        ])?>
    </div>
</div>

below is UploadForm model

<?php


namespace app\modules\app\models\form;


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

class UploadForm extends Model
{
    /**
     * @var UploadedFile
     */
    public $file;

    public function rules()
    {
        return [
            [['file'], 'file', 'skipOnEmpty' => false, 'extensions' => 'xlsx, xls'],
        ];
    }

    public function upload()
    {
        if ($this->validate()) {
            $this->file->saveAs($this->file);
            return true;
        } else {
            return false;
        }
    }
}

below is upload view

<?php

use PhpOffice\PhpSpreadsheet\IOFactory;
use yii\helpers\Html;
use yii\helpers\Url;
use yii\widgets\ActiveForm;

$this->title = 'Import Excel';
?>

<?php $form = ActiveForm::begin(['options' => ['enctype' => 'multipart/form-data']]) ?>


<!--    <button>Submit</button>-->
<div class="card-body">
    <div class="row">
        <div class="col-md-4">
            <?= $form->field($model, 'file')->fileInput()->label(false) ?>
        </div>
        <div class="col-md-4"></div>
        <div class="col-md-4">
            <div class="btn-group pull-right">
                <?= Html::submitButton('<span class="ladda-label"><i class="icon-database-insert"></i> Nhập tất cả</span>') ?>
            </div>
            <div class="btn-group pull-right">
                <?= Html::a('<span class="ladda-label"><i class="icon-database-insert"></i> Xem</span>','display') ?>
            </div>
        </div>
    </div>
</div>

<?php ActiveForm::end() ?>

please help me!

My idea of importing excel data is like this:

  1. Use 4 models (and 2 tables)
    1. Cabenh … cabenh table
    2. TempCabenh … temp_cabenh table
      • almost the same with Cabenh model
      • but all the attributes (columns) are of string type, since you may not expect the user to upload the excel with the expected format
    3. UploadForm … without underlying table
      • must have the excel file data field $file
    4. ImportForm … without underlying table
      • must have a string field $action, which must be “import”
  2. Use 3 actions
    1. ActionUpload … uploads excel and save TempCabenh data
    2. ActionDisplay … displays the uploaded data (TempCabenh data)
    3. ActionImport … imports TempCabenh data to Cabenh data
  3. Use 3 views
    1. upload view
    2. display view
    3. import result view … this can be the existing “index” view

The workflow goes like the following:

  1. upload view
    • renders the upload form to let the user select an excel file and upload it.
  2. upload action
    • receives UploadForm post data
    • validates if the uploaded file is in fact an excel file
    • validates if the uploaded excel file has the expected format of data - it must have expected number of columns, and the columns of the 1st row (the header row) must have the expected titles
    • if validation failes, shows the upload view again with some error messages
    • if it’s OK, imports the data to TempCabenh and redirects to “display”
  3. display view
    • displays the TempCabenh data with a grid view, which allows the user to see if the uploaded file has expected kind of data
    • user can go back to “upload”, or go forward to “import” by submitting ImportForm to import action
  4. import action
    • receives ImportForm post data
    • validates if $action is “import”
    • if it’s OK, reads TempCabenh data and imports it to Cabenh
    • if something goes wrong, shows “display” again with error message
    • if it’s OK, redirects to import result view (for which you can use “index” view)

You must empty the TempCabenh somewhere, probably before importing data from an excel file.

And, I think you may consider using other medium other than database table for TempCabenh, maybe cache data or session data, though I’m not very sure.

Thank you very much, you are so enthusiastic for giving me your precious time. Looks like I’ve come up with something, I’ll try it now. Once again sincerely thank you

1 Like

@pinky2070500 Thank you, I appreciate your kind words.
I wanted to clarify my vague idea by writing it down.
I hope it may help someone a little, and I also hope someone may help me correct or improve my idea.

Thank you for what you share
I have basically completed my task. And it worked smoothly following the following route: upload → display → import.
Now I want when I import the data, the lines that have errors will be ignored and continue to import until the end and output a warning for the lines with errors. Do you have an opinion? thanks in advance

I think that TempCabenh model can have some extra fields which indicate the integrity of the row data and the result of import.

We can check each row data of the uploaded excel sheet and flag it as OK or NG, and if it’s NG we can add an error message why it’s NG.

This checking of the data may be done before the “display” view is rendered, or after the import is done. But IMO the former might be more user friendly since you can tell the result of importing before actually doing import.