Laravel Excel 导出多维表头通用方法_laravel5.5 excel导出-CSDN博客

本文链接：https://blog.csdn.net/chu_yubo/article/details/125015745

在我们的实际开发中可能会出现导出excel中表头是合并单元格的数据，一般形如：

姓名	年龄	收入
		基本工资	绩效工资
xxx	xx	xxxx	xxxx

这时我们可以根据表头的内容逐一进行区分合并单元格，但是这样做没有通用性，我们可以写一个通用的处理表头的方法，思路就是表头数据以二位数组表示如本例子


        [
            ['姓名', '年龄', '收入', '收入'],
            ['姓名', '年龄', '基本工资', '绩效工资']
        ]

将需要合并的数据内容置为一样，判断相邻数据是否一致进行合并，姓名为数组的array[0][0],array[1][0]，此时合并列；年龄array[0][1],array[1][1], 合并列；收入array[0][2], array[0][3]，此时合并行，具体实现如下：

namespace App\Exports;

use Illuminate\Support\Str;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataType as ExcelDataType;
use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class TitleMergeExport extends BasicExport
{
    /**
     * 头信息 注意 这里要二维数组 要求等长
     * eg:
     * [
     * ['column1','column2'],
     * ['column1','column2']
     * ]
     * @var array
     */
    public $headings = [
    ];

    public $afterSheetFunction = null;

    public function headings(): array
    {
        $heading = $this->headings;
        foreach ($heading as $key => $keyValue) {
            $baseRaw = count($keyValue ?? []);
            for ($itemKey = 0; $itemKey < $baseRaw; $itemKey++) {
                if ($keyValue[$itemKey] !== null) {
                    $this->getMergeRawLen($key, $itemKey, $heading);
                }
            }
        }
        return $heading;
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function (AfterSheet $event) {
                /** @var self $basicExport */
                $basicExport = $event->getConcernable();
                $rawCount = $basicExport->collection()->count();
                if ($basicExport->headings) {
                    $rawCount += count($basicExport->headings);
                    $heading = $basicExport->headings;
                    $baseLen = count($heading);
                    for ($key = 0; $key < $baseLen; $key++) {
                        $baseRaw = count($heading[$key] ?? []);
                        for ($itemKey = 0; $itemKey < $baseRaw; $itemKey++) {
                            if ($heading[$key][$itemKey] !== null) {
                                [$raw, $len] = $this->getMergeRawLen($key, $itemKey, $heading);

                                if ($raw !== $itemKey && $len !== $key) {
                                    $event->sheet->getDelegate()->mergeCells(Coordinate::stringFromColumnIndex($itemKey + 1) . ($key + 1) . ':' . Coordinate::stringFromColumnIndex($raw + 1) . ($len + 1));
                                } else {
                                    if ($raw !== $itemKey) {
                                        $event->sheet->getDelegate()->mergeCells(Coordinate::stringFromColumnIndex($itemKey + 1) . ($key + 1) . ':' . Coordinate::stringFromColumnIndex($raw + 1) . ($key + 1));
                                    }
                                    if ($len !== $key) {
                                        $event->sheet->getDelegate()->mergeCells(Coordinate::stringFromColumnIndex($itemKey + 1) . ($key + 1) . ':' . Coordinate::stringFromColumnIndex($itemKey + 1) . ($len + 1));
                                    }
                                }

                            }
                        }
                    }
                }

                $number = count($basicExport->collection()->first() ?? []);
                if ($basicExport->columnWidths()) {
                    foreach ($this->columnWidths() as $k => $v) {
                        $event->sheet->getDelegate()->getColumnDimension($k)->setWidth($v);
                    }
                }

                $event->sheet->getDelegate()->getStyle('A1:' . Coordinate::stringFromColumnIndex($number) . $rawCount)
                    ->getAlignment()->setHorizontal('center');
                $event->sheet->getDelegate()->getStyle('A1:' . Coordinate::stringFromColumnIndex($number) . $rawCount)
                    ->getAlignment()->setVertical('center');
                $basicExport::afterSheet($event);
            },
        ];
    }

    /**
     * @param AfterSheet $event
     */
    public static function afterSheet(AfterSheet $event): void
    {
        /** @var self $basicExport */
        $basicExport = $event->getConcernable();
        if ($basicExport->afterSheetFunction) {
            //如果有自定义函数闭包，进行闭包函数
            ($basicExport->afterSheetFunction)($event);
        }
    }

    public function columnFormats(): array
    {
        $heads = array_values($this->collection()->first() ?? []);
        $formats = [];
        foreach ($heads as $headKey => $headValue) {
            $formats[Coordinate::stringFromColumnIndex($headKey)] = NumberFormat::FORMAT_TEXT;
        }
        return $formats;
    }

    /**
     * @param $key
     * @param $itemKey
     * @param $heading
     * @return array
     */
    public function getMergeRawLen($key, $itemKey, &$heading): array
    {
        $len = count($heading);
        $raw = count($heading[$key]);
        $returnRaw = $itemKey;
        $returnLen = $key;
        for ($i = $itemKey; $i < $raw; $i++) {
            if ($heading[$key][$itemKey] !== $heading[$key][$i]) {
                $returnRaw = $i - 1;
                break;
            }

            if ($i !== $itemKey) {
                $heading[$key][$i] = null;
            }

            if ($i === $raw - 1) {
                $returnRaw = $i;
                break;
            }
        }

        for ($i = $key; $i < $len; $i++) {
            if ($heading[$key][$itemKey] !== $heading[$i][$itemKey]) {
                $returnLen = $i - 1;
                break;
            }

            if ($i !== $key) {
                $heading[$i][$itemKey] = null;
            }

            if ($i === $len - 1) {
                $returnLen = $i;
                break;
            }
        }

        return [$returnRaw, $returnLen];
    }
}

其中 BasicExport：

<?php


namespace App\Exports;


use Illuminate\Support\Collection;
use Illuminate\Support\Str;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\RegistersEventListeners;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\WithCustomStartCell;
use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataType as ExcelDataType;
use PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

/**
 * Class BasicExport
 * @package App\Exports
 */
class BasicExport extends DefaultValueBinder implements FromCollection, WithCustomStartCell, WithTitle, WithHeadings, WithEvents, WithColumnFormatting, WithCustomValueBinder
{
    use RegistersEventListeners;

    /**
     * excel 填充内容
     * @var  Collection
     */
    public $collection;

    /**
     * 初始位置
     * 暂时写死初始位置A1,如果更改导出会有问题
     * @var string
     */
    public $startCell = 'A1';

    public $firstMerge = false;

    /**
     * 常规宽度
     * 未定义列宽时，使用常规宽度
     * @var int
     */
    public $normalColumnWidth = 20;

    /**
     * 实际宽度
     * 根据需求，定义实际宽度
     * @var array
     */
    public $columnWidth = [];

    /**
     * Sheet 名称
     * @var string
     */
    public $sheetTitle = 'WorkSheet';

    /**
     * 头信息 注意 这里要二维数组
     * eg [
     *  ['标题'],
     * ['column1','column2']
     * ]
     * oreg:
     * [
     * ['column1','column2'],
     * ['column1','column2']
     * ]
     * @var array
     */
    public $headings = [
    ];

    /**
     * $headings 是否有标题
     * 如果 $headings 有标题 就设为 true 否则为false
     * @var bool
     */
    public $isTitle = false;

    public $afterSheetFunction = null;


    /**
     * 设置初始位置
     *
     * @param string $start
     */
    public function setStartCell(string $start): void
    {
        $this->startCell = $start;
    }

    /**
     * @return Collection
     */
    public function collection(): Collection
    {
        return $this->collection ?? collect([]);
    }

    /**
     * @return string
     */
    public function startCell(): string
    {
        return $this->startCell;
    }


    public function columnWidths(): array
    {
        $columnWidths = [];
        $heads = $this->collection()->first();
        if ($heads) {
            if (is_array($heads)) {
                $heads = array_values($heads);
            }
            foreach ($heads as $headKey => $headValue) {
                if (isset($this->columnWidth[$headKey])) {
                    $columnWidths[Coordinate::stringFromColumnIndex($headKey + 1)] = $this->columnWidth[$headKey];
                } else {
                    $columnWidths[Coordinate::stringFromColumnIndex($headKey + 1)] = $this->normalColumnWidth;
                }
            }
        }
        return $columnWidths;
    }

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

    public function headings(): array
    {
        return $this->headings;
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function (AfterSheet $event) {
                /** @var self $basicExport */
                $basicExport = $event->getConcernable();
                $rawCount = $basicExport->collection()->count();
                if ($basicExport->headings) {
                    if (isset($basicExport->headings[0]) && is_array($basicExport->headings[0])) {
                        $rawCount += count($basicExport->headings);
                    } else {
                        ++$rawCount;
                    }
                }
                $number = count($basicExport->collection()->first() ?? []);
                if ($basicExport->columnWidths()) {
                    foreach ($this->columnWidths() as $k => $v) {
                        $event->sheet->getDelegate()->getColumnDimension($k)->setWidth($v);
                    }
                }
                if ($number > 0 && $basicExport->isTitle) {
                    $event->sheet->getDelegate()->mergeCells('A1:' . Coordinate::stringFromColumnIndex($number) . '1');
                }

                $event->sheet->getDelegate()->getStyle('A1:' . Coordinate::stringFromColumnIndex($number) . $rawCount)
                    ->getAlignment()->setHorizontal('center');
                $event->sheet->getDelegate()->getStyle('A1:' . Coordinate::stringFromColumnIndex($number) . $rawCount)
                    ->getAlignment()->setVertical('center');

                $basicExport::afterSheet($event);
            },
        ];
    }

    /**
     * @param AfterSheet $event
     */
    public static function afterSheet(AfterSheet $event): void
    {
        /** @var self $basicExport */
        $basicExport = $event->getConcernable();
        if ($basicExport->afterSheetFunction) {
            //如果有自定义函数闭包，进行闭包函数
            ($basicExport->afterSheetFunction)($event);
        }
    }


    public function columnFormats(): array
    {
        $heads = array_values($this->collection()->first() ?? []);
        $heads = array_values($heads);
        $formats = [];
        foreach ($heads as $headKey => $headValue) {
            $formats[Coordinate::stringFromColumnIndex($headKey + 1)] = NumberFormat::FORMAT_TEXT;
        }
        return $formats;
    }

    /**
     * @param \PhpOffice\PhpSpreadsheet\Cell\Cell $cell
     * @param mixed $value
     * @return bool
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public function bindValue($cell, $value)
    {
        //处理超长字符串
        if (is_numeric($value) && Str::length($value) >= 12) {
            $cell->setValueExplicit($value, ExcelDataType::TYPE_STRING);
            return true;
        }

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

此时，我们在controller或者其他地方进行调用方法：

        $export = new TitleMergeExport();
        $export->headings = [
            ['姓名', '年龄', '收入', '收入'],
            ['姓名', '年龄', '基本工资', '绩效工资']
        ];
        $export->collection = collect([]);
        header("access-control-expose-headers: Authorization, Content-Disposition");
        return Excel::download($export, ('导出数据' . '.xlsx'));