tp5 excel导出类

use think\Loader;

/**

 * excel表格导出

 * @param string $fileName 文件名称

 * @param array $headArr 表头名称

 * @param array $data 要导出的数据

 * @author static7  */

function excelExport($fileName = '', $headArr = [], $data = [], $widths=[]) {

    $fileName = iconv("UTF-8", "GB2312//IGNORE", @$fileName);

    $fileName .=".xls";

    Loader::import('PHPExcel.PHPExcel');

    Loader::import('PHPExcel.PHPExcel.IOFactory.PHPExcel_IOFactory');

    $objPHPExcel = new \PHPExcel();

    $objPHPExcel->getProperties();

    $ordA = ord('A'); //

    $key2 = ord("@"); //

    foreach ($headArr as $v) {

        if($ordA > ord("Z"))

        {

            $colum = chr(ord("A")).chr(++$key2);//超过26个字母 AA1,AB1,AC1,AD1...BA1,BB1...

        }else{

            $colum = chr($ordA++);

        }

        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);

    }

    $column = 2;

    $objActSheet = $objPHPExcel->getActiveSheet();

    foreach ($data as $key => $rows) { // 行写入

        $ordA = ord('A');//重新从A开始

        $key2 = ord("@"); //

            foreach ($rows as $keyName => $value) { // 列写入

                if($ordA > ord("Z"))

                {

                    $colum = chr(ord("A")).chr(++$key2);//超过26个字母 AA1,AB1,AC1,AD1...BA1,BB1...

                }else{

                    $colum = chr($ordA++);

                }

                $objActSheet->setCellValue($colum . $column, $value);

            }

        $column++;

    }

    //表格宽度

    if(count($widths)){

        $ordA = ord('A');//重新从A开始

        $key2 = ord("@"); //

        foreach ($widths as  $value) { // 列写入

            if($ordA > ord("Z"))

            {

                $colum = chr(ord("A")).chr(++$key2);//超过26个字母 AA1,AB1,AC1,AD1...BA1,BB1...

            }else{

                $colum = chr($ordA++);

            }

            $objActSheet->getColumnDimension($colum)->setWidth($value);

        }

    }

    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

    //$objWriter->save('./excelFile/'.$fileName);

    header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件

    header('Content-Disposition: attachment;filename="'.$fileName.'"');//告诉浏览器将输出文件的名称(文件下载)

    header('Cache-Control: max-age=0');//禁止缓存

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

}

                //整理显示数据
　　　　　　　　　　$data[$k]['seller']=Db::table('ims_bj_shopn_member')->where('id',$v['staffid'])->value('realname');

                $data[$k]['sign']=$v['sign'];

                $data[$k]['title']=$v['title'];

                $data[$k]['bsc']=Db::table('sys_departbeauty_relation')->alias('r')->join(['sys_department' => 'd'],'r.id_department=d.id_department','left')->where('r.id_beauty',$v['storeid'])->value('d.st_department');

                $data[$k]['order_sn']="\t".$v['order_sn']."\t";

                $data[$k]['p_name']=$v['p_name'];

                $data[$k]['p_price']=$v['tuan_price'];

                $data[$k]['insert_time']=date('Y-m-d H:i:s',$v['insert_time']);

                $data[$k]['success_time']=date('Y-m-d H:i:s',$v['success_time']);

                $data[$k]['status']=$statusText;

                $data[$k]['fqr_pay']=$sonList[0]['pay_price'];

                $data[$k]['fqr_tel']=$sonList[0]['mobile'];

                $data[$k]['cantuan1']=$sonList[1]['realname'];

                $data[$k]['cantuan1_tel']=$sonList[1]['mobile'];

                $data[$k]['cantuan1_pay']=$sonList[1]['mobile']?$sonList[1]['pay_price']:'';

                $data[$k]['cantuan1_num']=$sonList[1]['join_num'];

                $data[$k]['cantuan2']=$sonList[2]['realname'];

                $data[$k]['cantuan2_tel']=$sonList[2]['mobile'];

                $data[$k]['cantuan2_pay']=$sonList[2]['mobile']?$sonList[2]['pay_price']:'';

                $data[$k]['cantuan2_num']=$sonList[2]['join_num'];;

                $data[$k]['cantuan3']=$sonList[3]['realname'];

                $data[$k]['cantuan3_tel']=$sonList[3]['mobile'];

                $data[$k]['cantuan3_pay']=$sonList[3]['mobile']?$sonList[3]['pay_price']:'';

                $data[$k]['cantuan3_num']=$sonList[3]['join_num'];;

                $searchOrder=Db::name('tuan_list')->where(['create_uid'=>$v['create_uid'],'status'=>2])->whereTime('begin_time', '<', $v['begin_time'])->count();

                $data[$k]['is_first']=$searchOrder?0:1;

                $data[$k]['number']=$join_num;

                $seaMap['list.create_uid']=array('neq',$v['create_uid']);

                $seaMap['list.status']=array('eq',2);

                $seaMap['list.order_sn']=array('neq',$v['order_sn']);

                $seaMap['order.uid']=array('eq',$v['create_uid']);

                $searchJoinOrder=Db::name('tuan_order')->alias('order')->join('tuan_list list','order.parent_order=list.order_sn','left')->where($seaMap)->whereTime('order.insert_time', '<', $v['begin_time'])->count();

                $data[$k]['is_join']=$searchJoinOrder?1:0;

　　　　　　　 
　　　　　　　　//调用导出
　　　　　　　 $filename = "拼团订单列表".date('YmdHis');

            $header = array ('活动发起人','所属美容师','所属门店编码','所属门店名称','所属办事处','拼购单号','拼购商品','商品总价','发起时间','成团时间','拼购状态','发起人支付金额','发起人手机号码','参团人1','手机号码','支付金额','参团人1拓客留客','参团人2','手机号码','支付金额','参团人2拓客留客','参团人3','手机号码','支付金额','参团人3拓客留客','发起人是否首次发起','参团人数','是否参加过拼团');

            $widths=array('15','20','20','30','30','30','60','10','20','20','20','20','20','20','20','20','20','20','20','20','20','20','20','20','20','20','20','20');

            if($data) {

                excelExport($filename, $header, $data, $widths);//生成数据

　　　　　　　　｝