При регулярном обмене данными, загрузке больших номенклатурных прайс-листов или остатков поставщиков разработчики неизбежно сталкиваются с проблемой производительности. Если файл содержит 50 000–70 000 строк, классический построчный опрос ячеек через COM-объект Excel.Application может занимать от нескольких минут до получаса. Рассмотрим причины низкой скорости и разберем наиболее производительные альтернативные способы импорта данных в таблицу значений платформы 1С.
Выясним причину низкой скорости штатного COM-объекта. Медленная работа обусловлена накладными расходами операционной системы на межпроцессный вызов (IPC) при каждом обращении к свойству ячейки вида Лист.Cells(Строка, Колонка).Value. При обработке таблицы в 65 000 строк и 5 колонок платформа 1С совершает более 300 000 межпроцессных обращений, что и дает суммарную задержку свыше двух минут.
Разберем способы ускорения взаимодействия с объектом Excel при использовании OLE/COM:
ScreenUpdating = 0, обработку системных событий EnableEvents = 0 и автоматический пересчет формул Calculation = -4135 (ручной режим).МассивДанных = Лист.Range(Лист.Cells(1, 1), Лист.Cells(КолСтрок, КолКолонок)).Value. Выгрузка в оперативную память выполняется за 1–2 секунды. Однако платформа 1С 7.7 не поддерживает прямую работу с многомерными массивами COM (SafeArray). Для обхода этого ограничения в 1С 7.7 выгруженный массив распаковывают через вспомогательный компонент сценариев MSScriptControl.ScriptControl (на VBScript/JScript) либо внешние компоненты.Проанализируем самый быстрый и распространенный архитектурный подход в среде 1С 7.7 — работу с файлом книги как с реляционной базой данных через интерфейсы ADO или ODBC с помощью внешней компоненты 1cpp.dll (1С++). Скорость загрузки 65 000 строк при таком подходе возрастает примерно в 10 раз по сравнению с COM и занимает около 10–12 секунд.
Посмотрим на пример практической реализации чтения книги через ODBCDatabase и ODBCRecordSet компоненты 1С++:
SQL_ПостроительЗапроса = СоздатьОбъект("ODBCRecordSet");
BaseExel = СоздатьОбъект("ODBCDatabase");
ConnectionString = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=1;PageTimeout=5;MaxScanRows=8;FIL=excel 12.0;DriverId=1046;DBQ=" + СокрЛП(КаталогФайла) + СокрЛП(ИмяФайла) + ";";
Попытка
Если BaseExel.Соединение(ConnectionString) = 0 Тогда
Сообщить(BaseExel.ПолучитьОписаниеОшибки());
Возврат;
КонецЕсли;
Исключение
Сообщить(ОписаниеОшибки());
Возврат;
КонецПопытки;
SQL_ПостроительЗапроса.УстБД(BaseExel);
ТекстЗапросаExel = "
|SELECT
| Excel.[АРТИКУЛ] AS [НомерПоКаталогу],
| Excel.[РЕКОММЕНДОВАННАЯ] / Excel.[КОЛ-ВО] * 1.18 AS [РРЦ],
| Excel.[ДИЛЕРСКАЯ] / Excel.[КОЛ-ВО] * 1.18 AS [Закуп]
|FROM
| [Price$] AS Excel
|";
ТаблицаДанных = SQL_ПостроительЗапроса.ВыполнитьИнструкцию(ТекстЗапросаExel);
Главное преимущество использования 1С++ и SQL-подобного синтаксиса — возможность сразу производить первичную обработку на лету (арифметические операции, фильтрацию по колонкам) и получать на выходе готовую типизированную таблицу значений 1С без предварительного перебора строк на встроенном языке.
При использовании OLE DB и ODBC важно учитывать механизм определения типов (Type Guessing). Драйвер Excel читает физические данные файла, а не их визуальное представление в ячейках. По умолчанию драйвер сканирует первые несколько строк листа (параметр MaxScanRows=8). Если колонка содержит смешанные типы данных (например, артикулы, где часть значений состоит только из цифр, а часть содержит буквенные индексы), драйвер определит тип всей колонки как числовой, а все текстовые артикулы заменит на пустые значения (NULL).
Разберем по шагам, как избежать потери и искажения данных:
IMEX=1 (Import Mixed Types), который указывает драйверу приводить смешанные типы к строковому виду.TypeGuessRows в ветке реестра драйвера (Jet или ACE) определяет количество строк для анализа типов. Установка значения 0 принуждает драйвер сканировать весь лист перед назначением типа колонки, исключая потерю строковых данных.Рассмотрим другие проверенные варианты решения задачи, если использование ODBC-драйвера невозможно:
Конвертер позволяет с высокой скоростью преобразовывать и читать файлы формата Excel напрямую в память без вызова офисного пакета. Следует учитывать ограничение: библиотека ориентирована на классический бинарный формат BIFF8 (Excel 97–2003 / XLS) и не предназначена для прямого разбора формата OpenXML (XLSX).Текст платформы 1С с последующим разбором через строковые функции выполняется за доли секунды.MSXML2.SAXXMLReader), обрабатывающие узлы файла последовательно без загрузки документа целиком.