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');
}