Рассмотрим часто встречающуюся задачу при интеграции или переносе данных: нам необходимо загрузить список договоров из файла Excel. При этом важно отсеять те договоры, которые уже существуют в базе 1С, чтобы не наплодить дублей. Идентификация происходит по связке Контрагент + Наименование договора.
Многие начинающие разработчики пытаются решить эту задачу "в лоб", обходя строки Excel в цикле и делая запрос к базе на каждой итерации. Проанализируем, почему этот подход ошибочен, и разберем по шагам, как написать оптимальный, быстрый и красивый код.
Давайте посмотрим на распространенные ошибки, которые мы увидели в первоначальном варианте кода, и выясним причины, по которым так делать не стоит:
Контрагентй, содержащая ссылку на справочник, передается в параметр, который в условии запроса сравнивается с наименованием (строкой): ГДЕ ДоговорыКонтрагентов.Владелец.Наименование = &Наименование. Это приведет к неверному результату.НайтиПоНаименованию на каждой строке также сильно тормозит процесс.Если строк в Excel совсем мало (например, до 50), можно обойтись без сложных запросов, используя встроенные возможности платформы для поиска по справочнику с учетом владельца. Разберем этот пример:
Для СтрокаОтсчета = 2 по КоличествоСтрок Цикл
ДоговорНаименование = Excel.Cells(СтрокаОтсчета, 21).Text;
КонтрагентНаименование = Excel.Cells(СтрокаОтсчета, 23).Text;
// Ищем контрагента
КонтрагентСсылка = Справочники.Контрагенты.НайтиПоНаименованию(КонтрагентНаименование, Истина);
Если ЗначениеЗаполнено(КонтрагентСсылка) Тогда
// Ищем договор с указанием владельца
ДоговорСсылка = Справочники.ДоговорыКонтрагентов.НайтиПоНаименованию(ДоговорНаименование, Истина, , КонтрагентСсылка);
Если ДоговорСсылка.Пустая() Тогда
// Создаем новый договор
НовДоговор = Справочники.ДоговорыКонтрагентов.СоздатьЭлемент();
НовДоговор.Владелец = КонтрагентСсылка;
НовДоговор.Наименование = ДоговорНаименование;
// ... заполнение остальных реквизитов ...
НовДоговор.Записать();
КонецЕсли;
КонецЕсли;
КонецЦикла;
Этот код уже лучше, так как избавляет от громоздкого запроса в цикле, но он всё еще не оптимален для больших объемов данных.
Давайте разберем по шагам, как пишут подобные загрузки опытные разработчики. Наш алгоритм будет состоять из трех этапов: подготовка данных из файла, пакетный поиск в базе и создание недостающих элементов.
Шаг 1. Чтение данных из Excel и свертка дублей
Сначала мы создадим ТаблицаЗначений, прочитаем в нее данные из файла, а затем обязательно удалим дублирующиеся строки внутри самого файла. Если в файле дважды встретится один и тот же новый договор, без свертки мы создадим его два раза.
// Создаем структуру ТЗ
ОписаниеТиповС = Новый ОписаниеТипов("Строка", , , Новый КвалификаторыСтроки(150));
ТЗДанные = Новый ТаблицаЗначений;
ТЗДанные.Колонки.Добавить("КонтрагентНаименование", ОписаниеТиповС);
ТЗДанные.Колонки.Добавить("ДоговорНаименование", ОписаниеТиповС);
// Заполняем ТЗ данными из Excel (в цикле)
Для СтрокаОтсчета = 2 по КоличествоСтрок Цикл
НовСтрока = ТЗДанные.Добавить();
НовСтрока.КонтрагентНаименование = СокрЛП(Excel.Cells(СтрокаОтсчета, 23).Text);
НовСтрока.ДоговорНаименование = СокрЛП(Excel.Cells(СтрокаОтсчета, 21).Text);
КонецЦикла;
// Обязательно сворачиваем для удаления дублей внутри самого файла
ТЗДанные.Свернуть("КонтрагентНаименование, ДоговорНаименование");
Шаг 2. Пакетный поиск существующих договоров через запрос
Теперь передадим нашу ТаблицаЗначений в запрос в качестве параметра. Мы поместим её во временную таблицу и с помощью ЛЕВОГО СОЕДИНЕНИЯ найдем, какие контрагенты и договоры уже есть в базе.
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ДанныеФайла.КонтрагентНаименование КАК КонтрагентНаименование,
| ДанныеФайла.ДоговорНаименование КАК ДоговорНаименование
|ПОМЕСТИТЬ ВТ_ДанныеИзExcel
|ИЗ
| &ТаблицаДанных КАК ДанныеФайла
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВТ_ДанныеИзExcel.КонтрагентНаименование КАК ИмяКонтрагентаВФайле,
| ВТ_ДанныеИзExcel.ДоговорНаименование КАК ИмяДоговораВФайле,
| Контрагенты.Ссылка КАК КонтрагентСсылка,
| ДоговорыКонтрагентов.Ссылка КАК ДоговорСсылка
|ИЗ
| ВТ_ДанныеИзExcel КАК ВТ_ДанныеИзExcel
| ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Контрагенты КАК Контрагенты
| ПО ВТ_ДанныеИзExcel.КонтрагентНаименование = Контрагенты.Наименование
| ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ДоговорыКонтрагентов КАК ДоговорыКонтрагентов
| ПО ВТ_ДанныеИзExcel.ДоговорНаименование = ДоговорыКонтрагентов.Наименование
| И (ДоговорыКонтрагентов.Владелец = Контрагенты.Ссылка)";
Запрос.УстановитьПараметр("ТаблицаДанных", ТЗДанные);
РезультатЗапроса = Запрос.Выполнить();
Выборка = РезультатЗапроса.Выбрать();
Шаг 3. Обработка результатов и создание новых элементов
Проанализируем результаты выборки. Нам нужно создать договор только в том случае, если контрагент найден, а договор — нет (ДоговорСсылка ЕСТЬ NULL).
Пока Выборка.Следующий() Цикл
Если ЗначениеЗаполнено(Выборка.КонтрагентСсылка) Тогда
Если НЕ ЗначениеЗаполнено(Выборка.ДоговорСсылка) Тогда
// Договора нет, создаем новый
НовДоговор = Справочники.ДоговорыКонтрагентов.СоздатьЭлемент();
НовДоговор.Владелец = Выборка.КонтрагентСсылка;
НовДоговор.Наименование = Выборка.ИмяДоговораВФайле;
// ... заполняем валюту, вид договора ...
НовДоговор.Записать();
КонецЕсли;
Иначе
Сообщить("В базе не найден контрагент: " + Выборка.ИмяКонтрагентаВФайле);
КонецЕсли;
КонецЦикла;
Рассмотрим несколько важных практик, которые сделают вашу обработку загрузки пуленепробиваемой:
НачатьТранзакцию() перед циклом и ЗафиксироватьТранзакцию() после). Это ускорит запись в десятки раз.Quit() для приложения Excel и очищать переменные. Иначе в диспетчере задач скопятся "зависшие" процессы, которые съедят всю оперативную память. А еще лучше — читайте Excel штатными средствами 1С через ТабличныйДокумент.