laravel-excel合并行导入

最新推荐文章于 2024-04-07 10:39:48 发布

Mrzdc

最新推荐文章于 2024-04-07 10:39:48 发布

阅读量525

点赞数

文章标签： laravel php

本文链接：https://blog.csdn.net/qq_40838461/article/details/123556657

版权

这篇博客详细介绍了如何使用PHPExcel库在处理Excel文件导入时，针对合并单元格的数据进行拆分和恢复操作。实现了WithBatchInserts、WithMapping、WithEvents等接口，确保批量导入数据的正确性和效率。在BeforeImport事件中收集合并单元格信息，并在数据映射过程中恢复合并数据，确保数据完整无误。

摘要由CSDN通过智能技术生成

<?php

namespace App\Excels\Imports\BasicInformation;

use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithCalculatedFormulas;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithStartRow;
use Maatwebsite\Excel\Events\BeforeImport;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use Maatwebsite\Excel\Concerns\ToArray;
use Maatwebsite\Excel\Concerns\WithEvents;
//合并行的数据导入拆分详解
class StoreSiteImportExcel implements WithBatchInserts, WithMapping, WithEvents, WithStartRow, WithCalculatedFormulas, WithChunkReading
{

    public $mergeCells = [];
    public $filePath;

    public function __construct($filePath = '')
    {
        $this->filePath = $filePath;
    }

    protected $header = [
        0 => 'account',
        1 => 'primary_account',
        2 => 'platform',
        3 => 'site',
        4 => 'remark',
    ];

    protected $chunk_size = 1000;

    // 批量导入10000条
    public function batchSize(): int
    {
        return 10000;
    }

    // 以1000条数据基准切割数据
    public function chunkSize(): int
    {
        return $this->chunk_size;
    }

    public function startRow(): int
    {
        return 2;
    }

    public function map($row): array
    {
        $mapped = [];
        foreach ($this->header as $key => $value) {
            if ($value) {
                $val = preg_replace("/^[\s\v" . chr(194) . chr(160) . "]+/", "", $row[$key]);
                $mapped[$value] = preg_replace("/[\s\v" . chr(194) . chr(160) . "]+$/", "", $val);
            }
        }
        return $mapped;
    }

    //做合并行的数据恢复操作，也就是将合并行的数据恢复到未合并行数据
    public function array(array $array): array
    {
        if ($this->mergeCells) {
            $startRow = 2;
            foreach ($this->mergeCells as $key => $mergeCells) {
                if ($mergeCells && is_array($mergeCells)) {
                    foreach ($mergeCells as $mergeCell) {
                        [$begin, $end] = Coordinate::getRangeBoundaries($mergeCell);
                        //要和数组对应行列保持一直 数据对应: $array[$key][$row][$col],所以要进行行数据 - startRow ;列数据 - 1
                        $beginRow = $begin[1] - $startRow;
                        $beginCol = Coordinate::columnIndexFromString($begin[0]) - 1;//代表列，赋值后，直接用字段来替代
                        $endRow = $end[1] - $startRow;
                        $endCol = Coordinate::columnIndexFromString($end[0]) - 1;//代表列，赋值后，直接用字段来替代
//                        dd($begin,$end,$beginRow,$beginCol,$endRow,$endCol,$array);
                        $value = $array[$key][$beginRow][$this->header[$beginCol]];
                        for ($intCol = $beginCol; $intCol <= $endCol; $intCol++) {
                            for ($intRow = $beginRow; $intRow <= $endRow; $intRow++) {
                                $array[$key][$intRow][$this->header[$intCol]] = $value;
                            }
                        }
                    }
                }
            }
        }
        return $array;
    }

    public function registerEvents(): array
    {
        return [
            //这里做合并行数据收集
            BeforeImport::class => function (BeforeImport $event) {
                //因为无法设置laravel-excel中的setReadDataOnly 为false，所以在原生iof中设置setReadDataOnly 为false，获取合并数据
                $iofReader = $event->reader->getPhpSpreadsheetReader();
                $iofReader->setReadDataOnly(false);
                $iofLoad = $iofReader->load($event->getConcernable()->filePath);
                $mergeCells = [];
                foreach ($iofLoad->getAllSheets() as $key => $item) {
                    $mergeCells[$key] = $item->getMergeCells();
                }
                $event->getConcernable()->mergeCells = $mergeCells;
            },
        ];
    }
}