thinkphp 导出导入表格 PHPExcel
PHPExcel类是php一个excel表格处理插件了，下面我来给大家介绍利用PHPExcel类来导入与导出excel表格的应用方法，有需要了解的朋友不防参考参考(PHPExcel自己百度下载这里不介绍了)。
下载phpExcel插件的地址：https://files.cnblogs.com/williamhu/Classes.rar
加压后放到\Extend\Vendor\里，然后就是编写代码了：
/**
* phpEscel导出用户表
* @author H.J.H
* date 2014.8.5 11:10
*/
function pushExcel(){
if($_SESSION['admin']['role_id'] != 1) {
$this->error('无权限操作');
}
$total=$this->_mod->count('id');//总数
$res=$this->_mod->field('id,share_id,username,mobile,gender,address,last_time')->select();
Vendor("Classes.PHPExcel");
Vendor("Classes.PHPExcel.php");
//创建处理对象实例
$objPhpExcel=new PHPExcel();
$objPhpExcel->getActiveSheet()->getDefaultColumnDimension()->setAutoSize(true);//设置单元格宽度
//设置表格的宽度 手动
$objPhpExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
$objPhpExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$objPhpExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
$objPhpExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
//设置标题
$rowVal = array(0=>'编号',1=>'级', 2=>'用户名', 3=>'手机号', 4=>'性别',5=>'地址',6=>'推荐人数',7=>'登录时间',8=>'会员总数');
foreach ($rowVal as $k=>$r){
$objPhpExcel->getActiveSheet()->getStyleByColumnAndRow($k,1)
->getFont()->setBold(true);//字体加粗
$objPhpExcel->getActiveSheet()->getStyleByColumnAndRow($k,1)->
getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//文字居中
$objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($k,1,$r);
}
$objPhpExcel->getActiveSheet()->setCellValue('A1', '编号');
$objPhpExcel->getActiveSheet()->setCellValue('B1', '用户名');
$objPhpExcel->getActiveSheet()->setCellValue('C1', '推荐者');
$objPhpExcel->getActiveSheet()->setCellValue('D1', '手机号');
$objPhpExcel->getActiveSheet()->setCellValue('E1', '性别');
$objPhpExcel->getActiveSheet()->setCellValue('F1', '地址');
$objPhpExcel->getActiveSheet()->setCellValue('G1', '推荐人数');
$objPhpExcel->getActiveSheet()->setCellValue('H1', '最后登录时间');
$objPhpExcel->getActiveSheet()->setCellValue('I1', '总会员数');
//设置当前的sheet索引 用于后续内容操作
$objPhpExcel->setActiveSheetIndex(0);
$objActSheet=$objPhpExcel->getActiveSheet();
//设置当前活动的sheet的名称
$title="公司用户录";
$objActSheet->setTitle($title);
//设置单元格内容
foreach($res as $k => $v)
{
$v['count']=$this->_mod->where('share_id='.$v['id'])->count('id');//推荐人数
$v['tname']=$this->_mod->where('id='.$v['share_id'])->getfield('username');//推荐人
if($v['gender']==1){
$v['gender']="男";
}elseif ($v['gender']==0){
$v['gender']="女";
}else {
$v['gender']="";
}
$v['last_time']=$v['last_time']==0 ? '' : date('Y-m-d H:i',$v['last_time']);
$num=$k+2;
$objPhpExcel->setActiveSheetIndex(0)
//Excel的第A列，uid是你查出数组的键值，下面以此类推
->setCellValue('A'.$num, $v['id'])
->setCellValue('B'.$num, $v['username'])
->setCellValue('C'.$num, $v['tname'])
->setCellValue('D'.$num, $v['mobile'])
->setCellValue('E'.$num, $v['gender'])
->setCellValue('F'.$num, $v['address'])
->setCellValue('G'.$num, $v['count'])
->setCellValue('H'.$num, $v['last_time']);
}
$objPhpExcel->setActiveSheetIndex(0)->setCellValue('I2', $total);
$title="公司用户录";
$name=date('Y-m-d');//设置文件名
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Transfer-Encoding:utf-8");
header("Pragma: no-cache");
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$title.'_'.urlencode($name).'.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPhpExcel, 'Excel5');
$objWriter->save('php://output');
}
还有个小小的提示，那就是：
在thinkphp中用phpexcel导出数据错误
<b>Fatal error</b>: print_r() [<a href='ref.outcontrol'>ref.outcontrol</a>]: Cannot use output buffering in
output buffering display handlers in <b>D:\www\bjydf\ThinkPHP\Common\common.php</b> on line <b>601</b><br />
导出文件打开显示以上错误，数据没有到成功，实质原因是在thinkphp配置文件中加上
<b>Fatal error</b>: print_r() [<a href='ref.outcontrol'>ref.outcontrol</a>]: Cannot use output buffering in
output buffering display handlers in <b>D:\www\bjydf\ThinkPHP\Common\common.php</b> on line <b>601</b><br />
导出文件打开显示以上错误，数据没有到成功，实质原因是在thinkphp配置文件中加上
'OUTPUT_ENCODE' => false,
是因为thinkphp3.0以后默认开启了”网页压缩输出”。关闭就好了
//导入数据 2014.9.19 H.J.H public function out_put(){ if($_POST['leadExcel'] == "true"){ $tmp_name = $_FILES['inputExcel']['tmp_name']; Vendor("Classes.PHPExcel"); Vendor("Classes.PHPExcel.php"); Vendor("Classes.PHPExcel.IOFactory"); Vendor("Classes.PHPExcel.Reader.Excel5"); $objReader = PHPExcel_IOFactory::createReader('Excel5'); $objPHPExcel = $objReader->load($tmp_name); $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); // 取得总行数 $highestColumn = $sheet->getHighestColumn(); // 取得总列数 $k = 0; for($j=2;$j<=$highestRow;$j++){ $data['username'] = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue(); $data['mobile'] = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue(); $data['address'] = $objPHPExcel->getActiveSheet()->getCell("D".$j)->getValue(); $data['city_id'] = $objPHPExcel->getActiveSheet()->getCell("F".$j)->getValue(); $data['share_id']=1332; $data['password']='96e79218965eb72c92a549dd5a330112'; $data['gender']=2; $data['last_time']=time()-60*60*24*$j; $data['reg_time']=time()-60*60*24*$j; $data['status']=1; $last_id = $this->_mod->add($data);//生成id if($last_id){ echo "第".$j."行导入成功,fph_user表第：".$last_id."条 "; }else { echo "第".$j."行导入失败<br/>"; } } } }
