Yii2 Excel Export

Note: The minimum requirement since 2.6.0 is Yii 2.0.13. The latest version for older Yii releases is 2.5.0.

Features

• Export data from ActiveQuery results
• Export any other data (Array, Iterable, ...)
• Create excel files with multiple sheets
• Format cells and values

To write the Excel file, we use the excellent PHPSpreadsheet package.

Installation

Install the package with composer:

composer require codemix/yii2-excelexport

Quickstart example

<?php
$file = \Yii::createObject([
    'class' => 'codemix\excelexport\ExcelFile',
    'sheets' => [
        'Users' => [
            'class' => 'codemix\excelexport\ActiveExcelSheet',
            'query' => User::find(),
        ]
    ]
]);
$file->send('user.xlsx');

Find more examples below.

Configuration and Use

ExcelFile

Property	Description
writerClass	The file format as supported by PHPOffice. The default is \PhpOffice\PhpSpreadsheet\Writer\Xlsx
sheets	An array of sheet configurations (see below). The keys are used as sheet names.
fileOptions	Options to pass to the constructor of mikehaertl\tmp\File. Available keys are prefix, suffix and directory.

Methods	Description
saveAs($name)	Saves the excel file under $name
send($name=null, $inline=false, $contentType = 'application/vnd.ms-excel')	Sends the excel file to the browser. If $name is empty, the file is streamed for inline display, otherwhise a download dialog will open, unless $inline is true which will force inline display even if a filename is supplied.
createSheets()	Only creates the sheets of the excel workbook but does not save the file. This is usually called implicitely on saveAs() and send() but can also be called manually to modify the sheets before saving.
getWriter()	Returns the \PhpOffice\PhpSpreadsheet\Writer\BaseWrite instance
getWorkbook()	Returns the \PhpOffice\PhpSpreadsheet\Spreadsheet workbook instance
getTmpFile()	Returns the mikehaertl\tmp\File instance of the temporary file

ExcelSheet

Property	Description
data	An array of data rows that should be used as sheet content
titles (optional)	An array of column titles
types (optional)	An array of types for specific columns as supported by PHPOffice, e.g. DataType::TYPE_STRING, indexed either by column name (e.g. H) or 0-based column index.
formats (optional)	An array of format strings for specific columns as supported by Excel, e.g. #,##0.00, indexed either by column name (e.g. H) or 0-based column index.
formatters (optional)	An array of value formatters for specific columns. Each must be a valid PHP callable whith the signature function formatter($value, $row, $data) where $value is the cell value to format, $row is the 0-based row index and $data is the current row data from the data configuration. The callbacks must be indexed either by column name (e.g. H) or by the 0-based column index.
styles (optional)	An array of style configuration indexed by cell coordinates or a range.
callbacks (optional)	An array of callbacks indexed by column that should be called after rendering a cell, e.g. to apply further complex styling. Each must be a valid PHP callable with the signature function callback($cell, $col, $row) where $cell is the current PhpOffice\PhpSpreadsheet\Cell\Cell object and $col and $row are the 0-based column and row indices respectively.
startColumn (optional)	The start column name or its 0-based index. When this is set, the 0-based offset is added to all numeric keys used anywhere in this class. Columns referenced by name will stay unchanged. Default is 'A'.
startRow (optional)	The start row. Default is 1.

Event	Description
beforeRender	Triggered before the sheet is rendered. The sheet is available via $event->sender->getSheet().
afterRender	Triggered after the sheet was rendered. The sheet is available via $event->sender->getSheet().

ActiveExcelSheet

The class extends from ExcelSheet but differs in the following properties:

Property	Description
query	The ActiveQuery for the row data (the data property will be ignored).
data	The read-only property that returns the batched query result.
attributes (optional)	The attributes to use as columns. Related attributes can be specifed in dot notation as usual, e.g. team.name. If not set, the attributes() from the corresponding ActiveRecord class will be used.
titles (optional)	The column titles, indexed by column name (e.g. H) or 0-based column index. If a column is not listed here, the respective attribute label will be used. If set to false no title row will be rendered.
formats (optional)	As in ExcelSheet but for date, datetime and decimal DB columns, the respective formats will be automatically set by default, according to the respective date format properties (see below) and the decimal precision.
formatters (optional)	As in ExcelSheet but for date and datetime columns the value will be autoconverted to the correct excel time format with \PHPExcel_Shared_Date::PHPToExcel() by default.
dateFormat	The excel format to use for date DB types. Default is dd/mm/yyyy.
dateTimeFormat	The excel format to use for datetime DB types. Default is dd/mm/yyyy hh:mm:ss.
batchSize	The query batchsize to use. Default is 100.
modelInstance (optional)	The query's modelClass instance used to obtain attribute types and titles. If not set an instance of the query's modelClass is created automatically.

Note Since version 2.3.1 datetime attributes will automatically be converted to the correct timezone. This feature makes use of the current defaultTimeZone and timeZone setting of the app.

Examples

ActiveQuery results

<?php
$file = \Yii::createObject([
    'class' => 'codemix\excelexport\ExcelFile',

    'writerClass' => '\PhpOffice\PhpSpreadsheet\Writer\Xls', // Override default of `\PhpOffice\PhpSpreadsheet\Writer\Xlsx`

    'sheets' => [

        'Active Users' => [
            'class' => 'codemix\excelexport\ActiveExcelSheet',
            'query' => User::find()->where(['active' => true]),

            // If not specified, all attributes from `User::attributes()` are used
            'attributes' => [
                'id',
                'name',
                'email',
                'team.name',    // Related attribute
                'created_at',
            ],

            // If not specified, the label from the respective record is used.
            // You can also override single titles, like here for the above `team.name`
            'titles' => [
                'D' => 'Team Name',
            ],
        ],

    ],
]);
$file->send('demo.xlsx');

Raw data

<?php
$file = \Yii::createObject([
    'class' => 'codemix\excelexport\ExcelFile',
    'sheets' => [

        'Result per Country' => [   // Name of the excel sheet
            'data' => [
                ['fr', 'France', 1.234, '2014-02-03 12:13:14'],
                ['de', 'Germany', 2.345, '2014-02-05 19:18:39'],
                ['uk', 'United Kingdom', 3.456, '2014-03-03 16:09:04'],
            ],

            // Set to `false` to suppress the title row
            'titles' => [
                'Code',
                'Name',
                'Volume',
                'Created At',
            ],

            'formats' => [
                // Either column name or 0-based column index can be used
                'C' => '#,##0.00',
                3 => 'dd/mm/yyyy hh:mm:ss',
            ],

            'formatters' => [
                // Dates and datetimes must be converted to Excel format
                3 => function ($value, $row, $data) {
                    return \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(strtotime($value));
                },
            ],
        ],

        'Countries' => [
            // Data for another sheet goes here ...
        ],
    ]
]);
// Save on disk
$file->saveAs('/tmp/export.xlsx');

Query builder results

<?php
$file = \Yii::createObject([
    'class' => 'codemix\excelexport\ExcelFile',
    'sheets' => [

        'Users' => [
            'data' => new (\yii\db\Query)
                ->select(['id','name','email'])
                ->from('user')
                ->each(100);
            'titles' => ['ID', 'Name', 'Email'],
        ],
    ]
]);
$file->send('demo.xlsx');

Styling

Since version 2.3.0 you can style single cells and cell ranges via the styles property of a sheet. For details on the accepted styling format please consult the PhpSpreadsheet documentation.

<?php
$file = \Yii::createObject([
    'class' => 'codemix\excelexport\ExcelFile',
    'sheets' => [
        'Users' => [
            'class' => 'codemix\excelexport\ActiveExcelSheet',
            'query' => User::find(),
            'styles' => [
                'A1:Z1000' => [
                    'font' => [
                        'bold' => true,
                        'color' => ['rgb' => 'FF0000'],
                        'size' => 15,
                        'name' => 'Verdana'
                    ],
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_RIGHT,
                    ],
                ],
            ],
        ]
    ]
]);

As you have access to the PHPExcel object you can also "manually" modify the excel file as you like.

<?php
// Create the actual workbook and sheets
$file->createSheets();
$file
    ->getWorkbook();
    ->getSheet(1)
    ->getStyle('B1')
    ->getFont()
    ->getColor()
    ->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
$file->send();

Alternatively you can also use the callback feature from our ExcelSheet:

<?php
$file = \Yii::createObject([
    'class' => 'codemix\excelexport\ExcelFile',
    'sheets' => [
        'Users' => [
            'class' => 'codemix\excelexport\ActiveExcelSheet',
            'query' => User::find(),
            'callbacks' => [
                // $cell is a \PhpOffice\PhpSpreadsheet\Cell object
                'A' => function ($cell, $row, $column) {
                    $cell->getStyle()->applyFromArray([
                        'font' => [
                            'bold' => true,
                        ],
                        'alignment' => [
                            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT,
                        ],
                        'borders' => [
                            'top' => [
                                'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                            ],
                        ],
                        'fill' => [
                            'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
                            'rotation' => 90,
                            'startColor' => [
                                'argb' => 'FFA0A0A0',
                            ],
                            'endColor' => [
                                'argb' => 'FFFFFFFF',
                            ],
                        ],
                    ]);
                },
            ],
        ],
    ],
]);

Events

Since version 2.5.0 there are new events which make it easier to further modify each sheet.

<?php
$file = \Yii::createObject([
    'class' => 'codemix\excelexport\ExcelFile',
    'sheets' => [
        'Users' => [
            'class' => 'codemix\excelexport\ActiveExcelSheet',
            'query' => User::find(),
            'startRow' => 3,
            'on beforeRender' => function ($event) {
                $sheet = $event->sender->getSheet();
                $sheet->setCellValue('A1', 'List of current users');
            }
        ],
    ],
]);