不败君

前端萌新&初级后端攻城狮

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

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

2021-01-02 00:15:59

围观(5900)

在开发一些数据列表的时候经常会遇到需要导出到 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 命令行.


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

1.png

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

2.png

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

使用 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 截图:

3.png

本文地址 : bubaijun.com/page.php?id=228

版权声明 : 未经允许禁止转载!

评论:我要评论

junyu 沙发

666

评论时间:2024-03-11 17:28:24

回复

发布评论:
Copyright © 不败君 粤ICP备18102917号-1

不败君

首 页 作 品 微 语