При автоматизации складского учета, импорте прайс-листов поставщиков или загрузке накладных перед разработчиком часто возникает задача сопоставления внешних данных с объектами базы 1С. Самый частый сценарий — это поиск элементов справочника Номенклатура по текстовому коду или артикулу, полученному из файла Excel.
Загрузка данных строка за строкой с поиском через метод НайтиПоРеквизиту крайне неэффективна и создает огромную нагрузку на СУБД. Профессиональный и быстрый способ решения этой задачи — считывание данных из файла во временную таблицу значений, передача этой таблицы в качестве параметра в запрос и выполнение левого соединения со справочником номенклатуры. В этой статье мы подробно разберем, как реализовать этот алгоритм на управляемых формах 1С 8.3, разберем типичные ошибки сопоставления типов и невидимых пробелов.
Для того чтобы наше решение работало стабильно, быстро и соответствовало стандартам разработки платформы 1С:Предприятие 8.3, мы разделим весь процесс на пять последовательных этапов:
ТабличныйДокумент.Давайте подробно проанализируем каждый из этих шагов и посмотрим на практические примеры кода.
Работа с файловой системой на клиенте в управляемом приложении должна осуществляться асинхронно или через стандартный диалог выбора файлов. После того как пользователь выбрал файл на своем компьютере, мы считываем его содержимое с помощью встроенного объекта ТабличныйДокумент.
Рассмотрим процедуру начала выбора файла на клиенте:
&НаКлиенте
Процедура ФайлДляЗагрузкиНачалоВыбора(Элемент, ДанныеВыбора, СтандартнаяОбработка)
СтандартнаяОбработка = Ложь;
ДиалогВыбора = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
ДиалогВыбора.Заголовок = "Выберите файл Excel для загрузки";
ДиалогВыбора.Фильтр = "Файлы Excel (*.xls; *.xlsx)|*.xls; *.xlsx";
Если ДиалогВыбора.Выбрать() Тогда
ФайлДляЗагрузки = ДиалогВыбора.ПолноеИмяФайла;
ЗагрузитьДанныеИзФайлаНаСервере(ФайлДляЗагрузки);
КонецЕсли;
КонецПроцедуры
Обратите внимание, что непосредственное чтение файла Excel методом Прочитать() объекта ТабличныйДокумент рекомендуется производить в серверном контексте, так как это гарантирует более высокую скорость обработки данных и отсутствие проблем с блокировкой файлов операционной системой на клиенте.
Когда табличный документ прочитан в памяти сервера, нам необходимо преобразовать его строки в плоскую таблицу значений. Для автоматического разбора колонок отлично подходит системный объект ПостроительЗапроса. Он позволяет мгновенно выгрузить область ячеек табличного документа в коллекцию строк.
Однако здесь нас подстерегает самая частая проблема интеграции с Excel — грязные данные. Пользователи часто случайно добавляют пробелы в начале или конце артикулов, а сам Excel при форматировании чисел может автоматически добавлять так называемые неразрывные пробелы (символ с кодом 160) в качестве разделителей групп разрядов. Строка "123 456" (с неразрывным пробелом) никогда не сопоставится в запросе со строкой "123456" из базы 1С.
Поэтому перед отправкой таблицы в запрос мы обязательно выполняем нормализацию каждого артикула. Мы заменим все виды пробелов на пустые строки с помощью функции СтрЗаменить() и метода СокрЛП().
Рассмотрим, как выглядит этот шаг на встроенном языке:
&НаСервере
Функция ПодготовитьТаблицуИзФайла(ПутьКФайлу)
ТабДок = Новый ТабличныйДокумент;
Попытка
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
ВызватьИсключение "Не удалось прочитать файл. Возможно, он открыт в другой программе! " + ОписаниеОшибки();
КонецПопытки;
// Используем построитель запроса для быстрого импорта данных
Построитель = Новый ПостроительЗапроса;
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабДок.Область());
Построитель.Выполнить();
ТЗ_Исходная = Построитель.Результат.Выгрузить();
// Создаем строго типизированную таблицу значений для передачи в запрос
ТЗ_Очищенная = Новый ТаблицаЗначений;
ТЗ_Очищенная.Колонки.Добавить("Артикул", Новый ОписаниеТипов("Строка", Новый КвалификаторыСтроки(100)));
ТЗ_Очищенная.Колонки.Добавить("Цена", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 2)));
Для Каждого СтрокаИмпорта Из ТЗ_Исходная Цикл
Если НЕ ЗначениеЗаполнено(СтрокаИмпорта.Артикул) Тогда
Продолжить;
КонецЕсли;
НоваяСтрока = ТЗ_Очищенная.Добавить();
// Удаляем обычные пробелы, неразрывные пробелы (символ 160) и приводим к верхнему регистру
ЧистыйАртикул = Строка(СтрокаИмпорта.Артикул);
ЧистыйАртикул = СтрЗаменить(ЧистыйАртикул, Символ(160), "");
ЧистыйАртикул = СтрЗаменить(ЧистыйАртикул, " ", "");
ЧистыйАртикул = СокрЛП(ЧистыйАртикул);
НоваяСтрока.Артикул = ЧистыйАртикул;
НоваяСтрока.Цена = Число(СтрокаИмпорта.Цена);
КонецЦикла;
Возврат ТЗ_Очищенная;
КонецФункции
Переходим к ключевому этапу. Переданную таблицу значений мы можем использовать непосредственно внутри текста запроса как источник данных. Для этого ее необходимо передать через метод УстановитьПараметр().
Важно помнить правила работы с таблицами значений в запросах 1С:
Артикул).ЛЕВОЕ СОЕДИНЕНИЕ), чтобы мы не потеряли строки из файла Excel, для которых артикул не был найден в базе данных. Это позволит нам сообщить пользователю об ошибках сопоставления.Проанализируем текст и структуру оптимального запроса:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ВремТЗ.Артикул КАК Артикул,
| ВремТЗ.Цена КАК Цена
|ПОМЕСТИТЬ ВТ_ДанныеФайла
|ИЗ
| &ТаблицаДанных КАК ВремТЗ
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВТ_ДанныеФайла.Артикул КАК Артикул,
| ВТ_ДанныеФайла.Цена КАК Цена,
| ЕСТЬNULL(СпрНоменклатура.Ссылка, ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка)) КАК Номенклатура
|ИЗ
| ВТ_ДанныеФайла КАК ВТ_ДанныеФайла
| ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК СпрНоменклатура
| ПО ВТ_ДанныеФайла.Артикул = СпрНоменклатура.Артикул
|ГДЕ
| НЕ СпрНоменклатура.ПометкаУдаления";
Запрос.УстановитьПараметр("ТаблицаДанных", ТЗ_Очищенная);
РезультатСопоставления = Запрос.Выполнить().Выгрузить();
В данном запросе мы соединяем временную таблицу ВТ_ДанныеФайла со справочником Номенклатура по равенству полей артикулов. Условие ГДЕ НЕ СпрНоменклатура.ПометкаУдаления гарантирует, что мы не выберем устаревшие или помеченные на удаление дубли карточек товаров.
После успешного выполнения запроса мы получаем готовую таблицу, в которой содержатся и исходный артикул, и цена, и найденная ссылка на номенклатуру 1С. Нам остается лишь перенести эти данные в табличную часть на форме документа (например, реквизит формы с типом ДанныеФормыКоллекция под названием БазовыеЦены).
Чтобы избежать мерцания интерфейса и повысить скорость работы, перед заполнением рекомендуется очистить старые строки (если это предполагает бизнес-логика) и производить обход коллекции в одном цикле:
&НаСервере
Процедура ЗаполнитьТабличнуюЧастьНаСервере(РезультатСопоставления)
// Получаем коллекцию строк табличной части формы
ТабличнаяЧастьФормы = ЭтаФорма.БазовыеЦены;
ТабличнаяЧастьФормы.Очистить();
Для Каждого СтрокаРезультата Из РезультатСопоставления Цикл
НоваяСтрока = ТабличнаяЧастьФормы.Добавить();
НоваяСтрока.Артикул = СтрокаРезультата.Артикул;
НоваяСтрока.Цена = СтрокаРезультата.Цена;
Если ЗначениеЗаполнено(СтрокаРезультата.Номенклатура) Тогда
НоваяСтрока.Номенклатура = СтрокаРезультата.Номенклатура;
Иначе
// Опционально: красивое информирование пользователя о ненайденном товаре
ОбщегоНазначенияКлиентСервер.СообщитьПользователю(
НСтр("ru = 'Внимание! Товар с артикулом """ + СтрокаРезультата.Aртикул + """ не найден в справочнике!'")
);
КонецЕсли;
КонецЦикла;
КонецПроцедуры
Рассмотрим несколько критических нюансов, которые часто приводят к ошибкам при сопоставлении артикулов на реальных проектах:
000145), Excel может прочитать их как число 145 и отбросить ведущие нули. На стороне 1С артикул останется строковым типом "000145". В результате левое соединение вернет пустую ссылку.
Решение: Либо настройте жесткий формат ячеек в Excel как текстовый, либо программно дополняйте числовые артикулы ведущими нулями с помощью функции Прав("00000000" + АртикулИзExcel, ДлинаАртикулаВБазе).ТаблицаЗначений не может существовать на клиенте в тонком клиенте 1С. Все операции с ТЗ (включая создание, заполнение параметрами запроса и сам запрос) должны производиться в процедурах с директивой &НаСервере или &НаСервереБезКонтекста.Для удобства использования объединим все наши шаги в один красивый и структурированный рабочий блок кода, который вы можете перенести в модуль своей управляемой формы:
&НаКлиенте
Процедура ВыполнитьЗагрузку(Команда)
ДиалогВыбора = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
ДиалогВыбора.Заголовок = "Выберите файл для импорта";
ДиалогВыбора.Фильтр = "Таблицы Excel (*.xls; *.xlsx)|*.xls; *.xlsx";
Если ДиалогВыбора.Выбрать() Тогда
ФайлИмя = ДиалогВыбора.ПолноеИмяФайла;
ОбработатьИЗаполнитьНаСервере(ФайлИмя);
ПоказатьПредупреждение(, "Загрузка и сопоставление номенклатуры успешно завершены!");
КонецЕсли;
КонецПроцедуры
&НаСервере
Процедура ОбработатьИЗаполнитьНаСервере(ПутьКФайлу)
// Шаг 1 и 2: Чтение и чистка данных
ТаблицаДанных = ПодготовитьТаблицуИзФайла(ПутьКФайлу);
// Шаг 3: Сопоставление через СУБД
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ВремТЗ.Артикул КАК Артикул,
| ВремТЗ.Цена КАК Цена
|ПОМЕСТИТЬ ВТ_ДанныеФайла
|ИЗ
| &ТаблицаДанных КАК ВремТЗ
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВТ_ДанныеФайла.Артикул КАК Артикул,
| ВТ_ДанныеФайла.Цена КАК Цена,
| ЕСТЬNULL(СпрНоменклатура.Ссылка, ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка)) КАК Номенклатура
|ИЗ
| ВТ_ДанныеФайла КАК ВТ_ДанныеФайла
| ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК СпрНоменклатура
| ПО ВТ_ДанныеФайла.Артикул = СпрНоменклатура.Артикул
|ГДЕ
| НЕ СпрНоменклатура.ПометкаУдаления";
Запрос.УстановитьПараметр("ТаблицаДанных", ТаблицаДанных);
РезультатЗапроса = Запрос.Выполнить().Выгрузить();
// Шаг 4: Заполнение управляемой формы
ТабличнаяЧастьФормы = ЭтаForm.БазовыеЦены;
ТабличнаяЧастьФормы.Очистить();
Для Каждого СтрокаДанных Из РезультатЗапроса Цикл
НоваяСтрока = ТабличнаяЧастьФормы.Добавить();
НоваяСтрока.Артикул = СтрокаДанных.Артикул;
НоваяСтрока.Цена = СтрокаДанных.Цена;
Если ЗначениеЗаполнено(СтрокаДанных.Номенклатура) Тогда
НоваяСтрока.Номенклатура = СтрокаДанных.Номенклатура;
Иначе
Сообщение = Новый СообщениеПользователю;
Сообщение.Текст = "Не найден товар с артикулом: " + СтрокаДанных.Артикул;
Сообщение.Сообщить();
КонецЕсли;
КонецЦикла;
КонецПроцедуры
&НаСервере
Функция ПодготовитьТаблицуИзФайла(ПутьКФайлу)
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Построитель = Новый ПостроительЗапроса;
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабДок.Область());
Построитель.Выполнить();
ТЗ_Исходная = Построитель.Результат.Выгрузить();
ТЗ_Очищенная = Новый ТаблицаЗначений;
ТЗ_Очищенная.Колонки.Добавить("Артикул", Новый ОписаниеТипов("Строка", Новый КвалификаторыСтроки(100)));
ТЗ_Очищенная.Колонки.Добавить("Цена", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 2)));
Для Каждого Стр Из ТЗ_Исходная Цикл
Если НЕ ЗначениеЗаполнено(Стр.Артикул) Тогда
Продолжить;
КонецЕсли;
НоваяСтрока = ТЗ_Очищенная.Добавить();
// Очищаем артикул от всех видов пробелов
ЧистыйАртикул = Строка(Стр.Артикул);
ЧистыйАртикул = СтрЗаменить(ЧистыйАртикул, Символ(160), "");
ЧистыйАртикул = СтрЗаменить(ЧистыйАртикул, " ", "");
ЧистыйАртикул = СокрЛП(ЧистыйАртикул);
НоваяСтрока.Артикул = ЧистыйАртикул;
НоваяСтрока.Цена = Число(Стр.Цена);
КонецЦикла;
Возврат ТЗ_Очищенная;
КонецФункции
Использование этого кода позволяет обрабатывать файлы на тысячи строк буквально за несколько секунд. Вы избавитесь от медленных построчных обращений к базе данных, корректно обработаете ошибки форматирования Excel и обеспечите надежную работу вашей информационной системы.