youfeng/app/Exports/ExportOrder2.php

<?php

namespace App\Exports;

use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
use Maatwebsite\Excel\Concerns\WithEvents;     // 自动注册事件监听器
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;    // 导出 0 原样显示，不为 null
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder;

class ExportOrder2 extends DefaultValueBinder implements WithCustomValueBinder , FromCollection, WithEvents, WithStrictNullComparison,withHeadings
{
    public $mergeStart;
    public $mergeEnd;
    public $mergeTitles;

    /**
     * @return \Illuminate\Support\Collection
     */
    public function __construct($data,$type=1,$headers)
    {
        $this->data = $data;
        $this->type = $type;
        $this->headers = $headers;
    }

    public function registerEvents(): array
    {
        //区分不通状态的合同导出，格式不同
            $type = $this->type.'_set';
            return $this->$type();
    }

    //数组转集合
    public function collection()
    {
        return new Collection($this->createData());
    }

    //业务代码
    public function createData()
    {
        $name = $this->type;
        $data = $this->export();
        return $data;

    }

    public function bindValue(Cell $cell, $value)
    {
        // 检查是否需要保留前导零
//        if (is_string($value) && ctype_digit($value) && strpos($value, '0') === 0) {
//            $cell->setValueExplicit($value, DataType::TYPE_STRING);
//            return true;
//        }

        if (is_numeric($value)) {
            $cell->setValueExplicit($value, DataType::TYPE_STRING2);

            return true;
        }

        // else return default behavior
        return parent::bindValue($cell, $value);
    }

    // 自定义表头，需实现withHeadings接口
    public function headings(): array
    {
        return $this->headers;
    }

    private function export(){
        $list = [];
        foreach ($this->data as $v){
            $list[] = $v;
        }
        return $list;
    }


    private function jc_set()
    {
        return [
            AfterSheet::class => function (AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();

                // 隐藏 C 列
                $sheet->getColumnDimension('C')->setVisible(false);

                // ==========================
                // 固定合并
                // ==========================
                $sheet->mergeCells('A1:A4');  // 分类
                $sheet->mergeCells('B1:B4');  // 项目
                $sheet->mergeCells('D1:AL2'); // 各项目利润

                // 设置标题
                $sheet->setCellValue('A1', '分类');
                $sheet->setCellValue('B1', '项目');
                $sheet->setCellValue('D1', '各项目利润');

                $startIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($this->mergeStart);
                $endIndex   = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($this->mergeEnd);

                $titleIndex = 0;

                for ($col = $startIndex; $col <= $endIndex; $col += 2) {

                    // 当前两列坐标
                    $col1 = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col);
                    $col2 = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col + 1);

                    // 合并 3-3 行
                    $sheet->mergeCells("{$col1}3:{$col2}3");

                    // 设置内容（外部传入）
                    $title = $this->mergeTitles[$titleIndex] ?? '';
                    $sheet->setCellValue("{$col1}3", $title);

                    // ====================================
                    // 自动填充 D4/E4、F4/G4…… 当月 / 累计
                    // ====================================
                    $sheet->setCellValue("{$col1}4", '当月');
                    $sheet->setCellValue("{$col2}4", '累计');

                    $titleIndex++;
                }

                // ==========================
                // A 列合并（分类）
                // ==========================
                $sheet->mergeCells('A5:A7');   // 收入
                $sheet->mergeCells('A8:A26');  // 销售费用

                $sheet->setCellValue('A5', '收入');
                $sheet->setCellValue('A8', '销售费用');

                // ==========================
                // B5 - B26 内容填充
                // ==========================
                $items = [
                    "收款销售收入",
                    "成本",
                    "收款销售毛利",

                    "结算费用",
                    "管理费用",
                    "物流配送",
                    "合同费",
                    "账扣费用",
                    "促销员工资",
                    "团购及其他返点",
                    "陈列费",
                    "客情费",
                    "赠品费用",
                    "广告宣传费",
                    "快递费",
                    "销售其他费用",
                    "人员工资",
                    "员工社保、福利费",
                    "采购费用（手工）",
                    "税金（手工）开票收入*1%",
                    "财务手续费（手工）",
                    "销售支出小计",
                ];

                $row = 5;
                foreach ($items as $text) {
                    $sheet->setCellValue("B{$row}", $text);
                    $row++;
                }

                // =================================================================
                // 样式
                // =================================================================
                $sheet->getStyle('A1:'.$this->mergeEnd.'200')
                    ->getAlignment()
                    ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
                    ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)
                    ->setWrapText(true);

                foreach (range('A', 'Z') as $col) {
                    $sheet->getColumnDimension($col)->setWidth(12);
                }
                foreach (range('A', 'I') as $col) {
                    $sheet->getColumnDimension('A'.$col)->setWidth(12); // AA, AB ...
                }
                $sheet->getColumnDimension('B')->setWidth(25);

                // D5~AC26 填充数据
                $startRow = 5;
                $endRow   = 26;
                $startCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($this->mergeStart);
                $endCol   = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($this->mergeEnd);

                for ($row = $startRow; $row <= $endRow; $row++) {
                    $dataRow = $this->data[$row - $startRow] ?? [];
                    $colIndex = 0;
                    for ($col = $startCol; $col <= $endCol; $col++) {
                        $colLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col);
                        $value = $dataRow[$colIndex] ?? '';
                        $sheet->setCellValue($colLetter . $row, $value);
                        $colIndex++;
                    }
                }
            },
        ];
    }

    private function jc2_set()
    {
        return [
            AfterSheet::class => function (AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();

                // 隐藏 C 列
                $sheet->getColumnDimension('C')->setVisible(false);

                // ==========================
                // 固定合并
                // ==========================
                $sheet->mergeCells('A1:A4');  // 分类
                $sheet->mergeCells('B1:B4');  // 项目
                $sheet->mergeCells('D1:K2'); // 通路各业务员利润

                // 设置标题
                $sheet->setCellValue('A1', '分类');
                $sheet->setCellValue('B1', '项目');
                $sheet->setCellValue('D1', '通路各业务员利润');

                $startIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($this->mergeStart);
                $endIndex   = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($this->mergeEnd);

                $titleIndex = 0;

                for ($col = $startIndex; $col <= $endIndex; $col += 2) {

                    // 当前两列坐标
                    $col1 = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col);
                    $col2 = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col + 1);

                    // 合并 3-3 行
                    $sheet->mergeCells("{$col1}3:{$col2}3");

                    // 设置内容（外部传入）
                    $title = $this->mergeTitles[$titleIndex] ?? '';
                    $sheet->setCellValue("{$col1}3", $title);

                    // ====================================
                    // 自动填充 D4/E4、F4/G4…… 当月 / 累计
                    // ====================================
                    $sheet->setCellValue("{$col1}4", '当月');
                    $sheet->setCellValue("{$col2}4", '累计');

                    $titleIndex++;
                }

                // ==========================
                // A 列合并（分类）
                // ==========================
                $sheet->mergeCells('A5:A7');   // 收入
                $sheet->mergeCells('A8:A26');  // 销售支出

                $sheet->setCellValue('A5', '收入');
                $sheet->setCellValue('A8', '销售支出');

                // ==========================
                // B5 - B26 内容填充
                // ==========================
                $items = [
                    "收入",
                    "成本",
                    "毛利",
                    "结算费用（560116）",
                    "管理费用",
                    "物流配送",
                    "合同费（560115）",
                    "账扣费用（560114）",
                    "促销员工资（560113）",
                    "团购及其他返点（560105）",
                    "陈列费（560120）",
                    "客情费（560112）",
                    "赠品费用（560111）",
                    "广告宣传费（560110）",
                    "快递费（560109）",
                    "销售其他费用（560101）",
                    "员工工资支出（560115）",
                    "员工社保、福利费（560118）",
                    "采购费用（手工）（560107）",
                    "税金（手工）",
                    "财务手续费（手工）",
                    "销售支出小计",
                ];

                $row = 5;
                foreach ($items as $text) {
                    $sheet->setCellValue("B{$row}", $text);
                    $row++;
                }

                // =================================================================
                // 样式
                // =================================================================
                $sheet->getStyle('A1:'.$this->mergeEnd.'200')
                    ->getAlignment()
                    ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
                    ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)
                    ->setWrapText(true);

                foreach (range('A', 'Z') as $col) {
                    $sheet->getColumnDimension($col)->setWidth(12);
                }
                foreach (range('A', 'I') as $col) {
                    $sheet->getColumnDimension('A'.$col)->setWidth(12); // AA, AB ...
                }
                $sheet->getColumnDimension('B')->setWidth(25);

                // D5~AC26 填充数据
                $startRow = 5;
                $endRow   = 26;
                $startCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($this->mergeStart);
                $endCol   = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($this->mergeEnd);

                for ($row = $startRow; $row <= $endRow; $row++) {
                    $dataRow = $this->data[$row - $startRow] ?? [];
                    $colIndex = 0;
                    for ($col = $startCol; $col <= $endCol; $col++) {
                        $colLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col);
                        $value = $dataRow[$colIndex] ?? '';
                        $sheet->setCellValue($colLetter . $row, $value);
                        $colIndex++;
                    }
                }
            },
        ];
    }

    private function jc3_set()
    {
        return [
            AfterSheet::class => function (AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();

                // ==========================
                // 1. 第一行：大标题 (A1:Z2)
                // 根据你的要求到 X2，但后面合计到了 Z，这里建议合并到 Z 保持美观
                // ==========================
                $sheet->mergeCells('A1:Z2');
                $sheet->setCellValue('A1', '各业务员利润');

                // ==========================
                // 2. 第三、四行：固定表头与人员分组
                // ==========================
                $sheet->mergeCells('A3:A4');
                $sheet->setCellValue('A3', '分类');
                $sheet->mergeCells('B3:B4');
                $sheet->setCellValue('B3', '项目');

                // --- C-F: 张春勇、霍尚琳 (4列) ---
                $sheet->mergeCells('C3:F3');
                $sheet->setCellValue('C3', '张春勇、霍尚琳（社区加通路）');
                $sheet->setCellValue('C4', '张春勇当月（社区)');
                $sheet->setCellValue('D4', '张春勇、霍尚琳当月（通路)');
                $sheet->setCellValue('E4', '合计');
                $sheet->setCellValue('F4', '年累计（7月-明年的6月）');

                // --- G-J: 金小勇 (4列) ---
                $sheet->mergeCells('G3:J3');
                $sheet->setCellValue('G3', '金小勇（通路，不含加盟）');
                $sheet->setCellValue('G4', '当月通路');
                $sheet->setCellValue('H4', '当月联华加盟');
                $sheet->setCellValue('I4', '月合计');
                $sheet->setCellValue('J4', '年累计（7月-明年的6月）');

                // --- K-N: 沈强 (4列) ---
                $sheet->mergeCells('K3:N3');
                $sheet->setCellValue('K3', '沈强（通路，不含智鲸）');
                $sheet->setCellValue('K4', '当月通路');
                $sheet->setCellValue('L4', '当月智鲸');
                $sheet->setCellValue('M4', '月合计');
                $sheet->setCellValue('N4', '年累计（7月-明年的6月）');

                // --- O-P: 王利英等 (2列) ---
                $sheet->mergeCells('O3:P3');
                $sheet->setCellValue('O3', '王利英、翁春燕、裴志华（通路加大卖场）');
                $sheet->setCellValue('O4', '当月');
                $sheet->setCellValue('P4', '年累计（7月-明年的6月）');

                // --- Q-T: 叶南汝 (4列) ---
                $sheet->mergeCells('Q3:T3');
                $sheet->setCellValue('Q3', '叶南汝（通路加大卖场）');
                $sheet->setCellValue('Q4', '当月通路');
                $sheet->setCellValue('R4', '当月大卖场');
                $sheet->setCellValue('S4', '当月合计');
                $sheet->setCellValue('T4', '年累计（7月-明年的6月）');

                // --- U-V: 行政 (2列) ---
                $sheet->mergeCells('U3:V3');
                $sheet->setCellValue('U3', '行政');
                $sheet->setCellValue('U4', '当月');
                $sheet->setCellValue('V4', '年累计（7月-明年的6月）');

                // --- W-X: 鲍总 (2列) ---
                $sheet->mergeCells('W3:X3');
                $sheet->setCellValue('W3', '鲍总');
                $sheet->setCellValue('W4', '当月');
                $sheet->setCellValue('X4', '年累计（7月-明年的6月）');

                // --- Y-Z: 合计 (2列) ---
                $sheet->mergeCells('Y3:Z3');
                $sheet->setCellValue('Y3', '合计');
                $sheet->setCellValue('Y4', '当月');
                $sheet->setCellValue('Z4', '年累计（7月-明年的6月）');

                // ==========================
                // 3. A列与B列内容 (5-32行)
                // ==========================
                $sheet->mergeCells('A5:A7');
                $sheet->setCellValue('A5', '收入');

                $sheet->mergeCells('A8:A32');
                $sheet->setCellValue('A8', '销售支出');

                $items = [
                    5  => "收入",
                    6  => "成本",
                    7  => "毛利",
                    8  => "结算费用（560116）",
                    9  => "管理费（收入*5%）",
                    10 => "物流配送（2.3%）",
                    11 => "合同费（560115）",
                    12 => "账扣费用（560114）",
                    13 => "促销员工资（560113）",
                    14 => "团购及其他返点（560105）",
                    15 => "陈列费（560120）",
                    16 => "客情费（560112）",
                    17 => "赠品费用（560111）",
                    18 => "广告宣传费（560110）",
                    19 => "快递费（560109）",
                    20 => "销售其他费用（560101）",
                    21 => "员工工资支出（560119）",
                    22 => "员工社保、福利费（560118）",
                    23 => "退货亏损（不良仓）",
                    24 => "采购费用（手工）（按人事计提）",
                    25 => "税金（1%）",
                    26 => "财务手续费（1%）",
                    27 => "其他出库单",
                    28 => "其他(1)为后续准备",
                    29 => "其他(2)为后续准备",
                    30 => "其他(3)为后续准备",
                    31 => "其他(4)为后续准备",
                    32 => "销售支出小计",
                ];

                foreach ($items as $rowNum => $text) {
                    $sheet->setCellValue("B{$rowNum}", $text);
                }

                // ==========================
                // 4. 样式设置
                // ==========================
                $lastCol = 'Z';
                $sheet->getStyle("A1:{$lastCol}32")
                    ->getAlignment()
                    ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
                    ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)
                    ->setWrapText(true);

                // 设置列宽
                $sheet->getColumnDimension('A')->setWidth(10);
                $sheet->getColumnDimension('B')->setWidth(35);
                foreach (range('C', $lastCol) as $col) {
                    $sheet->getColumnDimension($col)->setWidth(16);
                }

                // ==========================
                // 5. 数据填充逻辑 (从C5开始)
                // ==========================
                // 假设 $this->data 是一个二维数组，每一行代表从 C 列到 Z 列的值
                $dataRowIndex = 0;
                for ($row = 5; $row <= 32; $row++) {
                    $rowValues = $this->data[$dataRowIndex] ?? [];
                    $colIndex = 0;
                    // 从 C (Index 3) 到 Z (Index 26)
                    for ($col = 3; $col <= 26; $col++) {
                        $colLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col);
                        $val = $rowValues[$colIndex] ?? '';
                        $sheet->setCellValue($colLetter . $row, $val);
                        $colIndex++;
                    }
                    $dataRowIndex++;
                }
            },
        ];
    }
}