1.安装

composer require phpoffice/phpspreadsheet

2.一个写好的Excel类

``

<?php

namespace app\common\library;

use app\common\exception\BusiException;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

/**
 * 导出excel class
 * @author daichongweb
 */
class Export
{
    // 表格坐标
    private $cellIndex = ['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'];

    // 默认配置
    public $config = [
        'bold' => true, // 加粗
        'size' => 12, // 文字大小
        'column' => 4, // 设置列数
        'title' => '默认导出', // 表格标题
        'name' => '特抱抱', // 文件名
        'type' => 'Xls', // 导出格式
    ];

    // 默认表头
    public $tableHeader = [
        'php',
        'vue',
        'java',
        'go',
    ];

    // 默认数据
    public $tableDefaultData = [
        [
            '天下第一',
            'Vue牛逼',
            'java牛逼',
            'go牛逼',
        ],
    ];

    /**
     * @param array $tableHeader 表头
     * @param array $tableDefaultData 需要导出数据
     */
    public function __construct($tableHeader, $tableDefaultData)
    {
        if (empty($tableHeader)) {
            throw new BusiException('export error', '请设置表头');
        }

        if (empty($tableDefaultData)) {
            throw new BusiException('export error', '请设置导出数据');
        }

        $this->tableHeader = $tableHeader;
        $this->config['column'] = count($tableHeader);
        $this->tableDefaultData = $tableDefaultData;
    }

    /**
     * 创建表格 createTable
     */
    public function createTable()
    {
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();

        //居中
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];

        // 设置基本属性
        $worksheet->setTitle($this->config['title']);
        $worksheet->getStyle($this->getColumn())->applyFromArray($styleArray)
            ->getFont()
            ->setBold($this->config['bold'])
            ->setName('Verdana')
            ->setSize($this->config['size']);

        foreach ($this->tableHeader as $index => $name) {
            $worksheet->setCellValue($this->cellIndex[$index] . '1', $name);
        }

        $baseRow = 2;
        foreach ($this->tableDefaultData as $index => $data) {
            $i = $index + $baseRow;
            for ($k = 0; $k <= $this->config['column'] - 1; $k++) {
                $item = $data[$k];
                $worksheet->setCellValue($this->cellIndex[$k] . $i, ' ' . $item);
                // 中文设置表格宽度
                if (preg_match("/[\x7f-\xff]/", $data[$k])) {
                    $worksheet->getColumnDimension($this->cellIndex[$k])->setWidth(strlen($item));
                } else {
                    // 非中文自动设置宽度
                    $worksheet->getColumnDimension($this->cellIndex[$k])->setAutoSize(true);
                }
            }
        }
        $worksheet->calculateColumnWidths();
        self::downloadExcel($spreadsheet, $this->config['name'], 'Xls');
    }

    /**
     * 文件下载 downloadExcel
     *
     * @param data $spreadsheet
     * @param string $filename
     * @param string $format
     * @return void
     */
    private function downloadExcel($spreadsheet, $filename, $format)
    {
        // $format只能为 Xlsx 或 Xls
        if ($format == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        } elseif ($format == 'Xls') {
            header('Content-Type: application/vnd.ms-excel');
        }

        header("Content-Disposition: attachment;filename="
            . urlencode($filename) . '.' . strtolower($format)); //. date('Y-m-d')
        header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($spreadsheet, $format);
        $objWriter->save('php://output');
        exit;
    }

    private function getColumn($row = 1)
    {
        $index = $this->cellIndex[$this->config['column']];
        return 'A' . $row . ':' . $index . $row;
    }

    private function autoFitColumnWidthToContent($sheet, $fromCol, $toCol)
    {
        if (empty($toCol)) { //not defined the last column, set it the max one
            $toCol = $sheet->getColumnDimension($sheet->getHighestColumn())->getColumnIndex();
        }
        for ($i = $fromCol; $i <= $toCol; $i++) {
            $sheet->getColumnDimension($i)->setAutoSize(true);
        }
        $sheet->calculateColumnWidths();
    }
}

3.使用

``

use Excel;

class Index extends BaseController
{
    public function index(){
        $title = [
            'aaa',
            'bbb',
            'ccc',
            'ddd'
        ];

        $data = [
            [1,2,3,4],
            [5,6,7,8],
            [9,10,11,12]
        ];

        $excel = new Excel($title, $data);
        $excel->createTable();
    }