PHP百万数据级导出excel

fatrbaby

280 声望 / 首席换水官 @ 名剑风流

本站有一个大佬 @viest ，写了一个扩展 xlswriter 导出百万不在话下。

2年前评论

gongmeng （楼主）

看了下，这个属于巨佬级别了，确实牛逼

通尼和它的朋友

33 声望

xlswriter扩展 + 队列，一次性导出的话应该会内存溢出或者超时

2年前评论

gongmeng （楼主）

确实，我的想法也是出发队列，异步处理

中文

我想问下就是，队列的话，如果我一口气放进去百万条数据，队列内存不就满了吗，还是说，查询一定量的值传进去，还是传进入一个范围，然后在队列里面再进行查询，写入

微加加的朋友

课程读者 115 声望

在 Laravel 中导出大量数据，避免内存溢出或者超时问题可以使用流式输出，将数据一行一行地输出到浏览器或者文件中，而不是将所有数据都加载到内存中。Laravel 中可以使用 Chunk 方法来实现流式输出，Chunk 方法将查询结果分块处理，每次处理一定数量的数据

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Response;
use App\Models\User;

public function exportUsers(Request $request)
{
    $fileName = 'users.csv';

    $headers = [
        'Content-type' => 'text/csv',
        'Content-Disposition' => sprintf('attachment; filename="%s"', $fileName),
    ];

    $totalRows = User::count();
    $chunkSize = 1000;
    $totalChunks = ceil($totalRows / $chunkSize);

    $stream = fopen('php://output', 'w');

    // 写入表头
    fputcsv($stream, ['ID', 'Name', 'Email']);

    for ($i = 1; $i <= $totalChunks; $i++) {
        $offset = ($i - 1) * $chunkSize;
        $users = User::offset($offset)->limit($chunkSize)->get();

        foreach ($users as $user) {
            // 写入一行数据
            fputcsv($stream, [$user->id, $user->name, $user->email]);
        }

        // 强制刷新输出缓冲区
        ob_flush();
        flush();
    }

    fclose($stream);

    return Response::make('', 200, $headers);
}

在这个示例代码中，我们使用 php://output 创建一个流，将数据输出到这个流中。这样可以避免将所有数据读入内存中，降低内存占用。

我们通过循环分块读取数据，每次读取 $chunkSize 条数据，然后将这些数据写入到流中。在写入数据后，我们使用 ob_flush() 和 flush() 强制刷新输出缓冲区，将已经写入的数据发送到客户端，避免输出缓冲区占用过多内存。

最后，我们通过 Response 返回一个空内容的响应，同时设置相应的 Content-Type 和 Content-Disposition 头，告诉浏览器下载这个 CSV 文件。

为了避免超时和内存溢出问题，我们可以在 php.ini 中设置 max_execution_time 和 memory_limit。如果需要在代码中设置这些限制，可以使用 set_time_limit() 和 ini_set() 函数。

2年前评论

gongmeng （楼主）

感谢感谢，我参考参考

fatrbaby

建议使用Laravel自带的chunks，能简化不少。

pi_phq

还是超时啊下载不完整

gongmeng

课程读者 170 声望 / 技术小组长 @ 阿里巴巴

这是我写的一段导出百万数据的测试代码，其整个运行时间40秒左右，这个xslwriter真是厉害哈

        ini_set('memory_limit', '1024M');
        set_time_limit(0);

        $path = \think\facade\App::getRootPath() . 'public/upload/';

        $config = [
            'path' => $path,
        ];

        $fileName = 'tutorial01.xlsx';

        $excel      = new \Vtiful\Kernel\Excel($config);
        $fileObject = $excel->constMemory($fileName, NULL, false);
        $fileHandle = $fileObject->getHandle();

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

        $fileObject = $fileObject->setRow('A1', 10, $boldStyle) // 写入数据前设置行样式
                                 ->header(['id', 'person_id', 'person_name', 'gmt_create', 'gmt_modified']);

        for ($i = 5000; $i <= 1000000; $i += 5000) {
            $offset     = $i - 5000;
            $limit      = 5000;
            $data       = Db::table('t1')->limit($offset, $limit)->select();
            $data       = collect($data)->map(function ($item) {
                return collect($item)->values();
            })->toArray();
            $fileObject = $fileObject->data($data);
        }

        $filePath = $excel->output();

        header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        header('Content-Disposition: attachment;filename="' . $fileName . '"');
        header('Content-Length: ' . filesize($filePath));
        header('Content-Transfer-Encoding: binary');
        header('Cache-Control: must-revalidate');
        header('Cache-Control: max-age=0');
        header('Pragma: public');
        ob_clean();
        flush();
        if (copy($filePath, 'php://output') === false) {
            exit('copy file to path error');
        }
        @unlink($filePath);

2年前评论

晏南风

集合操作那里每次5000，字段少还好，如果字段多，字段值长，会不会load到内存很耗内存

caixingyue

@晏南风使用yield就好了，可以减少内存损耗，但是$fileObject->data($data)这里还是需要消耗大量的内存，我导出一百万条数据，二十几个字段，默认的内存完全不够用，这个应该是设计的问题

mowangjuanzi

Laravel 9.x 译者 263 声望 / PHP工程师 @ 山东起码信息科技

如果仅仅是数据，完全可以先导出为 csv。然后本地转换为 excel 即可。

2年前评论

Complicated

正解

kkokk

329 声望

大数据导出，要防止内存溢出，需要用游标读取（laravel封装的cursor是使用生成器完成的），再用xlswriter，最近我也根据这个写了大数据导入导出，导出就是前面说的这个逻辑，导入用分片上传，再游标读取每行，在分批存入数据库

2年前评论

gongmeng （楼主）

大佬，方便贴一下代码嘛

kkokk （作者）

@gongmeng 贴一下导出代码逻辑，不过我是根据xlswriter自己封装了一下

$fields = ['*'];

$header = ['表格头1'];

$config = [
    'path' => 'path'
];

$fileName = date('YmdHis') . '.xlsx';

// 根据 xls 封装的一个类
$excel = Office::xls($config)->constMemory($fileName, NULL, false)->header($header);

// 设置 header 粗体
$fileHandle = $excel->getHandle();
$format = Office::Format($fileHandle);
$boldStyle = $format->bold()->toResource();
$excel = $excel->setRow('A1', 16, $boldStyle);

$users = $this->UserRepository->getAllOnCursor($where, $orderBy, $fields);
foreach ($users as $user) {
    $user = $user->toArray();
    // 根据表格头顺序返回正确数据格式
    $data = $this->UserService->getRealValue($user);
    $excel = $excel->data([$data]);
}

$res = $excel->output();

gongmeng （楼主）

@Z_Laravel 感谢感谢

勇敢的心

见习助教 854 声望

yield + csv 怎么样？到处多少都没有问题吧？

2年前评论

gongmeng （楼主）

这个还没有用过，有空了解一下

Diudiuuuu

见习助教 85 声望

博客：PHP导出大量数据,保存为CSV文件

2年前评论

MZ0x01

121 声望

写成csv，用工具转成excel。

2年前评论

巅峰互联

课程读者 24 声望 / PHP开发 @ 自由职业者

异步导出，最后压缩下载

2年前评论

woodong

69 声望

我不是杠，我就是想知道这么多的数据，office可以打开吗？另外，Excel最大好像是1048576行。

2年前评论

gongmeng （楼主）

可以分成几个sheet，单个sheet最大限制就是100多万行

lovewei

技术实现可以讨论，从应用来讲，如果是普通办公电脑，那么打开这么个文件就会相当卡顿了，更别提办公了

芝麻开门

课程读者 144 声望

学习了，很赞

2年前评论

一念沧海一念桑田

课程读者 185 声望

我是这样写的，分批查询，边查询边输出的，截取一段代码：

        set_time_limit(0);
        ini_set('memory_limit', '128M');
        $fileName = "零钱提现".date('YmdHis', time());
        header('Content-Type: application/vnd.ms-execl');
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');
        $begin = microtime(true);
        //打开php标准输出流
        //以写入追加的方式打开
        $fp = fopen('php://output', 'a');
        //我们试着用fputcsv从数据库中导出1百万的数据
        //我们每次取1万条数据，分100步来执行
        //如果线上环境无法支持一次性读取1万条数据，可把$nums调小，$step相应增大。
        $total = self::$model->getTotal($startDate, $endDate);
        $nums = 10000;
        $totalPage = ceil($total / $nums);
        //设置标题
        $title = [
            'recordID',
            'playerID',
            'showID',
            '玩家昵称',
            '提现金额(元)',
            '提现后零钱(元)',
            '时间',
            '状态',
        ];
        //将标题写到标准输出中
        fputcsv($fp, $title);
        for ($page = 1; $page <= $totalPage; ++$page) {
            //分页取
            $data = self::$model->getList($startDate, $endDate, $nums);
            //先取出玩家id
            $playerIds = array_unique(array_column($data, 'playerID'));
            //获取玩家信息
            $user = Obj::model('playerBasicData')->getUserInfoByAttr($playerIds);
            if ($data) {
                foreach ($data as $row) {
                    $temp = [
                        //recordID
                        $row['recordID'] . "\t",
                        //playerID
                        $row['playerID'] . "\t",
                        //showID
                        $user[$row['playerID']]['showID'] . "\t",
                        //昵称
                        $user[$row['playerID']]['nickName'] . "\t",
                        //'提现金额(元)'
                        ($row['amount'] / 100),
                        //'提现后零钱(元)'
                        ($row['changeAfter'] / 100),
                        //'时间'
                        $row['createTime'] . "\t",
                        //'状态'
                        Model_My_WithdrawRecord::STATUS_TEXT[$row['status']] . "\t"
                    ];
                    fputcsv($fp, $temp);
                }
                //每1万条数据就刷新缓冲区
                ob_flush();
                flush();
            }
        }

2年前评论

一念沧海一念桑田

课程读者 185 声望

我是这么写的，贴个代码参考一下，当时测试20几秒

set_time_limit(0);
        ini_set('memory_limit', '128M');
        $fileName = "零钱提现".date('YmdHis', time());
        header('Content-Type: application/vnd.ms-execl');
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');
        $begin = microtime(true);
        //打开php标准输出流
        //以写入追加的方式打开
        $fp = fopen('php://output', 'a');
        //我们试着用fputcsv从数据库中导出1百万的数据
        //我们每次取1万条数据，分100步来执行
        //如果线上环境无法支持一次性读取1万条数据，可把$nums调小，$step相应增大。
        $total = self::$model->getTotal($startDate, $endDate);
        $nums = 10000;
        $totalPage = ceil($total / $nums);
        //设置标题
        $title = [
            'recordID',
            'playerID',
            'showID',
            '玩家昵称',
            '提现金额(元)',
            '提现后零钱(元)',
            '时间',
            '状态',
        ];
        //将标题写到标准输出中
        fputcsv($fp, $title);
        for ($page = 1; $page <= $totalPage; ++$page) {
            //分页取
            $data = self::$model->getList($startDate, $endDate, $nums);
            //先取出玩家id
            $playerIds = array_unique(array_column($data, 'playerID'));
            //获取玩家信息
            $user = Obj::model('playerBasicData')->getUserInfoByAttr($playerIds);
            if ($data) {
                foreach ($data as $row) {
                    $temp = [
                        //recordID
                        $row['recordID'] . "\t",
                        //playerID
                        $row['playerID'] . "\t",
                        //showID
                        $user[$row['playerID']]['showID'] . "\t",
                        //昵称
                        $user[$row['playerID']]['nickName'] . "\t",
                        //'提现金额(元)'
                        ($row['amount'] / 100),
                        //'提现后零钱(元)'
                        ($row['changeAfter'] / 100),
                        //'时间'
                        $row['createTime'] . "\t",
                        //'状态'
                        Model_My_WithdrawRecord::STATUS_TEXT[$row['status']] . "\t"
                    ];
                    fputcsv($fp, $temp);
                }
                //每1万条数据就刷新缓冲区
                ob_flush();
                flush();
            }
        }

2年前评论

一念沧海一念桑田

课程读者 185 声望

我这样写的

        set_time_limit(0);
        ini_set('memory_limit', '128M');
        $fileName = "零钱提现".date('YmdHis', time());
        header('Content-Type: application/vnd.ms-execl');
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');
        $begin = microtime(true);
        //打开php标准输出流
        //以写入追加的方式打开
        $fp = fopen('php://output', 'a');
        //我们试着用fputcsv从数据库中导出1百万的数据
        //我们每次取1万条数据，分100步来执行
        //如果线上环境无法支持一次性读取1万条数据，可把$nums调小，$step相应增大。
        $total = self::$model->getTotal($startDate, $endDate);
        $nums = 10000;
        $totalPage = ceil($total / $nums);
        //设置标题
        $title = [
            'recordID',
            'playerID',
            'showID',
            '玩家昵称',
            '提现金额(元)',
            '提现后零钱(元)',
            '时间',
            '状态',
        ];
        //将标题写到标准输出中
        fputcsv($fp, $title);
        for ($page = 1; $page <= $totalPage; ++$page) {
            //分页取
            $data = self::$model->getList($startDate, $endDate, $nums);
            //先取出玩家id
            $playerIds = array_unique(array_column($data, 'playerID'));
            //获取玩家信息
            $user = Obj::model('playerBasicData')->getUserInfoByAttr($playerIds);
            if ($data) {
                foreach ($data as $row) {
                    $temp = [
                        //recordID
                        $row['recordID'] . "\t",
                        //playerID
                        $row['playerID'] . "\t",
                        //showID
                        $user[$row['playerID']]['showID'] . "\t",
                        //昵称
                        $user[$row['playerID']]['nickName'] . "\t",
                        //'提现金额(元)'
                        ($row['amount'] / 100),
                        //'提现后零钱(元)'
                        ($row['changeAfter'] / 100),
                        //'时间'
                        $row['createTime'] . "\t",
                        //'状态'
                        Model_My_WithdrawRecord::STATUS_TEXT[$row['status']] . "\t"
                    ];
                    fputcsv($fp, $temp);
                }
                //每1万条数据就刷新缓冲区
                ob_flush();
                flush();
            }
        }

2年前评论

33qis

24 声望

导出excel内存超了主要就是因为执行期间，整个excel都放到内存里，这样子数据越多就越卡

我这边总结了三个方法

离线异步生成excel，然后前端只需要直接下载excel文件就不会有这个问题

使用csv，分批查询，一批一批的输出到前端

选一个可以分批处理的excel扩展，这个扩展是支持分批处理，解决csv样式问题

2年前评论

Four

课程读者 15 声望

用 xlswriter 扩展，然后队列消费。sql查询导出的话有条件的最好用 chunkById，chunk 最终是 offset，复杂点的后面会一言难尽~

2年前评论

sanders

课程读者 648 声望 / 程序员鼓励师 @ KDD

我这里有个问题，我想通过多个队列任务给一个xlsx文件添加多个sheet的方式来减少内存开销。但我发现下次加载相同的文件会覆盖上一次生成的文件。是否有通过不同的队列任务打开相同的文件，追加sheet的方法？

2年前评论

yandihuangdi

课程读者 55 声望

我用的方案是队列，因为导出的数据较大（带图片），高清图，一张好几 MB，还得限制 Excel 文件的大小。

2年前评论

导出excel内存超了主要就是因为执行期间，整个excel都放到内存里，这样子数据越多就越卡

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

PHP百万数据级导出excel

导出excel内存超了主要就是因为执行期间，整个excel都放到内存里，这样子数据越多就越卡

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

请登录