实时生成并下载大数据量的EXCEL文件，用PHP如何实现

May 16, 2019

8471 views

679 words

有一个这样的需求，通过选择的时间段导出对应的用户访问日志到excel中，由于用户量较大，经常会有导出50万加数据的情况。而常用的PHPexcel包需要把所有数据拿到后才能生成excel，在面对生成超大数据量的excel文件时这显然是会造成内存溢出的，所以考虑使用让PHP边写入输出流边让浏览器下载的形式来完成需求。通过以下的方式写入PHP输出流。

<?php
$tmp = fopen('php://output', 'a');
fputs($tmp, 'strings');
....
....
fclose($tmp)

php://output 是一个可写的输出流，允许程序像操作文件一样将输出写入到输出流中，PHP会把输出流中的内容发送给web服务器并返回给发起请求的浏览器。

另外由于excel数据是从数据库里逐步读出然后写入输出流的所以需要将PHP的执行时间设长一点（默认30秒）set_time_limit(0)不对PHP执行时间做限制。

注：以下代码只是阐明生成大数据量EXCEL的思路和步骤，并且在去掉项目业务代码后程序有语法错误不能拿来直接运行，请根据自己的需求修改对应的业务代码！我这里就拿学生信息表测试！首先添加测试数据。当然我这边逻辑代码没有封装，你可以做一个相应的封装的，这里只是提供一个大概的思路。

<?php
/**

     * 大文件导出

     * 下载的日志文件通常很大, 所以先设置csv相关的Header头, 然后打开

     * PHP output流, 渐进式的往output流中写入数据, 写到一定量后将系统缓冲冲刷到响应中

     * 避免缓冲溢出

     */
 public function articleAccessLog($timeStart, $timeEnd)
    {
        set_time_limit(0);

        $columns = ['学生ID', '姓名', '年龄', '性别', '创建时间', '更新时间'];

        $csvFileName = '学生列表' . $timeStart . '_' . $timeEnd . '.xlsx';

        //设置好告诉浏览器要下载excel文件的headers

        header('Content-Description: File Transfer');

        header('Content-Type: application/vnd.ms-excel');

        header('Content-Disposition: attachment; filename="' . $csvFileName . '"');

        header('Expires: 0');

        header('Cache-Control: must-revalidate');

        header('Pragma: public');

        $fp = fopen('php://output', 'a');//打开output流

        mb_convert_variables('GBK', 'UTF-8', $columns);

        fputcsv($fp, $columns,"\t");//将数据格式化为CSV格式并写入到output流中

        $res = $this->mysqli->query('SELECT COUNT(`id`) AS `allCount`,MAX(`id`) AS `lastId` FROM `student`');

        $result = $res->fetch_object();

        $allCount = $result->allCount;

        $perSize = 1000;//每次查询的条数

        $pages = ceil($allCount / $perSize);

        $lastId = $result->lastId;

        for ($i = 1; $i <= $pages; $i++) {

            $sqlTpl = 'SELECT * FROM 

                             %s 

                            WHERE `created_at` >= %s AND 

                                   `created_at` &lt;= %s AND 

                                    `id` %s %d

                                    ORDER BY `id` DESC LIMIT %d';

            $symbol = $i === 1 ? "<=" : "<";

            $sql = sprintf($sqlTpl, self::$tableName, $timeStart, $timeEnd, $symbol, $lastId, $perSize);

            $accessLog = $this->mysqli->query($sql);

            foreach ($accessLog as $rowData) {

                mb_convert_variables('GBK', 'UTF-8', $rowData);

                fputcsv($fp, $rowData,"\t");

                $lastId = $rowData['id'];

            }

            unset($accessLog);//释放变量的内存

            //刷新输出缓冲到浏览器

            ob_flush();

            flush();//必须同时使用 ob_flush() 和flush() 函数来刷新输出缓冲。

        }

        fclose($fp);

        exit();
}
?>

好了，其实很简单，就是用逐步写入输出流并发送到浏览器让浏览器去逐步下载整个文件，由于是逐步写入的无法获取文件的总体size所以就没办法通过设置header("Content-Length: $size");在下载前告诉浏览器这个文件有多大了。不过不影响整体的效果这里的核心问题是解决大文件的实时生成和下载。

更新说明

数据库查询这里的思路，因为逐步写入EXCEL的数据实际上来自Mysql的分页查询，大家知道其语法是LIMIT offset, num 不过随着offset越来越大Mysql在每次分页查询时需要跳过的行数就越多，这会严重影响Mysql查询的效率(包括MongoDB这样的NoSQL也是不建议skip掉多条来取结果集)，所以我采用LastId的方式来做分页查询。类似下面的语句：

SELECT * FROM student 

              WHERE `created_at` >= 1552110271 

              AND `created_at` <= 15521102122

              AND `id` <= 20 

              ORDER BY `id` DESC 

              LIMIT 1000;

实时生成并下载大数据量的EXCEL文件，用PHP如何实现