README

注意：从code1mix/yii2-excelexport项目分支，仅做了简单修改以支持在php7.2版本下使用。在此感谢原作者提供了如此优秀的开源项目。

功能

• 从ActiveQuery结果导出数据
• 导出任何其他数据（数组、可迭代对象等）
• 创建包含多个工作表的Excel文件
• 格式化单元格和值

为了写入Excel文件，我们使用了优秀的PHPSpreadsheet包。

安装

使用composer安装此包

composer require sunyujin/yii2-excelexport

快速入门示例

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

下面有更多示例。

配置和使用

ExcelFile

ExcelSheet

ActiveExcelSheet

该类继承自ExcelSheet，但在以下属性上有所不同

注意 从版本2.3.1开始，datetime属性将自动转换为正确的时区。此功能使用了当前应用的默认时区和时区设置。

示例

ActiveQuery结果

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

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

    'sheets' => [

        'Active Users' => [
            'class' => 'sunyujin\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');

原始数据

<?php
$file = \Yii::createObject([
    'class' => 'sunyujin\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');

查询构建器结果

<?php
$file = \Yii::createObject([
    'class' => 'sunyujin\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');

样式

从版本2.3.0开始，您可以通过工作表的styles属性来样式化单个单元格和单元格范围。有关接受样式格式的详细信息，请参阅PhpSpreadsheet文档。

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

由于您有访问PHPExcel对象，因此也可以“手动”按您喜欢的任何方式修改Excel文件。

<?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();

或者，您还可以使用我们的ExcelSheet的回调功能

<?php
$file = \Yii::createObject([
    'class' => 'sunyujin\excelexport\ExcelFile',
    'sheets' => [
        'Users' => [
            'class' => 'sunyujin\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',
                            ],
                        ],
                    ]);
                },
            ],
        ],
    ],
]);

事件

从版本2.5.0开始，有一些新事件，这使得进一步修改每个工作表更加容易。

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