接上一篇导出excel,本文涉及接口类和抽象类,看似没关系,实际就是没什么关系,直接使用即可,下面是代码:

需要掌握的技能:使用composer引入phpspreadsheet代码

实现一个Base基本抽象类

<?php
declare (strict_types = 1);
namespace app\index\stract;

/**
 * 实现一个Base基本抽象类
 */
abstract class QueryBase
{
    public $public_url = 'http://www.xxxxxxxxx.cn';

}
继承公共抽象类实现导出抽象类

<?php
declare (strict_types = 1);
namespace app\index\stract;

/**
 * 导入composer引入的phpspreadsheet类
 */
use PhpOffice\PhpSpreadsheet\Spreadsheet;

/**
 * 继承公共抽象类实现导出抽象类
 */
abstract class Export extends QueryBase
{
    public $spreadsheet;

    abstract function loadExportClass(): object;

}
实现一个excel导出接口类,写出继承类需要实现的几个方法

<?php
declare (strict_types = 1);
namespace app\index\face;

/**
 * 实现一个excel导出接口类,写出继承类需要实现的几个方法
 */
interface Export
{
    function exportSheet(): bool;

}
实现最终的导出类

<?php
declare (strict_types = 1);
namespace app\index\controller;
use app\index\face\Export as ExportInterface;
use app\index\model\Business;
use app\index\model\Merchant;
use app\index\model\Store;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use app\index\stract\Export as ExportStract;

/**
 * 最终实现的导出类
 */
class Export extends ExportStract implements ExportInterface
{
    //定义文件名称
    public $file_name = '';

    //构造方法
    public function __construct()
    {
        self::loadExportClass();
    }

    //实现抽象类的方法
    public function loadExportClass(): object
    {
        $this->spreadsheet = new Spreadsheet();
        return $this->spreadsheet;
    }

    //实际导出方法
    public function exportSheet(): bool
    {
        $Business = Business::all()->toArray();
        $Merchant = Merchant::all()->toArray();
        $Store = Store::all()->toArray();
        $data_array = [
            ['title'=>'表名1', 'rows'=>$Business],
            ['title'=>'表名2', 'rows'=>$Merchant],
            ['title'=>'表名3', 'rows'=>$Store],
        ];
        foreach ($data_array as $key => $data) {
            $this->handleSheet($this->spreadsheet, $key, $data);
        }
        $this->file_name = '导出信息表';
        return true;
    }

    public  function handleSheet(Spreadsheet $spreadsheet, int $n, array $data): bool
    {
        $spreadsheet->createSheet();//创建sheet
        $sheet = $spreadsheet->getActiveSheet($n);
        $objActSheet = $spreadsheet->setActiveSheetIndex($n);//设置当前的活动sheet
        $sheet = $spreadsheet->getActiveSheet($n)->setTitle($data['title']);//设置sheet的名称
        $column = [];
        switch($n){
            case 0:
                $column = ['id', 'name', 'age'];
                $sheet->setCellValue('A1', 'ID');
                $sheet->setCellValue('B1', '姓名');
                $sheet->setCellValue('C1', '年龄');
                $sheet->getColumnDimension('A')->setWidth(15);
                $sheet->getColumnDimension('B')->setWidth(15);
                $sheet->getColumnDimension('C')->setWidth(50);
                break;
            case 1:
               $column = ['id', 'name', 'age', 'sex', 'address'];
               $sheet->setCellValue('A1', 'ID');
               $sheet->setCellValue('B1', '姓名');
               $sheet->setCellValue('C1', '年龄');
               $sheet->setCellValue('D1', '性别');
               $sheet->setCellValue('E1', '地址');
               $sheet->getColumnDimension('A')->setWidth(15);
               $sheet->getColumnDimension('B')->setWidth(15);
               $sheet->getColumnDimension('C')->setWidth(50);
               $sheet->getColumnDimension('D')->setWidth(20);
               $sheet->getColumnDimension('E')->setWidth(20);
                break;
            case 2:
                $column = ['id', 'name', 'age', 'business_name'];
                $sheet->setCellValue('A1', 'ID');
                $sheet->setCellValue('B1', '姓名');
                $sheet->setCellValue('C1', '年龄');
                $sheet->setCellValue('D1', '公司名称');
                $sheet->getColumnDimension('A')->setWidth(15);
                $sheet->getColumnDimension('B')->setWidth(15);
                $sheet->getColumnDimension('C')->setWidth(15);
                $sheet->getColumnDimension('D')->setWidth(50);
                break;
            default:
                break;
        }
        $count = count($column);
        foreach ($data['rows'] as $key => $item)
        {
            //循环设置单元格:
            //$key+$infoStart,因为第一行是表头,所以写到表格时   从第数据行开始写
            for ($i = 65; $i < $count + 65; $i++)
            {
                //数字转字母从65开始:
                $sheet->setCellValue(strtoupper(chr($i)) . ($key+2), $item[$column[$i - 65]]);
            }
        }
        return true;
    }

    //析构方法,所有方法结束之后都执行
    public function __destruct()
    {
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$this->file_name.'.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($this->spreadsheet);
        $writer->save('php://output');
        //删除清空
        $this->spreadsheet->disconnectWorksheets();
        unset($this->spreadsheet);
        exit;
    }

}

最后直接运行exportSheet方法即可导出数据