PHP实时生成并下载超大数据量的EXCEL文件

Neilyo 发布于2019-07-01 12:25 / 1032人阅读

摘要：而常用的包需要把所有数据拿到后才能生成，在面对生成超大数据量的文件时这显然是会造成内存溢出的，所以考虑使用让边写入输出流边让浏览器下载的形式来完成需求。

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

我们通过如下的方式写入PHP输出流

$fp = fopen("php://output", "a");
fputs($fp, "strings");
....
....
fclose($fp)

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

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

注：以下代码只是阐明生成大数据量EXCEL的思路和步骤，并且在去掉项目业务代码后程序有语法错误不能拿来直接运行，请根据自己的需求填充对应的业务代码！

    /**
     * 文章访问日志
     * 下载的日志文件通常很大, 所以先设置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="". $fileName .""");
        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);//将数据格式化为CSV格式并写入到output流中
        $accessNum = "1000000"//从数据库获取总量，假设是一百万
        $perSize = 1000;//每次查询的条数
        $pages   = ceil($accessNum / $perSize);
        $lastId  = 0;
        for($i = 1; $i <= $pages; $i++) {
            $accessLog = $logService->getArticleAccessLog($timeStart, $timeEnd, $lastId, $perSize);
            foreach($accessLog as $access) {
                $rowData = [
                    ......//每一行的数据
                ];
                mb_convert_variables("GBK", "UTF-8", $rowData);
                fputcsv($fp, $rowData);
                $lastId = $access->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 columns FROM `table_name` 
WHERE `created_at` >= "time range start" 
AND `created_at` <= "time range end" 
AND  `id` < LastId 
ORDER BY `id` DESC 
LIMIT num

托管Hadoop集群 USDP大数据平台生成excel文件 js生成excel文件读取服务器文件并下载如何连接阿里云服务器并下载文件

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/30697.html

【SpringMvc】后台系统超大报表下载超时的处理

摘要：四层负载均衡不会引起超时。动态修改包的目标地址，并转发数据包使其到达不同的机器上来实现负载均衡的目的，因此节点不会引起超时。七层负载均衡等待上游响应超时。例如使用多线程并发减少远程查询的总体时间如需数据有序，可以使用方案。 B端业务经常要提供下载报表的功能，一般的方法是先查询出所有数据，然后在内存中组装成报表（如XLS/XLSX格式）后统一输出。但是如果生成报表需要查询的数据量很大，远...

kohoh_ 2019-07-25 14:05 评论0 收藏0
PHP读取超大的excel文件数据的方案

摘要：场景和痛点说明今天因为一个老同学找我，说自己公司的物流业务都是现在用处理，按月因为数据量大，一个差不多有百万数据，文件有接近，打开和搜索就相当的慢联想到场景要导入数据，可能数据量很大，这里利用常用的一些方法比如会常有时间和内存限制问题下面我场景和痛点说明今天因为一个老同学找我，说自己公司的物流业务都是现在用excel处理，按月因为数据量大，一个excel差不多有百万数据，文件有接...

dkzwm 2019-07-01 10:18 评论0 收藏0
php 导出 excel

关于 PHP 导出 excel csv 常用的有 PHPexcel ,本文整理了一些其他方案。高性能 Excel 扩展 sudo apt-get install -y zlib1g-dev git clone https://github.com/jmcnamara/libxlsxwriter.git cd libxlsxwriter make && sudo make install // ...

付永刚 2019-06-28 14:38 评论0 收藏0
PHP导出超大的CSV格式的Excel表方案

摘要：场景和痛点说明我们工作场景都常会导出相关的数据，有时候需要大量的数据，，都有可能我们现有方案都是直接利用等类库来操作，的加载或是写入一次导出会遇到超时内存和时间限制问题，就算我们依旧不是最好的方案下面我们利用输出，把数据依次输出清空再输出的场景和痛点说明我们工作场景都常会导出相关的excel数据，有时候需要大量的数据，10W，100W都有可能我们现有方案都是直接利用phpexce...

yedf 2019-07-01 10:17 评论0 收藏0
【PHP】PHPExcel类 excel常用操作小结

摘要：使用好久了，好像今天才想起来要记一篇博客呢。之前一直用的框架，后来做接口的时候打算换成不料机缘巧合之下又结识了，于是乎决然的站到了的大营之下。今天小记一下这个类库的常用操作。首先贴上地址，直接去下载最新版好了。 PHP使用好久了，好像今天才想起来要记一篇博客呢。之前一直用的 ci 框架，后来做接口的时候打算换成 tp5, 不料机缘巧合之下又结识了 node，于是乎决然的站到了 js 的...

suosuopuo 2019-06-27 15:42 评论0 收藏0