Setting up data export to Excel from 1C-Bitrix

Configuring Data Export to Excel from 1C-Bitrix

Managers need data in Excel: orders for a period, customer lists, stock levels. Standard 1C-Bitrix provides export in a limited number of places — primarily in the admin panel. Custom export for managers directly from the site interface or from admin reports is a separate development task.

Standard 1C-Bitrix Capabilities

Out of the box, 1C-Bitrix exports to Excel:

• Order list in the admin panel (/bitrix/admin/sale_order.php) — "Export" button
• Info block elements (/bitrix/admin/iblock_element_admin.php) — export via list view
• CRM reports in Bitrix24

These tools produce basic CSV/XLS without formatting, accessible only from the admin panel. Not suitable for managers without access to /bitrix/admin/.

Custom Order Export

The most common task — exporting orders filtered by date, status, or manager:

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Bitrix\Sale\OrderTable;
use Bitrix\Main\Type\DateTime;

class OrderExporter
{
    public function export(array $filter): string
    {
        $orders = OrderTable::getList([
            'filter' => [
                '>=DATE_INSERT' => new DateTime($filter['date_from']),
                '<=DATE_INSERT' => new DateTime($filter['date_to']),
                '=STATUS_ID'    => $filter['status'] ?? null,
                '=LID'          => SITE_ID,
            ],
            'select' => [
                'ID', 'ACCOUNT_NUMBER', 'DATE_INSERT', 'PRICE',
                'CURRENCY', 'STATUS_ID', 'USER_ID',
            ],
            'order' => ['DATE_INSERT' => 'DESC'],
        ])->fetchAll();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Headers
        $sheet->fromArray(
            ['Order #', 'Date', 'Customer', 'Email', 'Phone', 'Amount', 'Currency', 'Status'],
            null, 'A1'
        );
        $sheet->getStyle('A1:H1')->getFont()->setBold(true);

        $row = 2;
        foreach ($orders as $order) {
            $user = $this->getUserInfo($order['USER_ID']);
            $sheet->fromArray([
                $order['ACCOUNT_NUMBER'],
                $order['DATE_INSERT']->format('d.m.Y H:i'),
                $user['FULL_NAME'],
                $user['EMAIL'],
                $user['PHONE'],
                $order['PRICE'],
                $order['CURRENCY'],
                $this->getStatusName($order['STATUS_ID']),
            ], null, 'A' . $row);
            $row++;
        }

        $writer = new Xlsx($spreadsheet);
        $file = tempnam(sys_get_temp_dir(), 'orders_');
        $writer->save($file);
        return $file;
    }
}

Exporting Stock Levels

For stock export, use the b_catalog_store_product + info block combination:

$result = \Bitrix\Main\Application::getConnection()->query("
    SELECT
        ie.id,
        ie.name,
        prop_article.value AS article,
        SUM(csp.amount) AS total_amount,
        SUM(csp.quantity_reserved) AS reserved
    FROM b_iblock_element ie
    JOIN b_iblock_element_property prop_article
        ON prop_article.iblock_element_id = ie.id
        AND prop_article.iblock_property_id = :article_prop_id
    LEFT JOIN b_catalog_store_product csp ON csp.product_id = ie.id
    WHERE ie.iblock_id = :iblock_id AND ie.active = 'Y'
    GROUP BY ie.id, ie.name, prop_article.value
    ORDER BY ie.name
", ['iblock_id' => CATALOG_IBLOCK_ID, 'article_prop_id' => ARTICLE_PROP_ID]);

Access Control and AJAX Route

The export is triggered via an AJAX handler with permission checks:

// /local/ajax/export_orders.php
\Bitrix\Main\Loader::includeModule('sale');

if (!$USER->IsAdmin() && !$USER->IsInGroup(MANAGER_GROUP_ID)) {
    header('HTTP/1.1 403 Forbidden');
    exit;
}

$exporter = new OrderExporter();
$file = $exporter->export($_GET);

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="orders_' . date('Y-m-d') . '.xlsx"');
header('Content-Length: ' . filesize($file));
readfile($file);
unlink($file);

Timeline