PHP 生成.csv 文件并下载到浏览器

近期做了一个项目须要把订单的信息显示出来。而且可以把相关信息放到一个.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 sumpayfee
			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";
			$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);
		}
	}
}