探讨一下大数据量的导出Excel 方案

如果客户不要求 csv 是低配的最好方案。数据量再大也能逐行写入，还支持追加写入。你若是使用 oss 或者 鹅厂 cos 这类对象存储的服务，都支持文件内容追加写入的 api 都不用在本地生成后再上传。

github.com/viest/php-ext-xlswriter
你可以看下这个项目，它的导入导出有固定内存模式，号称 内存仅需<1MB

如果生成时间上没有太高要求，可以用定时脚本来生成这份数据。

如果要求高一些，可以考虑使用一张冗余数据表来存储这部分数据，随着业务数据写入异步来更新冗余数据，这里可能会出现数据同步过程中的差异，需要产品和客户考虑能否接受差异以及多长时间内的差异。冗余的数据建立好查询必然要用到的索引。

再进一步，如果成本上能接受，可以考虑将冗余数据存储的方案变化为使用 es 这种搜索引擎或者 clickhouse 这种分析型数据库来加速查询。

我之前知道 xlsx 是压缩过的，今天试了下， 感觉对他再次压缩的意义不太大，如果只是想减小文件体积的话。

对了， xlsx 扩展名改成 zip 可以直接解压
看图

如果使用excel文件的话，要考虑行数溢出，单个sheet页最多支持 1048576 行数据。那么根据你的要求数量溢出，只能分页导出。如果是单纯的数据导出，没有单元格设置，可以考虑 csv文件。

导出 csv 解决方案

创建 function.php, 在 composer 中添加自动加载，执行 composer dump-autoload

添加函数

if (!function_exists('save_to_csv')) {

    function save_to_csv(string $filename, callable $callable, array $heads = []): bool
    {
        if (!file_exists(dirname($filename))) {
            mkdir($filename, 0777, true);
        }
        $fp = fopen($filename, 'a');
        fwrite($fp, chr(0xEF) . chr(0xBB) . chr(0xBF));
        if ($heads) {
            fputcsv($fp, $heads);
        }
        while ($data = $callable($fp)) {
            foreach ($data as $row) {
                fputcsv($fp, $row);
            }
            unset($data);
            usleep(500000);
        }
        fclose($fp);
        return true;
    }
}

创建测试控制台命令 php artisan make:command TestCommand

编写测试代码

<?php

namespace App\Console\Commands;

use Faker\Factory;
use Illuminate\Console\Command;

class TestCommand extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'test';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $count = 0;
        $file = '/tmp/test.csv';
        $faker = Factory::create();

        $max = 2000000;
        $bar = $this->output->createProgressBar($max);
        $bar->start();

        save_to_csv($file, function () use ($faker, &$count, $max, $bar) {

            $data = [];
            for ($i = 500; $i--;) {

                if ($count > $max) {
                    break; // 停止生产
                }

                $data[] = [
                    $faker->uuid,
                    $faker->name,
                    $faker->email,
                    rand(20, 40)
                ];
                $count++;
            }

            $bar->advance(count($data));
            return $data;
        }, ['id', 'name', 'email', 'age']);

        $bar->finish();
        $this->info(sprintf('文件保存成功, 路径为 %s', $file));

        return 0;
    }
}

执行程序 php artisan test

本机测试结果

内存占用：0.8%
CPU占用：13%-40%

CPU可能过高是因为循环遍历次数过多，占用cpu时间过长，可以适当减小for $i , 建议在 200 - 1000，如果服务器性能高，可以适当调整。

// – 补充 使用ORM查询数据

<?php

namespace App\Console\Commands;

use App\Models\User;
use Faker\Factory;
use Illuminate\Console\Command;

class TestCommand extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'test';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $page = 1;
        $file = '/tmp/test.csv';

        $query = User::query()->where('status', '=', 1);
        $bar = $this->output->createProgressBar($query->count());
        $bar->start();

        save_to_csv($file, function () use (&$page, $query, $bar) {

            $chunk = 500; // 每页查询条数
            $data = $query->forPage($page, $chunk)->get();

            if ($data->isEmpty()) {
                return false;
            }

            $page++;
            $bar->advance($data->count());

            return $data->transform(function (User $user) {
                return [
                    $user->id,
                    $user->username,
                    $user->email,
                    $user->age,
                ];
            });
        }, ['id', 'name', 'email', 'age']);

        $bar->finish();
        $this->info(sprintf('文件保存成功, 路径为 %s', $file));

        return 0;
    }
}

以上为测试代码，可以考究，可以纠错。

• 压缩大小：没太理解，是打包还是压缩？
  • 大概20万条数据，一个文件是5千条，差不多40个csv文件，然后利用压缩，目前大概能压缩到多少？
• 查询方式：目前用的数据库直接读取吗，比如MySQL ？
  • 数据目前是通过主表 chunk 分批查询，然后关联查询整合字段，然后导入到csv文件中吗？
• 数据列：导出的数据列字段大概多少？
  • 看楼上说的 冗余数据表 也是个思路，之前我落地过，冗余表大概50个字段，就是定期通过异步方式，提前将其他 六七个表的字段全部查询变更到冗余表中，问题不大
• 时间要求：目前导出是通过提交申请，然后过一段时间提供下载地址的方式给用户吗？
  • 如果时间上没有要求特别快的情况下，通过异步跑数据到 csv 中，然后发一个通知什么的就行
  • 就看时间上限制多久了，比如 3个小时内，根据时间来加快导出的速度
  • 另外数据的全量导出，可以考虑跟需求方沟通，是否可以 N+1 第二天完成，这样凌晨跑前一天数据问题不大
  • 因为随着数据量的增大，时间肯定会拉长，除非有更多的内存，也就是空间和时间的选择了
• 组件选择：楼主已经确定是 fast-excel ，这里只是列出来仅供参考
  • fast-excel 听说好像很快，前身貌似就是 spout
  • phpoffice/phpspreadsheet 我之前用的这个，功能比较多，很强大
  • Laravel-Excel 基于 phpspreadsheet
  • spout 号称只需要 3MB 内存，但是不维护了，考虑和PHP8版本兼容性问题
  • xlswriter C扩展， 没用过，但是C应该不会比PHP慢吧
• 内存占用
  • ORM 数据读取，chunk 分批读取
  • 在 Excel 写入数据的时候，是把 5千条数据加载到内存中吗，可以结合 yield 生成器来做 ，比如 LazyCollection 按需加载
  • 可以使用 queue 将写入任务放入队列中，异步处理

我们生成任务后后台进程执行导出到csv 100w数据导出大概6分钟

可以先csv再然后csv转excel

我们公司用的方案把导出丢在队列当中，让队列去处理，写入到CSV文件里，这样不耗内存，50W条订单数据几分钟就好了，只需要导出之后手动转换文件格式即可

总结下咱们主要的思路：

内存方面：
这块针对内存的数据一定要采用 cursor() 游标 或者 yield 迭代器去处理数据，否则大量的ORM 循环操作，内存很快都上去

查询方面：
如果数据量比如在10w 范围内，我们可以结合Laravel 自带的ORM 查询 以lazyById方式查询，一定要进行chunkId 方式查询，不要采用offset 方式。如果数据过大，楼上有建议是数据导入中间表，在处理。也或者是把数据写入ES 服务，后两种都要考虑容错

导出方式：
目前看到的方案：数据量差不多的情况如果针对格式有要求，可以用 php-ext-excel 或者 fast-excel 导出，采用迭代器方式导入数据，直接生成excel 文件。另外一个建议是数据量过大，可以先导出csv ，然后再用其他工具导出成excel ，第二个我是比较推荐的。内存占用也低，如果有composer 包支持 csv 转成 excel ，会更好

数据分块：
单个excel 过大，其实普通电脑也不好打开，综合建议是数据拆分成若干区间，写入文件，这样子体验也很好，分组之后最后压缩一个zip 文件提供下载也不错

队列使用：
使用laravel 的普通队列 + laravel 8+ 以上的Batch批次队列处理起来会更舒服

使用场景：
以上的方案，基本上能满足市面上大多数的需求

总监牛逼

定时任务异步队列导出

可以考虑用webman

xlsWrite 快的一批，就是装的时候有点麻烦

博客：laravel扩展：xlswriter导出，自定义复杂合并及样式

如果取数据方式比较复杂，涉及计算，可以考虑将数据同步OLAP库后再查数据，比如阿里云ADB，查询速度上可以省很多时间。

写文件部分我们用Go做了，支持用SQL的方式导出，写一条SQL基本就能导出，导出格式为CSV。

数据参考：单表千万级，导出CSV文件30-40W，几十秒到几分钟，一般5分钟以内。

调研过各支付平台的导出，基本都是CSV格式为主。

大量数据导出是个综合题，光用某个库不一定好解决，同时每个项目的情况不一样。

另外还需要注意下编码和数据类型格式。

不是有大佬这个吗？？xlswriter-docs.viest.me/zh-cn/an-z...

github.com/mk-j/PHP_XLSXWriter 也可以参考下这个方法，复制或者引入类都可以，比较轻量，也很好用。实际业务中通过job执行，数据通过clickhouse查询的， 50多万行写入excel 并上传到阿里云速度也很快的

正好可以推广下自己的博文了 :-)

dbwu.tech/posts/optimize/big_data_...