如何以数字格式导出具有100K条记录的HTML表，而不会浪费内存 [英] How to export HTML table with 100K records with number formatting without memory exhaust

问题描述

我正在使用PHP和MySQL将数据提取到HTML表中.该数据应该在Excel文件中下载HTML.使用以下代码:

I'm using PHP and MySQL to fetch the data to HTML Table. The data is supposed to download the HTML in Excel File. Following code is used:

$filename = "individual_list_" . date('Ymdhms') . ".xls";
    header("Content-type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=\"$filename\"");
    $html='<style>.num {mso-number-format:General;}.text{mso-number-format:"\@";}</style><table border=1>';
    $rowCount=1;
    foreach ($export_data as $key => $row) {
        if(!$header)
        {
            $header_field=array_keys($row);
            $html .='<thead><tr>';
            foreach ($header_field as $k => $value) {      
                $html .='<th class="text">'.$value.'</th>';
            }
            $html .='</tr></head>';
            $header=true;
        }
        $values=array_values($row);
        $rowCount++;
        $html .='<tbody><tr>';
        foreach ($values as $k => $value) {
            if (DateTime::createFromFormat('Y-m-d G:i:s', $value) !== FALSE) {
             $value = date('Y-m-d', strtotime($value));
            }
            $html .='<td class="text">'.$value.'</td>';
        }
        $html .='</tr></tbody>';
    }
    $html .='</table>';
    echo $html;

大约有90K记录要导出.这段代码曾经产生Allowed Memory Exhausted错误，所以我更改了内存限制.现在，错误已解决，但数据显示为HTML Table而不是下载.该代码对减少记录集有效.该问题如何解决? 导出(下载)在下载Excel文件的弹出窗口中完成.下载完成后如何关闭弹出窗口?

There are around 90K records to export. This code once produced Allowed Memory Exhausted Error, So I changed the memory limit. Now the error is resolved but the data is displayed as HTML Table instead of download. The code is working good for less recordset. How can the issue be resolved? The export(download) is done in popup that downloads the Excel file. How can the popup window be closed after download complete?

推荐答案

在循环中输出html而不是将其缓冲到最后.这将减少PHP所需的内存量，并可以加快下载过程. PHP&您的Web服务器仍会进行一些缓冲.可以，但是您可以使用显式flush()覆盖；如果问题仍然存在，请执行命令.

Output the html within the loop rather than buffering it until the end. This will reduce the amount of memory required by PHP and may speed up the download process. PHP & your web server will still do some buffering. This is OK but you could override with explicit flush(); commands if the issues persist.

$filename = "individual_list_" . date('Ymdhms') . ".xls";
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"$filename\"");

echo '<style>.num {mso-number-format:General;}.text{mso-number-format:"\@";}</style><table border=1>';
$rowCount=1;
foreach ($export_data as $key => $row) {
    $html = '';
    if(!$header)
    {
        $header_field=array_keys($row);
        $html .='<thead><tr>';
        foreach ($header_field as $k => $value) {      
            $html .='<th class="text">'.$value.'</th>';
        }
        $html .='</tr></head>';
        $header=true;
    }
    $values=array_values($row);
    $rowCount++;
    $html .='<tbody><tr>';
    foreach ($values as $k => $value) {
        if (DateTime::createFromFormat('Y-m-d G:i:s', $value) !== FALSE) {
         $value = date('Y-m-d', strtotime($value));
        }
        $html .='<td class="text">'.$value.'</td>';
        echo  $html;
    }
    echo '</tr></tbody>';
    echo '</table>';
}

根据您的MySQL客户端库，您也许还可以使用无缓冲的MySQL查询，该查询使您的脚本在从MySQL接收到数据后立即开始生成HTML，并且使用的内存少于等待将整个结果加载到其中的时间.一个PHP缓冲区.

Depending on your MySQL client library, you may also be able to use unbuffered MySQL queries which allow your script to start generating HTML as soon as data is received from MySQL and uses less memory than waiting for the entire result to be loaded into a PHP buffer.

这篇关于如何以数字格式导出具有100K条记录的HTML表，而不会浪费内存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！