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;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

class ResearchExpenseSheetExport implements FromCollection, WithEvents, WithStyles, WithCustomStartCell, WithTitle
{
    protected $sheetTitle;
    protected $data;
    protected $projectInfo;
    protected $year;
    protected $dynamicHeaders;

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

    public function title(): string { return $this->sheetTitle; }
    public function startCell(): string { return 'A7'; }
    public function collection() { return collect($this->data); }

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

                // --- 1. 计算列信息 ---
                $baseColumnCount = 8; // A-H (日期 到 折旧)
                $dynamicCount = count($this->dynamicHeaders);
                $totalColumnCount = $baseColumnCount + $dynamicCount + 2; // 加上委托研发2列
                $highestColumn = Coordinate::stringFromColumnIndex($totalColumnCount);
                $highestRow = $sheet->getHighestRow(); // 获取最后一行（合计行）

                // --- 2. 样式初始化：防止灰色块和默认填充 ---
                $sheet->getStyle("A1:{$highestColumn}{$highestRow}")->getFill()->setFillType(Fill::FILL_NONE);

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

                // --- 4. 项目信息行 (A3) ---
                $sheet->mergeCells("A3:C3");
                $sheet->setCellValue('A3', '项目编号：' . ($this->projectInfo['code'] ?? ''));
                $sheet->mergeCells("D3:G3");
                $sheet->setCellValue('D3', '项目名称：' . ($this->projectInfo['name'] ?? ''));

                // 单位放在最右边一列
                $sheet->setCellValue($highestColumn . '3', '单位：元');
                $sheet->getStyle($highestColumn . '3')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

                // --- 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-末尾: 费用明细总标题
                $sheet->mergeCells("G4:{$highestColumn}4");
                $sheet->setCellValue('G4', '费用明细（税法规定）');

                // 固定明细项：人员人工(G), 折旧(H)
                $sheet->mergeCells("G5:G6"); $sheet->setCellValue("G5", '人员人工费用');
                $sheet->mergeCells("H5:H6"); $sheet->setCellValue("H5", '折旧费用');

                // 动态明细项 (从 I/第9列开始)
                $currentColIdx = 9;
                foreach ($this->dynamicHeaders as $headerText) {
                    $colLetter = Coordinate::stringFromColumnIndex($currentColIdx);
                    $sheet->mergeCells("{$colLetter}5:{$colLetter}6");
                    $sheet->setCellValue("{$colLetter}5", $headerText);
                    $currentColIdx++;
                }

                // 委托研发 (最后两列)
                $mCol = Coordinate::stringFromColumnIndex($currentColIdx);
                $nCol = Coordinate::stringFromColumnIndex($currentColIdx + 1);
                $sheet->mergeCells("{$mCol}5:{$nCol}5");
                $sheet->setCellValue("{$mCol}5", '委托研发费用');
                $sheet->setCellValue("{$mCol}6", "委托境内研发");
                $sheet->setCellValue("{$nCol}6", "委托境外研发");

                // --- 6. 强制刷新表头样式 (边框和居中) ---
                $headerRange = "A4:{$highestColumn}6";
                $sheet->getStyle($headerRange)->applyFromArray([
                    'borders' => [
                        'allBorders' => ['borderStyle' => Border::BORDER_THIN],
                    ],
                    'alignment' => [
                        'wrapText' => true,
                        'vertical' => Alignment::VERTICAL_CENTER,
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                    ],
                ]);

                // --- 7. 合计行特殊处理 (合并 A-D 列) ---
                // 注意：由于数据从 A7 开始，且我们在 Controller 塞入了合计行，
                // 所以 $highestRow 对应的就是那一行。
                $sheet->mergeCells("A{$highestRow}:D{$highestRow}");
                // 确保合计单元格样式加粗且有背景色
                $footerRange = "A{$highestRow}:{$highestColumn}{$highestRow}";
                $sheet->getStyle($footerRange)->applyFromArray([
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => ['argb' => 'FFF2F2F2'], // 灰色背景区分
                    ],
                    'borders' => [
                        'allBorders' => ['borderStyle' => Border::BORDER_THIN],
                    ],
                ]);
                $sheet->getStyle("A{$highestRow}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

                // --- 8. 添加会计主管 (合计行下方空一行) ---
                $supervisorRow = $highestRow + 1;
                $sheet->setCellValue("A{$supervisorRow}", '会计主管：');
                $sheet->getStyle("A{$supervisorRow}")->applyFromArray([
                    'font' => ['size' => 10],
                    'alignment' => ['horizontal' => Alignment::HORIZONTAL_LEFT]
                ]);

                // 设置行高
                $sheet->getRowDimension('2')->setRowHeight(35);
                $sheet->getRowDimension('6')->setRowHeight(45);
            },
        ];
    }

    public function styles(Worksheet $sheet)
    {
        $highestRow = $sheet->getHighestRow();
        // 样式中也需要重新动态获取列，否则 styles 的作用范围会跟不上
        $totalCol = 8 + count($this->dynamicHeaders) + 2;
        $highestCol = Coordinate::stringFromColumnIndex($totalCol);

        // 数据区域边框 (从第7行开始)
        $sheet->getStyle("A7:{$highestCol}{$highestRow}")->applyFromArray([
            'borders' => [
                'allBorders' => ['borderStyle' => Border::BORDER_THIN],
            ],
            'alignment' => [
                'vertical' => Alignment::VERTICAL_CENTER,
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
            'font' => ['name' => '宋体', 'size' => 10],
        ]);

        return [];
    }
}