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

← На главную

Загрузка данных из внешних файлов, таких как Excel, является одной из самых частых задач для разработчика 1С. Казалось бы, задача проста: прочитать файл, найти товар в базе и вывести его характеристики. Однако на практике программисты сталкиваются с рядом проблем, начиная от несовпадения типов данных и заканчивая низкой производительностью запросов. В этой статье мы подробно разберем, как правильно организовать процесс обработки списка из Excel, чтобы заполнить колонки остатков, цен и сумм продаж.

Проблема сопоставления типов: почему «Строка» не равна «Ссылке»

Разберем типичную ситуацию: вы импортировали список номенклатуры из Excel в таблицу значений (ТЗ) на форме. В этой таблице в колонке Номенклатура содержатся текстовые названия товаров. Когда вы пытаетесь соединить эту таблицу с виртуальными таблицами остатков или цен в запросе, вы обнаруживаете, что результат пуст. Проанализируем причину.

В системе 1С:Предприятие поле Товар в регистрах имеет ссылочный тип (СправочникСсылка.Номенклатура). В то же время данные, пришедшие из Excel, всегда имеют тип Строка. Прямое соединение в запросе по полю ТЗ.Номенклатура = Регистр.Товар не сработает, так как система не может автоматически сопоставить уникальный идентификатор (GUID) ссылки с произвольным текстом. Для решения этой задачи нам необходимо выполнить промежуточное преобразование строк в ссылки.

Шаг 1: Подготовка данных и загрузка из Excel

Прежде чем приступать к запросам, необходимо правильно прочитать файл. Рассмотрим современный и быстрый способ загрузки через табличный документ, который не требует наличия установленного Microsoft Office на сервере. Мы будем использовать метод Прочитать() объекта ТабличныйДокумент.


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

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

Обратите внимание на использование функции СокрЛП(). Это критически важный момент, так как в Excel часто встречаются невидимые пробелы в начале или конце строки, которые сделают невозможным поиск точного совпадения в базе 1С.

Шаг 2: Преобразование строк в ссылки и получение данных

Теперь, когда у нас есть таблица со строковыми наименованиями, мы должны написать запрос, который решит три задачи: сопоставит строки со справочником Номенклатура, получит остатки и найдет актуальные цены. Проанализируем структуру эффективного пакета запросов.

Рассмотрим первый пакет: мы помещаем нашу таблицу значений во временную таблицу и индексируем её. Индексация необходима для ускорения соединения, если в списке более 100 позиций.

Рассмотрим второй пакет: мы соединяем временную таблицу со справочником номенклатуры по наименованию, чтобы получить реальные ссылки.

Рассмотрим третий пакет: соединяем полученные ссылки с виртуальными таблицами остатков и цен.


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

Разберем важные нюансы этого решения

Функция ЕСТЬNULL: При использовании ЛЕВОЕ СОЕДИНЕНИЕ (которое необходимо, чтобы не потерять строки, по которым нет остатков), поля из регистров могут содержать значение NULL. Чтобы в таблице на форме отображались нули вместо пустоты, мы используем ЕСТЬNULL(Поле, 0).
Срез последних: Для получения актуальной продажной цены мы обращаемся к регистру сведений ЦеныНоменклатуры.СрезПоследних. В качестве параметра даты лучше передавать КонДата (конец периода обработки), чтобы видеть цены, актуальные на момент завершения периода.
Аналитика в оборотах: В современных конфигурациях (УТ 11, ERP, КА 2) данные о продажах часто хранятся с использованием ключей аналитики. Поэтому в соединении с регистром ВыручкаИСебестоимостьПродаж мы обращаемся через точку: АналитикаУчетаНоменклатуры.Номенклатура.
Поиск по артикулу: Если в вашем Excel-файле есть колонка "Артикул", крайне рекомендуется выполнять сопоставление именно по нему, а не по наименованию. Наименования часто меняются, содержат лишние точки или кавычки, что приводит к ошибкам поиска.

Шаг 3: Обработка ненайденных позиций

Выясним причину, по которой некоторые строки остаются пустыми после выполнения запроса. Это происходит, если ВНУТРЕННЕЕ СОЕДИНЕНИЕ во втором пакете запроса не нашло соответствия в справочнике. Проанализируем ситуацию: пользователь может допустить опечатку в Excel. Чтобы помочь ему, можно изменить ВНУТРЕННЕЕ СОЕДИНЕНИЕ на ЛЕВОЕ СОЕДИНЕНИЕ и в финальной выборке добавить условие:

ВЫБОР КОГДА СправочникНоменклатура.Ссылка ЕСТЬ NULL ТОГДА "Товар не найден!" ИНАЧЕ СправочникНоменклатура.Наименование КОНЕЦ

Это позволит пользователю визуально определить проблемные строки и исправить их в исходном файле или в базе 1С.

Заключение

Мы рассмотрели комплексный подход к обработке списков Excel. Главный секрет успеха заключается в последовательном приведении типов: Файл -> Строка -> Ссылка -> Данные регистров. Использование временных таблиц и пакетных запросов позволяет выполнить эту операцию быстро даже на больших объемах данных, обеспечивая при этом корректность числовых показателей остатков и цен.