workHour/app/Exports/MonthlyPsMatrixExport.php

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithMultipleSheets;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

// 1. 主导出类：负责拆分年份和分发 Sheet
class MonthlyPsMatrixExport implements WithMultipleSheets
{
    protected $yearlyData;

    public function __construct(array $yearlyData)
    {
        $this->yearlyData = $yearlyData;
    }

    /**
     * 系统会自动识别这个方法，有多少个年份就自动创建多少个 Sheet
     */
    public function sheets(): array
    {
        $sheets = [];
        foreach ($this->yearlyData as $year => $employeeRows) {
            // 将每一年独立的数据和年份名称传给子 Sheet 类
            $sheets[] = new SingleYearPsMatrixSheet($year, $employeeRows);
        }
        return $sheets;
    }
}

// 2. 子 Sheet 类：独立负责单张年份表的样式和数据填充
class SingleYearPsMatrixSheet implements WithEvents, WithTitle
{
    protected $year;
    protected $employeeRows;

    public function __construct($year, array $employeeRows)
    {
        $this->year = $year;
        $this->employeeRows = $employeeRows;
    }

    /**
     * 定义当前 Sheet 页的标签名称
     */
    public function title(): string
    {
        return $this->year . '年';
    }

    /**
     * 编写当前单张表的渲染逻辑
     */
    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function (AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();

                // 🆕 细节修改 1：去掉了工号列后，总列数减少到 14 列。
                // A列(姓名) + B到M列(1-12月) = 13列，所以“年度合计”刚好是第 14 列，即 N 列。
                $lastColLetter = 'N';
                $dataCount = count($this->employeeRows);
                $lastRow = 2 + $dataCount; // 表头占2行

                // --- 1. 第一行：主标题 ---
                $sheet->mergeCells("A1:{$lastColLetter}1");
                $sheet->setCellValue('A1', "{$this->year}年 员工月份薪资明细");
                $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);

                // --- 2. 第二行：表头设置 ---
                $sheet->setCellValue('A2', '人员/月份');

                // 循环生成 1月 到 12月 的列头
                for ($m = 1; $m <= 12; $m++) {
                    // 🆕 细节修改 2：A列是姓名(1)，1月应该从 B列(2) 开始。所以是 $m + 1
                    $colLetter = Coordinate::stringFromColumnIndex($m + 1);
                    $sheet->setCellValue("{$colLetter}2", $m . '月');
                }
                // 🆕 细节修改 3：显式写入最后一列“年度合计”
                $sheet->setCellValue("{$lastColLetter}2", '年度合计');
                $sheet->getStyle("A2:{$lastColLetter}2")->getFont()->setBold(true);

                // --- 3. 第三行开始：填充矩阵数据 ---
                $currentRow = 3;
                foreach ($this->employeeRows as $empKey => $monthsData) {
                    $empInfo   = explode('_', $empKey);
                    $empName   = $empInfo[1] ?? ($empInfo[0] ?? ''); // 预防万一，如果切不出姓名则用工号兜底

                    // 🆕 细节修改 4：这一行数据从 A 列开始，第一个格子放姓名
                    $rowData = [$empName];
                    $rowYearTotal = 0;

                    for ($m = 1; $m <= 12; $m++) {
                        $salary = $monthsData[$m] ?? 0;
                        if ($salary > 0) {
                            $rowData[] = $salary;
                            $rowYearTotal += $salary;
                        } else {
                            $rowData[] = '-'; // 空白月用 '-' 占位
                        }
                    }
                    // 追加年度合计
                    $rowData[] = $rowYearTotal;

                    // 将一整行数组直接写入当前行的 A 列起始位置
                    $sheet->fromArray($rowData, null, "A{$currentRow}");
                    $currentRow++;
                }

                // --- 4. 全局对齐与样式 ---
                $fullRange = "A1:{$lastColLetter}{$lastRow}";
                $sheet->getStyle($fullRange)->getAlignment()->applyFromArray([
                    'vertical'   => Alignment::VERTICAL_CENTER,
                    'horizontal' => Alignment::HORIZONTAL_CENTER,
                    'wrapText'   => true,
                ]);

                // --- 5. 行高控制 ---
                $sheet->getRowDimension(1)->setRowHeight(35); // 主标题高度
                $sheet->getRowDimension(2)->setRowHeight(30); // 表头高度

                // --- 6. 列宽自适应调节 ---
                // 🆕 细节修改 5：range范围自动变成了 A 到 N，不会多出空白列
                foreach (range('A', $lastColLetter) as $col) {
                    $sheet->getColumnDimension($col)->setAutoSize(true);
                }

                // --- 7. 渲染全边框 ---
                $sheet->getStyle("A2:{$lastColLetter}{$lastRow}")
                    ->getBorders()
                    ->getAllBorders()
                    ->setBorderStyle(Border::BORDER_THIN);
            },
        ];
    }
}