thinkphp5.0数据导出excel表格

第一步、创建model类文件（名称自定）

第二步、在类中写入以下代码

<?php

namespace admin\admin\model;

use think\model;

class markmodel extends model{

    //导出预约信息
    public function outexcel1(){

        $del=@implode(",",$_post["del"]);

        if($del!=""){

            $art = db('messga');

            $where['id'] = array('in',$del);

            $rs = $art->where($where)->order('id desc')->select();

            foreach ($rs as $v){
                $excdata[]=array(

                    'id'=>$v["id"],

                    'names'=>$v["names"],

                    'phone'=>$v["phone"],

                    'email'=>$v["email"],

                    'cid'=>$v["cid"],

                    'companynames'=>$v["companynames"],

                    'tel'=>$v["tel"],

                    'num'=>$v["num"],

                    'p_names'=>$v["p_names"],

                    'dates'=>date('y-m-d h:i:s',$v["dates"])

                );
            }


        }else{

            $art = db('message');

            $rs = $art->order('id desc')->select();

            foreach($rs as $v){
                $excdata[]=array(

                    'id'=>$v["id"],

                    'names'=>$v["names"],


                    'phone'=>$v["phone"],

                    'email'=>$v["email"],

                    'cid'=>$v["cid"],

                    'companynames'=>$v["companynames"],

                    'tel'=>$v["tel"],


                    'num'=>$v["num"],

                    'p_names'=>$v["p_names"],

                    'dates'=>date('y-m-d h:i:s',$v["dates"])

                );
            }

            //echo "<script>alert ('请选中要导出的数据'); history.go(-1);</script>";

        }


　　　　//数组信息根据自己项目而定
        $expcellname=array(

            array('id','序号'),

            array('names','姓名'),

            array('phone','电话'),

            array('email','邮箱'),

            array('companynames','公司名称'),

            array('num','人数'),

            array('cid','公开课'),

            array('p_names','职务'),

            array('tel','座机'),

            array('dates','提交日期')

        );

        $this->exportexcel('留言信息',$expcellname,$excdata);

        exit;


    }




    /**

     * 导出操作

     * 参数1：$exptitle :导出excel的标题

     * 参数2：$expcellname:导出到表格中需要显示每一列的标题 如下：

     * $expcellname=array(

     *  参数三：$exptabledata : 需要导出的表中数据

     *

     *

     */

    public function exportexcel($exptitle='', $expcellname, $exptabledata){

　　　　　//引入phpexcell类
        import('phpexcel.classes.phpexcel');

        import('phpexcel.classes.phpexcel.iofactory.phpexcel_iofactory');

        $xlstitle = iconv('utf-8', 'gb2312', $exptitle);

        //文件名称

        $filename = $exptitle.date('_ymdhis');

        //or $xlstitle 文件名称可根据自己情况设定

        $cellnum = count($expcellname);

        $datanum = count($exptabledata);

        vendor("phpoffice.phpexcel.classes.phpexcel");

        $objphpexcel = new \phpexcel();

        $cellname    = array('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z');

        // 设置水平垂直居中

        $objphpexcel->getactivesheet()->getdefaultstyle()->getalignment()->sethorizontal(\phpexcel_style_alignment::horizontal_center);

        $objphpexcel->getactivesheet()->getdefaultstyle()->getalignment()->setvertical(\phpexcel_style_alignment::vertical_center);

        //设置excel表格 从 a1 到 ab1 这一行的字体加粗

        $objphpexcel->getactivesheet()->getstyle('a1:ab1')->getfont()->setbold(true);

        // 设置某一行的高度 1.2.3

        $objphpexcel->getactivesheet()->getrowdimension('1')->setrowheight(20);



        //j 、 k列为文本 (输入什么就是什么，不会随着excel系统格式变化)

        // $objphpexcel->getactivesheet()->getstyle('j')->getnumberformat()->setformatcode(phpexcel_style_numberformat::format_text);

        // $objphpexcel->getactivesheet()->getstyle('k')->getnumberformat()->setformatcode(phpexcel_style_numberformat::format_text);

        //设置某一列的宽度

        $objphpexcel->getactivesheet()->getcolumndimension('b')->setwidth(25);

        $objphpexcel->getactivesheet()->getcolumndimension('c')->setwidth(25);

        $objphpexcel->getactivesheet()->getcolumndimension('d')->setwidth(25);

        $objphpexcel->getactivesheet()->getcolumndimension('e')->setwidth(45);

        $objphpexcel->getactivesheet()->getcolumndimension('f')->setwidth(105);

        $objphpexcel->getactivesheet()->getcolumndimension('g')->setwidth(20);

        $objphpexcel->getactivesheet()->getcolumndimension('h')->setwidth(20);

        $objphpexcel->getactivesheet()->getcolumndimension('i')->setwidth(20);

        $objphpexcel->getactivesheet()->getcolumndimension('j')->setwidth(15);

        $objphpexcel->getactivesheet()->getcolumndimension('k')->setwidth(15);

        $objphpexcel->getactivesheet()->getcolumndimension('n')->setwidth(20);

        // 设置某一行的高度 1.2.3

        $objphpexcel->getactivesheet()->getrowdimension('1')->setrowheight(20);

        // 隐藏某一列

        //$objphpexcel->getactivesheet()->getcolumndimension('a')->setvisible(false);

        //$objphpexcel->getactivesheet()->getcolumndimension('b')->setvisible(false);

        //$objphpexcel->getactivesheet()->getcolumndimension('d')->setvisible(false);

        //$objphpexcel->getactivesheet()->getcolumndimension('e')->setvisible(false);

        //合并单元格

        // $objphpexcel->getactivesheet(0)->mergecells('a1:' . $cellname[$cellnum - 1] . '1');

        // $objphpexcel->setactivesheetindex(0)->setcellvalue('a1', $exptitle.'  export time:'.date('y-m-d h:i:s'));

        for ($i = 0; $i < $cellnum; $i++) {

            $objphpexcel->setactivesheetindex(0)->setcellvalue($cellname[$i] . '1', $expcellname[$i][1]);

        }

        // miscellaneous glyphs, utf-8

        for ($i = 0; $i < $datanum; $i++) {

            for ($j = 0; $j < $cellnum; $j++) {

                $objphpexcel->getactivesheet(0)->setcellvalue($cellname[$j] . ($i + 2), $exptabledata[$i][$expcellname[$j][0]]);

            }

        }

        // header('pragma:public');

        header('content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlstitle . '.xls"');

        header("content-disposition:attachment;filename={$filename}.xls");

        //attachment新窗口打印inline本窗口打印

        $objwriter = \phpexcel_iofactory::createwriter($objphpexcel, 'excel5');

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

        exit;

    }



}