esun3d/app/code/Esun/Catalog/Model/PrintingParameter/ExcelParser.php

<?php
declare(strict_types=1);

namespace Esun\Catalog\Model\PrintingParameter;

use Magento\Framework\Exception\LocalizedException;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class ExcelParser
{
    private const PRODUCT_ROW = 2;
    private const DATA_START_ROW = 3;
    private const PARAMETER_COLUMN = 3;
    private const PRODUCT_START_COLUMN = 4;

    /**
     * @return array<string, array<string, array<string, string>>>
     * @throws LocalizedException
     */
    public function parse(string $filePath): array
    {
        if (!is_file($filePath)) {
            throw new LocalizedException(__('The uploaded printing parameter Excel file was not found.'));
        }

        try {
            $spreadsheet = IOFactory::load($filePath);
        } catch (\Throwable $exception) {
            throw new LocalizedException(__('Unable to read the printing parameter Excel file: %1', $exception->getMessage()));
        }

        $sheet = $spreadsheet->getActiveSheet();
        $highestColumnIndex = Coordinate::columnIndexFromString($sheet->getHighestColumn());
        $highestRow = $sheet->getHighestRow();
        $products = $this->getProducts($sheet, $highestColumnIndex);
        if (!$products) {
            throw new LocalizedException(__('No product names were found from D2 onward in the printing parameter Excel file.'));
        }

        $groupByRow = $this->getGroupRowMap($sheet);
        $data = [];
        $currentGroup = '';
        for ($row = self::DATA_START_ROW; $row <= $highestRow; $row++) {
            $group = $groupByRow[$row] ?? $this->readCell($sheet, 1, $row);
            if ($group !== '') {
                $currentGroup = $group;
            }
            if ($currentGroup === '') {
                continue;
            }

            $parameter = $this->readCell($sheet, self::PARAMETER_COLUMN, $row);
            if ($parameter === '') {
                continue;
            }

            foreach ($products as $column => $productName) {
                $value = $this->readCell($sheet, $column, $row);
                if ($value === '') {
                    continue;
                }

                $data[$productName][$currentGroup] ??= [];
                $data[$productName][$currentGroup][$parameter] = $value;
            }
        }

        $spreadsheet->disconnectWorksheets();
        return $data;
    }

    /**
     * @return array<int, string>
     */
    private function getProducts(Worksheet $sheet, int $highestColumnIndex): array
    {
        $products = [];
        for ($column = self::PRODUCT_START_COLUMN; $column <= $highestColumnIndex; $column++) {
            $productName = $this->readCell($sheet, $column, self::PRODUCT_ROW);
            if ($productName !== '') {
                $products[$column] = $productName;
            }
        }

        return $products;
    }

    /**
     * @return array<int, string>
     */
    private function getGroupRowMap(Worksheet $sheet): array
    {
        $groups = [];
        foreach ($sheet->getMergeCells() as $range) {
            [$start, $end] = Coordinate::rangeBoundaries($range);
            [$startColumn, $startRow] = $start;
            [$endColumn, $endRow] = $end;
            if ($startColumn !== 1 || $endColumn !== 1 || $endRow < self::DATA_START_ROW) {
                continue;
            }

            $group = $this->readCell($sheet, 1, $startRow);
            if ($group === '') {
                continue;
            }

            for ($row = max($startRow, self::DATA_START_ROW); $row <= $endRow; $row++) {
                $groups[$row] = $group;
            }
        }

        return $groups;
    }

    private function readCell(Worksheet $sheet, int $column, int $row): string
    {
        $cell = $sheet->getCell(Coordinate::stringFromColumnIndex($column) . $row);
        $value = $cell->getFormattedValue();
        if ($value === null || $value === '') {
            $value = $cell->getCalculatedValue();
        }

        return $this->normalize((string)$value);
    }

    private function normalize(string $value): string
    {
        $value = str_replace(["\r\n", "\r"], "\n", $value);
        $lines = array_map(function (string $line): string {
            $line = trim(preg_replace('/[ \t]+/', ' ', $line) ?: '');
            return $this->cleanNumber($line);
        }, explode("\n", $value));
        $lines = array_values(array_filter($lines, static fn (string $line): bool => $line !== ''));

        return implode("\n", $lines);
    }

    private function cleanNumber(string $value): string
    {
        if (!is_numeric($value) || !str_contains($value, '.')) {
            return $value;
        }

        [, $decimal] = explode('.', $value, 2);
        if (strlen($decimal) <= 8) {
            return $value;
        }

        return rtrim(rtrim(sprintf('%.8F', (float)$value), '0'), '.');
    }
}