PHP导出MySQL数据到Excel文件

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