workHour/app/Exports/ProjectDepreciationSheetExport.php

<?php

namespace App\Exports;

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

class ProjectDepreciationSheetExport implements WithEvents, WithTitle
{
    protected $groupKey;
    protected $projectName;
    protected $monthsData;

    public function __construct($groupKey, $projectName, $monthsData)
    {
        $this->groupKey = $groupKey;
        $this->projectName = $projectName;
        $this->monthsData = $monthsData;
    }

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

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

                // --- 关键：预先设置列宽 ---
                $sheet->getColumnDimension('A')->setWidth(6);
                $sheet->getColumnDimension('B')->setWidth(25);
                $sheet->getColumnDimension('J')->setWidth(15);
                $sheet->getColumnDimension('L')->setWidth(12); // 给 L 列足够的空间

                foreach ($this->monthsData as $month => $items) {
                    // 记录本月表格的起始行
                    $tableStartRow = $currentRow;

                    // 1. 标题 (A-J)
                    $sheet->mergeCells("A{$currentRow}:J{$currentRow}");
                    $displayYear = date('Y', strtotime($month));
                    $sheet->setCellValue("A{$currentRow}", "{$displayYear}年度研发项目折旧费用调整表");
                    $sheet->getStyle("A{$currentRow}")->getFont()->setBold(true)->setSize(16);
                    $sheet->getStyle("A{$currentRow}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                    $currentRow++;

                    // 2. 项目名称与单位 (A-J)
                    $sheet->setCellValue("A{$currentRow}", "研发项目名称:{$this->projectName}");
                    $sheet->mergeCells("H{$currentRow}:J{$currentRow}");
                    $sheet->setCellValue("H{$currentRow}", "单位：元以下角分");
                    $sheet->getStyle("H{$currentRow}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
                    $currentRow++;

                    // 3. 表头 (A-J)
                    $headerRow1 = $currentRow;
                    $sheet->mergeCells("A{$currentRow}:A" . ($currentRow + 1)); $sheet->setCellValue("A{$currentRow}", "序号");
                    $sheet->mergeCells("B{$currentRow}:B" . ($currentRow + 1)); $sheet->setCellValue("B{$currentRow}", "设备名称");
                    $sheet->mergeCells("C{$currentRow}:E{$currentRow}"); $sheet->setCellValue("C{$currentRow}", "设备工时");
                    $sheet->mergeCells("F{$currentRow}:I{$currentRow}"); $sheet->setCellValue("F{$currentRow}", "设备折旧");
                    $sheet->mergeCells("J{$currentRow}:J" . ($currentRow + 1)); $sheet->setCellValue("J{$currentRow}", "加计调整\n金额");
                    $currentRow++;

                    $sheet->setCellValue("C{$currentRow}", "设备总工时");
                    $sheet->setCellValue("D{$currentRow}", "其中本项目\n工时");
                    $sheet->setCellValue("E{$currentRow}", "研发活动工\n时占比(%)");
                    $sheet->setCellValue("F{$currentRow}", "原值");
                    $sheet->setCellValue("G{$currentRow}", "折旧额");
                    $sheet->setCellValue("H{$currentRow}", "确定的本\n项目折旧额");
                    $sheet->setCellValue("I{$currentRow}", "备注说明");
                    $currentRow++;

                    // 4. 数据行
                    $dataRowsCount = count($items);
                    foreach ($items as $index => $item) {
                        $sheet->setCellValue("A{$currentRow}", $index + 1);
                        $sheet->setCellValue("B{$currentRow}", $item['device_name'] ?? '');
                        $sheet->setCellValue("C{$currentRow}", $item['total_hours'] ?? 0);
                        $sheet->setCellValue("D{$currentRow}", $item['project_hours'] ?? 0);
                        $sheet->setCellValue("E{$currentRow}", ($item['ratio'] ?? 0) . '%');
                        $sheet->setCellValue("F{$currentRow}", $item['original_value'] ?? 0);
                        $sheet->setCellValue("G{$currentRow}", $item['allocated_depreciatio'] ?? 0);
                        $sheet->setCellValue("H{$currentRow}", $item['allocated_depreciatio'] ?? 0);
                        $sheet->setCellValue("I{$currentRow}", "");
                        $sheet->setCellValue("J{$currentRow}", $item['adjust_amount'] ?? 0);

                        // --- 核心修正：确保月份在 L 列显示 ---
                        // 我们把月份放在数据行的第一行对应的 L 列
                        if ($index === 0) {
                            $sheet->setCellValue("L{$currentRow}", $month . "月");
                            // 样式：宋体、加粗、靠左对齐
                            $sheet->getStyle("L{$currentRow}")->getFont()->setName('SimSun')->setBold(true)->setSize(12);
                            $sheet->getStyle("L{$currentRow}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
                        }
                        $currentRow++;
                    }

                    // 5. 合计行
                    $sheet->setCellValue("A{$currentRow}", "合计");
                    $sheet->mergeCells("A{$currentRow}:B{$currentRow}");

                    $total = 0;
                    $totalProjectHours = 0;
                    $totalOriginalValue = 0;
                    $totalDepreciation = 0;
                    $totalConfirmedDepreciation = 0;
                    $totalAdjustAmount = 0;

                    foreach ($items as $item) {
                        $total += ($item['total_hours'] ?? 0);
                        $totalProjectHours += ($item['project_hours'] ?? 0);
                        $totalOriginalValue += ($item['original_value'] ?? 0);
                        $totalDepreciation += ($item['depreciation'] ?? 0);
                        $totalConfirmedDepreciation += ($item['confirmed_depreciation'] ?? 0);
                        $totalAdjustAmount += ($item['adjust_amount'] ?? 0);
                    }

                    $sheet->setCellValue("C{$currentRow}", $total);
                    $sheet->setCellValue("D{$currentRow}", $totalProjectHours);
                    $sheet->setCellValue("F{$currentRow}", $totalOriginalValue);
                    $sheet->setCellValue("G{$currentRow}", $totalDepreciation);
                    $sheet->setCellValue("H{$currentRow}", $totalConfirmedDepreciation);
                    $sheet->setCellValue("J{$currentRow}", $totalAdjustAmount);

                    // 设置合计行样式：加粗
                    $sheet->getStyle("A{$currentRow}:J{$currentRow}")->getFont()->setBold(true);

                    $currentRow++;

                    // 6. 备注
                    $sheet->mergeCells("A{$currentRow}:J{$currentRow}");
                    $sheet->setCellValue("A{$currentRow}", "备注:按研发项目的研发活动工作使用情况记录等相关证据资料...");
                    $sheet->getStyle("A{$currentRow}")->getFont()->setSize(9);
                    $currentRow += 2; // 间隔

                    // --- 7. 样式应用：仅针对 A-J 列加边框 ---
                    $tableRange = "A{$tableStartRow}:J" . ($currentRow - 2);
                    $sheet->getStyle($tableRange)->getFont()->setName('SimSun');
                    $sheet->getStyle("A" . ($tableStartRow + 2) . ":J" . ($currentRow - 2))->applyFromArray([
                        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
                    ]);

                    // 居中对齐
                    $sheet->getStyle("A{$tableStartRow}:J" . ($currentRow - 2))->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
                }
            },
        ];
    }
}