codemix / yii2-excelexport
A utility to quickly create Excel files from query results or raw data
Installs: 311 128
Dependents: 2
Suggesters: 0
Security: 0
Stars: 104
Watchers: 13
Forks: 33
Open Issues: 1
Type:yii2-extension
Requires
- php: >=7.4
- mikehaertl/php-tmpfile: ^1.0.0
- phpoffice/phpspreadsheet: ^1.25.2
- yiisoft/yii2: ~2.0.13
README
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
ExcelSheet
ActiveExcelSheet
The class extends from ExcelSheet
but differs in the following properties:
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'); } ], ], ]);