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 = 6; // A-F (固定前 6 列：从 日期 到 税法规定的归集金额)
                $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-末尾: 费用明细大总标题 (第7列到最后一列)
                $sheet->mergeCells("G4:{$highestColumn}4");
                $sheet->setCellValue('G4', '费用明细（税法规定）');

                // ================== 【核心调整：动态平铺所有科目】 ==================
                // 从第 7 列 (G列) 开始动态输出所有排好序的科目（包含人员人工、折旧以及其他动态科目）
                $currentColIdx = 7;
                foreach ($this->dynamicHeaders as $headerText) {
                    $colLetter = Coordinate::stringFromColumnIndex($currentColIdx);
                    // 纵向合并 5 和 6 行
                    $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 列) ---
                $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();
        // 这里的总列数计算也同步保持一致逻辑
        $totalCol = 6 + 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 [];
    }
}