如何使用带有数字格式的100K记录导出HTML表而不耗尽内存

Question

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;

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?

Answer 1

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>';
}

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.