PHP百万数据级导出excel

分享 / 91 / 34 / 创建于 2年前

单次导出一百万条数据，保证服务器的资源不会因为这个导出变卡。导出的时间要求不作限制，大家有没有比较好的解决方案哈。

本帖已被设为精华帖！

本帖由系统于 2年前自动加精

gongmeng

课程读者 169 声望

PHP开发工程师 @ 麦风科技

暂无个人描述~

14 人点赞

我们将带你从零开发一个项目并部署到线上，本课程教授 Web 开发中专业、实用的技能，如 Git 工作流、Laravel Mix 前端工作流等。

从零开始带你一步步开发一个 Go 博客项目，让你在最短的时间内学会使用 Go 进行编码。项目结构很大程度上参考了 Laravel。

推荐文章：

更多推荐...

博客

花了四个月打磨的 Laravel Plus 开源 30 / 98 |

博客

如何让DcatAdmin再放光芒,惠及百万PHPer 18 / 39 |

博客

PHP 程序员转 Go 语言的经历分享 19 / 12 |

博客

手摸手带你使用 docker-compose 编排一个开发环境 19 / 15 |

工作

[求职] 重生之不再做PHP 12 / 83 |

博客

冯老师的困惑 —— PHP 挂了 21 / 4 |

讨论数量: 34

fatrbaby

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

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

2年前评论

gongmeng （楼主）

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

通尼和它的朋友

32 声望

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

课程读者 169 声望 / PHP开发工程师 @ 麦风科技

这是我写的一段导出百万数据的测试代码，其整个运行时间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 译者 259 声望 / PHP工程师 @ null

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

2年前评论

Complicated

正解

kkokk

325 声望

大数据导出，要防止内存溢出，需要用游标读取（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 感谢感谢

勇敢的心

见习助教 847 声望

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

2年前评论

gongmeng （楼主）

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

Diudiuuuu

见习助教 85 声望

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

2年前评论

MZ0x01

118 声望

写成csv，用工具转成excel。

2年前评论

巅峰互联

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

异步导出，最后压缩下载

2年前评论

woodong

69 声望

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

2年前评论

gongmeng （楼主）

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

lovewei

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

芝麻开门

课程读者 143 声望

学习了，很赞

2年前评论

一念沧海一念桑田

课程读者 180 声望

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

        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年前评论

一念沧海一念桑田

课程读者 180 声望

我是这么写的，贴个代码参考一下，当时测试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年前评论

一念沧海一念桑田

课程读者 180 声望

我这样写的

        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();
            }
        }