excel导入 导出

PHP页面

 //设置header
        header("content-Type:text/html;charset=utf-8");
        //设置文件大小的限制
        ini_set("memory_limit",'1024M');
     //引入类文件
        Loader::import("PHPExcel.Classes.PHPExcel",EXTEND_PATH,'.php');
        $obj=new PHPExcel();
        //当前展示出来的页面
        $sheetobj=$obj->getActiveSheet();
       //查询数据库
        $data=Db::table('qq')->select();
        //赋值表格(重命名)
        $sheetobj->setTitle("测试导出");
        $sheetobj->setCellValue("A1","ID")
            ->setCellValue("B1","导航名称")
            ->setCellValue("C1","性别")
            ->setCellValue("D1","城市");
        //开始位置从第二行开始
        $j=2;
        foreach($data as $k =>$v){
           $sheetobj->setCellValue("A".$j,$v['id'])
               ->setCellValue("B".$j,$v['nickname'])
               ->setCellValue("C".$j,$v['gender'])
               ->setCellValue("D".$j,$v['city']);
           $j++;
        }
（来自于[PHPExcel文档）
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="01simple.xlsx"');
        header('Cache-Control: max-age=0');
     // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

    // If you're serving to IE over SSL, then the following may be needed
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0
        $objWriter = PHPExcel_IOFactory::createWriter($obj, 'Excel2007');
        $objWriter->save('php://output');
        exit;






//导入  数据入库


public function excel_do(){
    //设置header头
    header("content-Type:text/html;charset=utf-8");
    //设置文件大大小限制
    ini_set("memory_limit",'1024M');
    //引入类文件
    Loader::import("PHPExcel.Classes.PHPExcel");
    $obj=new PHPExcel();
  //接受一下前台传过来的源文件
    $excel=$_FILES;
    //获取上传文件名字
    $file_name=$excel['excel']['name'];
    //获取后缀
   $extension=strtolower(pathinfo($file_name,PATHINFO_EXTENSION));
 if($extension=='xlsx'){
     //新版本 实例化excel2007
     $reader=PHPExcel_IOFactory::createReader('Excel2007');
 }else{
     //老版本 实例化excel5
     $reader=PHPExcel_IOFactory::createReader('Excel5');
 }
    //已阅读者的身份加载文件
     $content=$reader->load($excel['excel']['tmp_name']);
     $arr=$content->getSheet(0)->toArray();
    // print_r($arr);die;
     unset($arr[0]);
     foreach ($arr as $k =>$v){
        $data['name']=$v[0];
        $data['pwd']=$v[1];
        $res[]=$data;
    }
    $a=Db::table('week1')->insertAll($res);
    if($a){
        $this->success("入库成功");
    }else{
        $this->error("入库失败");
    }
}

 
//excel导出
 public function comList(){
        $title=array("唯一标识","单位名称","负责人","工作负责人","负责人电话","合同材料","清运资质","上传时间");
        $filename="小区列表";
        header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:filename=$filename.xls");
        echo "<table style='border:1px solid #000;'><tr >";
        $data=Db::table('bf_community_sub')->select();
        //导出表头（也就是表中拥有的字段）
        for($i=0;$i<count($title);$i++){
            echo "<th style='border:1px solid #000;'>".$title[$i]."</th>";
        }
        echo "</tr>";
        for($i=0;$i<count($data);$i++){
            $contractImg=$data[$i]['contractImg'];
            $qualifica=$data[$i]['qualifica'];
            $contract=explode(",",$contractImg);
            $clean=explode(",",$qualifica);
            echo "<tr>";
            echo "<td style='border:1px solid #000;'>".$data[$i]['id']."</td>";
            echo "<td style='border:1px solid #000;'>".$data[$i]['company']."</td>";
            echo "<td style='border:1px solid #000;'>".$data[$i]['person']."</td>";
            echo "<td style='border:1px solid #000;'>".$data[$i]['workperson']."</td>";
            echo "<td style='border:1px solid #000;'>".$data[$i]['workphone']."</td>";
            echo "<td style=' 100px;height: 100px;border:1px solid #000;' ><img  width='100' height='100'  src='http://bfsj.yiqifenlei.com/".$contract[0]."'></td>";
            echo "<td style=' 100px;height: 100px;border:1px solid #000;'><img  width='100' height='100' src='http://bfsj.yiqifenlei.com/".$clean[0]."'></td>";
            echo "<td style='border:1px solid #000;'>".$data[$i]['created_at']."</td>";
            echo "</tr>";
        }
        echo "<tr>";
        echo "<td style='border:1px solid #000;'></td>";
        echo "<td style='border:1px solid #000;'></td>";
        echo "<td style='border:1px solid #000;'></td>";
        echo "<td style='border:1px solid #000;'></td>";
        echo "<td style='border:1px solid #000;'></td>";

//
        echo "</tr>";
        echo "</table>";die;
    }