TP5 PHPExcel導(dǎo)出類(lèi)

電商商城小程序公眾號(hào)開(kāi)源項(xiàng)目：下載地址
導(dǎo)出表格圖片

<?php
namespace service;

use service\JsonService as Json;
use think\Request;

class PHPExcelService
{
    //PHPExcel實(shí)例化對(duì)象
    private static $PHPExcel=null;
    //表頭計(jì)數(shù)
    protected static $count;
    //表頭占行數(shù)
    protected static $topNumber = 3;
    //表能占據(jù)表行的字母對(duì)應(yīng)self::$cellkey
    protected static $cells;
    //表頭數(shù)據(jù)
    protected static $data=[];
    //文件名
    protected static $title='訂單導(dǎo)出';
    //行寬
    protected static $where=20;
    //行高
    protected static $height=50;
    //表行名
    private static $cellKey = 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',
        'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM',
        'AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'
    );
    //設(shè)置style
    private static $styleArray = array(
        'borders' => array(
            'allborders' => array(
//                PHPExcel_Style_Border里面有很多屬性，想要其他的自己去看
//                'style' => \PHPExcel_Style_Border::BORDER_THICK,//邊框是粗的
//                'style' => \PHPExcel_Style_Border::BORDER_DOUBLE,//雙重的
//                'style' => \PHPExcel_Style_Border::BORDER_HAIR,//虛線
//                'style' => \PHPExcel_Style_Border::BORDER_MEDIUM,//實(shí)粗線
//                'style' => \PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT,//虛粗線
//                'style' => \PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT,//點(diǎn)虛粗線
                'style' => \PHPExcel_Style_Border::BORDER_THIN,//細(xì)邊框
                //'color' => array('argb' => 'FFFF0000'),
            ),
        ),
        'font'=>[
            'bold'=>true
        ],
        'alignment'=>[
            'horizontal'=>\PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            'vertical'=>\PHPExcel_Style_Alignment::VERTICAL_CENTER
        ]
    );
    /**
     *初始化PHPExcel類(lèi)
     *@param $data array()
     *@param $fun function()
     * return
     */
    private static function initialize($data,$fun){
        vendor("PHPExcel.PHPExcel.PHPExcel");
        self::$PHPExcel= new \PHPExcel();
        if($fun!==null && is_callable($fun)){
            self::$styleArray=$fun();
        }
        if(!is_array($data)) exit(Json::fail('data 為數(shù)組'));
        self::$data=$data;
    }
    /**
     *設(shè)置字體格式
     *@param $title string 必選
     * return string
     */
    public static function setUtf8($title){
        return iconv('utf-8', 'gb2312', $title);
    }
    /**
     *
     * execl數(shù)據(jù)導(dǎo)出
     * @param  $list 需要導(dǎo)出的數(shù)據(jù) 格式和以前的可是一樣
     * @param  $list 也可以為匿名函數(shù) 匿名函數(shù)參數(shù)有 $sheet PHPExcel->getActiveSheet(),self::$topNumber 從第幾行設(shè)置,$cellkey 行號(hào)為數(shù)組,self::$cells現(xiàn)在設(shè)置的最大行號(hào)
     *
     * 特殊處理：合并單元格需要先對(duì)數(shù)據(jù)進(jìn)行處理
     */
    public function setExcelContent($list=null)
    {
        $sheet=self::$PHPExcel->getActiveSheet();
        foreach(self::$data as $key=>$val){
            $row=self::$cellKey[$key];
            $sheet->getColumnDimension($row)->setWidth(isset($val['w'])?$val['w']:self::$where);
            $sheet->setCellValue($row.self::$topNumber,isset($val['name'])?$val['name']:$val);
        }
        $cellkey=array_slice(self::$cellKey,0,self::$count);
        if($list!==null && is_array($list)){
            foreach ($cellkey as $k=>$v){
                foreach ($list as $key=>$val){
                    if(isset($val[$k]) && !is_array($val[$k])){
                        $sheet->setCellValue($v.(self::$topNumber+1+$key),$val[$k]);
                    }else if(isset($val[$k]) && is_array($val[$k])){
                        $str='';
                        foreach ($val[$k] as $value){
                            $str.=$value.chr(10);
                        }
                        $sheet->setCellValue($v.(self::$topNumber+1+$key),$str);
                    }
                }
            }
            $sheet->getDefaultRowDimension()->setRowHeight(self::$height);
            //設(shè)置邊框
            $sheet->getStyle('A1:'.self::$cells.(count($list)+self::$topNumber))->applyFromArray(self::$styleArray);
            //設(shè)置自動(dòng)換行
            $sheet->getStyle('A4:'.self::$cells.(count($list)+self::$topNumber))->getAlignment()->setWrapText(true);
        }else if($list!==null && is_callable($list)){
            $list($sheet,self::$topNumber,$cellkey,self::$cells)->applyFromArray(self::$styleArray);
        }

        return $this;
    }
    /**
     * 保存表格數(shù)據(jù)，并下載
     * @param
     * @return
     */
    public function ExcelSave(){
        $objWriter=\PHPExcel_IOFactory::createWriter(self::$PHPExcel,'Excel2007');
        $filename=self::$title.'.xlsx';
        ob_end_clean();
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
        exit;
    }

    /**
     * 設(shè)置頭部信息
     * @param $data array
     * @param $fun function() 主要設(shè)置邊框的粗細(xì)
     * @return $this
     */
    public static function setExcelHeader($data,$fun=null)
    {
        self::initialize($data,$fun);

        if(self::$count=count(self::$data)){
            if(self::$count>count(self::$cellKey)){
                return Json::fail('表頭長(zhǎng)度過(guò)長(zhǎng)');
            }
            self::$cells=self::$cellKey[self::$count-1];
        }else{
            return Json::fail('data 參數(shù)二不能為空');
        }
        return new self;
    }
    /**
     * 設(shè)置標(biāo)題
     * @param $title string || array ['title'=>'','name'=>'','info'=>[]]
     * @param $Name string
     * @param $info string || array;
     * @param $funName function($style,$A,$A2) 自定義設(shè)置頭部樣式
     * @return $this
     */
    public function setExcelTile($title='',$Name='',$info=[],$funName=null){
        //設(shè)置參數(shù)
        if(is_array($title)){
            if(isset($title['title'])) $title=$title['title'];
            if(isset($title['name'])) $Name=$title['name'];
            if(isset($title['info'])) $info=$title['info'];
        }
        if(empty($title))
            $title=self::$title;
        else
            self::$title=$title;
        if(empty($Name)) $Name=time();
        //設(shè)置Excel屬性
        self::$PHPExcel ->getProperties()
            ->setCreator("Neo")
            ->setLastModifiedBy("Neo")
            ->setTitle(self::setUtf8($title))
            ->setSubject($Name)
            ->setDescription("")
            ->setKeywords($Name)
            ->setCategory("");
        self::$PHPExcel ->getActiveSheet()->setCellValue('A1', $title);
        //文字居中
        self::$PHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        self::$PHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        self::$PHPExcel->setActiveSheetIndex(0);
        self::$PHPExcel->getActiveSheet()->setTitle($Name);

        self::$PHPExcel->getActiveSheet()->setCellValue('A2',self::setCellInfo($info));
        //合并表頭單元格
        self::$PHPExcel->getActiveSheet()->mergeCells('A1:'.self::$cells.'1');
        self::$PHPExcel->getActiveSheet()->mergeCells('A2:'.self::$cells.'2');

        self::$PHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(40);
        self::$PHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(20);
        //設(shè)置表頭行高
        if($funName!==null && is_callable($funName)){
            $fontstyle=self::$PHPExcel->getActiveSheet();
            $funName($fontstyle,'A1','A2');
        }else{
            //設(shè)置表頭字體
            self::$PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('黑體');
            self::$PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
            self::$PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
            self::$PHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setName('宋體');
            self::$PHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);
        }
        self::$PHPExcel->getActiveSheet()->getStyle('A3:'.self::$cells.'3')->getFont()->setBold(true);

        return $this;
    }
    /**
     * 設(shè)置第二行標(biāo)題內(nèi)容
     * @param $info  array (['name'=>'','site'=>'','phone'=>123] || ['我是表名','我是地址','我是手機(jī)號(hào)碼'] ) || string 自定義
     * @return string
     */
    private static function setCellInfo($info){
        $content=['操作者：','導(dǎo)出日期：'.date('Y-m-d',time()),'地址：','電話(huà)：'];
        if(is_array($info) && !empty($info)){
            if(isset($info['name'])){
                $content[0].=$info['name'];
            }else{
                $content[0].=isset($info[0])?$info[0]:'';
            }
            if(isset($info['site'])){
                $content[2].=$info['site'];
            }else{
                $content[2].=isset($info[1])?$info[1]:'';
            }
            if(isset($info['phone'])){
                $content[3].=$info['phone'];
            }else{
                $content[3].=isset($info[2])?$info[2]:'';
            }
            return implode(' ',$content);
        }else if(is_string($info)){
            return empty($info)?implode(' ',$content):$info;
        }
    }
}

用法示例：

PHPExcelService::setExcelHeader(['訂單號(hào)','性別','電話(huà)','收貨人姓名','收貨人電話(huà)','收貨地址','商品信息',
            '總價(jià)格','實(shí)際支付','郵費(fèi)','優(yōu)惠金額','支付狀態(tài)','支付時(shí)間','訂單狀態(tài)','下單時(shí)間','用戶(hù)備注'])
            ->setExcelTile('訂單導(dǎo)出'.date('YmdHis',time()),'訂單信息'.time(),' 生成時(shí)間：'.date('Y-m-d H:i:s',time()))
            ->setExcelContent($export)
            ->ExcelSave();

$export 為數(shù)組變量，變量格式為：

array(2) {
  [0]=>
  array(16) {
    [0]=>
    string(21) "wx2019071611093410001"
    [1]=>
    string(3) "男"
    [2]=>
    NULL
    [3]=>
    string(4) "1123"
    [4]=>
    string(11) "13055558888"
    [5]=>
    string(30) "上海 閘北區(qū) 城區(qū) 123123"
    [6]=>
    array(1) {
      [0]=>
      string(48) "無(wú)線吸塵器F8 玫瑰金禮盒版  [1 * 0.10]"
    }
    [7]=>
    string(4) "0.10"
    [8]=>
    string(4) "0.10"
    [9]=>
    string(4) "0.00"
    [10]=>
    string(4) "0.00"
    [11]=>
    string(12) "余額支付"
    [12]=>
    string(15) "2019/0716 11:09"
    [13]=>
    string(9) "待收貨"
    [14]=>
    string(19) "2019-07-16 11:09:34"
    [15]=>
    string(0) ""
  }
  [1]=>
  array(16) {
    [0]=>
    string(21) "wx2019070209390010001"
    [1]=>
    string(3) "男"
    [2]=>
    NULL
    [3]=>
    string(6) "劉強(qiáng)"
    [4]=>
    string(11) "18092217410"
    [5]=>
    string(37) "北京市 北京市 東城區(qū) 1823號(hào)"
    [6]=>
    array(1) {
      [0]=>
      string(22) "測(cè)撒測(cè)試  [1 * 10]"
    }
    [7]=>
    string(5) "10.00"
    [8]=>
    string(5) "10.00"
    [9]=>
    string(4) "0.00"
    [10]=>
    string(4) "0.00"
    [11]=>
    string(12) "余額支付"
    [12]=>
    string(15) "2019/0702 09:39"
    [13]=>
    string(9) "待評(píng)價(jià)"
    [14]=>
    string(19) "2019-07-02 09:39:00"
    [15]=>
    string(0) ""
  }
}