Laravel Maatwebsite/Excel 3.1 导出表格

环境

• Laravel 9.28.0
• PHP 8.0.20

安装

composer安装

composer require maatwebsite/excel

使用

1 普通导出

1.1 创建导出类

php artisan make:export ExcleExport

会在 app 目录下创建 Exports 目录

.
├── app
│   ├── Exports
│   │   ├── ExcleExport.php
│ 

ExcleExport.php内容

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithColumnWidths;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;

class ExcelExport implements ShouldAutoSize, FromCollection, withHeadings, WithColumnWidths, WithStrictNullComparison
{
	// 外部调用传入数据，用来实现多次调用，也可在该文件中直接设置
    private $headers; // 表头
    private $data; // 数据
    private $column_widths; // 自定义列宽

    // 数据注入
    public function __construct($headers, $data, $column_widths = []) {
        $this->headers = $headers;
        $this->data = $data;
        $this->column_widths = $column_widths;
    }
    // 自定义表头，需实现withHeadings接口
    public function headings(): array
    {
        return $this->headers;
    }

    // 列宽，实现WithColumnWidths接口
    public function columnWidths(): array {
        return $this->column_widths;
    }

    // 数据
    public function collection()
    {
        return $this->data;
    }
}

1.2 调用

	/**
     * 导出活动数据
     */
    public function export()
    {
        $headers = ['编号', '活动名称', '发起单位', '开始时间', '结束时间', '当前阶段', '投稿人数'];
        $column_widths = ['A' => 10, 'B' => 20, 'C' => 20, 'D' => 15, 'E' => 15, 'F' => 10, 'G' => 10];

        $data = Activity::query()->where('is_del', 2)->get(['id', 'activity_name', 'company_name', 'start_time', 'end_time', 'activity_state', 'join_count']);

        $export = new ExcelExport($headers, $data, $column_widths);
        return Excel::download($export, date('YmdHis') . '.xlsx',);
    }

1.3最终结果

2 多sheet导出

多sheet导出和普通导出类似，只是分为了两步，下面示例为导出app渠道数据，包含4个sheet，分别为渠道统计，30天内每天的下载量，注册量，启动次数

2.1 创建sheet类

php artisan make:export ChannelListSheet
php artisan make:export ChannelDownloadSheet
php artisan make:export ChannelRegisterSheet
php artisan make:export ChannelStartupSheet

分别实现四个sheet导出类，代码与普通导出一样，下面只展示其中一个类的代码：

<?php

namespace App\Exports\Channel;

use Illuminate\Support\Facades\Cache;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\WithColumnWidths;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithTitle;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class ChannelListSheet implements WithTitle, FromArray, WithHeadings, WithColumnWidths, WithColumnFormatting
{
    // 标题
    public function title(): string
    {
        return '渠道统计';
    }

    public function headings(): array
    {
        return ['渠道', '下载量', '注册量', '注册转化率', '认证转化率', '付费转化率', '创作者转化率'];
    }

    // 数据
    public function array(): array {
        $channelList = Cache::get('CHANNEL_GENGRAL_VIEW');
        if (is_null($channelList)) {
            return [];
        } else {
            foreach ($channelList as $k => $v) {
                $channelList[$k]['download_num'] = strval($v['download_num']);
                $channelList[$k]['register_num'] = strval($v['register_num']);
                $channelList[$k]['register_percent'] = strval($v['register_percent']);
                $channelList[$k]['id_percent'] = strval($v['id_percent']);
                $channelList[$k]['pay_percent'] = strval($v['pay_percent']);
                $channelList[$k]['author_percent'] = strval($v['author_percent']);
            }
            return $channelList;
        }
    }

    // 列宽
    public function columnWidths(): array {
        return [];
    }

    // 格式化列
    public function columnFormats(): array
    {
        return [
            'B' => NumberFormat::FORMAT_NUMBER,
            'C' => NumberFormat::FORMAT_NUMBER,
            'D' => NumberFormat::FORMAT_PERCENTAGE_00,
            'E' => NumberFormat::FORMAT_PERCENTAGE_00,
            'F' => NumberFormat::FORMAT_PERCENTAGE_00,
            'G' => NumberFormat::FORMAT_PERCENTAGE_00,
        ];
    }
}

2.2 创建导出类

创建导出类，调用上面4个sheet类

php artisan make:export ChannelExport

代码如下：

<?php

namespace App\Exports\Channel;

use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class ChannelExport implements WithMultipleSheets
{

    public function sheets(): array
    {
        $sheets = [];
        $sheets[] = new ChannelListSheet();
        $sheets[] = new ChannelDownloadSheet();
        $sheets[] = new ChannelRegisterSheet();
        $sheets[] = new ChannelStartupSheet();
        return $sheets;
    }
}

2.3 调用

public function exportChannelData() {
	return Excel::download(new ChannelExport(), '渠道统计'.date('YmdHis') . '.xlsx');
}

2.4 最终结果