Часто в практике разработчика 1С возникает задача: имеется список наименований контрагентов в файле Excel, и необходимо быстро выгрузить их коды или другие реквизиты из базы 1С. При этом критически важно сохранить ту же последовательность строк, которая была в исходном файле. Просто выполнить запрос с условием ГДЕ Наименование В (&СписокИмен) недостаточно, так как СУБД возвращает результат в произвольном порядке. Если вам нужно готовое пользовательское решение без программирования, подойдет универсальная загрузка из Excel или общий импорт данных (доступные форматы: *.xls, *.csv и др.), но в данной статье мы разберем комплексный программный подход к решению этой задачи: от чтения файла до написания оптимального запроса.
Прежде всего, нам необходимо перенести данные из файла в среду 1С. Рассмотрим наиболее современный и быстрый способ, не требующий установки Microsoft Office на сервере — использование объекта ТабличныйДокумент. С версии платформы 8.3.6 этот метод работает стабильно и быстро. В качестве основы для своего кода вы можете взять шаблон простой обработки загрузки из excel или воспользоваться готовой библиотекой для загрузки файлов Excel и CSV.
Проанализируем программный код для загрузки данных. Нам важно не просто прочитать строки, но и сразу подготовить структуру для передачи в запрос. Для сохранения порядка мы добавим вспомогательную колонку НомерСтроки.
// Создаем таблицу значений для хранения данных из файла
ТЗ_Excel = Новый ТаблицаЗначений;
// Обязательно типизируем колонки для корректной работы запроса
МассивТиповСтрока = Новый ОписаниеТипов("Строка",,,, Новый КвалификаторыСтроки(150));
МассивТиповЧисло = Новый ОписаниеТипов("Число",,,, Новый КвалификаторыЧисла(10, 0));
ТЗ_Excel.Колонки.Добавить("НаименованиеИзФайла", МассивТиповСтрока);
ТЗ_Excel.Колонки.Добавить("Порядок", МассивТиповЧисло);
// Читаем файл
ТабДок = Новый ТабличныйДокумент;
Попытка
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
Сообщить("Не удалось прочитать файл: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
// Обходим строки табличного документа
Для НомСтр = 1 По ТабДок.ВысотаТаблицы Цикл
ТекстНаименования = ТабДок.Область(НомСтр, 1).Текст;
// Очищаем данные от лишних пробелов и невидимых символов
ТекстНаименования = СокрЛП(СтрЗаменить(ТекстНаименования, Символы.НПП, " "));
Если ЗначениеЗаполнено(ТекстНаименования) Тогда
НоваяСтрока = ТЗ_Excel.Добавить();
НоваяСтрока.НаименованиеИзФайла = ТекстНаименования;
НоваяСтрока.Порядок = НомСтр; // Сохраняем исходную позицию
КонецЕсли;
КонецЦикла;
Разберем подробнее, почему в коде выше мы использовали ОписаниеТипов и очистку строк. Если передать в запрос таблицу значений, колонки которой не имеют четкого типа (тип "Произвольный"), запрос может завершиться ошибкой "Несовместимые типы". Платформа должна точно знать длину строки и параметры чисел, чтобы корректно построить временную таблицу в СУБД.
Проанализируем ситуацию с "грязными" данными. В Excel часто встречаются неразрывные пробелы (код 160). Если их не заменить на обычные пробелы функцией СтрЗаменить(Текст, Символы.НПП, " "), то при соединении в запросе 1С не найдет соответствия, даже если визуально названия идентичны. В сложных случаях, когда в файле много опечаток, может помочь алгоритм поиска по схожести наименования, но в рамках стандартного запроса использование СокрЛП() и очистка спецсимволов являются обязательным минимумом.
Теперь, когда у нас есть подготовленная ТаблицаЗначений, мы передадим её в запрос как параметр. Наша цель — соединить эту таблицу со справочником Контрагенты и упорядочить результат по колонке Порядок.
Посмотрим на пример текста запроса:
ВЫБРАТЬ
ВременнаяТаблицаExcel.НаименованиеИзФайла КАК НаименованиеИзФайла,
ВременнаяТаблицаExcel.Порядок КАК Порядок,
СправочникКонтрагенты.Ссылка КАК Ссылка,
СправочникКонтрагенты.Код КАК Код,
СправочникКонтрагенты.Наименование КАК НаименованиеВБазе
ПОМЕСТИТЬ ВТ_ДанныеExcel
ИЗ
&ТЗ_Excel КАК ВременнаяТаблицаExcel
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ.НаименованиеИзФайла КАК ИсходноеИмя,
ВТ.Код КАК КодКонтрагента,
ВТ.НаименованиеВБазе КАК ИмяВБазе
ИЗ
ВТ_ДанныеExcel КАК ВТ
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Контрагенты КАК СправочникКонтрагенты
ПО ВТ.НаименованиеИзФайла = СправочникКонтрагенты.Наименование
ГДЕ
НЕ СправочникКонтрагенты.ЭтоГруппа // Исключаем группы, если нужно
УПОРЯДОЧИТЬ ПО
ВТ.Порядок
Выясним причину использования ЛЕВОГО СОЕДИНЕНИЯ. Если мы используем просто СОЕДИНЕНИЕ (внутреннее), то в итоговую таблицу попадут только те контрагенты, которые были найдены в базе. Если контрагент в Excel есть, а в 1С его нет — строка просто исчезнет. При использовании ЛЕВОЕ СОЕДИНЕНИЕ строка из Excel останется в результате, а в полях Код и Ссылка будет значение NULL, что позволит нам легко идентифицировать отсутствующие в базе записи.
Если в вашем файле Excel не 2000, а, например, 20 000 строк, запрос может начать работать медленно. Чтобы ускорить процесс сопоставления, необходимо проиндексировать временную таблицу по полю, по которому происходит связь.
Рассмотрим добавление индекса в запрос:
ВЫБРАТЬ
ВременнаяТаблицаExcel.НаименованиеИзФайла КАК НаименованиеИзФайла,
ВременнаяТаблицаExcel.Порядок КАК Порядок
ПОМЕСТИТЬ ВТ_ДанныеExcel
ИЗ
&ТЗ_Excel КАК ВременнаяТаблицаExcel
ИНДЕКСИРОВАТЬ ПО
НаименованиеИзФайла
;
Индексация позволяет СУБД мгновенно находить нужные строки, не перебирая всю таблицу целиком при каждом сравнении. Это стандарт "хорошего тона" при работе с временными таблицами.
Если вам нужно решить задачу разово и вы не хотите писать программный код, можно воспользоваться продвинутыми консолями запросов, например, функциональной консолью запросов УФ (мод от Dr.Zombi) или типовой консолью из состава инструментов разработчика. Многие из них поддерживают функцию "Загрузить таблицу из файла".
ТЗ и типом ТаблицаЗначений.&ТЗ, как было показано выше.Этот способ экономит время на написание кода чтения файла, предоставляя при этом всю мощь механизмов запросов 1С для сопоставления данных.
Подведем итоги нашего анализа. Для корректного получения данных из справочника с сохранением порядка необходимо выполнить три ключевых условия:
Порядок еще на этапе загрузки данных из Excel.ТаблицыЗначений в запрос.УПОРЯДОЧИТЬ ПО, так как без этой инструкции порядок записей в результате не гарантирован.Соблюдение этих правил обеспечит надежную работу вашего алгоритма и точность получаемых данных.