strprfcrm/app/Services/ImportService.php

<?php

namespace App\Services;

use App\Models\Product;
use Exception;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

class ImportService
{
    const HEADERS_TO_FIELDS = [
        "Фото" => '',
        "Наименование по ТЗ"            => 'name_tz',
        "Тип по ТЗ"                     => 'type_tz',
        "№ по номенкл."                 => 'nomenclature_number',
        "Габаритные размеры"            => 'sizes',
        "Производитель"                 => 'manufacturer',
        "ед. изм."                      => 'unit',
        "Тип оборудования"              => 'type',
        "Статус цены"                   => 'price_status',
        "Цена поставки"                 => 'product_price',
        "Цена установки"                => 'installation_price',
        "Цена обслуживания"             => 'service_price',
        "Итого цена"                    => 'total_price',
        "Наименование производителя"    => 'manufacturer_name',
        "Артикул образца"               => 'article',
        "Примечание"                    => 'note',
    ];


    /**
     * @param string $path
     * @param int $year
     * @return void
     * @throws Exception
     */
    public function handle(string $path, int $year): void
    {
        $path = Storage::disk('upload')->path($path);

        $reader = new Xlsx();
        $spreadsheet = $reader->load($path);
        $sheet = $spreadsheet->getActiveSheet();

        $rowIterator = $sheet->getRowIterator();

        $headers = $this->rowToArray($rowIterator->current());



        if($this->checkHeaders($headers)) {
            foreach ($rowIterator as $row){
                $record = $this->rowToArray($row);
                if($record[0] === 'Фото') continue;

                Product::query()
                    ->updateOrCreate(['year' => $year, 'nomenclature_number' => $record[3]],
                    [
                        'name_tz'           => $record[1],
                        'type_tz'           => $record[2],
                        'sizes'             => $record[4],
                        'manufacturer'      => $record[5],
                        'unit'              => $record[6],
                        'type'              => $record[7],
                        'price_status'      => $record[8],
                        'product_price'     => $record[9],
                        'installation_price'=> $record[10],
                        'service_price'     => $record[11],
                        'total_price'       => $record[12],
                        'manufacturer_name' => $record[13],
                        'article'           => $record[14],
                        'note'              => $record[15],
                    ]);
            }
        } else {
            throw new Exception('Ошибка заголовков файла!');
        }
    }

    protected function rowToArray($row): array
    {
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells, even if a cell value is not set.
        $row_content = [];
        foreach ($cellIterator as $cell) {
            $row_content[] = $cell->getValue();
        }

        return $row_content;
    }

    /**
     * @param array $headers
     * @return bool
     */
    protected function checkHeaders(array $headers): bool
    {
        return $this->getHeaders() == $headers;
    }


    /**
     * @return array
     */
    protected function getHeaders(): array
    {
        return array_keys(self::HEADERS_TO_FIELDS);
    }
}