JinKong_Cms/application/back/lib/xls/Client.php

<?php


namespace app\operationcenter\lib\xls;


use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

//表格处理
class Client
{
    const Letters=['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'];

    protected $data=[];
    protected $excel=null;
    protected $sheet=null;
    protected $fileName='列表';
    protected $fields=array();

    public function __construct()
    {
        $this->excel = new Spreadsheet();
        $this->sheet = $this->excel->getActiveSheet();
        $this->sheet->setTitle('视图1');
    }
    //配置标题数据
    public function setTitle($title,$subTitle=''){
        $this->fileName=$title;
        $this->sheet->setCellValue('A1', $title);
        $this->sheet->setCellValue('A2', $subTitle);
        return $this;
    }
    //配置头部数据
    public function setHeader($data,$startLine=3){
        $lastLetter='';//最后的字母
        foreach ($data as $k=>$datum) {
            if(!isset(self::Letters[$k]))continue;//字段数量超出
            $lastLetter=self::Letters[$k];
            $this->sheet->setCellValue(self::Letters[$k].$startLine,$datum);
        }
        if($lastLetter){
            //全并列
            $this->sheet->mergeCells("A1:{$lastLetter}1");
            $this->sheet->mergeCells("A2:{$lastLetter}2");
        }
        return $this;
    }
    //设置字段
    public function setFields($data){
        $this->fields=$data;
        return $this;
    }
    //配置数据
    public function setBody($data=array(),$startLine=4){
        if(gettype($data)=='object'){
            $data=$data->toArray();
        }
        foreach ($data as $k=>$datum) {
            $line=$startLine+$k;
            switch (gettype($datum)){
                case 'object':
                case 'array':
                    if($this->fields){
                        foreach ($this->fields as $i=>$field) {
                            if(!isset(self::Letters[$i]))continue;//字段数量超出
                            if(!isset($datum[$field]))continue;//字段数量超出
                            $this->sheet->setCellValue(self::Letters[$i].$line,$datum[$field]);
                        }
                    }else{
                        $i=0;
                        foreach ($datum as $item) {
                            if(!isset(self::Letters[$i]))continue;//字段数量超出
                            $this->sheet->setCellValue(self::Letters[$i].$line,$item);
                            $i++;
                        }
                    }
                    break;
                default:
                    $this->sheet->setCellValue('A'.$line,$datum);
            }
        }
        return $this;
    }
    //配置宽度
    public function setWidth($data=null,$indexStr=''){
        if($indexStr){
            //设置单列
            $this->sheet->getColumnDimension($indexStr)->setWidth($data);
        }else{
            //设置多列
            foreach ($data as $k=>$datum) {
                if(!isset(self::Letters[$k]))continue;//字段数量超出
                $this->sheet->getColumnDimension(self::Letters[$k])->setWidth($datum);
            }
        }
        return $this;
    }
    //设置列高
    public function setHeight($height,$line){
        $this->sheet->getRowDimension($line)->setRowHeight($height);
        return $this;
    }
    //导出
    public function export(){
        $fileName=$this->fileName.date('Y.m.d.H.i').".xlsx";
        $writer = new Xlsx($this->excel);
//        die();
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
//            $excel->setActiveSheetIndex(0);

        // Redirect output to a client’s web browser (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $fileName . '"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

//            $writer = IOFactory::createWriter($excel, 'Xlsx');
        $writer->save('php://output');
    }
}