1. Latest Update:
2. Goal:
3. Sample Picture:
4. Steps:
5. Details:
6. 1. Create a php file in your components folder, such as HzlUtil.php
7. 2. Build in functions save2excel, excelColumnName, getCssClass, excelDataFormat into HzlUtil.php
8. 3. Create a controller actionDownload in your site controller
9. 4. Create a behavior file in your components folder, such as ExportBehavior.php
10. 5. Bind that behavior in any of your controllers.

Latest Update: ¶

I have generate one extension base below wiki. You can easily download and use it directly instead of digest below article. http://www.yiiframework.com/extension/yii2-export2excel/

Goal: ¶

To generate excel by query content, and download through any controller. You also can bind cssClass to header/odd/even rows. As for header, you even can set cssClass for each column.

$this->export2excel($excel_content,$excel_file);

in your controller.

It also can invoke from model or view too. But I recommend you do it through controller.

Sample Picture: ¶

Steps: ¶

1. Create a php file in your components folder, such as HzlUtil.php
2. Build in functions save2excel, excelColumnName, getCssClass, excelDataFormat into HzlUtil.php
3. Create a controller actionDownload in your site controller
4. Create a behavior file in your components folder, such as ExportBehavior.php It will invoke #1~#3.
5. Bind that behavior in any of your controllers. Then you can easily export any of database contents.

Details: ¶

1. Create a php file in your components folder, such as HzlUtil.php ¶

You need to install PHPExcel in advance, better through composer, such as below: Add phpexcel into composer.json file require session.

"require": {
        "phpoffice/phpexcel": "1.8.0"
    },

Please baidu/google for how to install it if still have questions. thanks.

2. Build in functions save2excel, excelColumnName, getCssClass, excelDataFormat into HzlUtil.php ¶

use \PHPExcel;
use \PHPExcel_IOFactory;
use \PHPExcel_Settings;
use \PHPExcel_Style_Fill;
use \PHPExcel_Writer_IWriter;
use \PHPExcel_Worksheet;
use \PHPExcel_Style;

    public static function excelColumnName($index)
    {
        --$index;
        if ($index >= 0 && $index < 26)
            return chr(ord('A') + $index);
        else if ($index > 25)
            return (self::excelColumnName($index / 26)) . (self::excelColumnName($index % 26 + 1));
        else
            throw new Exception("Invalid Column # " . ($index + 1));
    }

    public static function excelDataFormat($data)
    {
        for ($i = 0; $i < count($data); $i++) {
            $each_arr = $data[$i];
            $new_arr[] = array_values($each_arr); //返回所有键值
        }
        $new_key[] = array_keys($data[0]); //返回所有索引值
        return array('excel_title' => $new_key[0], 'excel_ceils' => $new_arr);
    }

public static function getCssClass($code = '')
    {
        $cssClass =
            array(
                'red' => array('color' => 'FFFFFF', 'background' => 'FF0000'),
                'pink' => array('color' => '', 'background' => 'FFCCCC'),
                'green' => array('color' => '', 'background' => 'CCFF99'),
                'lightgreen' => array('color' => '', 'background' => 'CCFFCC'),
                'yellow' => array('color' => '', 'background' => 'FFFF99'),
                'white' => array('color' => '', 'background' => 'FFFFFF'),
                'grey' => array('color' => 'FFFFFF', 'background' => '808080'),
                'greywhite' => array('color' => 'FFFFFF', 'background' => '808080'),
                'blue' => array('color' => 'FFFFFF', 'background' => 'blue'),
                'lightblue' => array('color' => 'FFFFFF', 'background' => '6666FF'),
                'notice' => array('color' => '514721', 'background' => 'FFF6BF'),
                'header' => array('color' => 'FFFFFF', 'background' => '519CC6'),
                'odd' => array('color' => '', 'background' => 'E5F1F4'),
                'even' => array('color' => '', 'background' => 'F8F8F8'),
            );

        if (empty($code)) return $cssClass;
        elseif (isset($cssClass[$code])) return $cssClass[$code];
        else return [];
    }

public static function save2Excel($excel_content, $excel_file
        , $excel_props = array('creator' => 'WWSP Tool'
        , 'title' => 'WWSP_Tracking EXPORT EXCEL'
        , 'subject' => 'WWSP_Tracking EXPORT EXCEL'
        , 'desc' => 'WWSP_Tracking EXPORT EXCEL'
        , 'keywords' => 'WWSP Tool Generated Excel, Author: Scott Huang'
        , 'category' => 'WWSP_Tracking EXPORT EXCEL'))
    {
        if (!is_array($excel_content)) {
            return FALSE;
        }
        if (empty($excel_file)) {
            return FALSE;
        }
        $objPHPExcel = new PHPExcel();
        $objProps = $objPHPExcel->getProperties();
        $objProps->setCreator($excel_props['creator']);
        $objProps->setLastModifiedBy($excel_props['creator']);
        $objProps->setTitle($excel_props['title']);
        $objProps->setSubject($excel_props['subject']);
        $objProps->setDescription($excel_props['desc']);
        $objProps->setKeywords($excel_props['keywords']);
        $objProps->setCategory($excel_props['category']);

        $style_obj = new PHPExcel_Style();
        $style_array = array(
//            'borders' => array(
//                'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
//                'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
//                'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
//                'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN)
//            ),
            'alignment' => array(
                'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
                'wrap' => true
            ),
        );
        $style_obj->applyFromArray($style_array);

//开始执行EXCEL数据导出
        for ($i = 0; $i < count($excel_content); $i++) {
            $each_sheet_content = $excel_content[$i];
            if ($i == 0) {
//默认会创建一个sheet页，故不需在创建
                $objPHPExcel->setActiveSheetIndex(intval(0));
                $current_sheet = $objPHPExcel->getActiveSheet();
            } else {
//创建sheet
                $objPHPExcel->createSheet();
                $current_sheet = $objPHPExcel->getSheet($i);
            }
//设置sheet title
            $current_sheet->setTitle(str_replace(array('/', '*', '?', '\\', ':', '[', ']'), array('_', '_', '_', '_', '_', '_', '_'), substr($each_sheet_content['sheet_name'], 0, 30))); //add by Scott
            $current_sheet->getColumnDimension()->setAutoSize(true); //Scott, set column autosize
//设置sheet当前页的标题
            $_columnIndex = 'A';

            $lineRange = "A1:" . HzlUtil::excelColumnName(count($each_sheet_content['sheet_title'])) . "1";
            $current_sheet->setSharedStyle($style_obj, $lineRange);

            if (array_key_exists('sheet_title', $each_sheet_content) && !empty($each_sheet_content['sheet_title'])) {
                //header color
                if (array_key_exists('headerColor', $each_sheet_content) && is_array($each_sheet_content['headerColor']) and !empty($each_sheet_content['headerColor'])) {
                    if (isset($each_sheet_content['headerColor']["color"]) and $each_sheet_content['headerColor']['color'])
                        $current_sheet->getStyle($lineRange)->getFont()->getColor()->setARGB($each_sheet_content['headerColor']['color']);
                    //background
                    if (isset($each_sheet_content['headerColor']["background"]) and $each_sheet_content['headerColor']['background']) {
                        $current_sheet->getStyle($lineRange)->getFill()->getStartColor()->setRGB($each_sheet_content['headerColor']["background"]);
                        $current_sheet->getStyle($lineRange)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                    }
                }

                for ($j = 0; $j < count($each_sheet_content['sheet_title']); $j++) {
                    $current_sheet->setCellValueByColumnAndRow($j, 1, $each_sheet_content['sheet_title'][$j]);
                    //start handle hearder column css
                    if (array_key_exists('headerColumnCssClass', $each_sheet_content)) {
                        if (isset($each_sheet_content["headerColumnCssClass"][$each_sheet_content['sheet_title'][$j]])) {
                            $tempStyle = $each_sheet_content["headerColumnCssClass"][$each_sheet_content['sheet_title'][$j]];
                            $tempColumn = HzlUtil::excelColumnName($j + 1) . "1";
                            if (isset($tempStyle["color"]) and $tempStyle['color'])
                                $current_sheet->getStyle($tempColumn)->getFont()->getColor()->setARGB($tempStyle['color']);
                            //background
                            if (isset($tempStyle["background"]) and $tempStyle['background']) {
                                $current_sheet->getStyle($tempColumn)->getFill()->getStartColor()->setRGB($tempStyle["background"]);
                                $current_sheet->getStyle($tempColumn)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                            }
                        }
                    }
                    $current_sheet->getColumnDimension($_columnIndex)->setAutoSize(true); //
                    $_columnIndex++;
                }
            }
            if (array_key_exists('freezePane', $each_sheet_content) && !empty($each_sheet_content['freezePane'])) {
                $current_sheet->freezePane($each_sheet_content['freezePane']);
            }
//写入sheet页面内容
            if (array_key_exists('ceils', $each_sheet_content) && !empty($each_sheet_content['ceils'])) {
                for ($row = 0; $row < count($each_sheet_content['ceils']); $row++) {
                    //setting row css
                    $lineRange = "A" . ($row + 2) . ":" . HzlUtil::excelColumnName(count($each_sheet_content['ceils'][$row])) . ($row + 2);
                    if (($row + 1) % 2 == 1 and isset($each_sheet_content["oddCssClass"])) {
                        if ($each_sheet_content["oddCssClass"]["color"])
                            $current_sheet->getStyle($lineRange)->getFont()->getColor()->setARGB($each_sheet_content["oddCssClass"]["color"]);
                        //background
                        if ($each_sheet_content["oddCssClass"]["background"]) {
                            $current_sheet->getStyle($lineRange)->getFill()->getStartColor()->setRGB($each_sheet_content["oddCssClass"]["background"]);
                            $current_sheet->getStyle($lineRange)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                        }
                    } else if (($row + 1) % 2 == 0 and isset($each_sheet_content["evenCssClass"])) {
//                        echo "even",$row,"<BR>";
                        if ($each_sheet_content["evenCssClass"]["color"])
                            $current_sheet->getStyle($lineRange)->getFont()->getColor()->setARGB($each_sheet_content["evenCssClass"]["color"]);
                        //background
                        if ($each_sheet_content["evenCssClass"]["background"]) {
                            $current_sheet->getStyle($lineRange)->getFill()->getStartColor()->setRGB($each_sheet_content["evenCssClass"]["background"]);
                            $current_sheet->getStyle($lineRange)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                        }
                    }
                    //write content
                    for ($l = 0; $l < count($each_sheet_content['ceils'][$row]); $l++) {
                        $current_sheet->setCellValueByColumnAndRow($l, $row + 2, $each_sheet_content['ceils'][$row][$l]);
                    }
                }
            }

        }
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $tempDir = Yii::getAlias('@webroot') . '/uploads/temp/'; 
        if (!is_dir($tempDir)) {
            mkdir($tempDir);
            chmod($tempDir, 0755);
// the default implementation makes it under 777 permission, which you could possibly change recursively before deployment, but here's less of a headache in case you don't
        }
        $file_name = $tempDir . yii::$app->user->identity->username . '-' . str_replace(array('/', '*', '?', '\\', ':', '[', ']'), array('_', '_', '_', '_', '_', '_', '_'), $excel_file) . '-' . date('Ymd-His') . '.xlsx';

        $objWriter->save($file_name);
        return $file_name;
//        if (!file_exists($file_name)) {
//            HzlUtil::setMsg("Error", "File not exist");
//            return false;
//        }
    }

3. Create a controller actionDownload in your site controller ¶

public function actionDownload($file_name, $file_type = 'excel', $deleteAfterDownload = false) {
        if (empty($file_name)) {
//            return $this->goBack();
            return 0;
        }
        $baseRoot = Yii::getAlias('@webroot') . "/uploads/";
        $file_name = $baseRoot . $file_name;
        //echo $file_name,"<BR/>";
        if (!file_exists($file_name)) {
            //HzlUtil::setMsg("Error", "File not exist");
            return 0;
        }
        $fp = fopen($file_name, "r");
        $file_size = filesize($file_name);
//下载文件需要用到的头
        if ($file_type == 'excel') {
            header('Pragma: public');
            header('Expires: 0');
            header('Content-Encoding: none');
            header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
            header('Cache-Control: public');
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Description: File Transfer');
            Header("Content-Disposition: attachment; filename=" . basename($file_name));
            header('Content-Transfer-Encoding: binary');
            Header("Content-Length:" . $file_size);
        } else if ($file_type == 'picture') { //pictures
            Header("Content-Type:image/jpeg");
            Header("Accept-Ranges: bytes");
            Header("Content-Disposition: attachment; filename=" . basename($file_name));
            Header("Content-Length:" . $file_size);
        } else { //other files
            Header("Content-type: application/octet-stream");
            Header("Accept-Ranges: bytes");
            Header("Content-Disposition: attachment; filename=" . basename($file_name));
            Header("Content-Length:" . $file_size);
        }

        $buffer = 1024;
        $file_count = 0;
//向浏览器返回数据
        while (!feof($fp) && $file_count < $file_size) {
            $file_con = fread($fp, $buffer);
            $file_count+=$buffer;
            echo $file_con;
        }
        //echo fread($fp, $file_size);
        fclose($fp);
        if ($deleteAfterDownload) {
            unlink($file_name);
        }
        return 1;
    }

4. Create a behavior file in your components folder, such as ExportBehavior.php ¶

It will invoke #1~#3.

<?php
/**
 * Created by PhpStorm.
 * User: Scott_Huang
 * Date: 6/16/2015
 * Time: 2:48 PM
 */
namespace app\components;

use yii\base\Behavior;
use yii\helpers\Url;

class ExportBehavior extends Behavior
{
    public $prop1;
    private $_prop2;

    public function getProp2()
    {
        return $this->_prop2;
    }

    public function setProp2($value)
    {
        $this->_prop2 = $value;
    }

public function export2Excel($excel_content, $excel_file
        , $excel_props = array('creator' => 'WWSP Tool'
        , 'title' => 'WWSP_Tracking EXPORT EXCEL'
        , 'subject' => 'WWSP_Tracking EXPORT EXCEL'
        , 'desc' => 'WWSP_Tracking EXPORT EXCEL'
        , 'keywords' => 'WWSP Tool Generated Excel, Author: Scott Huang'
        , 'category' => 'WWSP_Tracking EXPORT EXCEL'))
    {
        if (!is_array($excel_content)) {
            return FALSE;
        }
        if (empty($excel_file)) {
            return FALSE;
        }
        $excelName = HzlUtil::save2Excel($excel_content, $excel_file, $excel_props);
        if ($excelName) {
            return $this->owner->redirect([Url::to('site/download'), "file_name" => 'temp/' . basename($excelName)
                , "file_type" => 'excel'
                , 'deleteAfterDownload' => true]);
        }
    }

}

5. Bind that behavior in any of your controllers. ¶

~~~
    public function behaviors()
    {
        return [
            'export2excel' => [
                'class' => ExportBehavior::className(),
            ]
        ];
    }

   Then you can easily export any of database contents.
------------------------------------------------------==----

[PHP]

    $excel_data = HzlUtil::excelDataFormat(EOPStatus::find()->asArray()->all()); 
    $excel_title = $excel_data['excel_title'];
    $excel_ceils = $excel_data['excel_ceils'];
    $excel_content = array(
        array(
            'sheet_name' => 'EOPStatus',
            'sheet_title' => $excel_title,
            'ceils' => $excel_ceils,
            'freezePane' => 'B2',
            'headerColor' => HzlUtil::getCssClass("header"),
            'headerColumnCssClass' => array(
                'id' => HzlUtil::getCssClass('blue'),
                'Status_Description' => HzlUtil::getCssClass('grey'),
            ), //define each column's cssClass for header line only.  You can set as blank.
            'oddCssClass' => HzlUtil::getCssClass("odd"),
            'evenCssClass' => HzlUtil::getCssClass("even"),
        ),
        array(
            'sheet_name' => 'Important Note',
            'sheet_title' => array("Important Note For Region Template"),
            'ceils' => array(
                array("1.Column Platform,Part,Region must need update.")
            , array("2.Column Regional_Status only as Regional_Green,Regional_Yellow,Regional_Red,Regional_Ready.")
            , array("3.Column RTS_Date, Master_Desc, Functional_Desc, Commodity, Part_Status are only for your reference, will not be uploaded into NPI tracking system."))
        ),
    );
    $excel_file = "testYii2Save2Excel";
    $this->export2excel($excel_content,$excel_file);

......