php框架ci中excel导入导出

一、导入

function import_pro(){
	
	   //要处理的excel文件
	   //$fullpath = './sampleData/example2.xls';//指定文件
	   //$re = $this->read($fullpath,'utf-8');
	   
	   //用用选择excel文件
	   //print_r($_FILES);
	   $tmp_file = $_FILES ['file_stu'] ['tmp_name'];//第一个中括号里面的是你传过来的excel文件值
	   $file_types = explode ( ".", $_FILES ['file_stu'] ['name'] );
	   $file_type = $file_types [count ( $file_types ) - 1];
	   //判别是不是.xls文件，判别是不是excel文件
	   if (strtolower ( $file_type ) != "xls"){
		   $this->error ( '不是Excel文件，重新上传' );
	   }
	   $savePath = "public/";
	   //以时间来命名上传的文件
	   $str = date ( 'Ymdhis' );
	   $file_name = $str . "." . $file_type;
	   //是否上传成功
	   if(!copy($tmp_file,$savePath.$file_name)){
		   $this->error ( '上传失败' );
	   }
	   //路径连接
	   $fullpath = $savePath.$file_name;
	   //echo $fullpath;
     
	   $re = $this->read($fullpath);//调用读取方法read()
	   unset($re[1]);
	   //print_r($re);
		foreach ($re as $key => $v) {
			//导入循环添加 
			  	$res = 	$this->db->insert('dao',array('brand_id'=>$v[0],
			  		'brand_name'=>$v[1],//将此数组的键值等于成你数据库的字段名
			  		'brand_logo'=>$v[2],
			  		'brand_desc'=>$v[3],
			  		'site_url'=>$v[4],
			  		'sort_order'=>$v[5],
			  		'is_show'=>$v[6])
			  	);}
			  	//判断是否成功
			  if(!$res){
			  	echo "失败";
			  }
	}	

二、导出

public function export(){
        $data = $this->db->get('dao')->result_array();
        $titles = $this->db->get('dao')->list_fields();
        $this->load->helper('excel_helper');//这个是类文件,可将三的代码复制到你ci框架中的helpers中,文件名字为excel_helper即可
        export_excel($data,"商品信息",$titles);
    }

三、helper文件,放在heplers中文件名字为excel_helper

<?php

function export_excel($data, $file_name, $titles=array())
{
		require_once APPPATH.'third_party/phpexcel/PHPExcel.php';
		
		$objPHPExcel = new PHPExcel();
		$objPHPExcel->getProperties()-> setTitle("export") -> setDescription("none");
		$objPHPExcel -> setActiveSheetIndex(0);
		
		foreach($titles as $index=>$title)
		{
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($index,1,$title);
		}
		foreach ($data as $key => $va) {
			$arr[]=array_values($data[$key]);

		}

		foreach($arr as $i => $vv)
		{
			if(!empty($titles))
			{
					$i=$i+1;
			}
			foreach($vv as $j => $v)
			{
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($j,$i+1,$v);
			}
		}
		
		
		
		//$objPHPExcel -> setActiveSheetIndex(0);
		$objWriter = PHPExcel_IOFactory :: createWriter($objPHPExcel, 'Excel5');
		// Sending headers to force the user to download the file
		header('Content-Type:application/vnd.ms-excel');
		//header('Content-Disposition:attachment;filename="Products_' . date('dMy') . '.xls"');
		header('Content-Disposition:attachment;filename="'. $file_name . '.xls"');
		header('Cache-Control:max-age=0');
		$objWriter -> save('php://output');
}