利用PHPExcel导出Excel并设置Excel格式以及数据源

浏览：23969 发布日期：2013/07/24 分类：技术分享

代码有点长，读起来有点累。先来个截图

导出的Excel太宽了，所以将后面的列宽重新调整了再截的图

功能包括：

1、设置单元格格式，包括单元格边框、单元格高度、单元格宽度

2、合并指定的单元格

3、设置Excel数据源，并将数据源保护起来（这个是为了实现单元格下拉选项功能）

4、设置字体样式

public function export(){
//此处全是一维数组
$resumeState = C(‘RESUME_STATE’);
$processingStatus = C(‘PROCESSING_STATUS’);
$hunyin = C(‘HUNYIN’);
$sex = C(‘SEX’);
$AQE = C(‘AQE’);
//面试企业列表
$C = M(‘Company’);
$company = $C->where(‘status=1’)->field(‘id,title’)->order($this->order)->select();
//需要被出的数据
$condition = $this->condition;
$Model = $this->model;
$Dao = M($Model);
$list = $Dao->where($condition)->select();
if (empty($list)){
$this->error(‘没有可以导出的简历’);
}
vendor(‘PHPExcel.PHPExcel’);
$fileName = $this->fileName;
$fileName = empty($fileName)?’导出简历-‘.date(‘Y-m-d’,time()):$fileName;
$PHPExcel = new PHPExcel();
//设置基本信息
$PHPExcel->getProperties()->setCreator(“jecken”)
->setLastModifiedBy(“jecken”)
->setTitle(“上海**人力资源服务有限公司”)
->setSubject(“简历列表”)
->setDescription(“”)
->setKeywords(“简历列表”)
->setCategory(“”);
$PHPExcel->setActiveSheetIndex(0);
$PHPExcel->getActiveSheet()->setTitle($fileName);
//取得HR列表
$hrlist = M(‘Admin’)->field(‘id,nickname’)->select();
foreach ($hrlist as $key => $value){
$hr[$value[‘id’]] = $value[‘nickname’];
}
//存储Excel数据源到其他工作薄
$PHPExcel->createSheet();
$subObject = $PHPExcel->getSheet(1);
$subObject->setTitle(‘data’);
foreach ($resumeState as $key => $value){
$subObject->setCellValue(‘A’.$key,$value);
}
foreach ($processingStatus as $key => $value){
$subObject->setCellValue(‘B’.$key,$value);
}
foreach ($company as $key => $value){
$subObject->setCellValue(‘C’.($key+1),$value[‘title’]);
$companyList[$value[‘id’]]=$value[‘title’];
}
$subObject->getColumnDimension(‘A’)->setWidth(30);
$subObject->getColumnDimension(‘B’)->setWidth(30);
$subObject->getColumnDimension(‘C’)->setWidth(30);
//保护数据源
$subObject->getProtection()->setSheet(true);
$subObject->protectCells(‘A1:C1000’,time());
//填入主标题
$PHPExcel->getActiveSheet()->setCellValue(‘A1’, ‘上海**人力资源服务有限公司’);
//填入副标题
$PHPExcel->getActiveSheet()->setCellValue(‘A2’, ‘简历列表(导出日期：’.date(‘Y-m-d’,time()).’)’);
//填入表头
$PHPExcel->getActiveSheet()->setCellValue(‘A3’, ‘ID’);
$PHPExcel->getActiveSheet()->setCellValue(‘B3’, ‘姓名’);
$PHPExcel->getActiveSheet()->setCellValue(‘C3’, ‘性别’);
$PHPExcel->getActiveSheet()->setCellValue(‘D3’, ‘年龄’);
$PHPExcel->getActiveSheet()->setCellValue(‘E3’, ‘联系方式’);
$PHPExcel->getActiveSheet()->setCellValue(‘F3’, ‘学历’);
$PHPExcel->getActiveSheet()->setCellValue(‘G3’, ‘是否有AQE证书’);
$PHPExcel->getActiveSheet()->setCellValue(‘H3’, ‘住址’);
$PHPExcel->getActiveSheet()->setCellValue(‘I3’, ‘面试日期’);
$PHPExcel->getActiveSheet()->setCellValue(‘J3’, ‘面试时间’);
$PHPExcel->getActiveSheet()->setCellValue(‘K3’, ‘所属HR’);
$PHPExcel->getActiveSheet()->setCellValue(‘L3’, ‘备注’);
$PHPExcel->getActiveSheet()->setCellValue(‘M3’, ‘证件号码’);
$PHPExcel->getActiveSheet()->setCellValue(‘N3’, ‘QQ’);
$PHPExcel->getActiveSheet()->setCellValue(‘O3’, ‘电子邮箱’);
$PHPExcel->getActiveSheet()->setCellValue(‘P3’, ‘出生日期’);
$PHPExcel->getActiveSheet()->setCellValue(‘Q3’, ‘婚姻状况’);
$PHPExcel->getActiveSheet()->setCellValue(‘R3’, ‘户籍’);
$PHPExcel->getActiveSheet()->setCellValue(‘S3’, ‘毕业时间’);
$PHPExcel->getActiveSheet()->setCellValue(‘T3’, ‘毕业院校’);
$PHPExcel->getActiveSheet()->setCellValue(‘U3’, ‘专业’);
$PHPExcel->getActiveSheet()->setCellValue(‘V3’, ‘工作经历’);
$PHPExcel->getActiveSheet()->setCellValue(‘W3’, ‘期望工作地点’);
$PHPExcel->getActiveSheet()->setCellValue(‘X3’, ‘期望薪酬’);
$PHPExcel->getActiveSheet()->setCellValue(‘Y3’, ‘期望职业’);
$PHPExcel->getActiveSheet()->setCellValue(‘Z3’, ‘面试单位’);
$PHPExcel->getActiveSheet()->setCellValue(‘AA3’, ‘面试岗位’);
$PHPExcel->getActiveSheet()->setCellValue(‘AB3’, ‘目前状态’);
$PHPExcel->getActiveSheet()->setCellValue(‘AC3’, ‘处理状态’);
$PHPExcel->getActiveSheet()->setCellValue(‘AD3’, ‘自我评价’);
$PHPExcel->getActiveSheet()->setCellValue(‘AE3’, ‘其他’);
//填入列表
$k = 1;
foreach ($list as $key => $value){
$k++;
$PHPExcel->getActiveSheet()->setCellValue(‘A’.($key+4), $value[‘id’]);
$PHPExcel->getActiveSheet()->setCellValue(‘B’.($key+4), $value[‘name’]);
//性别
$PHPExcel->getActiveSheet()->getCell(‘C’.($key+4))->getDataValidation()
-> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
-> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
-> setAllowBlank(false)
-> setShowInputMessage(true)
-> setShowErrorMessage(true)
-> setShowDropDown(true)
-> setErrorTitle(‘请选择性别’)
-> setError(‘您输入的值不在下拉框列表内.’)
-> setPromptTitle(‘性别’)
-> setFormula1(‘”‘.join(‘,’, $sex).'”‘);
$PHPExcel->getActiveSheet()->setCellValue(‘C’.($key+4), $sex[$value[‘sex’]]);
//年龄
$PHPExcel->getActiveSheet()->setCellValue(‘D’.($key+4), $value[‘age’]);
$PHPExcel->getActiveSheet()->setCellValue(‘E’.($key+4), $value[‘tel’]);
$PHPExcel->getActiveSheet()->setCellValue(‘F’.($key+4), $value[‘xueli’]);
//是否有AQE证书
$PHPExcel->getActiveSheet()->getCell(‘G’.($key+4))->getDataValidation()
-> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
-> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
-> setAllowBlank(false)
-> setShowInputMessage(true)
-> setShowErrorMessage(true)
-> setShowDropDown(true)
-> setErrorTitle(‘请选择是否有AQE证书’)
-> setError(‘您输入的值不在下拉框列表内.’)
-> setPromptTitle(‘是否有AQE证书’)
-> setFormula1(‘”‘.join(‘,’, $AQE).'”‘);
$PHPExcel->getActiveSheet()->setCellValue(‘G’.($key+4), $AQE[$value[‘hasAQE’]]);
$PHPExcel->getActiveSheet()->setCellValue(‘H’.($key+4), $value[‘juzhudi’]);
$PHPExcel->getActiveSheet()->setCellValue(‘I’.($key+4), setDate($value[‘auditionTime’],’Y年m月d日’));//面试日期
$PHPExcel->getActiveSheet()->setCellValue(‘J’.($key+4), setDate($value[‘auditionTime’],’H点i分’));//面试时间
$PHPExcel->getActiveSheet()->setCellValue(‘K’.($key+4), $hr[$value[‘userid’]]); //所属HR
$PHPExcel->getActiveSheet()->setCellValue(‘L’.($key+4), $value[‘remark’]);//备注
$PHPExcel->getActiveSheet()->setCellValue(‘M’.($key+4), $value[‘cid’]);//证件号码
$PHPExcel->getActiveSheet()->setCellValue(‘N’.($key+4), $value[‘qq’]);
$PHPExcel->getActiveSheet()->setCellValue(‘O’.($key+4), $value[’email’]);
$PHPExcel->getActiveSheet()->setCellValue(‘P’.($key+4), setDate($value[‘birthday’]));
//婚姻
$PHPExcel->getActiveSheet()->getCell(‘Q’.($key+4))->getDataValidation()
-> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
-> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
-> setAllowBlank(false)
-> setShowInputMessage(true)
-> setShowErrorMessage(true)
-> setShowDropDown(true)
-> setErrorTitle(‘请选择婚姻’)
-> setError(‘您输入的值不在下拉框列表内.’)
-> setPromptTitle(‘性别’)
-> setFormula1(‘”‘.join(‘,’, $hunyin).'”‘);
$PHPExcel->getActiveSheet()->setCellValue(‘Q’.($key+4), $hunyin[$value[‘hunyin’]]);
$PHPExcel->getActiveSheet()->setCellValue(‘R’.($key+4), $value[‘huji’]);
$PHPExcel->getActiveSheet()->setCellValue(‘S’.($key+4), setDate($value[‘graduationTime’]));
$PHPExcel->getActiveSheet()->setCellValue(‘T’.($key+4), $value[‘graduationSchool’]);
$PHPExcel->getActiveSheet()->setCellValue(‘U’.($key+4), $value[‘specialty’]);
$PHPExcel->getActiveSheet()->setCellValue(‘V’.($key+4), $value[‘works’]);
$PHPExcel->getActiveSheet()->setCellValue(‘W’.($key+4), $value[‘expectAddress’]);
$PHPExcel->getActiveSheet()->setCellValue(‘X’.($key+4), $value[‘expectSalary’]);
$PHPExcel->getActiveSheet()->setCellValue(‘Y’.($key+4), $value[‘expectProfession’]);
//面试单位
$PHPExcel->getActiveSheet()->getCell(‘Z’.($key+4))->getDataValidation()
-> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
-> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
-> setAllowBlank(false)
-> setShowInputMessage(true)
-> setShowErrorMessage(true)
-> setShowDropDown(true)
-> setErrorTitle(‘输入的值有误’)
-> setError(‘您输入的值不在下拉框列表内.’)
-> setPromptTitle(‘面试单位’)
-> setFormula1(‘data!$C$1:$C$’.count($company));
$PHPExcel->getActiveSheet()->setCellValue(‘Z’.($key+4), $companyList[$value[‘company’]]);//面试单位
$PHPExcel->getActiveSheet()->setCellValue(‘AA’.($key+4), $value[‘post’]); //面试岗位
//简历状态
$PHPExcel->getActiveSheet()->getCell(‘AB’.($key+4))->getDataValidation()
-> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
-> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
-> setAllowBlank(false)
-> setShowInputMessage(true)
-> setShowErrorMessage(true)
-> setShowDropDown(true)
-> setErrorTitle(‘输入的值有误’)
-> setError(‘您输入的值不在下拉框列表内.’)
-> setPromptTitle(‘简历状态’)
-> setFormula1(‘data!$A$1:$A$’.count($resumeState));
$PHPExcel->getActiveSheet()->setCellValue(‘AB’.($key+4), $resumeState[$value[‘resumeState’]]);
//处理状态
$PHPExcel->getActiveSheet()->getCell(‘AC’.($key+4))->getDataValidation()
-> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
-> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
-> setAllowBlank(false)
-> setShowInputMessage(true)
-> setShowErrorMessage(true)
-> setShowDropDown(true)
-> setErrorTitle(‘输入的值有误’)
-> setError(‘您输入的值不在下拉框列表内.’)
-> setPromptTitle(‘处理状态’)
-> setFormula1(‘data!$B$1:$B$’.count($processingStatus));
$PHPExcel->getActiveSheet()->setCellValue(‘AC’.($key+4), $processingStatus[$value[‘processingStatus’]]);
$PHPExcel->getActiveSheet()->setCellValue(‘AD’.($key+4), $value[‘selfIntroduction’]);
$PHPExcel->getActiveSheet()->setCellValue(‘AE’.($key+4), $value[‘other’]);
//设置每一行行高
$PHPExcel->getActiveSheet()->getRowDimension($key+4)->setRowHeight(30);
}
//合并单元格
$PHPExcel->getActiveSheet()->mergeCells(‘A1:AE1’);
$PHPExcel->getActiveSheet()->mergeCells(‘A2:AE2’);
//设置单元格宽度
$PHPExcel->getActiveSheet()->getColumnDimension(‘A’)->setWidth(6);
$PHPExcel->getActiveSheet()->getColumnDimension(‘B’)->setWidth(15);
$PHPExcel->getActiveSheet()->getColumnDimension(‘C’)->setWidth(15);
$PHPExcel->getActiveSheet()->getColumnDimension(‘D’)->setWidth(15);
$PHPExcel->getActiveSheet()->getColumnDimension(‘E’)->setWidth(15);
$PHPExcel->getActiveSheet()->getColumnDimension(‘F’)->setWidth(15);
$PHPExcel->getActiveSheet()->getColumnDimension(‘G’)->setWidth(16);
$PHPExcel->getActiveSheet()->getColumnDimension(‘H’)->setWidth(15);
$PHPExcel->getActiveSheet()->getColumnDimension(‘I’)->setWidth(10);
$PHPExcel->getActiveSheet()->getColumnDimension(‘J’)->setWidth(10);
$PHPExcel->getActiveSheet()->getColumnDimension(‘K’)->setWidth(10);
$PHPExcel->getActiveSheet()->getColumnDimension(‘L’)->setWidth(30);
$PHPExcel->getActiveSheet()->getColumnDimension(‘M’)->setWidth(20);
$PHPExcel->getActiveSheet()->getColumnDimension(‘N’)->setWidth(20);
$PHPExcel->getActiveSheet()->getColumnDimension(‘O’)->setWidth(25);
$PHPExcel->getActiveSheet()->getColumnDimension(‘P’)->setWidth(15);
$PHPExcel->getActiveSheet()->getColumnDimension(‘Q’)->setWidth(10);
$PHPExcel->getActiveSheet()->getColumnDimension(‘R’)->setWidth(25);
$PHPExcel->getActiveSheet()->getColumnDimension(‘S’)->setWidth(15);
$PHPExcel->getActiveSheet()->getColumnDimension(‘T’)->setWidth(18);
$PHPExcel->getActiveSheet()->getColumnDimension(‘U’)->setWidth(15);
$PHPExcel->getActiveSheet()->getColumnDimension(‘V’)->setWidth(30);
$PHPExcel->getActiveSheet()->getColumnDimension(‘W’)->setWidth(15);
$PHPExcel->getActiveSheet()->getColumnDimension(‘X’)->setWidth(15);
$PHPExcel->getActiveSheet()->getColumnDimension(‘Y’)->setWidth(15);
$PHPExcel->getActiveSheet()->getColumnDimension(‘Z’)->setWidth(20);
$PHPExcel->getActiveSheet()->getColumnDimension(‘AA’)->setWidth(20);
$PHPExcel->getActiveSheet()->getColumnDimension(‘AB’)->setWidth(20);
$PHPExcel->getActiveSheet()->getColumnDimension(‘AC’)->setWidth(15);
$PHPExcel->getActiveSheet()->getColumnDimension(‘AD’)->setWidth(30);
$PHPExcel->getActiveSheet()->getColumnDimension(‘AE’)->setWidth(30);
//设置表头行高
$PHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(35);
$PHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(22);
$PHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(20);
//设置字体样式
$PHPExcel->getActiveSheet()->getStyle(‘A1’)->getFont()->setName(‘黑体’);
$PHPExcel->getActiveSheet()->getStyle(‘A1’)->getFont()->setSize(20);
$PHPExcel->getActiveSheet()->getStyle(‘A1’)->getFont()->setBold(true);
$PHPExcel->getActiveSheet()->getStyle(‘A3:AE3’)->getFont()->setBold(true);
$PHPExcel->getActiveSheet()->getStyle(‘A2’)->getFont()->setName(‘宋体’);
$PHPExcel->getActiveSheet()->getStyle(‘A2’)->getFont()->setSize(16);
$PHPExcel->getActiveSheet()->getStyle(‘A4:AE’.($k+2))->getFont()->setSize(10);
//设置居中
$PHPExcel->getActiveSheet()->getStyle(‘A1:AE’.($k+2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//所有垂直居中
$PHPExcel->getActiveSheet()->getStyle(‘A1:AE’.($k+2))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//设置单元格边框
$PHPExcel->getActiveSheet()->getStyle(‘A3:AE’.($k+2))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//设置自动换行
$PHPExcel->getActiveSheet()->getStyle(‘A3:AE’.($k+2))->getAlignment()->setWrapText(true);
//保存为2003格式
$objWriter = new PHPExcel_Writer_Excel5($PHPExcel);
header(“Pragma: public”);
header(“Expires: 0”);
header(“Cache-Control:must-revalidate, post-check=0, pre-check=0”);
header(“Content-Type:application/force-download”);
header(“Content-Type:application/vnd.ms-execl”);
header(“Content-Type:application/octet-stream”);
header(“Content-Type:application/download”);
//多浏览器下兼容中文标题
$encoded_filename = urlencode($fileName);
$ua = $_SERVER[“HTTP_USER_AGENT”];
if (preg_match(“/MSIE/”, $ua)) {
header(‘Content-Disposition: attachment; filename=”‘ . $encoded_filename . ‘.xls”‘);
} else if (preg_match(“/Firefox/”, $ua)) {
header(‘Content-Disposition: attachment; filename*=”utf8\’\” . $fileName . ‘.xls”‘);
} else {
header(‘Content-Disposition: attachment; filename=”‘ . $fileName . ‘.xls”‘);
}
header(“Content-Transfer-Encoding:binary”);
$objWriter->save(‘php://output’);
}