|
1、設(shè)置單元格格式，包括單元格邊框、單元格高度、單元格寬度 2、合并指定的單元格 3、設(shè)置Excel數(shù)據(jù)源，并將數(shù)據(jù)源保護(hù)起來（這個(gè)是為了實(shí)現(xiàn)單元格下拉選項(xiàng)功能） 4、設(shè)置字體樣式 public function export(){ //此處全是一維數(shù)組 $resumeState = C('RESUME_STATE'); $processingStatus = C('PROCESSING_STATUS'); $hunyin = C('HUNYIN'); $sex = C('SEX'); $AQE = C('AQE'); //面試企業(yè)列表 $C = M('Company'); $company = $C->where('status=1')->field('id,title')->order($this->order)->select(); //需要被出的數(shù)據(jù) $condition = $this->condition; $Model = $this->model; $Dao = M($Model); $list = $Dao->where($condition)->select(); if (empty($list)){ $this->error('沒有可以導(dǎo)出的簡歷'); } vendor('PHPExcel.PHPExcel'); $fileName = $this->fileName; $fileName = empty($fileName)?'導(dǎo)出簡歷-'.date('Y-m-d',time()):$fileName; $PHPExcel = new PHPExcel(); //設(shè)置基本信息 $PHPExcel->getProperties()->setCreator("jecken") ->setLastModifiedBy("jecken") ->setTitle("上海**人力資源服務(wù)有限公司") ->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數(shù)據(jù)源到其他工作薄 $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); //保護(hù)數(shù)據(jù)源 $subObject->getProtection()->setSheet(true); $subObject->protectCells('A1:C1000',time()); //填入主標(biāo)題 $PHPExcel->getActiveSheet()->setCellValue('A1', '上海**人力資源服務(wù)有限公司'); //填入副標(biāo)題 $PHPExcel->getActiveSheet()->setCellValue('A2', '簡歷列表(導(dǎo)出日期：'.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', '聯(lián)系方式'); $PHPExcel->getActiveSheet()->setCellValue('F3', '學(xué)歷'); $PHPExcel->getActiveSheet()->setCellValue('G3', '是否有AQE證書'); $PHPExcel->getActiveSheet()->setCellValue('H3', '住址'); $PHPExcel->getActiveSheet()->setCellValue('I3', '面試日期'); $PHPExcel->getActiveSheet()->setCellValue('J3', '面試時(shí)間'); $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', '畢業(yè)時(shí)間'); $PHPExcel->getActiveSheet()->setCellValue('T3', '畢業(yè)院校'); $PHPExcel->getActiveSheet()->setCellValue('U3', '專業(yè)'); $PHPExcel->getActiveSheet()->setCellValue('V3', '工作經(jīng)歷'); $PHPExcel->getActiveSheet()->setCellValue('W3', '期望工作地點(diǎn)'); $PHPExcel->getActiveSheet()->setCellValue('X3', '期望薪酬'); $PHPExcel->getActiveSheet()->setCellValue('Y3', '期望職業(yè)'); $PHPExcel->getActiveSheet()->setCellValue('Z3', '面試單位'); $PHPExcel->getActiveSheet()->setCellValue('AA3', '面試崗位'); $PHPExcel->getActiveSheet()->setCellValue('AB3', '目前狀態(tài)'); $PHPExcel->getActiveSheet()->setCellValue('AC3', '處理狀態(tài)'); $PHPExcel->getActiveSheet()->setCellValue('AD3', '自我評價(jià)'); $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('您輸入的值不在下拉框列表內(nèi).') -> 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('您輸入的值不在下拉框列表內(nèi).') -> 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點(diǎn)i分'));//面試時(shí)間 $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('您輸入的值不在下拉框列表內(nèi).') -> 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('您輸入的值不在下拉框列表內(nèi).') -> 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']); //面試崗位 //簡歷狀態(tài) $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('您輸入的值不在下拉框列表內(nèi).') -> setPromptTitle('簡歷狀態(tài)') -> setFormula1('data!$A$1:$A$'.count($resumeState)); $PHPExcel->getActiveSheet()->setCellValue('AB'.($key+4), $resumeState[$value['resumeState']]); //處理狀態(tài) $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('您輸入的值不在下拉框列表內(nèi).') -> setPromptTitle('處理狀態(tài)') -> 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']); //設(shè)置每一行行高 $PHPExcel->getActiveSheet()->getRowDimension($key+4)->setRowHeight(30); } //合并單元格 $PHPExcel->getActiveSheet()->mergeCells('A1:AE1'); $PHPExcel->getActiveSheet()->mergeCells('A2:AE2'); //設(shè)置單元格寬度 $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); //設(shè)置表頭行高 $PHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(35); $PHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(22); $PHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(20); //設(shè)置字體樣式 $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); //設(shè)置居中 $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); //設(shè)置單元格邊框 $PHPExcel->getActiveSheet()->getStyle('A3:AE'.($k+2))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); //設(shè)置自動(dòng)換行 $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"); //多瀏覽器下兼容中文標(biāo)題 $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'); } http://www./topic/3739.html // 第*列合并對應(yīng)單元格 $start_unit = 'A3'; foreach ($data as $k => $v) { $row = $k + 1; //行 $nn = 0; // 第*列合并對應(yīng)單元格 $px = 'area_region';//$px需要合并單元格列的下角標(biāo) if($row > 2){ if($data[$k][$px] != $data[$k+1][$px]){ $end_unit = 'A'.$row; $_unit = $start_unit.':'. $end_unit; $list_unit[] = $_unit; $start_unit = 'A'.($row+1); } } foreach ($v as $vv) { $col = chr(65 + $nn); //列 $obj->setCellValue($col . $row, $vv); //列,行,值 $nn++; } } foreach ($list_unit as $lu) { $objPHPExcel->getActiveSheet()->mergeCells($lu);//合并單元格 $objPHPExcel->getActiveSheet()->getStyle($lu)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //垂直居中 } |
|
|
