GridView To Excel Export Extension In Yii Framework 2.0

Are you struggling for exporting data to excel? This tutorial will helpful to create your own extension for exporting data as excel from your gridview data. What you display in gridview (text), It will export as excel.

Install PHP Excel In Yiiframework 2.0

Add below line in composer.json and update composer to install phpoffice excel.
"require": {
        ......
		"phpoffice/phpexcel": "*"
		......
    }

Excel Gridview Class In Yiiframework 2.0

I created 'ExcelGrid.php' class from existing class on web and it is extending the basic GridView class. First You have to download this class. Then add it in your application and change the namespace for this class (based on installation).

'ExcelGrid.php' Class Format


<?php
namespace app\components;
//namespace bsource\gridview;//in vendor folder
use Yii;
use Closure;
use yii\i18n\Formatter;
..........

class ExcelGrid extends \yii\grid\GridView
{
	..........
	public function init(){
		parent::init();
	}
	public function run(){
		..........
		parent::run();
	}
	
	public function init_provider(){
		..........
	}
	public function init_excel_sheet(){
		..........
	}
	public function initPHPExcelWriter($writer)
    {
        ..........
    }
	public function generateHeader(){
		..........
	}
	
	public function generateBody()
    {
        ..........
    }
	
    public function generateRow($model, $key, $index)
    {
        ..........
    }
	
	protected function setVisibleColumns()
    {
        ..........
    }
	
	public function getColumnHeader($col)
    {
		..........
    }
	public static function columnName($index)
    {
        ..........
    }
	
	protected function setHttpHeaders()
    {
		header("Cache-Control: no-cache");
		header("Expires: 0");
		header("Pragma: no-cache");
		header("Content-Type: application/{$this->extension}");
        header("Content-Disposition: attachment; filename={$this->filename}.{$this->extension}");
    }
}

Method 1:

Add this class in 'project/components' folder and defined namespace as
namespace app\components;

Method 2:

If would you like to add it in vendor folder, follow this method. First you have to creat a folder 'bsource/gridview' format and have to add this inside. Namespace like
namespace bsource/gridview;
After created class, you have to autoload this class. Goto file
'vendor/composer/autoload_psr4.php'
and add the below line in return array
'bsource\\gridview\\' => array($vendorDir . '/bsource/gridview'),

Gridview To Excel In Yiiframework 2.0

After completed above steps, Just you have to call 'ExcelGrid' widget using namespace to export data as excel in yii2.

Excel.php

<?php
// \app\components\ExcelGrid::widget([ OR 
\bsource\gridview\ExcelGrid::widget([
        'dataProvider' => $dataProvider,
        'filterModel' => $searchModel,
		//'extension'=>'xlsx',
		//'filename'=>'excel',
		'properties' =>[
			//'creator'	=>'',
			//'title' 	=> '',
			//'subject' 	=> '',
			//'category'	=> '',
			//'keywords' 	=> '',
			//'manager' 	=> '',
			//'description'=>'BSOURCECODE',
			//'company'	=>'BSOURCE',
		],
        'columns' => [
            ['class' => 'yii\grid\SerialColumn'],
			'username',
            'createdby',
            'createdon',
        ],
    ]);

Sample Controller.php


<?php
............
class CategoryController extends Controller
    public function actionExcel()
    {
        $searchModel = new categorySearch();
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

        return $this->renderPartial('excel', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
        ]);
    }
    public function actionIndex()
    {
        $searchModel = new categorySearch();
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

        return $this->render('index', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
        ]);
    }
............
?>
  • Raul

    Hola, when I call the index, in the menu report/index, only open dialog windows with the excel, wihtout data of course..
    How i can have a normal behavior, like a button.. Thanks I thinks will be a great works. 🙂

    • m bala

      I am using two individual actions to display data and generate excel like sample controller. I will try and update to display and excel export data in single action

  • Boki

    thank you

  • Nitish Mallick

    I have done all the steps mentioned above but I am getting a blank xls. can anybody tell me what did i miss ?

  • Reza Mukti

    Hi bscource,

    when i access to /category/excel.. its directly download into excel file. Is possible to display gridView like category/index an then export excel file with query criteria ?

    Thanks in advance

  • Miguel Almeida

    First off all, thank you for this simple “plugin”.

    I have two doubts ant I need your support:
    1) How to set the cell value format as currency? (EUR, €)
    2) How to set the header (with title and image) and footer (with date and page number?

    Thank you for your support.

  • Dvader

    how use this fuckin phpexcel without composer ?