Developing functionality for loading orders from Excel to 1C-Bitrix

Excel Order Upload Feature Development for 1C-Bitrix

Wholesale buyers work with their own price lists and specifications in Excel. They want to take a ready-made "SKU — quantity" spreadsheet, upload it to the site, and get a cart immediately. Without this feature, a manager spends an hour on manual entry, makes mistakes, and the customer switches to a competitor with a more convenient site. Standard 1C-Bitrix cannot read Excel — a custom implementation is required.

Server-side Excel parsing

The PhpSpreadsheet library (successor to PHPExcel) is used to read .xlsx files without MS Office installed. Added via Composer:

composer require phpoffice/phpspreadsheet

AJAX controller for file processing:

public function uploadAction(): array
{
    $file = $_FILES['excel_file'];
    if (!$file || $file['error'] !== UPLOAD_ERR_OK) {
        return ['status' => 'error', 'message' => 'File not uploaded'];
    }

    $allowedMimes = [
        'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        'application/vnd.ms-excel',
    ];
    if (!in_array($file['type'], $allowedMimes)) {
        return ['status' => 'error', 'message' => 'Invalid file format'];
    }

    $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createAutomatic(
        \PhpOffice\PhpSpreadsheet\IOFactory::identify($file['tmp_name'])
    );
    $spreadsheet = $reader->load($file['tmp_name']);
    $sheet = $spreadsheet->getActiveSheet();
    $rows = $sheet->toArray();

    return $this->processRows($rows);
}

Row parsing logic

The customer's file format is unpredictable: the header row may be in the first or second row, columns can be in any order. A header detector is implemented:

private function processRows(array $rows): array
{
    // Find the header row by keywords
    $headerRow = null;
    $articleCol = null;
    $quantityCol = null;

    foreach ($rows as $i => $row) {
        foreach ($row as $j => $cell) {
            $cell = mb_strtolower(trim((string)$cell));
            if (in_array($cell, ['артикул', 'арт', 'article', 'sku', 'код'])) {
                $articleCol = $j;
                $headerRow = $i;
            }
            if (in_array($cell, ['количество', 'кол-во', 'qty', 'quantity', 'кол'])) {
                $quantityCol = $j;
            }
        }
        if ($headerRow !== null) break;
    }

    // If header not found — assume: column 0 = SKU, column 1 = quantity
    $articleCol = $articleCol ?? 0;
    $quantityCol = $quantityCol ?? 1;
    $dataStart = ($headerRow ?? -1) + 1;

    $items = [];
    for ($i = $dataStart; $i < count($rows); $i++) {
        $article = trim((string)($rows[$i][$articleCol] ?? ''));
        $qty = (float)str_replace(',', '.', $rows[$i][$quantityCol] ?? 1);
        if ($article !== '') {
            $items[] = ['article' => $article, 'quantity' => max(1, $qty)];
        }
    }
    return $this->resolveArticles($items);
}

SKU resolution

After parsing, we have an array of SKUs. They are resolved with a batch SQL query — faster than N individual queries:

private function resolveArticles(array $items): array
{
    $articles = array_column($items, 'article');
    // Search in trade offer properties
    $placeholders = implode(',', array_fill(0, count($articles), '?'));
    $rs = $connection->query(
        "SELECT ie.ID, iep.VALUE AS ARTICLE, ie.IBLOCK_ELEMENT_ID AS PRODUCT_ID
         FROM b_iblock_element ie
         JOIN b_iblock_element_property iep ON iep.IBLOCK_ELEMENT_ID = ie.ID
         WHERE iep.IBLOCK_PROPERTY_ID = ? AND iep.VALUE IN ($placeholders)",
        array_merge([$articlePropertyId], $articles)
    );
    // ...
}

Preview before adding to cart

After file upload, the user sees a results table:

The customer can adjust quantities, remove not-found items, and click "Add to cart". The add operation uses the same batch \Bitrix\Sale\Basket as in the quick order scenario.

Download template

A link to download an Excel template is placed on the upload page — a file with correctly named column headers. Generated via PhpSpreadsheet or stored as a static file at /upload/templates/order_template.xlsx.

Case study: building materials distributor

Situation: 300+ corporate clients, each with their own order spreadsheet; managers spent 2–3 hours a day entering line items manually.

Implementation:

• Format detector: recognizes 7 header variants (article, code, SKU, art, etc.)
• Support for .xlsx and .xls (via PhpSpreadsheet)
• Limit: maximum 1,000 rows per upload, file up to 5 MB
• Preview with the ability to edit quantities
• Automatic fallback matching by XML_ID if the SKU is not found in the property

Result: managers freed up 2+ hours per day; customers place orders themselves.

What is included in development

• .xlsx/.xls file uploader with format validation
• Parser with automatic header and column position detection
• Batch SKU resolution via b_iblock_element_property
• Results preview with the ability to correct quantities before adding to cart
• Batch add of found products to cart via \Bitrix\Sale\Basket
• Excel template for download