PHP即時產生並下載超大數據量的EXCEL文件

最近接到一個需求，透過選擇的時間段匯出對應的使用者存取日誌到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(&#39;GBK&#39;, &#39;UTF-8&#39;, $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` <= &#39;time range end&#39; 
AND  `id` < LastId 
ORDER BY `id` DESC 
LIMIT num

以上是PHP即時產生並下載超大數據量的EXCEL文件的詳細內容。更多資訊請關注PHP中文網其他相關文章！