ExcelController.php

<?php

namespace backend\controllers;

use backend\models\ExcelExportForm;
use backend\models\ExcelImportForm;
use backend\models\File;
use backend\models\search\ProductSearch;
use common\models\Product;
use moonland\phpexcel\Excel;
use Yii;
use yii\helpers\ArrayHelper;
use yii\web\Controller;
use yii\web\UploadedFile;

/**
 * Class ExcelController
 * @package backend\controllers
 */
class ExcelController extends Controller
{
    /**
     * @return string|\yii\web\Response
     */
    public function actionIndex()
    {
        $searchModel = new ProductSearch();
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

        $excelExportForm = new ExcelExportForm();
        if (Yii::$app->request->isPost && $excelExportForm->load(Yii::$app->request->post())){
            $exportAttributes = $excelExportForm->getExportAttributes();

            $file = new File(['fileName' => 'product-export-'.date('Y-m-d-H-i-s').'.xls']);

            $result = Excel::export([
                'savePath' => $file->path,
                'fileName' => $file->fileName,
                'format' => 'Excel5',
                'asAttachment' => false,
                'models' => $searchModel->queryExport(Yii::$app->request->queryParams, array_keys($exportAttributes))
                    ->asArray()->all(),
                'columns' => $exportAttributes, //without header working, because the header will be get label from attribute label.
                'headers' => $exportAttributes,
            ]);


            if ($result) {
                $this->setFlash('alert-success', 'File was successfully created');
                return $this->redirect(['files']);
            } else {
                $this->setFlash('alert-danger', 'Error occurred');
                return $this->refresh();
            }

        }
        return $this->render('index', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
            'excelExportForm' => $excelExportForm,
        ]);
    }

    /**
     * @return string|\yii\web\Response
     */
    public function actionImport()
    {
        $model = new ExcelImportForm();
        if ($model->load(Yii::$app->request->post())) {
            $model->file = UploadedFile::getInstance($model, 'file');
            $file = new File(['fileName' => 'product-import-'.date('Y-m-d-H-i-s').'.xls']);
            $model->filePath = $file->path . $file->fileName;
            if ($model->upload()) {
                $this->setFlash('alert-success', 'File was successfully loaded');
                $data = Excel::import([
                    'fileName' => $model->filePath,
//                    'format' => 'Excel5',
//                    'setFirstRecordAsKeys' => true,
                ]);

                $data = ArrayHelper::index($data['fileName'], 'part_numb');
                $part_numbs = ArrayHelper::getColumn($data, 'part_numb');

                if($data && key_exists('brand_id', current($data))){

                    foreach (Product::find()->byPartNumb($part_numbs)->each(200) as $product){
                        /* @var $product Product */

                        if(isset($data[$product->part_numb]) && $data[$product->part_numb]['brand_id'] == $product->brand_id){
                            $product->setScenario(Product::SCENARIO_EXPORT);
                            $product->setAttributes($data[$product->part_numb]);
                            $product->save(false);
                        }
                    }
                    if (!empty($product)){
                        $product->invalidateTagDependency();
                    }

                    Yii::warning([
                        'user' => Yii::$app->user->identity->username . ' ' . Yii::$app->user->identity->email,
                        'update_products' => implode(', ',$part_numbs)
                    ], 'import-products');
                }

                $this->setFlash('alert-success', 'File was successfully loaded and importing');

            } else {
                $this->setFlash('alert-danger', 'Error occurred');
            }
        }
        return $this->render('import', ['model' => $model]);
    }

    /**
     * @return string
     */
    public function actionFiles()
    {
        $csv = new File();

        return $this->render('files', [
            'scroll' => $csv->scroll(),
        ]);
    }

    /**
     * @param string $name
     * @return \yii\web\Response
     */
    public function actionDelete($name)
    {
        $csv = new File;
        if ($csv->delete($name)) {
            $this->setFlash('alert-success', 'File was successfully deleted');
        } else {
            $this->setFlash('alert-danger', 'Error occurred');
        }
        return $this->redirect(['excel/files']);
    }

    /**
     * @param $class
     * @param $message
     */
    protected function setFlash($class, $message)
    {
        Yii::$app->session->setFlash('alert', [
            'body'=> Yii::t('backend', $message),
            'options'=>['class'=>$class]
        ]);
    }
}