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;

    }

?>

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 213,186评论 6赞 492
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,858评论 3赞 387
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 158,620评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,888评论 1赞 285
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 66,009评论 6赞 385
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,149评论 1赞 291
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,204评论 3赞 412
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,956评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,385评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,698评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,863评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,544评论 4赞 335
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,185评论 3赞 317
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,899评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,141评论 1赞 267
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,684评论 2赞 362
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,750评论 2赞 351