workHour/app/Exports/ManActivityTimeCardSheetExport.php

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\WithCustomStartCell;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

class ManActivityTimeCardSheetExport implements FromCollection, WithEvents, WithStyles, WithCustomStartCell, WithTitle
{
    protected $projectName;
    protected $data;
    protected $year;

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

    public function title(): string
    {
        // Sheet名不能超过31个字符
        return mb_substr($this->projectName, 0, 30);
    }

    public function startCell(): string
    {
        return 'A6'; // 数据从 A6 开始写入
    }

    public function collection()
    {
        return collect($this->data);
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();
                $lastCol = 'R';

                // --- 1. 设置列宽 ---
                $widths = [
                    'A' => 8, 'B' => 12, 'C' => 10, 'D' => 10, 'E' => 10, 'F' => 10,
                    'G' => 12, 'H' => 10, 'I' => 10, 'J' => 12, 'K' => 10, 'L' => 10,
                    'M' => 12, 'N' => 10, 'O' => 10, 'P' => 12, 'Q' => 10, 'R' => 10
                ];
                foreach ($widths as $col => $w) {
                    $sheet->getColumnDimension($col)->setWidth($w);
                }

                // --- 2. 第一行：主标题 (宋体 18 加粗) ---
                $sheet->mergeCells("A1:{$lastCol}1");
                $sheet->setCellValue('A1', "{$this->year}年研发人员研发活动工时占比计算及研发人员费用调整表");
                $sheet->getStyle('A1')->applyFromArray([
                    'font' => ['name' => '宋体', 'size' => 18, 'bold' => true],
                    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
                ]);

                // --- 3. 第二行：项目名称 (宋体 9 加粗) ---
                $sheet->mergeCells("A2:{$lastCol}2");
                $sheet->setCellValue('A2', "项目名称：{$this->projectName}");
                $sheet->getStyle('A2')->applyFromArray([
                    'font' => ['name' => '宋体', 'size' => 9, 'bold' => true],
                    'alignment' => ['horizontal' => Alignment::HORIZONTAL_LEFT],
                ]);

                // --- 4. 第三至五行：表头逻辑 (宋体 11 加粗 自动换行) ---
                foreach (['A', 'B', 'C'] as $col) { $sheet->mergeCells("{$col}3:{$col}5"); }
                $sheet->setCellValue('A3', "月份"); $sheet->setCellValue('B3', "人员类别"); $sheet->setCellValue('C3', "姓名");

                $sheet->mergeCells('D3:F3'); $sheet->setCellValue('D3', '工时占比计算');
                $sheet->mergeCells('D4:D5'); $sheet->setCellValue('D4', '应出勤工时');
                $sheet->mergeCells('E4:E5'); $sheet->setCellValue('E4', '研发出勤工时');
                $sheet->mergeCells('F4:F5'); $sheet->setCellValue('F4', '研发工时占比');

                $sheet->mergeCells('G3:L3'); $sheet->setCellValue('G3', '账面归集金额');
                $sheet->mergeCells('G4:I4'); $sheet->setCellValue('G4', '归集总额');
                $sheet->setCellValue('G5', '工资总额'); $sheet->setCellValue('H5', '社保'); $sheet->setCellValue('I5', '公积金');

                $sheet->mergeCells('J4:L4'); $sheet->setCellValue('J4', '其中属于研发活动期间金额');
                $sheet->setCellValue('J5', '工资总额'); $sheet->setCellValue('K5', '社保'); $sheet->setCellValue('L5', '公积金');

                $sheet->mergeCells('M3:O4'); $sheet->setCellValue('M3', '研发活动期间的确定金额');
                $sheet->setCellValue('M5', '工资总额'); $sheet->setCellValue('N5', '社保'); $sheet->setCellValue('O5', '公积金');

                $sheet->mergeCells('P3:R4'); $sheet->setCellValue('P3', '加计调整金额');
                $sheet->setCellValue('P5', '工资总额'); $sheet->setCellValue('Q5', '社保'); $sheet->setCellValue('R5', '公积金');

                // 应用三到五行样式
                $sheet->getStyle("A3:{$lastCol}5")->applyFromArray([
                    'font' => ['name' => '宋体', 'size' => 11, 'bold' => true],
                    'alignment' => [
                        'wrapText' => true,
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER
                    ],
                ]);

                // --- 5. 染色逻辑 ---
                $highestRow = $sheet->getHighestRow();
                for ($row = 6; $row <= $highestRow; $row++) {
                    $cellA = $sheet->getCell("A{$row}")->getValue();
                    if ($cellA === '小计:') {
                        $sheet->getStyle("A{$row}:{$lastCol}{$row}")->getFill()
                            ->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('F4B084');
                    } elseif ($cellA === '合计') {
                        $sheet->getStyle("A{$row}:{$lastCol}{$row}")->getFill()
                            ->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('FFFF00');
                    }
                }
            },
        ];
    }

    public function styles(Worksheet $sheet)
    {
        $highestRow = $sheet->getHighestRow();
        $lastCol = 'R';

        return [
            // 全表通用对齐和边框
            "A1:{$lastCol}{$highestRow}" => [
                'borders' => [
                    'allBorders' => ['borderStyle' => Border::BORDER_THIN],
                ],
                'alignment' => [
                    'vertical' => Alignment::VERTICAL_CENTER,
                ],
            ],
            // 数据行：宋体 10，居中，不加粗
            "A6:{$lastCol}{$highestRow}" => [
                'font' => [
                    'name' => '宋体',
                    'size' => 10,
                    'bold' => false
                ],
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_CENTER,
                ],
            ]
        ];
    }
}