Как правильно и быстро загрузить данные из Excel в 1С:Предприятие 8?

← На главную

Файлы формата Excel (.xls, .xlsx) исторически остаются основным источником обмена данными между внешними системами, контрагентами и базой 1С. Почти каждый разработчик сталкивается с задачей написания импорта прайс-листов, накладных, номенклатуры или остатков. Однако на практике часто возникает классическая проблема: небольшие файлы загружаются быстро, но при увеличении объема строк до нескольких десятков тысяч процесс зависает на часы, перегружая сервер 1С и СУБД.

В этой статье мы подробно разберем, как уйти от неэффективных алгоритмов, оптимизировать чтение файлов и реализовать правильный паттерн сопоставления данных, который ускорит обработку документов в сотни раз.

---

1. Альтернативные и быстрые способы чтения файлов (без MS Excel)

Использование COM-объекта (Excel.Application) — один из самых старых и, к сожалению, самых медленных способов чтения данных. Более того, этот метод накладывает жесткие инфраструктурные ограничения: он работает исключительно в операционной системе Windows и требует физической установки пакета Microsoft Office на сервере или клиентском компьютере. В современных реалиях, когда серверы 1С часто функционируют под управлением ОС Linux, а клиенты работают в веб-интерфейсе, этот метод становится абсолютно неприемлемым.

Рассмотрим два производительных и кроссплатформенных способа извлечения данных, которые работают «из коробки» на любой операционной системе.

Метод «Табличный документ + Построитель запроса» (Быстро и просто)

Вместо того чтобы обходить ячейки прочитанного файла в цикле, мы можем использовать мощный, но недокументированный механизм работы с ПостроителемЗапроса. Платформа 1С позволяет использовать область табличного документа в качестве источника данных для построителя.

Разберем по шагам, как это реализовать:

Сначала мы читаем файл во временный табличный документ на сервере.
Затем создаем объект ОписаниеИсточникаДанных на основе области табличного документа.
Передаем источник в ПостроительЗапроса и выгружаем результат сразу в типизированную ТаблицуЗначений.

Посмотрим на пример кода, реализующий этот подход:


// Функция быстрого чтения файла xlsx в Таблицу Значений
Функция ПрочитатьФайлВТаблицуЗначений(ПутьКФайлу) Экспорт
    
    ТабДок = Новый ТабличныйДокумент;
    Попытка
        ТабДок.Прочитать(ПутьКФайлу);
    Исключение
        ВызватьИсключение "Не удалось прочитать файл: " + ОписаниеОшибки();
    КонецПопытки;
    
    // Определяем заполненную область
    ВсегоСтрок = ТабДок.ВысотаТаблицы;
    ВсегоКолонок = ТабДок.ШиринаТаблицы;
    
    Если ВсегоСтрок = 0 Тогда
        Возврат Новый ТаблицаЗначений;
    КонецЕсли;
    
    ОбластьДанных = ТабДок.Область(1, 1, ВсегоСтрок, ВсегоКолонок);
    
    // Создаем источник данных для построителя
    ИсточникДанных = Новый ОписаниеИсточникаДанных(ОбластьДанных);
    ИсточникДанных.Колонки[0].Имя = "Артикул";
    ИсточникДанных.Колонки[1].Имя = "Наименование";
    ИсточникДанных.Колонки[2].Имя = "Цена";
    
    Построитель = Новый ПостроительЗапроса;
    Построитель.ИсточникДанных = ИсточникДанных;
    
    // Выполняем виртуальный запрос и выгружаем данные в ТЗ
    Построитель.Выполнить();
    ТаблицаРезультат = Построитель.Результат.Выгрузить();
    
    Возврат ТаблицаРезультат;
КонецФункции

Преимущества: Этот метод работает в десятки раз быстрее, чем построчный обход ячеек табличного документа, и полностью автономен от наличия стороннего ПО.

Низкоуровневый разбор (Прямое чтение XML из ZIP-архива)

Если вам требуется обрабатывать гигантские файлы (от 100 000 до 1 000 000 строк), то даже загрузка файла в ТабличныйДокумент может привести к аварийному завершению рабочего процесса 1С из-за нехватки оперативной памяти.

Дело в том, что формат .xlsx представляет собой упакованный ZIP-архив, внутри которого находятся XML-файлы со структурой таблиц. Мы можем прочитать их напрямую с помощью встроенных объектов 1С ЧтениеZipФайла и ЧтениеXML.

Проанализируем структуру такого архива. Внутри нас интересуют два основных файла:

xl/sharedStrings.xml — содержит все уникальные строковые значения файла (чтобы строки не дублировались и не раздували объем документа).
xl/worksheets/sheet1.xml — хранит сетку ячеек первого листа, где вместо самих текстовых данных прописаны числовые индексы, ссылающиеся на массив из sharedStrings.xml.

Последовательно читая эти файлы в потоковом режиме с помощью ЧтениеXML, мы можем собирать данные напрямую в базу данных или в файл базы данных без загрузки структуры в память сервера целиком. Это обеспечивает абсолютный рекорд по скорости и минимальное потребление RAM.

---

2. Оптимизация работы через COM-соединение (если оно неизбежно)

Бывают ситуации, когда отказаться от COM-соединения невозможно. Например, если файл содержит сложные динамические макросы, защищенные паролем листы, или вам необходимо программно пересчитать формулы MS Excel перед импортом.

Основная ошибка разработчиков при использовании COM заключается в построчном и поколоночном обходе ячеек:

Значение = ExcelApp.Cells(Строка, Колонка).Value;

Каждое такое обращение — это «трансграничный» вызов между процессами операционной системы (из процесса rphost 1С в процесс excel.exe). Если в вашем файле 10 000 строк и 10 колонок, это потребует 100 000 обращений, что приведет к колоссальным потерям времени.

Решение проблемы: Получить всю заполненную область за один вызов в виде многомерного массива 1С (типа COMSafeArray). Вызов происходит единожды, данные копируются в адресное пространство 1С, после чего процесс Excel можно сразу закрывать.

Выясним, как выглядит код такой оптимизации:


// Пример быстрого получения данных через COM за один вызов
Функция ПолучитьДанныеЧерезSafeArray(ПутьКФайлу)
    
    Попытка
        ExcelApp = Новый COMОбъект("Excel.Application");
    Исключение
        ЗаписатьЛогСобытий("Не удалось запустить MS Excel: " + ОписаниеОшибки());
        Возврат Неопределено;
    КонецПопытки;
    
    ExcelApp.DisplayAlerts = Ложь;
    Книга = ExcelApp.Workbooks.Open(ПутьКФайлу);
    Лист = Книга.Sheets(1);
    
    // Находим границы заполненной области
    ПоследняяСтрока = Лист.Cells.SpecialCells(11).Row; // xlCellTypeLastCell
    ПоследняяКолонка = Лист.Cells.SpecialCells(11).Column;
    
    // Получаем весь диапазон значений в COM-массив за ОДИН вызов
    Диапазон = Лист.Range(Лист.Cells(1, 1), Лист.Cells(ПоследняяСтрока, ПоследняяКолонка));
    ДанныеSafeArray = Диапазон.Value;
    
    // Закрываем Excel, он нам больше не нужен
    Книга.Close(Ложь);
    ExcelApp.Quit();
    
    // Преобразуем COMSafeArray в стандартный двухмерный массив 1С
    МассивДанных1С = ДанныеSafeArray.Выгрузить();
    
    Возврат МассивДанных1С;
КонецФункции

После вызова этого метода мы работаем с обычным массивом 1С в оперативной памяти, скорость обхода которого выше в сотни раз по сравнению с обращениями к COM-объекту.

---

3. Подводные камни технологий ADO и CSV

В практике разработки часто встречаются советы использовать технологию ADO (ActiveX Data Objects) для чтения листов Excel с помощью SQL-запросов (например, SELECT * FROM [Sheet1$]) или предварительно конвертировать файлы в формат .csv.

Рассмотрим ограничения и подводные камни этих технологий, чтобы избежать критических ошибок при внедрении.

Проблемы использования ADO:

Неявное определение типов (проблема смешанных данных). Драйвер Microsoft OLEDB сканирует только первые несколько строк таблицы для определения типа данных в колонке. Если в начале колонки «Артикул» идут только цифры (например, 102030), а на 500-й строке появляется текст (например, АП-102030), то драйвер ADO вернет для всех текстовых ячеек значение NULL. Изменение параметра IMEX=1 в строке подключения частично спасает ситуацию, переводя все в режим текста, но стабильность работы механизма на разных версиях ОС оставляет желать лучшего.
Платформенная зависимость. ADO — технология Windows-специфичная. На серверах под управлением Linux запустить этот механизм без эмуляторов невозможно.

Проблемы использования CSV:

Конвертация в CSV удобна для простых текстовых списков. Однако, если ячейки Excel содержат переносы строк внутри ячейки, формулы или запятые в качестве разделителей, процесс парсинга такого файла превращается в отдельную сложную задачу со множеством проверок на спецсимволы и кавычки.

---

4. Главная дилемма: Запрос или Цикл при сопоставлении данных?

После успешного чтения сырых данных из файла наступает важнейший этап — сопоставление их с объектами информационной базы (поиск номенклатуры по артикулу, поиск контрагентов по ИНН/КПП, поиск складов и т.д.).

Многие начинающие разработчики совершают фатальную ошибку: они начинают в цикле по строкам файла выполнять запросы к базе данных или использовать метод НайтиПоРеквизиту():


// КРАЙНЕ НЕЭФФЕКТИВНЫЙ КОД (ТАК ДЕЛАТЬ НЕЛЬЗЯ!)
Для Каждого СтрокаТЗ Из ТаблицаДанных Цикл
    НайденнаяСсылка = Справочники.Номенклатура.НайтиПоРеквизиту("Артикул", СтрокаТЗ.Артикул);
    Если ЗначениеЗаполнено(НайденнаяСсылка) Тогда
        // Обработка данных...
    КонецЕсли;
КонецЦикла;

Почему этот код тормозит? Каждое обращение к методу поиска или выполнение мелкого запроса в цикле инициирует отправку сетевого пакета к СУБД, компиляцию плана запроса, выделение ресурсов на стороне SQL-сервера и получение ответа. Если в файле 20 000 строк, ваша система совершит 20 000 транзакций с СУБД, что может занять от 20 минут до 1 часа.

«Золотой стандарт» сопоставления: Пакетный запрос с временной таблицей

Правильный архитектурный паттерн заключается в том, чтобы передать всю считанную таблицу значений в один единственный запрос в качестве параметра, поместить её во временную таблицу, проиндексировать и связать левым соединением с нужной таблицей БД.

Разберем по шагам правильный алгоритм:

Типизация колонок таблицы значений. Перед передачей ТЗ в запрос все ее колонки должны иметь жесткие типы (например, Строка, Число). Запрос не сможет проиндексировать колонки с неопределенным типом.
Передача ТЗ в запрос в виде параметра.
Создание временной таблицы. Помещаем данные во временную таблицу и обязательно индексируем поле поиска (например, Артикул).
Левое соединение. Соединяем временную таблицу с реальной таблицей базы данных (например, Справочник.Номенклатура) по индексированному ключевому полю.

Проанализируем реализацию этого паттерна на практике:


// Быстрое пакетное сопоставление данных одним запросом
Процедура СопоставитьДанныеЗапросом(ТаблицаФайла)
    
    // Шаг 1: Подготавливаем типы колонок ТЗ для запроса
    ТаблицаДляЗапроса = Новый ТаблицаЗначений;
    ТаблицаДляЗапроса.Колонки.Добавить("Артикул", Новый ОписаниеТипов("Строка", Новый КвалификаторыСтроки(50)));
    ТаблицаДляЗапроса.Колонки.Добавить("ЦенаФайла", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 2)));
    
    Для Каждого СтрокаФайла Из ТаблицаФайла Цикл
        ЗаполнитьЗначенияСвойств(ТаблицаДляЗапроса.Добавить(), СтрокаФайла);
    КонецЦикла;
    
    // Шаг 2: Создаем запрос
    Запрос = Новый Запрос;
    Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
    Запрос.УстановитьПараметр("ВнешнийИсточник", ТаблицаДляЗапроса);
    
    // Шаг 3-4: Формируем пакетный запрос
    Запрос.Текст = 
    "ВЫБРАТЬ
    |	ВнешнийИсточник.Артикул КАК Артикул,
    |	ВнешнийИсточник.ЦенаФайла КАК ЦенаФайла
    |ПОМЕСТИТЬ ВТ_ИсходныеДанные
    |ИЗ
    |	&ВнешнийИсточник КАК ВнешнийИсточник
    |
    |ИНДЕКСИРОВАТЬ ПО
    |	Артикул
    |;
    |
    |////////////////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ
    |	ВТ_ИсходныеДанные.Артикул КАК АртикулИзФайла,
    |	ВТ_ИсходныеДанные.ЦенаФайла КАК ЦенаИзФайла,
    |	Номенклатура.Ссылка КАК СсылкаНоменклатуры,
    |	Номенклатура.ПометкаУдаления КАК ПометкаУдаления
    |ИЗ
    |	ВТ_ИсходныеДанные КАК ВТ_ИсходныеДанные
    |		ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК Номенклатура
    |		ПО ВТ_ИсходныеДанные.Артикул = Номенклатура.Артикул";
    
    РезультатЗапроса = Запрос.Выполнить().Выгрузить();
    
    // Шаг 5: Анализ результатов и обработка ошибок
    Для Каждого СтрокаРезультата Из РезультатЗапроса Цикл
        
        Если СтрокаРезультата.СсылкаНоменклатуры = Справочники.Номенклатура.ПустаяСсылка() Тогда
            // Номенклатура не найдена - готовим пакет ошибок для пользователя
            ЗаписатьОшибку("Не найден артикул: " + СтрокаРезультата.АртикулИзФайла);
        ИначеЕсли СтрокаРезультата.ПометкаУдаления Тогда
            // Объект найден, но помечен на удаление
            ЗаписатьОшибку("Артикул помечен на удаление: " + СтрокаРезультата.АртикулИзФайла);
        Иначе
            // Объект найден успешно, выполняем бизнес-логику
            ОбработатьНайденныйЭлемент(СтрокаРезультата.СсылкаНоменклатуры, СтрокаРезультата.ЦенаИзФайла);
        КонецЕсли;
        
    КонецЦикла;
    
КонецПроцедуры

Результат: За счет индексации временной таблицы и выполнения сопоставления на уровне СУБД (одним запросом), обработка 50 000 строк занимает всего 1-2 секунды вместо часа мучительного ожидания.

---

5. Использование готового функционала БСП (Библиотека Стандартных Подсистем)

Создание кастомных обработок загрузки оправдано, если логика сопоставления уникальна. Однако в большинстве типовых конфигураций 1С (ERP, КА, УТ, УНФ) уже интегрирована готовая подсистема БСП «Загрузка данных из файла».

Преимущества использования стандартной подсистемы БСП:

Готовый UI: Пользователь может просто скопировать таблицу из Excel в буфер обмена и вставить ее в специальное поле ввода (Ctrl+V) без физического выбора файла.
Интерактивное сопоставление колонок: Система сама предложит сопоставить заголовки файла с реквизитами документов или справочников.
Контроль типов данных и поиск дублей: Встроенные алгоритмы БСП автоматически подсветят некорректно заполненные ячейки красным цветом, укажут на дубли строк и позволят исправить ошибки «на лету» прямо на форме перед финальным проведением импорта.

Использование этого шаблона экономит десятки часов разработки индивидуальных загрузчиков и повышает надежность системы.

---

Резюме и практические рекомендации:

Подведем краткий итог нашей совместной работы по оптимизации загрузки файлов Excel:

Избегайте COM-объектов. Читайте файлы кроссплатформенными методами платформы 1С через ТабличныйДокумент или разбирайте XML-структуру напрямую из ZIP, если размер файла превышает лимиты памяти.
Минимизируйте «трансграничные» вызовы. Если без COM-объекта не обойтись, выгружайте всю область Range в двумерный массив за одно обращение.
Никогда не делайте запросы в циклах. Передавайте всю таблицу прочитанных данных в один запрос, помещайте во временную таблицу, индексируйте ключевые поля и связывайте данные левым соединением на уровне СУБД.
Используйте БСП. Перед разработкой собственного загрузчика проверьте наличие подсистемы «Загрузка данных из файла» в вашей конфигурации.