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


浏览：23969 发布日期：2013/07/24 分类：技术分享
代码有点长，读起来有点累。先来个截图
利用PHPExcel导出Excel并设置Excel格式以及数据源
导出的Excel太宽了，所以将后面的列宽重新调整了再截的图

功能包括：
1、设置单元格格式，包括单元格边框、单元格高度、单元格宽度
2、合并指定的单元格
3、设置Excel数据源，并将数据源保护起来（这个是为了实现单元格下拉选项功能）
4、设置字体样式
  1. public function export(){
  2.         //此处全是一维数组
  3.         $resumeState = C('RESUME_STATE');
  4.         $processingStatus = C('PROCESSING_STATUS');
  5.         $hunyin = C('HUNYIN');
  6.         $sex = C('SEX');
  7.         $AQE = C('AQE');
  8.         
  9.         //面试企业列表
  10.         $C = M('Company');
  11.         $company = $C->where('status=1')->field('id,title')->order($this->order)->select();
  12.         
  13.         //需要被出的数据
  14.         $condition = $this->condition;
  15.         $Model = $this->model;
  16.         $Dao = M($Model);
  17.         $list = $Dao->where($condition)->select();
  18.         
  19.         if (empty($list)){
  20.             $this->error('没有可以导出的简历');
  21.         }
  22.         
  23.         vendor('PHPExcel.PHPExcel');
  24.         $fileName = $this->fileName;
  25.         $fileName = empty($fileName)?'导出简历-'.date('Y-m-d',time()):$fileName;
  26.         
  27.         $PHPExcel = new PHPExcel();
  28.         
  29.         //设置基本信息
  30.         $PHPExcel->getProperties()->setCreator("jecken")
  31.         ->setLastModifiedBy("jecken")
  32.         ->setTitle("上海**人力资源服务有限公司")
  33.         ->setSubject("简历列表")
  34.         ->setDescription("")
  35.         ->setKeywords("简历列表")
  36.         ->setCategory("");
  37.         $PHPExcel->setActiveSheetIndex(0);
  38.         $PHPExcel->getActiveSheet()->setTitle($fileName);
  39.         
  40.         //取得HR列表
  41.         $hrlist = M('Admin')->field('id,nickname')->select();
  42.         foreach ($hrlist as $key => $value){
  43.             $hr[$value['id']] = $value['nickname'];
  44.         }
  45.         
  46.         //存储Excel数据源到其他工作薄
  47.         $PHPExcel->createSheet();
  48.         $subObject = $PHPExcel->getSheet(1);
  49.         $subObject->setTitle('data');
  50.         foreach ($resumeState as $key => $value){
  51.             $subObject->setCellValue('A'.$key,$value);
  52.         }
  53.         foreach ($processingStatus as $key => $value){
  54.             $subObject->setCellValue('B'.$key,$value);
  55.         }
  56.         foreach ($company as $key => $value){
  57.             $subObject->setCellValue('C'.($key+1),$value['title']);
  58.             $companyList[$value['id']]=$value['title'];
  59.         }
  60.         $subObject->getColumnDimension('A')->setWidth(30);
  61.         $subObject->getColumnDimension('B')->setWidth(30);
  62.         $subObject->getColumnDimension('C')->setWidth(30);
  63.         
  64.         //保护数据源
  65.         $subObject->getProtection()->setSheet(true);
  66.         $subObject->protectCells('A1:C1000',time());
  67.         
  68.         //填入主标题
  69.         $PHPExcel->getActiveSheet()->setCellValue('A1', '上海**人力资源服务有限公司');
  70.         //填入副标题
  71.         $PHPExcel->getActiveSheet()->setCellValue('A2', '简历列表(导出日期：'.date('Y-m-d',time()).')');
  72.         
  73.         //填入表头
  74.         $PHPExcel->getActiveSheet()->setCellValue('A3', 'ID');
  75.         $PHPExcel->getActiveSheet()->setCellValue('B3', '姓名');
  76.         $PHPExcel->getActiveSheet()->setCellValue('C3', '性别');
  77.         $PHPExcel->getActiveSheet()->setCellValue('D3', '年龄');
  78.         $PHPExcel->getActiveSheet()->setCellValue('E3', '联系方式');
  79.         $PHPExcel->getActiveSheet()->setCellValue('F3', '学历');
  80.         $PHPExcel->getActiveSheet()->setCellValue('G3', '是否有AQE证书');
  81.         $PHPExcel->getActiveSheet()->setCellValue('H3', '住址');
  82.         $PHPExcel->getActiveSheet()->setCellValue('I3', '面试日期');
  83.         $PHPExcel->getActiveSheet()->setCellValue('J3', '面试时间');
  84.         $PHPExcel->getActiveSheet()->setCellValue('K3', '所属HR');
  85.         
  86.         $PHPExcel->getActiveSheet()->setCellValue('L3', '备注');
  87.         $PHPExcel->getActiveSheet()->setCellValue('M3', '证件号码');
  88.         $PHPExcel->getActiveSheet()->setCellValue('N3', 'QQ');
  89.         $PHPExcel->getActiveSheet()->setCellValue('O3', '电子邮箱');
  90.         $PHPExcel->getActiveSheet()->setCellValue('P3', '出生日期');
  91.         $PHPExcel->getActiveSheet()->setCellValue('Q3', '婚姻状况');
  92.         $PHPExcel->getActiveSheet()->setCellValue('R3', '户籍');
  93.         $PHPExcel->getActiveSheet()->setCellValue('S3', '毕业时间');
  94.         $PHPExcel->getActiveSheet()->setCellValue('T3', '毕业院校');
  95.         
  96.         $PHPExcel->getActiveSheet()->setCellValue('U3', '专业');
  97.         $PHPExcel->getActiveSheet()->setCellValue('V3', '工作经历');
  98.         $PHPExcel->getActiveSheet()->setCellValue('W3', '期望工作地点');
  99.         $PHPExcel->getActiveSheet()->setCellValue('X3', '期望薪酬');
  100.         $PHPExcel->getActiveSheet()->setCellValue('Y3', '期望职业');
  101.         $PHPExcel->getActiveSheet()->setCellValue('Z3', '面试单位');
  102.         $PHPExcel->getActiveSheet()->setCellValue('AA3', '面试岗位');
  103.         $PHPExcel->getActiveSheet()->setCellValue('AB3', '目前状态');
  104.         $PHPExcel->getActiveSheet()->setCellValue('AC3', '处理状态');
  105.         $PHPExcel->getActiveSheet()->setCellValue('AD3', '自我评价');
  106.         $PHPExcel->getActiveSheet()->setCellValue('AE3', '其他');
  107.         
  108.         //填入列表
  109.         $k = 1;
  110.         foreach ($list as $key => $value){
  111.             $k++;
  112.             
  113.             $PHPExcel->getActiveSheet()->setCellValue('A'.($key+4), $value['id']);
  114.             $PHPExcel->getActiveSheet()->setCellValue('B'.($key+4), $value['name']);
  115.             
  116.             //性别
  117.             $PHPExcel->getActiveSheet()->getCell('C'.($key+4))->getDataValidation()
  118.             -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
  119.             -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
  120.             -> setAllowBlank(false)
  121.             -> setShowInputMessage(true)
  122.             -> setShowErrorMessage(true)
  123.             -> setShowDropDown(true)
  124.             -> setErrorTitle('请选择性别')
  125.             -> setError('您输入的值不在下拉框列表内.')
  126.             -> setPromptTitle('性别')
  127.             -> setFormula1('"'.join(',', $sex).'"');
  128.             $PHPExcel->getActiveSheet()->setCellValue('C'.($key+4), $sex[$value['sex']]);
  129.             
  130.             //年龄
  131.             $PHPExcel->getActiveSheet()->setCellValue('D'.($key+4), $value['age']);
  132.             
  133.             $PHPExcel->getActiveSheet()->setCellValue('E'.($key+4), $value['tel']);
  134.             $PHPExcel->getActiveSheet()->setCellValue('F'.($key+4), $value['xueli']);
  135.             
  136.             //是否有AQE证书
  137.             $PHPExcel->getActiveSheet()->getCell('G'.($key+4))->getDataValidation()
  138.             -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
  139.             -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
  140.             -> setAllowBlank(false)
  141.             -> setShowInputMessage(true)
  142.             -> setShowErrorMessage(true)
  143.             -> setShowDropDown(true)
  144.             -> setErrorTitle('请选择是否有AQE证书')
  145.             -> setError('您输入的值不在下拉框列表内.')
  146.             -> setPromptTitle('是否有AQE证书')
  147.             -> setFormula1('"'.join(',', $AQE).'"');
  148.             $PHPExcel->getActiveSheet()->setCellValue('G'.($key+4), $AQE[$value['hasAQE']]);
  149.             
  150.             $PHPExcel->getActiveSheet()->setCellValue('H'.($key+4), $value['juzhudi']);
  151.             $PHPExcel->getActiveSheet()->setCellValue('I'.($key+4), setDate($value['auditionTime'],'Y年m月d日'));//面试日期
  152.             $PHPExcel->getActiveSheet()->setCellValue('J'.($key+4), setDate($value['auditionTime'],'H点i分'));//面试时间
  153.             $PHPExcel->getActiveSheet()->setCellValue('K'.($key+4), $hr[$value['userid']]); //所属HR
  154.             $PHPExcel->getActiveSheet()->setCellValue('L'.($key+4), $value['remark']);//备注
  155.             
  156.             $PHPExcel->getActiveSheet()->setCellValue('M'.($key+4), $value['cid']);//证件号码
  157.             $PHPExcel->getActiveSheet()->setCellValue('N'.($key+4), $value['qq']);
  158.             $PHPExcel->getActiveSheet()->setCellValue('O'.($key+4), $value['email']);
  159.             $PHPExcel->getActiveSheet()->setCellValue('P'.($key+4), setDate($value['birthday']));
  160.             
  161.                 
  162.             
  163.                 
  164.             //婚姻
  165.             $PHPExcel->getActiveSheet()->getCell('Q'.($key+4))->getDataValidation()
  166.             -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
  167.             -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
  168.             -> setAllowBlank(false)
  169.             -> setShowInputMessage(true)
  170.             -> setShowErrorMessage(true)
  171.             -> setShowDropDown(true)
  172.             -> setErrorTitle('请选择婚姻')
  173.             -> setError('您输入的值不在下拉框列表内.')
  174.             -> setPromptTitle('性别')
  175.             -> setFormula1('"'.join(',', $hunyin).'"');
  176.             $PHPExcel->getActiveSheet()->setCellValue('Q'.($key+4), $hunyin[$value['hunyin']]);
  177.                 

  179.             $PHPExcel->getActiveSheet()->setCellValue('R'.($key+4), $value['huji']);
  180.             $PHPExcel->getActiveSheet()->setCellValue('S'.($key+4), setDate($value['graduationTime']));
  181.             $PHPExcel->getActiveSheet()->setCellValue('T'.($key+4), $value['graduationSchool']);
  182.             $PHPExcel->getActiveSheet()->setCellValue('U'.($key+4), $value['specialty']);
  183.             $PHPExcel->getActiveSheet()->setCellValue('V'.($key+4), $value['works']);
  184.             $PHPExcel->getActiveSheet()->setCellValue('W'.($key+4), $value['expectAddress']);
  185.             $PHPExcel->getActiveSheet()->setCellValue('X'.($key+4), $value['expectSalary']);
  186.             $PHPExcel->getActiveSheet()->setCellValue('Y'.($key+4), $value['expectProfession']);
  187.                 
  188.             //面试单位
  189.             $PHPExcel->getActiveSheet()->getCell('Z'.($key+4))->getDataValidation()
  190.             -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
  191.             -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
  192.             -> setAllowBlank(false)
  193.             -> setShowInputMessage(true)
  194.             -> setShowErrorMessage(true)
  195.             -> setShowDropDown(true)
  196.             -> setErrorTitle('输入的值有误')
  197.             -> setError('您输入的值不在下拉框列表内.')
  198.             -> setPromptTitle('面试单位')
  199.             -> setFormula1('data!$C$1:$C$'.count($company));
  200.             $PHPExcel->getActiveSheet()->setCellValue('Z'.($key+4), $companyList[$value['company']]);//面试单位
  201.             $PHPExcel->getActiveSheet()->setCellValue('AA'.($key+4), $value['post']); //面试岗位
  202.                 
  203.             //简历状态
  204.             $PHPExcel->getActiveSheet()->getCell('AB'.($key+4))->getDataValidation()
  205.             -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
  206.             -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
  207.             -> setAllowBlank(false)
  208.             -> setShowInputMessage(true)
  209.             -> setShowErrorMessage(true)
  210.             -> setShowDropDown(true)
  211.             -> setErrorTitle('输入的值有误')
  212.             -> setError('您输入的值不在下拉框列表内.')
  213.             -> setPromptTitle('简历状态')
  214.             -> setFormula1('data!$A$1:$A$'.count($resumeState));
  215.             $PHPExcel->getActiveSheet()->setCellValue('AB'.($key+4), $resumeState[$value['resumeState']]);
  216.             
  217.             //处理状态
  218.             $PHPExcel->getActiveSheet()->getCell('AC'.($key+4))->getDataValidation()
  219.             -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
  220.             -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
  221.             -> setAllowBlank(false)
  222.             -> setShowInputMessage(true)
  223.             -> setShowErrorMessage(true)
  224.             -> setShowDropDown(true)
  225.             -> setErrorTitle('输入的值有误')
  226.             -> setError('您输入的值不在下拉框列表内.')
  227.             -> setPromptTitle('处理状态')
  228.             -> setFormula1('data!$B$1:$B$'.count($processingStatus));
  229.             $PHPExcel->getActiveSheet()->setCellValue('AC'.($key+4), $processingStatus[$value['processingStatus']]);
  230.                 
  231.             $PHPExcel->getActiveSheet()->setCellValue('AD'.($key+4), $value['selfIntroduction']);
  232.             $PHPExcel->getActiveSheet()->setCellValue('AE'.($key+4), $value['other']);
  233.         
  234.             //设置每一行行高
  235.             $PHPExcel->getActiveSheet()->getRowDimension($key+4)->setRowHeight(30);
  236.         }
  237.         
  238.         //合并单元格
  239.         $PHPExcel->getActiveSheet()->mergeCells('A1:AE1');
  240.         $PHPExcel->getActiveSheet()->mergeCells('A2:AE2');
  241.         
  242.         //设置单元格宽度
  243.         $PHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(6);
  244.         $PHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
  245.         $PHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
  246.         $PHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
  247.         $PHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
  248.         $PHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
  249.         $PHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(16);
  250.         $PHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
  251.         $PHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);
  252.         $PHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
  253.         $PHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10);
  254.         $PHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(30);
  255.         $PHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
  256.         $PHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
  257.         $PHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(25);
  258.         $PHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
  259.         $PHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(10);
  260.         $PHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(25);
  261.         $PHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(15);
  262.         $PHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(18);
  263.         $PHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(15);
  264.         $PHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(30);
  265.         $PHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(15);
  266.         $PHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(15);
  267.         $PHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(15);
  268.         $PHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(20);
  269.         $PHPExcel->getActiveSheet()->getColumnDimension('AA')->setWidth(20);
  270.         $PHPExcel->getActiveSheet()->getColumnDimension('AB')->setWidth(20);
  271.         $PHPExcel->getActiveSheet()->getColumnDimension('AC')->setWidth(15);
  272.         $PHPExcel->getActiveSheet()->getColumnDimension('AD')->setWidth(30);
  273.         $PHPExcel->getActiveSheet()->getColumnDimension('AE')->setWidth(30);
  274.         
  275.         //设置表头行高
  276.         $PHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(35);
  277.         $PHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(22);
  278.         $PHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(20);
  279.         
  280.         //设置字体样式
  281.         $PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('黑体');
  282.         $PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
  283.         $PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
  284.         $PHPExcel->getActiveSheet()->getStyle('A3:AE3')->getFont()->setBold(true);
  285.             
  286.         $PHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setName('宋体');
  287.         $PHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(16);
  288.         
  289.         $PHPExcel->getActiveSheet()->getStyle('A4:AE'.($k+2))->getFont()->setSize(10);
  290.         //设置居中
  291.         $PHPExcel->getActiveSheet()->getStyle('A1:AE'.($k+2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  292.             
  293.         //所有垂直居中
  294.         $PHPExcel->getActiveSheet()->getStyle('A1:AE'.($k+2))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
  295.             
  296.         //设置单元格边框
  297.         $PHPExcel->getActiveSheet()->getStyle('A3:AE'.($k+2))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  298.         
  299.         //设置自动换行
  300.         $PHPExcel->getActiveSheet()->getStyle('A3:AE'.($k+2))->getAlignment()->setWrapText(true);

  302.         
  303.         
  304.         //保存为2003格式
  305.         $objWriter = new PHPExcel_Writer_Excel5($PHPExcel);
  306.         header("Pragma: public");
  307.         header("Expires: 0");
  308.         header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
  309.         header("Content-Type:application/force-download");
  310.         header("Content-Type:application/vnd.ms-execl");
  311.         header("Content-Type:application/octet-stream");
  312.         header("Content-Type:application/download");
  313.         
  314.         //多浏览器下兼容中文标题
  315.         $encoded_filename = urlencode($fileName);
  316.         $ua = $_SERVER["HTTP_USER_AGENT"];
  317.         if (preg_match("/MSIE/", $ua)) {
  318.             header('Content-Disposition: attachment; filename="' . $encoded_filename . '.xls"');
  319.         } else if (preg_match("/Firefox/", $ua)) {
  320.             header('Content-Disposition: attachment; filename*="utf8\'\'' . $fileName . '.xls"');
  321.         } else {
  322.             header('Content-Disposition: attachment; filename="' . $fileName . '.xls"');
  323.         }
  324.         
  325.         header("Content-Transfer-Encoding:binary");
  326.         $objWriter->save('php://output');
  327.     }
posted @ 2017-04-05 18:18  码农编程进阶笔记  阅读(54)  评论(0)    收藏  举报
返回顶部 有事您Q我