ניתוח אוטומטי של רשימות מחירים: Excel, CSV, XML

ספק שלח קובץ Excel עם תאים ממוזגים, קובץ CSV ב-Windows-1251, ו-XML עם תגיות לא סטנדרטיות. עיבוד ידני פירושו שעות של שגרה ושגיאות סנכרון. לקוח בתחום האלקטרוניקה קיבל מחירונים מ-12 ספקים בפורמטים שונים: חלק שלחו Excel עם טבלאות ציר, אחרים CSV ב-KOI8-R, ואחרים XML עם namespaces. טעינה ידנית ארכה יומיים בשבוע, ושגיאות הובילו לרמות מלאי שגויות. כתבנו מפענח אוניברסלי עם קונפיגורציה לכל ספק — כעת העדכונים אורכים 5 דקות ביום, וחוסכים למעלה מ-40 שעות בחודש וכ-$2,500 בחודש. במשך 5 שנים פיתחנו מפענחים כאלה ל-50+ פרויקטים, והנה איך זה עובד.

אתגרים נפוצים בפענוח מחירונים

• Excel: הנתונים לא מתחילים מהשורה הראשונה, כותרות בתאים ממוזגים, מספרים מאוחסנים כטקסט, תאריכים בפורמט Excel.
• CSV: קידוד Windows-1251, תו מפריד נקודה-פסיק, מחירים עם רווחים או נקודות.
• XML: סכמות שונות — מוצרים יכולים להיות attributes, אלמנטים מקוננים, או namespaces.
• SKUs לא עקביים: ספק אחד כותב מק"ט כ-'12345', אחר כ-'AB-123/45'. המפענח מנרמל הכל לפורמט אחיד.
• שאילתת N+1: טעינת מוצרים אחד-אחד הייתה מפילה את מסד הנתונים. אנו משתמשים בקבוצות של 500 רשומות ותורים — זה מהיר פי 10 מייבוא שורה-אחר-שורה.

השוואת פורמטים לפי פרמטרים מרכזיים:

טיפול בתאים ממוזגים ב-Excel

אנו משתמשים בספריית PhpSpreadsheet (https://github.com/PHPOffice/PhpSpreadsheet). הטכניקה המרכזית היא קריאת הערך מהתא השמאלי-עליון של הטווח הממוזג. בקונפיגורציה אנו מציינים את שורת הכותרות, שורת תחילת הנתונים, ו-aliases לעמודות. המפענח ממפה אוטומטית כותרות לשדות שלך, תוך התעלמות מתאים ממוזגים ריקים. זה חוסך עד 90% מזמן הכנת הנתונים.

חשיבות הזיהוי האוטומטי לקידוד CSV

ספקים משתמשים לעתים קרובות ב-Windows-1251, וחלק ב-KOI8-R. ללא זיהוי אוטומטי, הנתונים מגיעים כג'יבריש. המפענח שלנו, באמצעות mb_detect_encoding, ממיר את התוכן ל-UTF-8 לפני הפענוח. הוא גם מנחש אוטומטית את התו המפריד (פסיק, נקודה-פסיק, טאב) על ידי ניתוח השורה הראשונה. כתוצאה מכך, שגיאות קידוד מצטמצמות ל-0.1% מהמקרים.

איך אנו מתכננים מפענח אוניברסלי

אנו משתמשים בספריית PhpSpreadsheet עבור Excel, בפונקציות PHP מובנות עבור CSV, וב-SimpleXML עבור XML. תבנית ה-Repository הקלאסית מבודדת את לוגיקת הפענוח ממקור הנתונים. להלן המחלקות המרכזיות.

מפענח Excel (PhpSpreadsheet) – מימוש PHP למפענח Excel

// app/Services/PriceList/ExcelParser.php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

class ExcelParser
{
    private array $columnMap = [];

    public function parse(string $filePath, array $config): array
    {
        $spreadsheet = IOFactory::load($filePath);
        $sheet = $spreadsheet->getSheetByName($config['sheet_name'] ?? null) ?? $spreadsheet->getActiveSheet();
        $headerRow = $config['header_row'] ?? 1;
        $dataStartRow = $config['data_start_row'] ?? 2;

        // Определяем маппинг колонок по заголовкам
        $this->detectColumns($sheet, $headerRow, $config['column_aliases']);

        $products = [];
        $highestRow = $sheet->getHighestRow();

        for ($row = $dataStartRow; $row <= $highestRow; $row++) {
            $sku = $this->getCellValue($sheet, $this->columnMap['sku'], $row);
            if (empty($sku)) continue;

            $products[] = [
                'sku' => trim($sku),
                'name' => $this->getCellValue($sheet, $this->columnMap['name'] ?? null, $row),
                'price' => $this->parsePrice($this->getCellValue($sheet, $this->columnMap['price'], $row)),
                'in_stock' => $this->parseStock($this->getCellValue($sheet, $this->columnMap['stock'] ?? null, $row)),
                'category' => $this->getCellValue($sheet, $this->columnMap['category'] ?? null, $row),
            ];
        }

        return $products;
    }

    private function detectColumns(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet, int $headerRow, array $aliases): void
    {
        $highestCol = $sheet->getHighestColumn();
        $highestColIndex = Coordinate::columnIndexFromString($highestCol);

        for ($col = 1; $col <= $highestColIndex; $col++) {
            $header = strtolower(trim(
                $sheet->getCellByColumnAndRow($col, $headerRow)->getValue() ?? ''
            ));

            foreach ($aliases as $fieldName => $possibleHeaders) {
                foreach ($possibleHeaders as $alias) {
                    if (str_contains($header, strtolower($alias))) {
                        $this->columnMap[$fieldName] = $col;
                        break 2;
                    }
                }
            }
        }
    }

    private function parsePrice(mixed $value): float
    {
        if (is_numeric($value)) return (float) $value;
        return (float) preg_replace('/[^\d.]/', '', str_replace(',', '.', (string) $value));
    }

    private function parseStock(mixed $value): bool
    {
        if (is_null($value)) return true;
        $s = strtolower(trim((string) $value));
        return !in_array($s, ['0', 'нет', 'no', 'false', 'отсутствует', '']);
    }
}

קונפיגורציה לספק ספציפי:

// config/price_list_parsers.php
return [
    'supplier_abc' => [
        'format' => 'excel',
        'sheet_name' => 'Прайс',
        'header_row' => 2,
        'data_start_row' => 3,
        'column_aliases' => [
            'sku' => ['Артикул', 'SKU', 'Код товара'],
            'name' => ['Наименование', 'Название', 'Товар'],
            'price' => ['Цена', 'Цена, руб.', 'Стоимость'],
            'stock' => ['Остаток', 'Наличие', 'Кол-во'],
            'category' => ['Категория', 'Группа', 'Раздел'],
        ],
    ],
];

מפענח CSV עם זיהוי אוטומטי של קידוד

// app/Services/PriceList/CsvParser.php
class CsvParser {
    public function parse(string $filePath, array $config = []): array {
        $content = file_get_contents($filePath);
        // Автоопределение кодировки
        $encoding = mb_detect_encoding($content, ['UTF-8', 'Windows-1251', 'KOI8-R'], true);
        if ($encoding && $encoding !== 'UTF-8') {
            $content = mb_convert_encoding($content, 'UTF-8', $encoding);
        }
        // Автоопределение разделителя
        $delimiter = $config['delimiter'] ?? $this->detectDelimiter($content);
        $lines = str_getcsv($content, "\n");
        $headers = str_getcsv(array_shift($lines), $delimiter);
        $headers = array_map('trim', $headers);
        $products = [];
        foreach ($lines as $line) {
            if (empty(trim($line))) continue;
            $row = str_getcsv($line, $delimiter);
            if (count($row) !== count($headers)) continue;
            $data = array_combine($headers, $row);
            $products[] = $this->normalizeRow($data, $config);
        }
        return $products;
    }

    private function detectDelimiter(string $content): string {
        $firstLine = strtok($content, "\n");
        $counts = [
            ',' => substr_count($firstLine, ','),
            ';' => substr_count($firstLine, ';'),
            "\t" => substr_count($firstLine, "\t"),
        ];
        return array_key_first(array_filter($counts, fn($c) => $c === max($counts)));
    }

    private function normalizeRow(array $row, array $config): array {
        $map = $config['field_map'] ?? [];
        return [
            'sku' => trim($row[$map['sku'] ?? 'sku'] ?? ''),
            'name' => trim($row[$map['name'] ?? 'name'] ?? ''),
            'price' => $this->parsePrice($row[$map['price'] ?? 'price'] ?? 0),
            'in_stock' => !empty($row[$map['stock'] ?? 'stock']),
        ];
    }
}

מפענח XML

// app/Services/PriceList/XmlParser.php
class XmlParser
{
    public function parse(string $filePath, array $config): array
    {
        $xml = simplexml_load_file($filePath, 'SimpleXMLElement', LIBXML_NOCDATA);
        if ($xml === false) {
            throw new \RuntimeException("Не удалось разобрать XML: " . $filePath);
        }

        // XPath для извлечения товаров
        $itemXpath = $config['item_xpath'] ?? '//item';
        $items = $xml->xpath($itemXpath);

        return array_map(fn($item) => $this->extractItem($item, $config), $items);
    }

    private function extractItem(\SimpleXMLElement $item, array $config): array
    {
        $fields = $config['fields'] ?? [];

        $extract = function(string $path) use ($item): string {
            // Поддержка атрибутов (@attr) и дочерних элементов (tag)
            if (str_starts_with($path, '@')) {
                return (string) ($item->attributes()[substr($path, 1)] ?? '');
            }

            $nodes = $item->xpath($path);
            return $nodes ? trim((string) $nodes[0]) : '';
        };

        return [
            'sku' => $extract($fields['sku'] ?? 'article'),
            'name' => $extract($fields['name'] ?? 'name'),
            'price' => (float) $extract($fields['price'] ?? 'price'),
            'in_stock' => $extract($fields['stock'] ?? 'available') !== '0',
            'category' => $extract($fields['category'] ?? 'category'),
        ];
    }
}

Facade לניתוב פורמטים

// app/Services/PriceList/PriceListParser.php
class PriceListParser {
    public function parse(string $filePath, array $config): array
    {
        $format = $config['format'] ?? $this->detectFormat($filePath);
        return match ($format) {
            'excel', 'xlsx', 'xls' => app(ExcelParser::class)->parse($filePath, $config),
            'csv' => app(CsvParser::class)->parse($filePath, $config),
            'xml' => app(XmlParser::class)->parse($filePath, $config),
            default => throw new \InvalidArgumentException("Неизвестный формат: {$format}"),
        };
    }

    private function detectFormat(string $filePath): string
    {
        return match (strtolower(pathinfo($filePath, PATHINFO_EXTENSION))) {
            'xlsx', 'xls', 'ods' => 'excel',
            'csv', 'txt' => 'csv',
            'xml' => 'xml',
            default => 'csv',
        };
    }
}