PHP 產生.csv 檔案並下載到瀏覽器

標籤：des   blog   http   os   檔案   資料   

最近做了一個項目需要把訂單的資訊顯示出來，並且能夠把相關資訊放到一個.csv 檔案中，下載到瀏覽器。首先我要說明的是.csv 檔案，PHP 有專門的函數去解析該類型的檔案，相關函數大家可以去官網查看。注意.csv 檔案內容的編碼格式是gbk格式的，所以有必要對字元格式進行轉碼。檔案的樣式如下。

                                                                       

                一、首先要定義header 頭

               // 輸出Excel檔案頭，可把user.csv換成你要的檔案名稱header ( 'Content-Type: application/vnd.ms-excel' );header ( 'Content-Disposition: attachment;filename="訂單資料.csv"' );header ( 'Cache-Control: max-age=0' );// 開啟PHP檔案控制代碼，php://output 表示直接輸出到瀏覽器$fp = fopen ( 'php://output', 'a' );// 輸出Excel列名資訊

     

              二 、下面是檔案的頭部

               

               $head = array ('訂單號','','訂單名稱','','業務ID','','渠道ID','','渠道類型','','產品線名稱','','原始訂單號','','訂單金額','','From值','','訂單時間','','收款合約號','','渠道名稱','','付款合約號','','供應商名稱','','運營平台','','產品類型','','記賬時間','','渠道成本比例','','渠道成本','','應收賬款','','結算比例','','應付結算','','是否已回款','','是否已提批次','','備忘');foreach ( $head as $i => $v ) {// CSV的Excel支援GBK編碼，一定要轉換，否則亂碼$head [$i] = iconv ( 'utf-8', 'gbk', $v );}// 將資料通過fputcsv寫到檔案控制代碼fputcsv ( $fp, $head );

  

    三、下面是檔案的內容，以我的例子是從資料庫讀出來的，看不懂沒關係，原理是把結果查出來放到數組中，迴圈數組，把每個結果放到一個新的數組並且進行編碼。忘了告訴你我用的是CI（codeignite）架構

    

                     if ($this->input->get () != false) {// 從資料庫中擷取資料，為了節省記憶體，不要把資料一次性讀到記憶體，從控制代碼中一行一行讀即可$sql = "select Charge_final.charge_id,   Charge_final.product_name,       Charge_final.business_id,   Charge_final.channel_id,       Charge_final.channel_type,   bass_productline.product_line_name,   Charge_final.business_linkid,   Charge_final.fee,   Charge_final.msgfrom,        Charge_final.charge_time,       Charge_final.income_contract,       Charge_final.income_channel_name,       Charge_final.payment_contract,       Charge_final.merchant_name,       Charge_final.platform,       Charge_final.product_type,       Charge_final.bill_time,       Charge_final.channel_rate,       Charge_final.channel_fee,       (Charge_final.fee-Charge_final.channel_fee) as account,       Charge_final.payment_rate,       Charge_final.payment_fee,       Charge_final.income_id,       Charge_final.payment_id,       Charge_final.note   from {$this->Charge_final} as Charge_final left join   {$this->bass_productline} as bass_productline   on Charge_final.channel_id=bass_productline.channel_id and                Charge_final.channel_type=bass_productline.channel_type                where 1=1 $conditions order by Charge_final.charge_time desc";   $query = $this->db->query ( $sql );// 計數器$cnt = 0;// 每隔$limit行，重新整理一下輸出buffer，不要太大，也不要太小$limit = 8000;foreach ( $query->result_array () as $row ) {$cnt ++;if ($limit == $cnt) { // 重新整理一下輸出buffer，防止由於資料過多造成問題ob_flush ();flush ();$cnt = 0;}$income_id=$row['income_id']==0?'未回款':'回款';$payment_id=$row['payment_id']==0?'未提':'已提';// 讀取表資料$content = array ();$content [] = iconv ( 'utf-8', 'gbk', $row ['charge_id'] . "\t" );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['product_name'] );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['business_id'] . "\t" );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['channel_id'] . "\t" );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['channel_type'] );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['product_line_name'] );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['business_linkid'] );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['fee'] );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['msgfrom'] );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['charge_time'] . "\t" );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['income_contract'] );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['income_channel_name'] );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['payment_contract'] );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['merchant_name'] );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['platform'] );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['product_type'] );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row['bill_time'].'\t');$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['channel_rate'] );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['channel_fee'] );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['account'] );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['payment_rate'] );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['payment_fee'] );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $income_id );$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $payment_id);$content [] = '';$content [] = iconv ( 'utf-8', 'gbk', $row ['note'] );fputcsv ( $fp, $content );}

            三、檔案的尾部

                      $sql="select sum(Charge_final.fee) as amountorder,sum(Charge_final.channel_fee) as sumchannel_fee,sum(Charge_final.fee-Charge_final.channel_fee) as sumaccout,sum(Charge_final.payment_fee) as sumpayfeefrom {$this->Charge_final} as Charge_final left join {$this->bass_productline} as bass_productline on Charge_final.channel_id=bass_productline.channel_id and Charge_final.channel_type=bass_productline.channel_typewhere 1=1 $conditions";$querys = $this->db->query ( $sql );$ro = $querys->result_array ();$this->data ['ro'] = $ro;$amountorder=$ro['0']['amountorder'];$sumchannel_fee=$ro['0']['sumchannel_fee'];$sumaccout=$ro['0']['sumaccout'];$sumpayfee=$ro['0']['sumpayfee']; $foot = array ('統計','','','','','','','','','','','','','',"$amountorder",'','','','','','','','','','','','','','','','','','','','','',"$sumchannel_fee",'',"$sumaccout",'','','',"$sumpayfee",'','','','','','');  foreach ($foot as $i => $v ) {  // CSV的Excel支援GBK編碼，一定要轉換，否則亂碼  $foot[$i] = iconv ( 'utf-8', 'gbk', $v );  }  // 將資料通過fputcsv寫到檔案控制代碼  fputcsv ( $fp, $foot);}}}