phpexcel导出含有多张sheet的excel表实例

<?php

//导出excel

    public function export(){

        $this->load->model("form/daily_activation");

        $this->load->language('form/form');

        $this->load->model('form/daily_activation');

        $export['export_date_added'] = $this->request->get['export_date_added'];

        $export['date_type'] = $this->request->get['date_type'];

        $lists = $this->model_form_daily_activation->getCustomersByDate($export);

        require_once '../phpexcel/PHPExcel.php';

        // 创建Excel文件对象

        $objPHPExcel = new PHPExcel();

        //第一个sheet填入主标题

        $first_sheet_title = $this->language->get('export_sheet1');

        $objPHPExcel->getActiveSheet()->setCellValue('A1', $first_sheet_title);

        //设置单元格宽

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(23);

        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(17);

        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(17);

        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(17);

        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(17);

        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(17);

        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(33);

        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);

        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);

        //根据excel坐标，添加数据

        $objPHPExcel->setActiveSheetIndex(0)

            ->setCellValue('A1', $this->language->get('export_card_number'))

            ->setCellValue('B1', $this->language->get('export_english_name'))

            ->setCellValue('C1', $this->language->get('export_chinese_name'))

            ->setCellValue('D1', $this->language->get('export_tel_code'))

            ->setCellValue('E1', $this->language->get('export_telephone'))

            ->setCellValue('F1', $this->language->get('export_address'))

            ->setCellValue('G1', $this->language->get('export_certificate_type'))

            ->setCellValue('H1', $this->language->get('export_certificate'))

            ->setCellValue('I1', $this->language->get('export_begin_date'))

            ->setCellValue('J1', $this->language->get('export_end_date'))

            ->setCellValue('K1', $this->language->get('export_referrer'))

            ->setCellValue('L1', $this->language->get('export_nationality'))

            ->setCellValue('M1', $this->language->get('export_cdd_category'))

            ->setCellValue('N1', $this->language->get('export_notes'))

            ->setCellValue('O1', $this->language->get('export_birthday'));

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(65);//行高

        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //所有单元格水平居中

        $objPHPExcel->getActiveSheet()->getStyle('A')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

        //接下来就是写数据到表格里面去  

        //$objActSheet = $objPHPExcel->getActiveSheet(); 

        foreach ($lists as $key=>$value) {  

            $key = $key+2;

            $objPHPExcel->getactivesheet()->setcellvalue('A'.$key," ".$value['card_number']);

            $objPHPExcel->getactivesheet()->setcellvalue('B'.$key,$value['english_name']);

            $objPHPExcel->getactivesheet()->setcellvalue('C'.$key,$value['chinese_name']);

            $objPHPExcel->getactivesheet()->setcellvalue('D'.$key,$value['tel_code']); //数字转文本格式

            $objPHPExcel->getactivesheet()->setcellvalue('E'.$key,$value['telephone']); //数字转文本格式

            $objPHPExcel->getactivesheet()->setcellvalue('F'.$key,$value['address']);

            $objPHPExcel->getactivesheet()->setcellvalue('G'.$key,$value['certificate_type']);

            $objPHPExcel->getactivesheet()->setcellvalue('H'.$key," ".$value['certificate_number']);

            $objPHPExcel->getactivesheet()->setcellvalue('I'.$key,$value['certificate_start_date']);

            $objPHPExcel->getactivesheet()->setcellvalue('J'.$key,$value['certificate_end_date']);

            $objPHPExcel->getactivesheet()->setcellvalue('K'.$key,$value['referrer']);

            $objPHPExcel->getactivesheet()->setcellvalue('L'.$key,$value['country']);

            $objPHPExcel->getactivesheet()->setcellvalue('M'.$key,$value['name_status']);

            $objPHPExcel->getactivesheet()->setcellvalue('N'.$key,$value['notes']);

            $objPHPExcel->getactivesheet()->setcellvalue('O'.$key,$value['birth_date']);

            //设置文本格式

            $objPHPExcel->getActiveSheet()->getStyle('A'.$key)->getNumberFormat()

    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); 

            $objPHPExcel->getActiveSheet()->getStyle('B'.$key)->getNumberFormat()

    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);  

            $objPHPExcel->getActiveSheet()->getStyle('C'.$key)->getNumberFormat()

    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);  

            $objPHPExcel->getActiveSheet()->getStyle('D'.$key)->getNumberFormat()

    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);  

            $objPHPExcel->getActiveSheet()->getStyle('E'.$key)->getNumberFormat()

    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);  

            $objPHPExcel->getActiveSheet()->getStyle('F'.$key)->getNumberFormat()

    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);  

            $objPHPExcel->getActiveSheet()->getStyle('G'.$key)->getNumberFormat()

    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);  

            $objPHPExcel->getActiveSheet()->getStyle('H'.$key)->getNumberFormat()

    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);  

            $objPHPExcel->getActiveSheet()->getStyle('I'.$key)->getNumberFormat()

    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);  

            $objPHPExcel->getActiveSheet()->getStyle('J'.$key)->getNumberFormat()

    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);  

            $objPHPExcel->getActiveSheet()->getStyle('K'.$key)->getNumberFormat()

    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);  

            $objPHPExcel->getActiveSheet()->getStyle('L'.$key)->getNumberFormat()

    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);  

            $objPHPExcel->getActiveSheet()->getStyle('M'.$key)->getNumberFormat()

    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);  

            $objPHPExcel->getActiveSheet()->getStyle('N'.$key)->getNumberFormat()

    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);  

            $objPHPExcel->getActiveSheet()->getStyle('O'.$key)->getNumberFormat()

    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);  

        }  

        /*保存到第一个sheet*/

        $objPHPExcel->getActiveSheet()->setTitle($first_sheet_title);

        $objPHPExcel->setActiveSheetIndex(0);

        //第一sheet -end

        //第二sheet转正

        $objPHPExcel->createSheet();

        $objPHPExcel->setActiveSheetIndex(1);

        $second = 1;

        $sheet_title = $this->language->get('export_sheet2');

        $lists2 = $this->model_form_daily_activation->getTemplteTips();

        $this->makeData($objPHPExcel,$second,$sheet_title,$lists2);

        //写入到第二个sheet-end

        //第三sheet转正

        $objPHPExcel->createSheet();

        $objPHPExcel->setActiveSheetIndex(1);

        $second = 2;

        $sheet_title = $this->language->get('export_sheet3');

        $lists3 = $this->model_form_daily_activation->getTelCodes();

        $this->makeData($objPHPExcel,$second,$sheet_title,$lists3);

        //写入到第三个sheet-end

        //第四sheet转正

        $objPHPExcel->createSheet();

        $objPHPExcel->setActiveSheetIndex(1);

        $second = 3;

        $sheet_title = $this->language->get('export_sheet4');

        $lists4 = $this->model_form_daily_activation->getCountrys();

        $this->makeData($objPHPExcel,$second,$sheet_title,$lists4);

        //写入到第四个sheet-end

        //第五sheet转正

        $objPHPExcel->createSheet();

        $objPHPExcel->setActiveSheetIndex(1);

        $second = 4;

        $sheet_title = $this->language->get('export_sheet5');

        $lists5 = $this->model_form_daily_activation->getIDcardCode();

        $this->makeData($objPHPExcel,$second,$sheet_title,$lists5);

        //写入到第五个sheet-end

        //设置保存版本格式  

        if($excel2007){  

            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);  

            $filename = 'data.xlsx';  

        }else{  

            $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);  

            $filename = 'data.xls';  

        }

        // 下载这个表格，在浏览器输出  

        $objPHPExcel->setActiveSheetIndex(0);

        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");;  

        header('Content-Disposition:attachment;filename='.$filename.'');  

        header("Content-Transfer-Encoding:binary");  

        $objWriter->save('php://output');  

    }

    /*

     * 封装

     * @ $objPHPExcel   phpexcel 对象

     * @ $num  int       第n个工作空间

     * @ $sheet_title    工作空间sheet名称

     * @ return true or false;

    */

    protected  function makeData($objPHPExcel,$num,$sheet_title,$lists){

        //$objPHPExcel->createSheet();

        $objPHPExcel->setActiveSheetIndex($num);

        //第2張表

        if($num==1){

            //写入到第2个sheet

            //向右对齐

            $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); 

            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(150);

            //设置自动换行

            $objPHPExcel->getDefaultStyle()->getAlignment()->setWrapText(TRUE); 

            foreach ($lists as $i=>$val){

                $i = $i+3;

                $objPHPExcel->getactivesheet()->setcellvalue('A'.$i, $val['name']);

            }

        }

        //第3張表

        if($num==2){

            //写入到第3个sheet

            //合并单元格F、G

            $objPHPExcel->getActiveSheet()->mergeCells('F1:G1');

            //C列水平居中

            $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //垂直方向上中间居中

            $objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);       

            $objPHPExcel->getActiveSheet()->getStyle('B1:F1')->getFont()->setName('宋体')->setSize(10)->setBold(true);

            $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(12)->setBold(true);

            $objPHPExcel->getactivesheet()->setcellvalue('A1', $this->language->get('export_country_regions'));

            $objPHPExcel->getactivesheet()->setcellvalue('B1',$this->language->get('export_country_regions2'));

            $objPHPExcel->getactivesheet()->setcellvalue('C1', $this->language->get('export_telcode'));

            $objPHPExcel->getactivesheet()->setcellvalue('D1', $this->language->get('export_country_simple'));

            $objPHPExcel->getactivesheet()->setcellvalue('E1', '');

            $objPHPExcel->getactivesheet()->setcellvalue('F1', $this->language->get('export_sm'));

            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(25);

            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);

            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(13);

            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(13);

            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(3);

            $this->load->model("form/form");

            $lists2 = $this->model_form_form->getCountrySm();

            foreach ($lists2 as $k=>$val){

                $k = $k+2;

                $objPHPExcel->getactivesheet()->setcellvalue('F'.$k, $val['name']);

                $objPHPExcel->getactivesheet()->setcellvalue('G'.$k, $val['code']);

                //字体颜色

                $objPHPExcel->getActiveSheet()->getStyle('F'.$k)->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_RED);

                //G列水平居中

                $objPHPExcel->getActiveSheet()->getStyle('G'.$k)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            }

            foreach ($lists as $i=>$val){

                $i = $i+2;

                $objPHPExcel->getactivesheet()->setcellvalue('A'.$i, $val['area_english_name']);

                $objPHPExcel->getactivesheet()->setcellvalue('B'.$i, $val['area_chinese_name']);

                $objPHPExcel->getactivesheet()->setcellvalue('C'.$i, $val['tel_code']);

                $objPHPExcel->getactivesheet()->setcellvalue('D'.$i, $val['area_name_code']);

                $objPHPExcel->getactivesheet()->setcellvalue('E'.$i, '');

            }

        }

        //第4張表

        if($num==3){

            //写入到第4个sheet

            $objPHPExcel->getActiveSheet()->getStyle('B')->getFont()->setSize(10);

            $objPHPExcel->getActiveSheet()->getStyle('A')->getFont()->setSize(12);

            $objPHPExcel->getActiveSheet()->getStyle('C')->getFont()->setSize(12);

            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(11);

            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(11);

            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(13);

            foreach ($lists as $key => $val) {

                if($val['code']=='CHN'){

                    $objPHPExcel->getactivesheet()->setcellvalue('A1', $val['english_name']);

                    $objPHPExcel->getactivesheet()->setcellvalue('B1', $val['chinese_name']);

                    $objPHPExcel->getactivesheet()->setcellvalue('C1', $val['code']);

                    //垂直方向上中间居中

                    $objPHPExcel->getActiveSheet()->getStyle('B'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                }

            }

            foreach ($lists as $i=>$val){

                $i = $i+2;

                if($val['code']!='CHN'){

                    $objPHPExcel->getactivesheet()->setcellvalue('A'.$i, $val['english_name']);

                    $objPHPExcel->getactivesheet()->setcellvalue('B'.$i, $val['chinese_name']);

                    $objPHPExcel->getactivesheet()->setcellvalue('C'.$i, $val['code']);

                    //垂直方向上中间居中

                    $objPHPExcel->getActiveSheet()->getStyle('B'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

                }      

            }

        }

        //第5張表

        if($num==4){

            //写入到第5个sheet

            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);

            $objPHPExcel->getActiveSheet()->getStyle('B')->getFont()->setSize(12);

            $objPHPExcel->getActiveSheet()->getStyle('D')->getFont()->setSize(10);

            foreach ($lists as $i=>$val){

                $i = $i+5;

                $objPHPExcel->getactivesheet()->setcellvalue('A'.$i, $val['code']);

                $objPHPExcel->getactivesheet()->setcellvalue('B'.$i, $val['name']);

                $objPHPExcel->getactivesheet()->setcellvalue('C'.$i, $val['status_code']);

                $objPHPExcel->getactivesheet()->setcellvalue('D'.$i, $val['status_name']);

                //設置文本格式

                $objPHPExcel->getActiveSheet()->getStyle('A'.$key)->getNumberFormat()

    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

                $objPHPExcel->getActiveSheet()->getStyle('C'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

                $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

            }

        }

        //设置保存版本格式  

        if($excel2007){  

            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);  

        }else{  

            $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);   

        } 

        /*保存到本地*/

        $objPHPExcel->getActiveSheet()->setTitle($sheet_title);

        return true;

    }

?>