PHP實(shí)時(shí)生成并下載超大數(shù)據(jù)量的EXCEL文件

Neilyo 發(fā)布于2019-07-01 12:25 / 1176人閱讀

摘要：而常用的包需要把所有數(shù)據(jù)拿到后才能生成，在面對(duì)生成超大數(shù)據(jù)量的文件時(shí)這顯然是會(huì)造成內(nèi)存溢出的，所以考慮使用讓邊寫入輸出流邊讓瀏覽器下載的形式來(lái)完成需求。

最近接到一個(gè)需求，通過(guò)選擇的時(shí)間段導(dǎo)出對(duì)應(yīng)的用戶訪問(wèn)日志到excel中，由于用戶量較大，經(jīng)常會(huì)有導(dǎo)出50萬(wàn)加數(shù)據(jù)的情況。而常用的PHPexcel包需要把所有數(shù)據(jù)拿到后才能生成excel，在面對(duì)生成超大數(shù)據(jù)量的excel文件時(shí)這顯然是會(huì)造成內(nèi)存溢出的，所以考慮使用讓PHP邊寫入輸出流邊讓瀏覽器下載的形式來(lái)完成需求。

我們通過(guò)如下的方式寫入PHP輸出流

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

php://output是一個(gè)可寫的輸出流，允許程序像操作文件一樣將輸出寫入到輸出流中，PHP會(huì)把輸出流中的內(nèi)容發(fā)送給web服務(wù)器并返回給發(fā)起請(qǐng)求的瀏覽器

另外由于excel數(shù)據(jù)是從數(shù)據(jù)庫(kù)里逐步讀出然后寫入輸出流的所以需要將PHP的執(zhí)行時(shí)間設(shè)長(zhǎng)一點(diǎn)（默認(rèn)30秒）set_time_limit(0)不對(duì)PHP執(zhí)行時(shí)間做限制。

注：以下代碼只是闡明生成大數(shù)據(jù)量EXCEL的思路和步驟，并且在去掉項(xiàng)目業(yè)務(wù)代碼后程序有語(yǔ)法錯(cuò)誤不能拿來(lái)直接運(yùn)行，請(qǐng)根據(jù)自己的需求填充對(duì)應(yīng)的業(yè)務(wù)代碼！

    /**
     * 文章訪問(wèn)日志
     * 下載的日志文件通常很大, 所以先設(shè)置csv相關(guān)的Header頭, 然后打開
     * PHP output流, 漸進(jìn)式的往output流中寫入數(shù)據(jù), 寫到一定量后將系統(tǒng)緩沖沖刷到響應(yīng)中
     * 避免緩沖溢出
     */
    public function articleAccessLog($timeStart, $timeEnd)
    {
        set_time_limit(0);
        $columns = [
            "文章ID", "文章標(biāo)題", ......
        ];
        $csvFileName = "用戶日志" . $timeStart ."_". $timeEnd . ".xlsx";
        //設(shè)置好告訴瀏覽器要下載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);//將數(shù)據(jù)格式化為CSV格式并寫入到output流中
        $accessNum = "1000000"http://從數(shù)據(jù)庫(kù)獲取總量，假設(shè)是一百萬(wàn)
        $perSize = 1000;//每次查詢的條數(shù)
        $pages   = ceil($accessNum / $perSize);
        $lastId  = 0;
        for($i = 1; $i <= $pages; $i++) {
            $accessLog = $logService->getArticleAccessLog($timeStart, $timeEnd, $lastId, $perSize);
            foreach($accessLog as $access) {
                $rowData = [
                    ......//每一行的數(shù)據(jù)
                ];
                mb_convert_variables("GBK", "UTF-8", $rowData);
                fputcsv($fp, $rowData);
                $lastId = $access->id;
            }
            unset($accessLog);//釋放變量的內(nèi)存
            //刷新輸出緩沖到瀏覽器
            ob_flush();
            flush();//必須同時(shí)使用 ob_flush() 和flush() 函數(shù)來(lái)刷新輸出緩沖。
        }
        fclose($fp);
        exit();
    }

好了，其實(shí)很簡(jiǎn)單，就是用逐步寫入輸出流并發(fā)送到瀏覽器讓瀏覽器去逐步下載整個(gè)文件，由于是逐步寫入的無(wú)法獲取文件的總體size所以就沒(méi)辦法通過(guò)設(shè)置header("Content-Length: $size");在下載前告訴瀏覽器這個(gè)文件有多大了。不過(guò)不影響整體的效果這里的核心問(wèn)題是解決大文件的實(shí)時(shí)生成和下載。

更新：說(shuō)一下我數(shù)據(jù)庫(kù)查詢這里的思路，因?yàn)橹鸩綄懭隕XCEL的數(shù)據(jù)實(shí)際上來(lái)自Mysql的分頁(yè)查詢，大家知道其語(yǔ)法是LIMIT offset, num 不過(guò)隨著offset越來(lái)越大Mysql在每次分頁(yè)查詢時(shí)需要跳過(guò)的行數(shù)就越多，這會(huì)嚴(yán)重影響Mysql查詢的效率(包括MongoDB這樣的NoSQL也是不建議skip掉多條來(lái)取結(jié)果集)，所以我采用LastId的方式來(lái)做分頁(yè)查詢。類似下面的語(yǔ)句：

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