При автоматизации складского учета и закупок часто возникает задача импорта накладных от поставщиков из внешних файлов формата Excel (XLS/XLSX). В конфигурациях 1С, использующих обычные формы (например, «1С:Комплексная автоматизация 1.1» или «1С:Управление торговлей 10.3»), для этого традиционно применяется стандартная обработка ЗагрузкаДанныхИзТабличногоДокумента.epf.
Одной из наиболее распространенных трудностей при такой загрузке является заполнение характеристик номенклатуры. Если сама номенклатура без проблем сопоставляется по коду или артикулу поставщика через регистр сведений НоменклатураКонтрагентов, то автоматический подбор характеристик часто вызывает вопросы. Разберем по шагам, как решить эту задачу различными способами, начиная от написания эффективного программного запроса и заканчивая использованием штатных связей по владельцу.
Представим стандартную рабочую ситуацию: поставщик присылает накладную в формате Excel. В табличном документе присутствует колонка с уникальным кодом товара поставщика (код номенклатуры контрагента), но отсутствует явное текстовое упоминание характеристики в отдельной колонке. При этом в базе данных 1С уже создано жесткое сопоставление в регистре сведений НоменклатураКонтрагентов, где каждому коду поставщика соответствует наша номенклатурная позиция и её конкретная характеристика (например, размер, цвет или объем).
При настройке стандартной обработки загрузки для реквизита Номенклатура мы можем легко указать режим поиска по коду контрагента. Однако для реквизита ХарактеристикаНоменклатуры такой простой связи «из коробки» не предусмотрено, так как характеристика является подчиненным справочником, а связь хранится в независимом регистре сведений. Нам необходимо вытащить эту связь программно в момент обработки каждой строки файла.
Самый быстрый и надежный способ решить эту задачу при наличии готового сопоставления в регистре — использовать режим загрузки «Вычислять» для реквизита ХарактеристикаНоменклатуры в настройках обработки загрузки. Это позволяет написать произвольный код на встроенном языке 1С, который будет выполняться для каждой импортируемой строки.
Рассмотрим пример кода, который выполняет запрос к регистру сведений НоменклатураКонтрагентов. Запрос отбирает запись по текущему контрагенту (полученному из шапки документа-приемника) и коду товара контрагента из Excel-файла:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| НоменклатураКонтрагентов.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры
|ИЗ
| РегистрСведений.НоменклатураКонтрагентов КАК НоменклатураКонтрагентов
|ГДЕ
| НоменклатураКонтрагентов.Контрагент = &КА
| И НоменклатураКонтрагентов.КодНоменклатурыКонтрагента = &КодКА";
// Устанавливаем параметры запроса.
// СсылкаИсточника указывает на создаваемый или редактируемый документ (например, ПоступлениеТоваровУслуг)
Запрос.УстановитьПараметр("КА", СсылкаИсточника.Контрагент);
// ТекстыЯчеек - это предопределенный массив данных текущей строки табличного документа.
// Индекс в квадратных скобках соответствует номеру колонки Excel (индексация начинается с 0 или 1 в зависимости от версии обработки)
Запрос.УстановитьПараметр("КодКА", ТекстыЯчеек[7]);
Выборка = Запрос.Выполнить().Выбрать();
Если Выборка.Следующий() Тогда
Результат = Выборка.ХарактеристикаНоменклатуры;
Иначе
Результат = Справочники.ХарактеристикиНоменклатуры.ПустаяСсылка();
КонецЕсли;
Проанализируем этот код подробнее. Переменная Результат является предопределенной для режима вычисления — именно то значение, которое мы в нее запишем в конце выполнения алгоритма, и будет подставлено в табличную часть документа в качестве характеристики. Если сопоставление в регистре найдено, мы возвращаем ссылку на конкретную характеристику; в противном случае возвращается пустая ссылка, что предотвратит ошибки незаполненных значений.
Предыдущее решение отлично работает на небольших объемах данных (до 50–100 строк). Однако, если в загружаемом файле содержатся тысячи позиций, выполнение отдельного SQL-запроса к базе данных для каждой строки приведет к критическому снижению скорости загрузки (проблема «запроса в цикле»). Программа начнет «зависать».
Чтобы избежать этой проблемы, применим метод кэширования. Мы можем единоразово считать все сопоставления характеристик для выбранного контрагента в оперативную память при обработке первой строки, сохранить их в специальное Соответствие и в дальнейшем осуществлять мгновенный поиск без обращений к СУБД. Посмотрим, как реализовать этот алгоритм:
// Объявляем или проверяем наличие кэша в глобальном контексте обработки (например, в структуре параметров загрузки)
Если ПараметрыЗагрузки.Свойство("КэшХарактеристикНоменклатуры") = Ложь Тогда
// Инициализируем кэш-таблицу один раз для всей загрузки
ЗапросКэш = Новый Запрос;
ЗапросКэш.Текст =
"ВЫБРАТЬ
| НоменклатураКонтрагентов.КодНоменклатурыКонтрагента КАК КодКА,
| НоменклатураКонтрагентов.ХарактеристикаНоменклатуры КАК Характеристика
|ИЗ
| РегистрСведений.НоменклатураКонтрагентов КАК НоменклатураКонтрагентов
|ГДЕ
| НоменклатураКонтрагентов.Контрагент = &КА";
ЗапросКэш.УстановитьПараметр("КА", СсылкаИсточника.Контрагент);
ТаблицаСопоставления = ЗапросКэш.Perform().Выгрузить();
// Переносим данные в быстрое Соответствие (Key-Value)
КэшСоответствий = Новый Соответствие;
Для Каждого СтрокаТаблицы Из ТаблицаСопоставления Цикл
Если ЗначениеЗаполнено(СтрокаТаблицы.КодКА) Тогда
КэшСоответствий.Вставить(СтрокаТаблицы.КодКА, СтрокаТаблицы.Характеристика);
КонецЕсли;
КонецЦикла;
ПараметрыЗагрузки.Вставить("КэшХарактеристикНоменклатуры", КэшСоответствий);
КонецЕсли;
// Получаем кэшированные данные
ТекущийКэш = ПараметрыЗагрузки.КэшХарактеристикНоменклатуры;
КодПоставщика = ТекстыЯчеек[7];
// Мгновенный поиск в оперативной памяти
НайденнаяХарактеристика = ТекущийКэш[КодПоставщика];
Если НайденнаяХарактеристика <> Неопределено Тогда
Результат = НайденнаяХарактеристика;
Иначе
Результат = Справочники.ХарактеристикиНоменклатуры.ПустаяСсылка();
КонецЕсли;
Использование кэширования позволяет обрабатывать файлы размером в несколько тысяч строк за считанные секунды, минимизируя нагрузку на сервер 1С:Предприятия.
В некоторых случаях поставщик все же указывает наименование характеристики (например, «Размер 42» или «Цвет Синий») в отдельной колонке XLS-файла. В такой ситуации использовать написание программного кода не обязательно — можно настроить загрузку штатными интерактивными средствами обработки.
Выясним алгоритм настройки такой связи:
Номенклатура указываем режим поиска «Искать» и выбираем поле поиска (например, артикул или наименование).ХарактеристикаНоменклатуры также устанавливаем режим поиска «Искать» по наименованию характеристики из файла.Номенклатура (текущую колонку товара).Благодаря этой настройке, обработка сначала успешно определит конкретную номенклатурную позицию в строке, а затем выполнит поиск текстовой характеристики исключительно среди тех элементов, которые подчинены этой найденной номенклатуре. Это полностью исключит возникновение дублей и ошибок сопоставления.
Рассмотрим альтернативную ситуацию (описанную в Сообщении №5 и №6): в XLS-файле нет выделенной колонки для характеристики, однако буквенно-цифровой код характеристики содержится прямо внутри сложного наименования товара (например: «Кабель силовой ВВГнг-LS (3х2.5)», где (3х2.5) — это наименование нашей характеристики). Если номенклатура уже найдена в базе по артикулу, мы можем перебрать все её характеристики программным путем и найти совпадение по вхождению подстроки.
Пример реализации такого алгоритма поиска в режиме «Вычислять»:
// Сначала получаем найденную номенклатуру из текущей обрабатываемой строки данных
НайденнаяНоменклатура = ТекущаяСтрока.Номенклатура;
Если ЗначениеЗаполнено(НайденнаяНоменклатура) Тогда
НаименованиеИзФайла = ТекстыЯчеек[2]; // Колонка с полным наименованием товара поставщика
// Делаем запрос для получения всех характеристик найденного товара
ЗапросХарактеристик = Новый Запрос;
ЗапросХарактеристик.Текст =
"ВЫБРАТЬ
| ХарактеристикиНоменклатуры.Ссылка КАК Ссылка,
| ХарактеристикиНоменклатуры.Наименование КАК Наименование
|ИЗ
| Справочник.ХарактеристикиНоменклатуры КАК ХарактеристикиНоменклатуры
|ГДЕ
| ХарактеристикиНоменклатуры.Владелец = &Владелец";
ЗапросХарактеристик.УстановитьПараметр("Владелец", НайденнаяНоменклатура);
ВыборкаХ = ЗапросХарактеристик.Perform().Выгрузить();
ХарактеристикаНайдена = Ложь;
Для Каждого СтрокаХ Из ВыборкаХ Цикл
// Проверяем, содержится ли наименование характеристики в названии товара из файла
Если Найти(ВРег(НаименованиеИзФайла), ВРег(СтрокаХ.Наименование)) > 0 Тогда
Результат = СтрокаХ.Ссылка;
ХарактеристикаНайдена = Истина;
Прервать;
КонецЕсли;
КонецЦикла;
Если Не ХарактеристикаНайдена Тогда
Результат = Справочники.ХарактеристикиНоменклатуры.ПустаяСсылка();
КонецЕсли;
Иначе
Результат = Справочники.ХарактеристикиНоменклатуры.ПустаяСсылка();
КонецЕсли;
Этот способ крайне гибок, так как позволяет сопоставлять товары без ручного ведения регистра номенклатуры контрагентов. Однако помните о рисках: если поставщик изменит формат названия товара в накладной, алгоритм поиска по подстроке может перестать корректно работать.
Стоит отметить, что в современных ERP-системах фирмы 1С (таких как «1С:ERP Управление предприятием 2», «1С:Комплексная автоматизация 2» и «1С:Управление торговлей 11») данный функционал претерпел значительные архитектурные улучшения.
Вместо разрозненных регистров сведений теперь применяется специализированный справочник НоменклатураПартнеров. В нем для каждой позиции поставщика жестко закрепляется её соответствие нашей внутренней номенклатуре и конкретной характеристике. Более того, в современные документы закупки встроен штатный интерактивный помощник импорта из внешних файлов. При загрузке он автоматически распознает коды партнеров, мгновенно подставляя нужные характеристики без необходимости написания программного кода.