PHP export MySQL data to Excel method introduction

Often encounter the need to export data from the database to Excel files, with some open source class library, such as Phpexcel, it is easy to implement, but the support for a large number of data is very bad, it is easy to reach the PHP memory usage limit. The method here is to use Fputcsv to write a CSV file, directly to the browser output Excel files.

<?php
Output Excel file headers, you can replace user.csv with the file name you want
Header (' Content-type:application/vnd.ms-excel ');
Header (' content-disposition:attachment;filename=&quot;user.csv&quot; ');
Header (' cache-control:max-age=0 ');</p>

<p>//data from the database, in order to save memory, do not read the data to memory, one line in the handle can read
$sql = ' SELECT * from tbl where ... ';
$stmt = $db-&gt;query ($sql);</p>

<p>//Open the php file handle, php://output indicates direct output to the browser
$fp = fopen (' php://output ', ' a ');</p>

<p>//output Excel Column name information
$head = Array (' name ', ' gender ', ' age ', ' Email ', ' telephone ', ' ... ');
foreach ($head as $i =&gt; $v) {
CSV Excel supports GBK encoding, be sure to convert, otherwise garbled
$head [$i] = iconv (' utf-8 ', ' GBK ', $v);
}</p>

<p>//writes data to a file handle through Fputcsv
Fputcsv ($fp, $head);</p>

<p>//counter
$cnt = 0;
Every $limit line, refresh the output buffer, not too big, not too small
$limit = 100000;</p>

<p>//data out of line, not wasting memory
while ($row = $stmt-&gt;fetch (zend<em>db::fetch</em>num)) {</p>

<pre><code> $cnt + +;
if ($limit = = $cnt) {//Refresh the output buffer to prevent problems caused by too much data
Ob_flush ();
Flush ();
$cnt = 0;
}

foreach ($row as $i =&amp;gt; $v) {
$row [$i] = iconv (' utf-8 ', ' GBK ', $v);
}
Fputcsv ($fp, $row);
</code></pre>

<p>}

Simple and easy to use, saves memory, and does not rely on Third-party class libraries.