美文网首页PHP开发PHP经验分享
phpexcel导出含有多张sheet的excel表实例

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

作者: 村长王无敌 | 来源:发表于2020-05-26 14:27 被阅读0次

    <?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;

        }

    ?>

    相关文章

      网友评论

        本文标题：phpexcel导出含有多张sheet的excel表实例

        本文链接：https://www.haomeiwen.com/subject/idzmahtx.html

        延伸阅读

        深度阅读

        • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

        栏目导航

        热点阅读

        PHP开发

        PHP经验分享

        关于我们|服务条款|联系我们|phpexcel导出含有多张sheet的excel表实例|投稿指南|网站地图|RSS订阅|排版工具|手机版

        提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

        Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

        备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

        本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

        所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！