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

← На главную

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

Анализ наивного подхода: почему нельзя делать все в одной процедуре

Рассмотрим предложенную в исходном вопросе схему: пошаговое чтение ячеек с проверкой справочника «на лету». В ней кроются три критические ошибки:

Запросы в цикле к базе данных. Если в файле содержится несколько тысяч строк, то выполнение методов поиска (НайтиПоНаименованию, НайтиПоРеквизиту или текста запроса) на каждой итерации вызовет тысячи отдельных обращений к СУБД. Это в разы замедлит выполнение обработки.
Дублирование создаваемых позиций. Если в файле один и тот же новый товар встречается более одного раза, простой построчный алгоритм «проверил — не нашел — создал» создаст ровно столько одинаковых карточек, сколько раз товар упомянут в документе поставщика.
Блокировка внешнего файла и утечка COM-ресурсов. Если при записи элемента номенклатуры или строки возникнет ошибка, незакрытый процесс Excel останется висеть в памяти операционной системы, а файл останется заблокированным для изменения.

Правильная пошаговая схема обработки

Оптимальная схема загрузки строится по принципу разделения ответственности (чтение, валидация/нормализация данных, пакетная обработка в БД, формирование документа):

Подключение и считывание файла во временную таблицу значений (ТЗ). На этом этапе мы читаем данные внешнего файла и сразу закрываем соединение или файл. База 1С и внешний файл больше не зависят друг от друга.
Нормализация и типизация данных. Очищаем строки от неразрывных пробелов, мусорных символов, приводим количества и цены к числам.
Пакетный поиск номенклатуры без запросов в цикле. Передаем список артикулов или наименований из ТЗ в единый запрос и связываем его со справочником.
Создание недостающих элементов с кэшированием. Новые карточки создаются только для уникальных ненайденных позиций со всеми обязательными реквизитами.
Заполнение и проведение документа. Заполняются реквизиты шапки и табличной части с вызовом типовых пересчетов, после чего документ записывается.

Разберем реализацию ключевых этапов подробнее.

Этап 1: Чтение файла Excel в Таблицу Значений

Считать файл можно разными способами. Если на рабочей станции установлен Excel, можно использовать COM-объект Excel.Application или быстрое подключение через ADODB.Connection. Однако в современных версиях платформы 1С наиболее надежным и быстрым вариантом является чтение через ТабличныйДокумент, так как он не требует установленного MS Office и корректно работает на любых клиентах.

Посмотрим на пример считывания листа файла в ТабличныйДокумент:


Функция ПрочитатьФайлВТаблицу(ПолноеИмяФайла)
    ТабДок = Новый ТабличныйДокумент;
    ТабДок.Прочитать(ПолноеИмяФайла);
    
    ТЗ = Новый ТаблицаЗначений;
    ТЗ.Колонки.Добавить("Артикул", Новый ОписаниеТипов("Строка",,,, Новый КвалификаторыСтроки(50)));
    ТЗ.Колонки.Добавить("Наименование", Новый ОписаниеТипов("Строка",,,, Новый КвалификаторыСтроки(150)));
    ТЗ.Колонки.Добавить("Количество", Новый ОписаниеТипов("Число",,,, Новый КвалификаторыЧисла(15, 3)));
    ТЗ.Колонки.Добавить("Цена", Новый ОписаниеТипов("Число",,,, Новый КвалификаторыЧисла(15, 2)));
    ТЗ.Колонки.Добавить("НоменклатураСсылка", Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));

    ВсегоСтрок = ТабДок.ВысотаТаблицы;
    Для НомерСтроки = 2 По ВсегоСтрок Цикл // Пропускаем заголовок
        ТекстАртикул = СокрЛП(ТабДок.ПолучитьОбласть(НомерСтроки, 1).ТекущийОбъект);
        ТекстНаименование = СокрЛП(ТабДок.ПолучитьОбласть(НомерСтроки, 2).ТекущийОбъект);
        ТекстКоличество = ТабДок.ПолучитьОбласть(НомерСтроки, 3).ТекущийОбъект;
        ТекстЦена = ТабДок.ПолучитьОбласть(НомерСтроки, 4).ТекущийОбъект;
        
        Если ПустаяСтрока(ТекстНаименование) И ПустаяСтрока(ТекстАртикул) Тогда
            Продолжить;
        КонецЕсли;

        НоваяСтрока = ТЗ.Добавить();
        // Убираем неразрывные пробелы, которые часто попадают из внешних выгрузок
        НоваяСтрока.Артикул = СтрЗаменить(Строка(ТекстАртикул), Символы.НПП, "");
        НоваяСтрока.Наименование = СтрЗаменить(Строка(ТекстНаименование), Символы.НПП, " ");
        НоваяСтрока.Количество = Число(ТекстКоличество);
        НоваяСтрока.Цена = Число(ТекстЦена);
    КонецЦикла;
    
    Возврат ТЗ;
КонецФункции

Этап 2: Пакетный поиск номенклатуры и исключение дублей

Вместо вызова поиска на каждой строке передадим всю таблицу загрузки в запрос к базе данных. Это позволит сопоставить существующие позиции за один вызов к СУБД:


Процедура СопоставитьНоменклатуру(ТЗ)
    Запрос = Новый Запрос;
    Запрос.Текст = 
        "ВЫБРАТЬ
        |   ДанныеФайла.Артикул КАК Артикул,
        |   ДанныеФайла.Наименование КАК Наименование,
        |   ДанныеФайла.НомерСтроки КАК НомерСтроки
        |ПОМЕСТИТЬ ВТ_Файл
        |ИЗ
        |   &ТаблицаФайла КАК ДанныеФайла
        |;
        |
        |////////////////////////////////////////////////////////////////////////////////
        |ВЫБРАТЬ
        |   ВТ_Файл.НомерСтроки КАК НомерСтроки,
        |   СпрНоменклатура.Ссылка КАК Ссылка
        |ИЗ
        |   ВТ_Файл КАК ВТ_Файл
        |   ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК СпрНоменклатура
        |       ПО ВТ_Файл.Артикул = СпрНоменклатура.Артикул
        |       И НЕ СпрНоменклатура.ПометкаУдаления";

    // Добавим колонку с номером строки для точного сопоставления
    ТЗ.Колонки.Добавить("НомерСтроки", Новый ОписаниеТипов("Число"));
    Для Индекс = 0 По ТЗ.Количество() - 1 Цикл
        ТЗ[Индекс].НомерСтроки = Индекс + 1;
    КонецЦикла;

    Запрос.УстановитьПараметр("ТаблицаФайла", ТЗ);
    Выборка = Запрос.Выполнить().Выбрать();
    
    Пока Выборка.Следующий() Цикл
        СтрокаТЗ = ТЗ[Выборка.НомерСтроки - 1];
        СтрокаТЗ.НоменклатураСсылка = Выборка.Ссылка;
    КонецЦикла;
КонецПроцедуры

Этап 3: Создание ненайденных позиций

Для создания недостающей номенклатуры проанализируем строки, где колонка НоменклатураСсылка осталась незаполненной. Чтобы избежать дублирования одинаковых элементов из файла, используем объект Соответствие как кэш созданных ссылок:


Процедура СоздатьНедостающуюНоменклатуру(ТЗ, БазоваяЕдиница, ВидНоменклатуры)
    КэшНовых = Новый Соответствие;
    
    Для Каждого СтрТЗ Из ТЗ Цикл
        Если ЗначениеЗаполнено(СтрТЗ.НоменклатураСсылка) Тогда
            Продолжить;
        КонецЕсли;
        
        КлючПоиска = ВРег(СтрТЗ.Артикул);
        СсылкаИзКэша = КэшНовых.Получить(КлючПоиска);
        
        Если СсылкаИзКэша <> Неопределено Тогда
            СтрТЗ.НоменклатураСсылка = СсылкаИзКэша;
        Иначе
            НовыйЭлемент = Справочники.Номенклатура.СоздатьЭлемент();
            НовыйЭлемент.Наименование = СтрТЗ.Наименование;
            НовыйЭлемент.Артикул = СтрТЗ.Артикул;
            НовыйЭлемент.БазоваяЕдиницаИзмерения = БазоваяЕдиница;
            НовыйЭлемент.ВидНоменклатуры = ВидНоменклатуры;
            
            // В типовых конфигурациях требуется заполнение единиц хранения и отчетов
            НовыйЭлемент.Записать();
            
            КэшНовых.Вставить(КлючПоиска, НовыйЭлемент.Ссылка);
            СтрТЗ.НоменклатураСсылка = НовыйЭлемент.Ссылка;
        КонецЕсли;
    КонецЦикла;
КонецПроцедуры

Этап 4: Заполнение табличной части и запись документа

Когда таблица данных полностью подготовлена и все ссылки на справочник гарантированно определены, создаем объект документа и переносим данные в табличную часть. Для обеспечения целостности данных операцию создания номенклатуры и формирования документа рекомендуется выполнять в единой явной транзакции:


Процедура ЗагрузитьДанныеВДокумент(ТЗ, Организация, Контрагент, Склад, Договор)
    НачатьТранзакцию();
    Попытка
        ДокОбъект = Документы.ПоступлениеТоваровУслуг.СоздатьДокумент();
        ДокОбъект.Дата = ТекущаяДата();
        ДокОбъект.Организация = Организация;
        ДокОбъект.Контрагент = Контрагент;
        ДокОбъект.ДоговорКонтрагента = Договор;
        ДокОбъект.СкладОрдер = Склад;
        
        Для Каждого Стр Из ТЗ Цикл
            НоваяСтрока = ДокОбъект.Товары.Добавить();
            НоваяСтрока.Номенклатура = Стр.НоменклатураСсылка;
            НоваяСтрока.Количество = Стр.Количество;
            НоваяСтрока.Цена = Стр.Цена;
            НоваяСтрока.Сумма = Стр.Количество * Стр.Цена;
            
            // Вызов типовых обработчиков конфигурации для пересчета сумм и НДС
            // Например: ОбработкаТабличнойЧастиТоварыПриИзмененииНоменклатуры(НоваяСтрока, ДокОбъект);
        КонецЦикла;
        
        ДокОбъект.Записать(РежимЗаписиДокумента.Запись);
        ЗафиксироватьТранзакцию();
        Сообщить("Документ успешно создан: " + Строка(ДокОбъект.Ссылка));
    Исключение
        ОтменитьТранзакцию();
        ВызватьИсключение "Ошибка при создании документа: " + ОписаниеОшибки();
    КонецПопытки;
КонецПроцедуры

Итог

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