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='width: 100px;height: 100px;border:1px solid #000;' ><img width='100' height='100' src='http://bfsj.yiqifenlei.com/".$contract[0]."'></td>"; echo "<td style='width: 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; }