PHP将大量数据导出为Excel的解决方式及性能测试

在开发一些数据列表的时候经常会遇到需要导出到 Excel 的需求，为了解决这个需求博主经常会用到一些包。

例如 PHP Excel 但是已经不维护了，但衍生了一个项目：PhpSpreadsheet https://github.com/PHPOffice/PhpSpreadsheet

还有 Laravel Excel https://github.com/Maatwebsite/Laravel-Excel 和 php-ext-xlswriter https://github.com/viest/php-ext-xlswriter

导出的数据如果不多，可以消耗比较少的性能和比较快的速度，但是遇到数据量比较大的导出时，就可能遇到一些内存不足或者耗时较长的情况。

本文就是针对上面三个包进行简单的测试。

测试环境

Windows 10

PHP 7.3.4

MySQL 8.0.12

Laravel 6.20

分配给 PHP 的内存是 512 M.

本次测试均为导出 1万 / 5万 / 10万 条数据记录 且 使用 Laravel 的 Artisan 命令行.

在进行导出测试前，博主有一个这样的数据表：

表里的数据记录行数是一百万行：

关于这些数据，如果感兴趣可以查阅博主写的相关文章：

使用 Mysql 存储过程生成5万条数据记录

使用 Laravel 填充一百万假数据

接着生成一个 Post 模型并写入一个查询方法：

public function getData($toArray = true, $size = 10000)
{
    $rows = $this->limit($size)
        ->select('id', 'name', 'content', 'created_at')
        ->orderBy('id', 'ASC')
        ->get();

    if ($toArray) {
        return $rows->toArray();
    }
    return $rows;
}

本文所测试的导出均为先调用这个模型方法获取到需要导出的数据。

PhpSpreadsheet

安装 PhpSpreadsheet 请看 PhpSpreadsheet 的文档，本文不会再讲述安装过程。

先是测试 PhpSpreadsheet，使用 Laravel 的 Artisan：

php artisan make:command PhpSpreadsheetExport

在生成好的 PhpSpreadsheetExport 写入：

public function handle()
{
    $start = microtime(true);

    $this->export();

    $time = microtime(true) - $start;

    $this->info("所需耗时{$time}秒");
}

private function export()
{
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    $modelPost = new Post();
    $rowsPost = $modelPost->getData(true, 10000);

    $title = ['ID', '名称', '内容', '创建时间'];

    foreach ($title as $key => $value) {
        // 单元格内容写入
        $sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
    }

    $row = 2; // 从第二行开始 因为上面的标题已经占用了一行
    foreach ($rowsPost as $item) {
        $column = 1;
        foreach ($item as $value) {
            // 单元格内容写入
            $sheet->setCellValueByColumnAndRow($column, $row, $value);
            $column++;
        }
        $row++;
    }

    $writer = new Xlsx($spreadsheet);
    $writer->save(public_path('PhpSpreadsheet.xlsx'));
}

注意：除了写入 export 方法和修改 handle 方法，还需要 use 对应的类 (具体可查看 PhpSpreadsheet 的文档)。

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

最后执行命令：

php artisan excel:PhpSpreadsheet

得到的结果：

导出一万行 Excel 文件所需耗时2.1501450538635秒

导出五万行 Excel 文件所需耗时19.01673913002秒

导出十万行 Excel 文件所需耗时55.245916128159秒

Laravel Excel

和上面一样，先是使用 Composer 安装 Laravel Excel 并创建一个 Artisan 命令行。

与上面 PhpSpreadsheet 不一样的是，Laravel Excel 导出更容易操作，只需要在 App/Exports 定义一个类并调用即可。

例如在 App/Exports 定义一个 PostExport 并写入:

<?php

namespace App\Exports;

use App\Model\Post;
use Maatwebsite\Excel\Concerns\FromCollection;

class PostExport implements FromCollection
{
    public function collection()
    {
        $modelPost = new Post;
        return $modelPost->getData();
    }
}

在 LaravelExcelExport 的命令行中写入:

public function handle()
{
    $start = microtime(true);

    $this->export();

    $time = microtime(true) - $start;

    $this->info("所需耗时{$time}秒");
}

private function export()
{
    return Excel::download(new PostExport, 'posts_laravel_excel.xlsx');
}

得到的结果：

导出一万行 Excel 文件所需耗时2.3059990406036秒

导出五万行 Excel 文件所需耗时20.298142910004秒

导出十万行 Excel 文件所需耗时58.98757314682秒

php-ext-xlswriter

相比上面的两个包，php-ext-xlswriter 的安装就不只是 Composer 执行命令安装这么简单了，php-ext-xlswriter 需要安装 PHP 扩展，具体的安装方法和使用的系统（比如本文编写时使用的是 Windows 系统进行测试的）有差异，本文不讲述安装扩展过程。

安装好 php-ext-xlswriter 后同样进行创建 Artisan 命令行并写入:

public function handle()
{
    $start = microtime(true);

    $this->export();

    $time = microtime(true) - $start;

    $this->info("所需耗时{$time}秒");
}

private function export()
{
    $modelPost = new Post;
    $rowsPost = $modelPost->getData(true, 10000);

    // 进行数据组装 如果你有更好的方法可以不这样
    $lstPost = [];
    foreach ($rowsPost as $rowPostKey => $rowPost) {
        $lstPost[$rowPostKey] = [
            $rowPost['id'],
            $rowPost['name'],
            $rowPost['content'],
            $rowPost['created_at']
        ];
    }

    $config = ['path' => public_path()];
    $excel  = new Excel($config);

    $filePath = $excel->fileName('xls_writer.xlsx', 'sheet1')
        ->header(['ID', '名称', '内容', '创建时间'])
        ->data($lstPost)
        ->output();
}

需要引入的 (具体的信息看 php-ext-xlswriter 的文档)：

use \Vtiful\Kernel\Excel;

得到的结果：

导出一万行 Excel 文件所需耗时0.88025903701782秒

导出五万行 Excel 文件所需耗时4.3131968975067秒

导出十万行 Excel 文件所需耗时8.807088136673秒

有趣的是，php-ext-xlswriter 还给出了另外一种 "固定内存模式"，做到内存最小化导出，使用该模式：

private function export()
{
    $modelPost = new Post;
    $rowsPost = $modelPost->getData(true, 100000);

    // 进行数据组装 如果你有更好的方法可以不这样
    $lstPost = [];
    foreach ($rowsPost as $rowPostKey => $rowPost) {
        $lstPost[$rowPostKey] = [
            $rowPost['id'],
            $rowPost['name'],
            $rowPost['content'],
            $rowPost['created_at']
        ];
    }

    $config = ['path' => public_path()];
    $excel  = new Excel($config);

    $fileObject = $excel->constMemory('xls_writer.xlsx');
    $fileHandle = $fileObject->getHandle();

    $format    = new \Vtiful\Kernel\Format($fileHandle);
    $boldStyle = $format->bold()->toResource();

    $fileObject->setRow('A1', 10, $boldStyle) // 写入数据前设置行样式
        ->header(['ID', '名称', '内容', '创建时间'])
        ->data($lstPost)
        ->output();
}

测试后得到结果：

导出一万行 Excel 文件所需耗时0.91289305686951秒

导出五万行 Excel 文件所需耗时4.0387449264526秒

导出十万行 Excel 文件所需耗时8.2343101501465秒

结果出来后有点尴尬...

限制了内存的使用反而导出五万和十万条数据时更快了...

不过博主只是简单测试了一下而已，更准确的测试需要导出多次再取平均值的.

总结

博主认为 PhpSpreadsheet 在这三个中是比较差的，因为文档提供的 DEMO 比较少，对新人不友好。 而且导出的数据需要封装一下。更重要的是性能也比较差。

Laravel Excel 还算可以，如果是在 Laravel 使用它，确实比较舒服，因为直接创建一个类再调用模型方法就好了，数据不需要封装，需要写的代码比较少，缺点就是性能太差，比 PhpSpreadsheet 还慢，如果导出数据量小而且使用了 Laravel 框架且不考虑性能和耗时可以考虑使用 Laravel Excel。

最后说一下 php-ext-xlswriter，其实博主在测试前已经了解到它，知道它的速度，但是经过前面两个的测试，对 php-ext-xlswriter 的测试结果还震惊了一下。

php-ext-xlswriter 还有中文文档，对于新手其实也很友好，就是安装扩展这一步可能会稍微阻拦一下而已。

php-ext-xlswriter 的官方是这样描述它的：

Two memory modes export 1 million rows of data (27 columns, data is string)

Normal mode: only 29S is needed, and the memory only needs 2083MB;

Fixed memory mode: only need 52S, memory only needs <1MB;

最后附上一张十万数据的 Excel 截图：