При автоматизации загрузки данных из внешних источников, таких как файлы Excel, часто возникает задача не просто перенести всю информацию, а отфильтровать ее по определенным критериям. Рассмотрим типовую ситуацию: у нас есть файл с данными контрагентов, где в одном из столбцов указан город или страна. Нам необходимо загрузить в базу 1С только тех контрагентов, у которых в этом столбце указана страна, причем название страны должно присутствовать в нашем справочнике «Страны». Проанализируем, как реализовать такую проверку и загрузить только нужные строки.
Исходный код загрузки может выглядеть так:
&НаСервере
Процедура ЗагрузитьДанныеНаСервере(АдресВХ)
ТабДок = Новый ТабличныйДокумент;
ДанныеВХ = ПолучитьИзВременногоХранилища(АдресВХ);
ПутьКФайлуНаСервере = ПолучитьИмяВременногоФайла("xlsx");
ДанныеВХ.Записать(ПутьКФайлуНаСервере);
Попытка
ТабДок.Прочитать(ПутьКФайлуНаСервере, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
Сообщение = Новый СообщениеПользователю;
Сообщение.Текст = "Не удалось прочитать указанный файл!" + ОписаниеОшибки();
Сообщение.Сообщить();
Возврат;
КонецПопытки;
КоличествоСтрок = ТабДок.ВысотаТаблицы;
ДанныеФайла = Новый ТаблицаЗначений;
ДанныеФайла.Колонки.Добавить("Наименование");
ДанныеФайла.Колонки.Добавить("ИНН");
ДанныеФайла.Колонки.Добавить("Телефон");
ДанныеФайла.Колонки.Добавить("КластерДоставки");
ДанныеФайла.Колонки.Добавить("ТНВЭД");
Для НомерСтроки = 2 По КоличествоСтрок Цикл
СтрокаДанных = ДанныеФайла.Добавить();
СтрокаДанных.Наименование = ТабДок.ПолучитьОбласть("R" + Формат(НомерСтроки, "ЧГ=0") + "C" + 13).ТекущаяОбласть.Текст;
СтрокаДанных.ИНН = ТабДок.ПолучитьОбласть("R" + Формат(НомерСтроки, "ЧГ=0") + "C" + 14).ТекущаяОбласть.Текст;
СтрокаДанных.Телефон = ТабДок.ПолучитьОбласть("R" + Формат(НомерСтроки, "ЧГ=0") + "C" + 25).ТекущаяОбласть.Текст;
СтрокаДанных.КластерДоставки = ТабДок.ПолучитьОбласть("R" + Формат(НомерСтроки, "ЧГ=0") + "C" + 27).ТекущаяОбласть.Текст;
СтрокаДанных.ТНВЭД = ТабДок.ПолучитьОбласть("R" + Формат(НомерСтроки, "ЧГ=0") + "C" + 35).ТекущаяОбласть.Текст;
КонецЦикла;
ЗагруженныеДанные.Загрузить(ДанныеФайла);
КонецПроцедуры
В этом коде данные из столбца 27 (КластерДоставки) загружаются без какой-либо проверки. Наша задача — добавить условие, которое будет проверять, является ли значение в этой ячейке наименованием элемента справочника Страны.
Это самый прямой и простой для понимания способ, который отлично подходит для обучения и для обработки небольших объемов данных. Логика заключается в том, чтобы для каждой строки из Excel-файла выполнять поиск в справочнике Страны.
Для этого используется метод менеджера справочника НайтиПоНаименованию(). Этот метод ищет элемент справочника по его наименованию и возвращает ссылку на найденный элемент. Если элемент не найден, метод возвращает пустую ссылку.
Посмотрим на пример:
Чтобы сделать код более читаемым и структурированным, вынесем проверку в отдельную функцию. Также учтем, что значение в ячейке может содержать лишние пробелы в начале или в конце, поэтому предварительно обработаем его функцией СокрЛП().
&НаСервере
Функция ЭтоСтрана(НаименованиеСтраны)
// Убираем лишние пробелы и проверяем, не пустая ли строка
ПроверяемоеНаименование = СокрЛП(НаименованиеСтраны);
Если ПустаяСтрока(ПроверяемоеНаименование) Тогда
Возврат Ложь;
КонецЕсли;
// Ищем элемент в справочнике "Страны" по наименованию.
// Второй параметр "Истина" означает точное совпадение.
НайденнаяСтрана = Справочники.Страны.НайтиПоНаименованию(ПроверяемоеНаименование, Истина);
// Метод Пустая() вернет Истина, если ссылка пустая (элемент не найден)
// Нам нужен обратный результат, поэтому используем "НЕ".
Возврат НЕ НайденнаяСтрана.Пустая();
КонецФункции
Теперь встроим вызов этой функции в наш основной цикл загрузки данных. Строку в таблицу значений ДанныеФайла будем добавлять только в том случае, если проверка прошла успешно.
&НаСервере
Процедура ЗагрузитьДанныеНаСервере(АдресВХ)
// ... начало процедуры без изменений ...
Для НомерСтроки = 2 По КоличествоСтрок Цикл
// Сначала получаем значение из ячейки
КластерДоставки = ТабДок.ПолучитьОбласть("R" + Формат(НомерСтроки, "ЧГ=0") + "C" + 27).ТекущаяОбласть.Текст;
// Выполняем проверку с помощью нашей новой функции
Если ЭтоСтрана(КластерДоставки) Тогда
// Если проверка успешна, добавляем строку и заполняем ее данными
СтрокаДанных = ДанныеФайла.Добавить();
СтрокаДанных.Наименование = ТабДок.ПолучитьОбласть("R" + Формат(НомерСтроки, "ЧГ=0") + "C" + 13).ТекущаяОбласть.Текст;
СтрокаДанных.ИНН = ТабДок.ПолучитьОбласть("R" + Формат(НомерСтроки, "ЧГ=0") + "C" + 14).ТекущаяОбласть.Текст;
СтрокаДанных.Телефон = ТабДок.ПолучитьОбласть("R" + Формат(НомерСтроки, "ЧГ=0") + "C" + 25).ТекущаяОбласть.Текст;
СтрокаДанных.КластерДоставки = КластерДоставки; // Используем уже полученное значение
СтрокаДанных.ТНВЭД = ТабДок.ПолучитьОбласть("R" + Формат(НомерСтроки, "ЧГ=0") + "C" + 35).ТекущаяОбласть.Текст;
КонецЕсли;
КонецЦикла;
ЗагруженныеДанные.Загрузить(ДанныеФайла);
КонецПроцедуры
Важный момент: этот подход имеет существенный недостаток — производительность. На каждой итерации цикла (для каждой строки Excel) выполняется отдельный запрос к базе данных. Если в файле тысячи или десятки тысяч строк, такая загрузка будет работать очень медленно.
Для обработки больших объемов данных следует избегать запросов к базе данных внутри циклов. Более правильный и производительный подход — сначала считать все необходимые данные из файла в память (в таблицу значений), а затем одной операцией (одним запросом) проверить все значения.
Разберем алгоритм по шагам:
ДанныеФайла без каких-либо проверок.ДанныеФайла.Страны по полю с наименованием.Посмотрим на пример реализации:
Сначала модифицируем процедуру загрузки, чтобы она просто считывала все данные в таблицу значений, без фильтрации.
&НаСервере
Процедура ЗагрузитьИОтфильтроватьДанные()
// ... код чтения файла Excel в ТаблицуЗначений "ДанныеФайла" ...
// ... цикл просто заполняет ДанныеФайла всеми строками ...
// Теперь у нас есть таблица "ДанныеФайла" со всеми данными из Excel.
// Начинаем фильтрацию.
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| тз.Наименование,
| тз.ИНН,
| тз.Телефон,
| тз.КластерДоставки,
| тз.ТНВЭД,
| Страны.Ссылка КАК СтранаСсылка
|ИЗ
| &ТаблицаИзФайла КАК тз
| ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Страны КАК Страны
| ПО тз.КластерДоставки = Страны.Наименование";
// Передаем нашу таблицу значений в качестве параметра в запрос
Запрос.УстановитьПараметр("ТаблицаИзФайла", ДанныеФайла);
РезультатЗапроса = Запрос.Выполнить();
ОтфильтрованныеДанные = РезультатЗапроса.Выгрузить();
// В таблице "ОтфильтрованныеДанные" остались только строки,
// у которых поле КластерДоставки совпало с наименованием страны.
// Строки, где совпадения не было, отсеялись, т.к. мы не использовали ВНЕШНЕЕ соединение
// или можно добавить условие "ГДЕ Страны.Ссылка ЕСТЬ НЕ NULL"
// Теперь создаем контрагентов на основе уже отфильтрованных данных
Для Каждого СтрокаОтфильтрованныхДанных Из ОтфильтрованныеДанные Цикл
НовыйЭлемент = Справочники.Контрагенты.СоздатьЭлемент();
НовыйЭлемент.Наименование = СтрокаОтфильтрованныхДанных.Наименование;
НовыйЭлемент.ИННКлиент = СтрокаОтфильтрованныхДанных.ИНН;
НовыйЭлемент.ТелефонКлиент = СтрокаОтфильтрованныхДанных.Телефон;
НовыйЭлемент.КластерДоставки = СтрокаОтфильтрованныхДанных.КластерДоставки;
НовыйЭлемент.ТНВЭД = СтрокаОтфильтрованныхДанных.ТНВЭД;
НовыйЭлемент.Записать();
КонецЦикла;
КонецПроцедуры
Этот подход на порядок производительнее, так как вся работа по сопоставлению данных выполняется на стороне сервера СУБД за один запрос, что является стандартом при работе с большими наборами данных в 1С.
ТабДок.ПолучитьОбласть() также является медленной операцией. Для максимальной производительности при работе с большими файлами рекомендуется использовать COMОбъект для взаимодействия с Excel. Это позволяет выгрузить весь используемый диапазон листа в массив данных 1С практически мгновенно, после чего обрабатывать уже этот массив.Для каждого ... Из ... старайтесь давать итератору осмысленное имя с префиксом или суффиксом, например СтрокаДанных или стрКонтрагент, а не просто Контрагент. Это помогает избежать путаницы с другими переменными и реквизитами формы, особенно если у вас есть реквизит с таким же именем.ПОДОБНО для поиска вхождения, например: ПО Страны.Наименование ПОДОБНО "%" + тз.КластерДоставки + "%" (или наоборот, в зависимости от задачи).