首頁  >  文章  >  後端開發  >  從資料庫匯出到csv檔案方法 csv是什麼檔案格式 adl檔案轉換csv csv檔案格式

從資料庫匯出到csv檔案方法 csv是什麼檔案格式 adl檔案轉換csv csv檔案格式

WBOY
WBOY原創
2016-07-29 08:50:001963瀏覽
<span>public function </span><span>export</span><span>(){
</span><span>// 从数据库中获取数据,为了节省内存,不要把数据一次性读到内存,从句柄中一行一行读即可
</span><span> // 输出Excel文件头,可把user.csv换成你要的文件名
</span><span>header(</span><span>'Content-Type: application/vnd.ms-excel'</span><span>)</span><span>;
</span><span>header(</span><span>'Content-Disposition: attachment;filename="order.csv"'</span><span>)</span><span>;
</span><span>header(</span><span>'Cache-Control: max-age=0'</span><span>)</span><span>;</span><span>$where</span><span>=</span><span>array</span><span>(
</span><span>"paid"</span><span>=></span><span>1</span><span>,
</span><span>"pay_type"</span><span>=></span><span>array</span><span>(</span><span>"NEQ"</span><span>,</span><span>"offline"</span><span>)</span><span>,
</span><span>"status"</span><span>=></span><span>array</span><span>(</span><span>"lt"</span><span>,</span><span>3</span><span>)</span><span>,
</span><span>)</span><span>;
</span><span>$stmt </span><span>= M(</span><span>"Group_order"</span><span>)-></span><span>field</span><span>(</span><span>"order_id,order_name,num,price,total_money,contact_name,phone,zipcode,adress,wx_cheap,balance_pay,payment_money,tuan_type,pay_time,pay_type,third_id,is_mobile_pay,paid,status"</span><span>)-></span><span>where</span><span>(</span><span>$where</span><span>)-></span><span>order</span><span>(</span><span>"order_id DESC"</span><span>)-></span><span>limit</span><span>(</span><span>1000</span><span>)-></span><span>select</span><span>()</span><span>;
</span><span>// 打开PHP文件句柄,php://output 表示直接输出到浏览器
</span><span>$fp </span><span>= fopen(</span><span>'php://output'</span><span>, </span><span>'a'</span><span>)</span><span>;
</span><span>// 输出Excel列名信息
</span><span>$head </span><span>= </span><span>array</span><span>(</span><span>"订单号"</span><span>,</span><span>"订单名称"</span><span>,</span><span>"购买数量"</span><span>,</span><span>"单价"</span><span>,</span><span>"总价"</span><span>,</span><span>"联系人姓名"</span><span>,</span><span>"联系人电话"</span><span>,</span><span>"邮编"</span><span>,</span><span>"详细地址"</span><span>,</span><span>"微信优惠金额"</span><span>,</span><span>"余额支付金额"</span><span>,</span><span>"真实支付金额"</span><span>,</span><span>"特卖类型(2为实物)"</span><span>,</span><span>"支付时间"</span><span>,</span><span>"支付类型"</span><span>,</span><span>"第三方支付id"</span><span>,</span><span>"是否是手机支付"</span><span>,</span><span>"是否支付"</span><span>,</span><span>"订单状态"</span><span>)</span><span>;
</span><span>foreach </span><span>(</span><span>$head </span><span>as </span><span>$i </span><span>=> </span><span>$v</span><span>) {
</span><span>// CSV的Excel支持GBK编码,一定要转换,否则乱码
</span><span>$head</span><span>[</span><span>$i</span><span>] = iconv(</span><span>'utf-8'</span><span>, </span><span>'gbk'</span><span>, </span><span>$v</span><span>)</span><span>;
</span><span>}
</span><span>// 将数据通过fputcsv写到文件句柄
</span><span>fputcsv(</span><span>$fp</span><span>, </span><span>$head</span><span>)</span><span>;
</span><span>// 计数器
</span><span>$cnt </span><span>= </span><span>0</span><span>;
</span><span>// 每隔$limit行,刷新一下输出buffer,不要太大,也不要太小
</span><span>$limit </span><span>= </span><span>500</span><span>;
</span><span>// 逐行取出数据,不浪费内存
</span><span>$count </span><span>= count(</span><span>$stmt</span><span>)</span><span>;
</span><span>for</span><span>(</span><span>$t</span><span>=</span><span>0</span><span>;</span><span>$t</span><span><</span><span>$count</span><span>;</span><span>$t</span><span>&#43;&#43;) {
</span><span>$cnt </span><span>&#43;&#43;</span><span>;
</span><span>if </span><span>(</span><span>$limit </span><span>== </span><span>$cnt</span><span>) { </span><span>//刷新一下输出buffer,防止由于数据过多造成问题
</span><span>ob_flush()</span><span>;
</span><span>flush()</span><span>;
</span><span>$cnt </span><span>= </span><span>0</span><span>;
</span><span>}
</span><span>$row </span><span>= </span><span>$stmt</span><span>[</span><span>$t</span><span>]</span><span>;
</span><span>foreach </span><span>(</span><span>$row </span><span>as </span><span>$i </span><span>=> </span><span>$v</span><span>) {
</span><span>if</span><span>(</span><span>$i</span><span>==</span><span>'pay_time'</span><span>){
</span><span>$v</span><span>=date(</span><span>"Y-m-d,H:i:s"</span><span>,</span><span>$v</span><span>)</span><span>;
</span><span>}
</span><span>$row</span><span>[</span><span>$i</span><span>] = iconv(</span><span>'utf-8'</span><span>, </span><span>'gbk'</span><span>, </span><span>$v</span><span>)</span><span>;
</span><span>}
</span><span> fputcsv(</span><span>$fp</span><span>, </span><span>$row</span><span>)</span><span>;
</span><span>}
</span><span>     fclose(</span><span>$fp</span><span>)</span><span>;
</span><span>}</span>

以上就介紹了從資料庫匯出到csv檔案方法,包括了csv檔案方面的內容,希望對PHP教學有興趣的朋友有幫助。

陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn