CI框架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);
}

<pre name="code" class="html">public function brand_list_in(){
        include ('admin/libraries/PHPExcel.php');
        $PHPExcel = new PHPExcel();
        //var_dump($PHPExcel);die;
        $filePath = "admin/tmp/test_data.xlsx";
        // 默认用excel2007读取excel，若格式不对，则用之前的版本进行读取
        $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列开始输出*/
            for($currentColumn= 'A';$currentColumn<= $allColumn; $currentColumn++){
                $val = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65,$currentRow)->getValue();/**ord()将字符转为十进制数*/
                if($currentColumn == 'A')
                {
                    //echo $val."\t";
                    $value[$currentRow][]=$val;
                }else{
                    /**如果输出汉字有乱码，则需将输出内容用iconv函数进行编码转换，如下将gb2312编码转为utf-8编码输出*/
                    //echo iconv('utf-8','gb2312', $val)."\t";
                    $value[$currentRow][]=iconv('utf-8','gb2312', $val);
                }
            }
        }
        foreach($value as $key => $val){
            $vo[] = $val;
        };
        foreach($vo as $k => $v ){
            $x = $v;
            $key=array('brand_name','brand_logo','brand_desc','site_url','sort_order','is_show');
            $arr=array_combine($key,$x);
            $this->db->insert('brand',$arr);
        }
    }

导出

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;