PHP实现导出MySQL数据到Excel文件

<ol class="dp-xml">
<li class="alt"><span><span>// 输出Excel文件头，可把user.csv换成你要的文件名  </span></span></li>
<li><span> </span></li>
<li class="alt"><span>　　header('Content-Type: application/vnd.ms-excel');  </span></li>
<li><span> </span></li>
<li class="alt">
<span>　　header('Content-Disposition: attachment;</span><span class="attribute">filename</span><span>=</span><span class="attribute-value">"user.csv"</span><span>');  </span>
</li>
<li><span> </span></li>
<li class="alt">
<span>　　header('Cache-Control: </span><span class="attribute">max-age</span><span>=</span><span class="attribute-value">0</span><span>');  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>　　// 从数据库中获取数据，为了节省内存，不要把数据一次性读到内存，从句柄中一行一行读即可  </span></li>
<li><span> </span></li>
<li class="alt">
<span>　　$</span><span class="attribute">sql</span><span> = </span><span class="attribute-value">'select * from tbl where ……'</span><span>;  </span>
</li>
<li><span> </span></li>
<li class="alt">
<span>　　$</span><span class="attribute">stmt</span><span> = $db-</span><span class="tag">></span><span>query($sql);  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>　　// 打开PHP文件句柄，php://output 表示直接输出到浏览器  </span></li>
<li><span> </span></li>
<li class="alt">
<span>　　$</span><span class="attribute">fp</span><span> = </span><span class="attribute-value">fopen</span><span>('php://output', 'a');  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>　　// 输出Excel列名信息  </span></li>
<li><span> </span></li>
<li class="alt">
<span>　　$</span><span class="attribute">head</span><span> = </span><span class="attribute-value">array</span><span>('姓名', '性别', '年龄', 'Email', '电话', '……');  </span>
</li>
<li><span> </span></li>
<li class="alt">
<span>　　foreach ($head as $</span><span class="attribute">i</span><span> =</span><span class="tag">></span><span> $v) {  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>　　// CSV的Excel支持GBK编码，一定要转换，否则乱码  </span></li>
<li><span> </span></li>
<li class="alt"><span>　　$head[$i] = iconv('utf-8', 'gbk', $v);  </span></li>
<li><span> </span></li>
<li class="alt"><span>　　}  </span></li>
<li><span> </span></li>
<li class="alt"><span>　　// 将数据通过fputcsv写到文件句柄  </span></li>
<li><span> </span></li>
<li class="alt"><span>　　fputcsv($fp, $head);  </span></li>
<li><span> </span></li>
<li class="alt"><span>　　// 计数器  </span></li>
<li><span> </span></li>
<li class="alt">
<span>　　$</span><span class="attribute">cnt</span><span> = </span><span class="attribute-value">0</span><span>;  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>　　// 每隔$limit行，刷新一下输出buffer，不要太大，也不要太小  </span></li>
<li><span> </span></li>
<li class="alt">
<span>　　$</span><span class="attribute">limit</span><span> = </span><span class="attribute-value">100000</span><span>;  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>　　// 逐行取出数据，不浪费内存  </span></li>
<li><span> </span></li>
<li class="alt">
<span>　　while ($</span><span class="attribute">row</span><span> = $stmt-</span><span class="tag">></span><span>fetch(Zend_Db::FETCH_NUM)) {  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>　　$cnt ++;  </span></li>
<li><span> </span></li>
<li class="alt">
<span>　　if ($</span><span class="attribute">limit</span><span> == $cnt) { //刷新一下输出buffer，防止由于数据过多造成问题  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>　　ob_flush();  </span></li>
<li><span> </span></li>
<li class="alt"><span>　　flush();  </span></li>
<li><span> </span></li>
<li class="alt">
<span>　　$</span><span class="attribute">cnt</span><span> = </span><span class="attribute-value">0</span><span>;  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>　　}  </span></li>
<li><span> </span></li>
<li class="alt">
<span>　　foreach ($row as $</span><span class="attribute">i</span><span> =</span><span class="tag">></span><span> $v) {  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>　　$row[$i] = iconv('utf-8', 'gbk', $v);  </span></li>
<li><span> </span></li>
<li class="alt"><span>　　}  </span></li>
<li><span> </span></li>
<li class="alt"><span>　　fputcsv($fp, $row);  </span></li>
<li><span> </span></li>
<li class="alt"><span>　　}   </span></li>
</ol>