利用PHPExcel導(dǎo)出Excel并設(shè)置Excel格式以及數(shù)據(jù)源

xibeifneg3 2015-12-17

展開(kāi)全文

1、設(shè)置單元格格式，包括單元格邊框、單元格高度、單元格寬度
2、合并指定的單元格
3、設(shè)置Excel數(shù)據(jù)源，并將數(shù)據(jù)源保護(hù)起來(lái)（這個(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('沒(méi)有可以導(dǎo)出的簡(jiǎn)歷');
        }

        vendor('PHPExcel.PHPExcel');
        $fileName = $this->fileName;
        $fileName = empty($fileName)?'導(dǎo)出簡(jiǎn)歷-'.date('Y-m-d',time()):$fileName;

        $PHPExcel = new PHPExcel();

        //設(shè)置基本信息
        $PHPExcel->getProperties()->setCreator("jecken")
        ->setLastModifiedBy("jecken")
        ->setTitle("上海**人力資源服務(wù)有限公司")
        ->setSubject("簡(jiǎn)歷列表")
        ->setDescription("")
        ->setKeywords("簡(jiǎn)歷列表")
        ->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'];
        }

        //存儲(chǔ)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', '簡(jiǎn)歷列表(導(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證書(shū)');
        $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', '證件號(hào)碼');
        $PHPExcel->getActiveSheet()->setCellValue('N3', 'QQ');
        $PHPExcel->getActiveSheet()->setCellValue('O3', '電子郵箱');
        $PHPExcel->getActiveSheet()->setCellValue('P3', '出生日期');
        $PHPExcel->getActiveSheet()->setCellValue('Q3', '婚姻狀況');
        $PHPExcel->getActiveSheet()->setCellValue('R3', '戶(hù)籍');
        $PHPExcel->getActiveSheet()->setCellValue('S3', '畢業(yè)時(shí)間');
        $PHPExcel->getActiveSheet()->setCellValue('T3', '畢業(yè)院校');

        $PHPExcel->getActiveSheet()->setCellValue('U3', '專(zhuān)業(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', '自我評(píng)價(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('請(qǐng)選擇性別')
            -> 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證書(shū)
            $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('請(qǐng)選擇是否有AQE證書(shū)')
            -> setError('您輸入的值不在下拉框列表內(nèi).')
            -> setPromptTitle('是否有AQE證書(shū)')
            -> 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']);//證件號(hào)碼
            $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('請(qǐng)選擇婚姻')
            -> 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']); //面試崗位

            //簡(jiǎn)歷狀態(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('簡(jiǎn)歷狀態(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

    //            第*列合并對(duì)應(yīng)單元格
    $start_unit = 'A3';
    foreach ($data as $k => $v) {
        $row = $k + 1; //行
        $nn = 0;
//            第*列合并對(duì)應(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); //垂直居中
    }

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶(hù)發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購(gòu)買(mǎi)等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來(lái)自： xibeifneg3 > 《IT-php》

舉報(bào)/認(rèn)領(lǐng)