workHour/app/Exports/ResearchExpenseSheetExport.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;

class ResearchExpenseSheetExport implements FromCollection, WithEvents, WithStyles, WithCustomStartCell, WithTitle
{
    protected $sheetTitle; // Sheet 标签名，如 "2025-RD01"
    protected $data;
    protected $projectInfo;
    protected $year;

    public function __construct(string $sheetTitle, array $data, array $projectInfo = [])
    {
        $this->sheetTitle = $sheetTitle;
        $this->data = $data;
        $this->projectInfo = $projectInfo;

        // 自动从 Sheet 标题中提取前4位作为大标题年份
        $this->year = substr($sheetTitle, 0, 4);
    }

    public function title(): string
    {
        return $this->sheetTitle;
    }

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

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

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

                // --- 1. 设置列宽 (精简适中版) ---
                $sheet->getColumnDimension('A')->setWidth(12);  // 日期
                $sheet->getColumnDimension('B')->setWidth(6);   // 种类
                $sheet->getColumnDimension('C')->setWidth(6);   // 号数
                $sheet->getColumnDimension('D')->setWidth(30);  // 摘要
                $sheet->getColumnDimension('E')->setWidth(14);  // 会计金额
                $sheet->getColumnDimension('F')->setWidth(14);  // 税法金额
                $sheet->getColumnDimension('G')->setWidth(13);  // 人员人工
                $sheet->getColumnDimension('H')->setWidth(13);  // 直接投入
                $sheet->getColumnDimension('I')->setWidth(11);  // 折旧
                $sheet->getColumnDimension('J')->setWidth(11);  // 无形资产
                $sheet->getColumnDimension('K')->setWidth(11);  // 新产品
                $sheet->getColumnDimension('L')->setWidth(11);  // 其他
                $sheet->getColumnDimension('M')->setWidth(18);  // 委托境内
                $sheet->getColumnDimension('N')->setWidth(18);  // 委托境外

                // --- 2. 设置行高 ---
                $sheet->getRowDimension('2')->setRowHeight(45); // 大标题行
                $sheet->getRowDimension('3')->setRowHeight(40); // 项目信息行
                $sheet->getRowDimension('4')->setRowHeight(22); // 表头1
                $sheet->getRowDimension('5')->setRowHeight(25); // 表头2
                $sheet->getRowDimension('6')->setRowHeight(60); // 表头底行 (容纳长文字)

                // --- 3. 第二行：大标题 (居中/加粗) ---
                $sheet->mergeCells("A2:N2");
                $sheet->setCellValue('A2', "{$this->year}年研发支出辅助账");
                $sheet->getStyle('A2')->applyFromArray([
                    'font' => ['size' => 16],
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                    ],
                ]);

                // --- 4. 第三行：项目信息 (精准合并/无边框) ---
                // 项目编号：标签 ABC，值 DE
                $sheet->mergeCells("A3:C3");
                $sheet->setCellValue('A3', '项目编号：');
                $sheet->mergeCells("D3:E3");
                $sheet->setCellValue('D3', $this->projectInfo['code'] ?? '');

                // 项目名称：标签 F，值 G (开启换行)
                $sheet->setCellValue('F3', '项目名称：');
                $sheet->setCellValue('G3', $this->projectInfo['name'] ?? '');
                $sheet->getStyle('G3')->getAlignment()->setWrapText(true);

                $sheet->setCellValue('H3', '完成情况：');
                $sheet->setCellValue('I3', '已完成');
                $sheet->setCellValue('J3', '支出类型：');
                $sheet->setCellValue('K3', '费用化');
                $sheet->setCellValue('M3', '金额单位：');
                $sheet->setCellValue('N3', '元');

                // 第三行对齐
                $sheet->getStyle('A3:N3')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

                // --- 5. 复杂表头绘制 (第 4-6 行) ---
                // A-D 凭证信息
                $sheet->mergeCells("A4:D5");
                $sheet->setCellValue('A4', '凭证信息');
                $sheet->setCellValue('A6', '日期');
                $sheet->setCellValue('B6', '种类');
                $sheet->setCellValue('C6', '号数');
                $sheet->setCellValue('D6', '摘要');

                // E-F 金额列 (垂直合并)
                $sheet->mergeCells("E4:E6");
                $sheet->setCellValue('E4', "会计凭证记载\n金额");
                $sheet->mergeCells("F4:F6");
                $sheet->setCellValue('F4', "税法规定的归\n集金额");

                // G-N 费用明细总标题
                $sheet->mergeCells("G4:N4");
                $sheet->setCellValue('G4', '费用明细（税法规定）');

                // 各科目垂直合并 (5-6行)
                $subItems = [
                    'G' => '人员人工费用', 'H' => '直接投入费用', 'I' => '折旧费用',
                    'J' => '无形资产摊销', 'K' => '新产品设计费等', 'L' => '其他相关费用'
                ];
                foreach ($subItems as $col => $text) {
                    $sheet->mergeCells("{$col}5:{$col}6");
                    $sheet->setCellValue("{$col}5", $text);
                }

                // 委托研发费用
                $sheet->mergeCells("M5:N5");
                $sheet->setCellValue('M5', '委托研发费用');
                $sheet->setCellValue('M6', "委托境内机构或个人进行研\n发活动所发生的费用");
                $sheet->setCellValue('N6', "委托境外机构进行研发活动\n所发生的费用");

                // --- 6. 开启换行 ---
                $sheet->getStyle('A4:N6')->getAlignment()->setWrapText(true);
            },
        ];
    }

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

        // 全局基础样式：从第 4 行开始加边框 (跳过第 3 行项目信息)
        $sheet->getStyle("A4:N{$highestRow}")->applyFromArray([
            'borders' => [
                'allBorders' => ['borderStyle' => Border::BORDER_THIN],
            ],
            'alignment' => [
                'vertical' => Alignment::VERTICAL_CENTER,
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
            'font' => ['name' => '宋体', 'size' => 10],
        ]);

        return [];
    }
}