Содержание:
- Основные преимущества SQL-подхода
- Как это работает в учетной системе 1С: технология Microsoft ADO
Загрузка данных из файлов Microsoft Excel в 1С:Предприятие — одна из самых частых задач в практике любого внедрения: это могут быть прайс-листы поставщиков, начальные остатки, списки клиентов. Классический подход к решению этой задачи — использование COM-объекта для последовательного чтения ячеек файла. Этот метод работает, но когда речь идет о файлах с десятками тысяч строк, он становится мучительно медленным.
Однако существует гораздо более элегантный и быстрый способ — обращение к файлу Excel как к базе данных с помощью SQL-запросов.
В чем принципиальное отличие?
Стандартный метод через COM-объект можно сравнить с чтением книги по одной букве. Программа открывает файл, встает на ячейку A1, читает ее значение, переходит к B1, читает ее, и так далее, строка за строкой. Это создает огромную нагрузку из-за постоянного взаимодействия между 1С и приложением Excel.
Метод SQL-запроса работает иначе. Мы говорим системе: «Подключись к этому файлу Excel и сразу принеси мне все данные из колонок “Артикул” и “Цена” с листа “Товары”, но только те строки, где остаток больше нуля». Вся работа по отбору, фильтрации и извлечению данных выполняется на стороне специального драйвера, а 1С получает уже готовый, отфильтрованный набор данных.
Основные преимущества SQL-подхода
- Скорость: Обработка больших файлов ускоряется в разы, а иногда и в десятки раз.
- Фильтрация на источнике: Можно не загружать в 1С весь файл на 50 000 строк, если вам нужны только 100 из них. Условие WHERE в SQL-запросе сделает всю грязную работу за вас.
- Компактность кода: Код в 1С получается более лаконичным и читаемым по сравнению с вложенными циклами для обхода ячеек.
Как это работает в учетной системе 1С: технология Microsoft ADO
Возможность отправлять SQL-запросы к файлам Excel реализуется через технологию Microsoft ADO (ActiveX Data Objects). Для этого в операционной системе должен быть установлен специальный драйвер (OLE DB Provider), который умеет “видеть” лист Excel как таблицу, столбцы — как поля, а строки — как записи. Чаще всего используется Microsoft ACE OLE DB Provider.
Процесс загрузки состоит из трех шагов:
Шаг 1: Формирование строки подключения
Это специальная строка, которая объясняет системе, как именно нужно подключиться к файлу. Она содержит три ключевых параметра:
- Provider: Имя драйвера (например, Microsoft.ACE.OLEDB.12.0).
- Data Source: Полный путь к файлу Excel (например, C:\Data\Price.xlsx).
- Extended Properties: Дополнительные параметры. Самый важный из них — HDR=YES, который указывает, что первая строка на листе Excel является заголовком колонок.
Пример строки подключения:
“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\Price.xlsx;Extended Properties=””Excel 12.0 Xml;HDR=YES;””
Шаг 2: Написание SQL-запроса
Запрос пишется на стандартном языке SQL. Имя листа Excel используется как имя таблицы, и его нужно заключать в квадратные скобки со знаком $ в конце.
Например, если у нас есть лист с названием “Лист1” и колонками “Артикул”, “Наименование” и “Остаток”, запрос на получение всех товаров с положительным остатком будет выглядеть так:
ВЫБРАТЬ * ИЗ [Лист1$] WHERE Остаток > 0
(В 1С это будет SELECT * FROM [Лист1$] WHERE Остаток > 0)
Шаг 3: Выполнение кода в 1С
В коде 1С создается COM-объект ADODB.Connection, с помощью которого открывается соединение с файлом, используя нашу строку подключения. Затем выполняется SQL-запрос, и результат возвращается в виде объекта ADODB.Recordset, который можно обойти в цикле для получения данных.
// Пример упрощенного кода на 1С
Соединение = Новый COMОбъект(“ADODB.Connection”);
ТекстПодключения = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\Price.xlsx;Extended Properties=””Excel 12.0 Xml;HDR=YES;”””;
Попытка
Соединение.Open(ТекстПодключения);
Исключение
Сообщить(“Не удалось подключиться к файлу!”);
Возврат;
КонецПопытки;
ТекстЗапроса = “SELECT Артикул, Наименование, Цена FROM [Лист1$]”;
Результат = Соединение.Execute(ТекстЗапроса);
Пока НЕ Результат.EOF Цикл
// Получаем данные из полей по их именам
Артикул = Результат.Fields(“Артикул”).Value;
Наименование = Результат.Fields(“Наименование”).Value;
Цена = Результат.Fields(“Цена”).Value;
// … здесь код по созданию/обновлению номенклатуры в 1С …
Результат.MoveNext(); // Переходим к следующей строке
КонецЦикла;
Соединение.Close();
Что важно учитывать?
- Разрядность: Драйвер Microsoft ACE OLE DB должен соответствовать разрядности процесса 1С (32-бит или 64-бит). Это частый источник проблем при настройке.
- Типы данных: Excel не имеет строгой типизации колонок. Драйвер пытается определить тип данных (число, строка, дата) по первым строкам. Иногда это может приводить к неожиданным результатам, если в одной колонке смешаны разные форматы.
- Выполнение на сервере: Если код выполняется на сервере 1С, то и файл Excel, и необходимый драйвер должны быть доступны именно на сервере.
Загрузка данных из Excel через SQL-запросы — мощный инструмент в арсенале разработчика 1С. Он позволяет создавать быстрые и эффективные обработки для обмена данными, особенно при работе с большими файлами. Несмотря на некоторые технические нюансы при первоначальной настройке, выигрыш в производительности и гибкости полностью оправдывает затраченные усилия.
Специалист компании ООО “Кодерлайн”,
Плешка Екатерина
Добавить комментарий