public function alliance_createxls(){
$allianceObj = M('alliance_apply');
$result = $allianceObj
->field("apply_name,sex,company,card,duty,phone,points_part,email,stayinfo,eatinfo,address,remark")
->select();
foreach ($result as $key => $vo) {
$result[$key]['card'] = $vo['card']." ";
$result[$key]['phone'] = $vo['phone']." ";
if($vo['sex'] == 0){
$result[$key]['sex'] = '男';
}else{
$result[$key]['sex'] = '女';
}
switch ($vo['duty']) {
case 1:
$result[$key]['duty'] = '学校院长';
break;
case 2:
$result[$key]['duty'] = '学校副院长';
break;
case 3:
$result[$key]['duty'] = '分院院长';
break;
case 4:
$result[$key]['duty'] = '分院副院长';
break;
case 5:
$result[$key]['duty'] = '系主任';
break;
case 6:
$result[$key]['duty'] = '副系主任';
break;
case 7:
$result[$key]['duty'] = '教研室主任';
break;
case 8:
$result[$key]['duty'] = '教师';
break;
case 9:
$result[$key]['duty'] = '总经理';
break;
case 10:
$result[$key]['duty'] = '副总经理';
break;
case 11:
$result[$key]['duty'] = '总监';
break;
case 12:
$result[$key]['duty'] = '职员';
break;
default:
$result[$key]['duty'] = '学校院长';
break;
}
switch ($vo['points_part']) {
case 1:
$result[$key]['points_part'] = '信息安全校企合作分盟';
break;
case 2:
$result[$key]['points_part'] = '云计算校企合作分盟';
break;
case 3:
$result[$key]['points_part'] = '计算机网络校企合作分盟';
break;
case 4:
$result[$key]['points_part'] = '机器人技术应用校企合作分盟';
break;
case 5:
$result[$key]['points_part'] = 'VR专业分盟';
break;
default:
$result[$key]['points_part'] = '--';
break;
}
if($vo['stayinfo'] == 1){
$result[$key]['stayinfo'] = '单住';
}else{
$result[$key]['stayinfo'] = '合住';
}
}
//导入PHPExcel类库,因为PHPExcel没有用命名空间,只能inport导入
import("Org.Util.PHPExcel");
import("Org.Util.PHPExcel.Writer.Excel5");
import("Org.Util.PHPExcel.IOFactory.php");
$filename="联盟大会报名";
$headArr=array("姓名","性别","单位名称","身份证号","职务","手机号码","分盟参与意向","邮箱地址","住宿要求","饮食要求","通讯地址","备注信息");
$this->getExcel($filename,$headArr,$result);
}
private function getExcel($fileName,$headArr,$data){
//对数据进行检验
if(empty($data) || !is_array($data)){
die("data must be a array");
}
//检查文件名
if(empty($fileName)){
exit;
}
$date = date("Y_m_d_H_i_s",time());
$fileName .= "_{$date}.xls";
//创建PHPExcel对象,注意,不能少了\
$objPHPExcel = new \PHPExcel();
$objProps = $objPHPExcel->getProperties();
//设置表头
$key = ord("A");
foreach($headArr as $v){
$colum = chr($key);
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
$key += 1;
}
$column = 2;
$objActSheet = $objPHPExcel->getActiveSheet();
foreach($data as $key => $rows){ //行写入
$span = ord("A");
foreach($rows as $keyName=>$value){// 列写入
$j = chr($span);
$objActSheet->setCellValue($j.$column, $value);
$span++;
}
$column++;
}
$fileName = iconv("utf-8", "gb2312", $fileName);
//重命名表
// $objPHPExcel->getActiveSheet()->setTitle('test');
//设置活动单指数到第一个表,所以Excel打开这是第一个表
$objPHPExcel->setActiveSheetIndex(0);
ob_end_clean();
ob_start();
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=\"$fileName\"");
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output'); //文件通过浏览器下载
exit;
}
关于PHPExcel导出Excel时身份证,数字会导出为科学计数的处理方法
这是因为Excel处理数字里默认数字太长会转化为科学计数法,处理起来很简单,我们在导出时把数字转为字符串,Excel就不会识别为数字了,也就不会显示为科学计数方式。
我的处理方式是在变量后面加个空格,简单方便。
$usercode .= ' ';
$money .= ' ';