hqyt/app/Exports/SingleSheetExport.php

<?php

namespace App\Exports;

use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithCustomStartCell;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;

class SingleSheetExport extends DefaultValueBinder implements
    FromCollection,
    WithTitle,
    WithCustomValueBinder,
    WithStyles,
    WithEvents,
    WithCustomStartCell
{
    protected $data;
    protected $sheetName;
    protected $type; // 1: 研发人员, 2: 研发设备
    protected $columns;
    protected $timeRow;

    public function __construct(array $data, string $sheetName, int $type = 1, array $columns, array $timeRow)
    {
        $this->data = $data;
        $this->sheetName = $sheetName;
        $this->type = $type;
        $this->columns = $columns;
        $this->timeRow = $timeRow;
    }

    /**
     * 设置数据开始写入的起始单元格
     * 因为 A1-A4 被标题占用，数据从 A5 开始
     */
    public function startCell(): string
    {
        return 'A5';
    }

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

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

    public function bindValue(Cell $cell, $value)
    {
        if (is_numeric($value) && strlen($value) > 10) {
            $cell->setValueExplicit($value, DataType::TYPE_STRING);
            return true;
        }
        return parent::bindValue($cell, $value);
    }

    /**
     * 处理合并单元格和固定文字
     */
    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();
                $sheet->freezePane('A6');
                $highestColumn = $sheet->getHighestColumn();
                $highestRow = $sheet->getHighestRow();

                // 1. 设置下拉筛选 (Auto Filter)
                // 范围从 A5 开始到最后一列的最后一行
//                $sheet->setAutoFilter('A5:' . $highestColumn . $highestRow);

                // --- 原有的配置定义 ---
                $config = [
                    1 => [
                        'title' => '研发人员工时统计单',
                        'dept'  => '部门：研发部',
                        'desc'  => '现就本月研发人员的工时统计数据如下：',
                    ],
                    2 => [
                        'title' => '仪器设备运行工时统计单',
                        'dept'  => '部门：研发部、生产部',
                        'desc'  => '现就本月研发设备的工时统计数据如下：',
                    ]
                ];
                $c = $config[$this->type] ?? $config[1];

                // --- 第一行：主标题 ---
                $sheet->mergeCells('A1:W1');
                $sheet->setCellValue('A1', $c['title']);
                $sheet->getStyle('A1')->getFont()->setSize(12)->setBold(false)->setName('宋体');
                $sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

                // --- 第二行：部门 ---
                $sheet->setCellValue('A2', $c['dept']);
                $sheet->getStyle('A2')->getFont()->setName('宋体');

                // --- 第三行：描述语 + Sheet名 ---
                if($this->type == 1){
                    $sheet->mergeCells('A3:J3');
                }else{
                    $sheet->mergeCells('A3:N3');
                }

                $sheet->setCellValue('A3', $c['desc']);
                $sheet->getStyle('A3')->getFont()->setName('宋体');

//                $sheet->mergeCells('W3:Y3');
//                $sheet_name = str_replace('合计','',$this->sheetName);
//                $sheet->setCellValue('W3', $sheet_name);
//                $sheet->getStyle('W3')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
//                $sheet->getStyle('W3')->getFont()->setName('宋体');

                $sheet_name = str_replace('合计', '', $this->sheetName);
                $highestColumn = $sheet->getHighestColumn(); // 获取当前数据最大的列标，例如 'Z'

                // 直接在第 3 行的最右侧单元格写入
                $sheet->setCellValue($highestColumn . '3', $sheet_name);

                // 设置样式：右对齐，确保文字靠边
                $sheet->getStyle($highestColumn . '3')->applyFromArray([
                    'font' => ['name' => '宋体'],
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_RIGHT,
                    ],
                ]);

                foreach ($this->columns as $index => $colKey) {
                    if (isset($this->timeRow[$colKey]) && $this->timeRow[$colKey] !== '') {
                        // 根据索引获取列字母 (0->A, 1->B...)
                        $colLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($index + 1);

                        // 写入 A4, B4...
                        $sheet->setCellValue($colLetter . '4', $this->timeRow[$colKey]);

                        // 样式：小字、居中、无边框（或根据你喜好）
                        $sheet->getStyle($colLetter . '4')->applyFromArray([
                            'font' => ['size' => 9, 'name' => 'Times New Roman'],
                            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
                        ]);
                    }
                }

                // 也可以给第 4 行设置一个较窄的行高，让它看起来更像表头附件
                $sheet->getRowDimension(4)->setRowHeight(15);
            },
        ];
    }

    public function styles(Worksheet $sheet)
    {
        $sheet->getSheetView()->setZoomScale(63);

        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();

        // 样式应用范围从 A5（表头）开始到最后
        $dataRange = 'A5:' . $highestColumn . $highestRow;

        // 全表基础样式（针对数据区）
        $sheet->getStyle($dataRange)->applyFromArray([
            'font' => ['name' => '宋体', 'size' => 12],
            'alignment' => [
                'vertical' => Alignment::VERTICAL_CENTER,
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                ],
            ],
        ]);

        // 表头（第五行）特殊处理
        $sheet->getStyle('A5:' . $highestColumn . '5')->applyFromArray([
            'borders' => [
                'bottom' => ['borderStyle' => Border::BORDER_MEDIUM],
            ],
        ]);

        // 3. 根据 type 类型定制列宽
        if ($this->type === 1) {
            // --- 研发人员格式设置 ---
            $this->setEmployeeWidths($sheet);
        } else {
            // --- 研发设备格式设置 ---
            $this->setDeviceWidths($sheet);
        }

        return [];
    }

    /**
     * 研发人员列宽定制
     */
    private function setEmployeeWidths(Worksheet $sheet)
    {
        foreach ($sheet->getColumnIterator() as $column) {
            $col = $column->getColumnIndex();
            $sheet->getColumnDimension($col)->setAutoSize(false);

            switch ($col) {
                case 'A': $sheet->getColumnDimension($col)->setWidth(6); break;  // 序号
                case 'B': $sheet->getColumnDimension($col)->setWidth(12); break; // 姓名
                case 'E': $sheet->getColumnDimension($col)->setWidth(25); break; // RD编号/项目名（通常较长）
                case 'F':
                case 'G':
                case 'H': $sheet->getColumnDimension($col)->setWidth(10); break; // 各种比例 %
                default:  $sheet->getColumnDimension($col)->setWidth(12); break; // 动态RD列
            }
        }
    }

    /**
     * 研发设备列宽定制
     */
    private function setDeviceWidths(Worksheet $sheet)
    {
        foreach ($sheet->getColumnIterator() as $column) {
            $col = $column->getColumnIndex();
            $sheet->getColumnDimension($col)->setAutoSize(false);

            switch ($col) {
                case 'A': $sheet->getColumnDimension($col)->setWidth(6); break;  // 序号
                case 'B': $sheet->getColumnDimension($col)->setWidth(20); break; // 设备名称（通常比人名长）
                case 'C': $sheet->getColumnDimension($col)->setWidth(15); break; // 型号/编号
                default:  $sheet->getColumnDimension($col)->setWidth(12); break;
            }
        }
    }
}