Excel导入导出

导入

public function excel_put(){  
    //先做一个文件上传，保存文件  
    $path=$_FILES['file'];  
    $filePath = "uploads/".$path["name"];  
    move_uploaded_file($path["tmp_name"],$filePath);  
    //默认用excel2007读取excel，若格式不对，则用之前的版本进行读取  
    //表格字段名字  
    $data=array('B'=>'name','C'=>'pwd','D'=>'money1','E'=>'salt');  
    $tablename='user1';//表名字  
    $this->excel_fileput($filePath,$data,$tablename);      
}  
private function excel_fileput($filePath,$data,$tablename){  
    $this->load->library("phpexcel");//ci框架中引入excel类  
    $PHPExcel = new PHPExcel();  
    $PHPReader = new PHPExcel_Reader_Excel2007();  
    if(!$PHPReader->canRead($filePath)){  
        $PHPReader = new PHPExcel_Reader_Excel5();  
        if(!$PHPReader->canRead($filePath)){  
            echo 'no Excel';  
            return ;  
        }  
    }  
    // 加载excel文件  
    $PHPExcel = $PHPReader->load($filePath);  
  
    // 读取excel文件中的第一个工作表  
    $currentSheet = $PHPExcel->getSheet(0);  
    // 取得最大的列号  
    $allColumn = $currentSheet->getHighestColumn();  
    // 取得一共有多少行  
    $allRow = $currentSheet->getHighestRow();  
  
    // 从第二行开始输出，因为excel表中第一行为列名  
    for($currentRow = 2;$currentRow <= $allRow;$currentRow++){  
        /**从第A列开始输出*/  
        //echo $allColumn;  
          
        for($currentColumn= 'A';$currentColumn<= $allColumn; $currentColumn++){    
            $val = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65,$currentRow)->getValue();  
            //print_r($val);  
            //die;  
              
            if($currentColumn == 'A')  
            {  
                //echo $val."\t";  
            }else if($currentColumn <= $allColumn){  
                $data1[$currentColumn]=$val;  
            }  
        }  
        foreach($data as $key=>$val){  
            $data2[$val]=$data1[$key];  
        }  
        $this->db->insert($tablename,$data2);  
        //print_r($data2);  
        //echo "</br>";         
    }  
    //echo "\n";  
    echo "导入成功";  
}  

导出

header("Content-type:application/vnd.ms-excel");  
header("Content-Disposition:attachment;filename=123.xls");  
  
$array=$this->db->get("shop_address")->result_array();  
$str = "Id\tName\tPid\n";  
foreach ($array as $val) {  
    $str .=  $val['id'] . "\t" .$val['name'] . "\t" . $val['pid'] . "\n";  
}  
echo $str;