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:AA2'); // 各项目利润

                // 设置标题
                $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++;
                    }
                }
            },
        ];
    }
}