lumen 导出数据 设置样式

<?php

namespace App\Http\Controllers\Api;
use Excel;
use Illuminate\Support\Facades\DB;
use App\Exceptions\MyException;

class ExcelController extends Controller
{
    /**
     * @var array
     */
    protected $cellLetter = [
        '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'
    ];

 /**excel样式测试
     *
     *   $data = [
     * ['序号', '姓名', '性别', '年龄'],
     * ['id' => 1, 'name' => '小话', 'sex' => '女', 'age' => '11'],
     * ['id' => 2, 'name' => '小花', 'sex' => '女', 'age' => '12'],
     * ['id' => 3, 'name' => '小湖', 'sex' => '男', 'age' => '13'],
     * ['id' => 4, 'name' => '小波', 'sex' => '男', 'age' => '14']
     * ];
     * $title ='测试';
     * @param string $filename
     * @param string $title
     * @param array $data
     * @return mixed
     * @throws MyException
     */
    public function excelStyle($filename, string $title, array $data)
    {
        try {
            $filename = iconv('UTF-8', 'GBK', $filename);
            $title = [$title];
            DB::commit();
            //表格格式先按照数组格式存储，样式在后续调整
            return Excel::create($filename, function ($excel) use ($data, $title) {
                $excel->sheet('first', function ($sheet) use ($data, $title) {
                    $sheet->rows($data);
                    //后续操作写在此处
                    $column = $this->cellLetter[count($data[0]) - 1];
                    /** 此为设置整体样式 */
                    $sheet->setStyle([
                        'font' => [
                            'name' => 'Calibri',
                            'size' => 16,
                            'bold' => false
                        ]
                    ])
                        //为标题设置样式begin
                        ->prependRow($title)
                        ->row(1, function ($row) {
                            $row->setFont(array(   //设置标题的样式
                                'family' => 'Calibri',
                                'size' => '16',
                                'bold' => true,
                            ));
                        })
                        ->mergeCells('A1:' . $column . '1')
                        ->cell('A2:' . $column . '2', function ($cells) {
                            $cells->setBackground('#AAAAFF');
                        })->setHeight(1, 30);  //为标题设置样式begin
//                ->setAutoFilter('A2:' . $column . '2');  //设置自动过滤
                    /** 此为针对每行的高宽进行设置 */
                    for ($i = 2; $i <= count($data[0]) + 3; $i++) {
                        $sheet->setHeight($i, 25);
                        $sheet->setWidth($this->cellLetter[$i - 2], 10);
                        $sheet->row($i - 1, function ($row) {
                            $row->setAlignment('center');
                            $row->setValignment('center');
                        });
                    }
                });
            })->export('xls');
        } catch (\Exception $e) {
            DB::rollback();
            if ($e instanceof MyException) {
                $message = $e->getMessage() ? $e->getMessage() : "数据处理失败";
                $status_code = $e->getCode() ? $e->getCode() : 400;
                return $this->responseData('', $status_code, $message);
            }
            return $this->responseData('', 400, "数据处理失败");
        }
    }