Как сопоставить данные из плоского файла Excel с иерархическим справочником 1С при повторяющихся наименованиях элементов?

← На главную

Нередко при автоматизации загрузки данных из внешних таблиц разработчики сталкиваются с ситуацией, когда структура исходного файла является плоской: в нем нет кодов справочников, отсутствуют встроенные механизмы группировки строк Excel, а визуальное оформление не позволяет однозначно отличить заголовок от позиции. Типичный пример — файл с бюджетом или планом затрат, где сначала идет строка статьи затрат (в 1С это группа справочника), а следом за ней перечисляются подразделения (элементы справочника) с одинаковыми именами («Управление», «Цех 1», «Ремонтный цех»).

Если выполнять поиск по наименованию напрямую для каждого элемента, платформа вернет первую попавшуюся ссылку без учета иерархии, что приведет к искажению аналитики. Разберем по шагам, как построить надежный алгоритм обработки таких данных, корректно распределить элементы по родительским папкам и оптимизировать производительность сопоставления.

Анализ исходных данных и выработка стратегии

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

Зарплата (в справочнике 1С это группа/папка верхнего уровня);
Управление (элемент справочника внутри группы «Зарплата»);
Цех 1 (элемент справочника внутри группы «Зарплата»);
Электроэнергия (следующая группа верхнего уровня);
Управление (элемент справочника с тем же наименованием, но внутри группы «Электроэнергия»);
Цех 1 (элемент справочника внутри группы «Электроэнергия»).

Главная сложность заключается в том, что имена элементов в разных ветках справочника дублируются, но наименования самих групп верхнего уровня уникальны. Соответственно, мы можем использовать группы в качестве опорных точек (контекстных маркеров).

Очистка и нормализация строк перед сопоставлением

Выясним частую причину сбоев при сопоставлении названий: невидимые спецсимволы. В файлах Excel часто встречаются неразрывные пробелы с кодом 160, которые визуально неотличимы от стандартного пробела, но функция СокрЛП() их не удаляет. Из-за этого платформа не находит даже уникальные наименования папок.

Перед анализом данных подготовим вспомогательную функцию нормализации текста:


Функция НормализоватьСтроку(ИсходныйТекст)
    Текст = СтрЗаменить(ИсходныйТекст, Символ(160), " ");
    Текст = СтрЗаменить(Текст, Символы.ПС, "");
    Текст = СтрЗаменить(Текст, Символы.ВК, "");
    Возврат СокрЛП(Текст);
КонецФункции

Пошаговый алгоритм сопоставления

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

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

Оптимизация выборки: кэширование и контекстный поиск

Если обращаться к методу Справочники.СтатьиЗатрат.НайтиПоНаименованию() на каждой строке файла, производительность обработки резко упадет из-за постоянных запросов к СУБД. Кроме того, при наличии дублей внутри одной группы метод без предупреждения вернет только первую найденную ссылку. Проанализируем, как сформировать структуру соответствий через один запрос:


// Сформируем карту групп и подчиненных элементов
Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |    Спр.Ссылка КАК Ссылка,
    |    Спр.Наименование КАК Наименование,
    |    Спр.ЭтоГруппа КАК ЭтоГруппа,
    |    Спр.Родитель КАК Родитель
    |ИЗ
    |    Справочник.СтатьиЗатрат КАК Спр
    |ГДЕ
    |    НЕ Спр.ПометкаУдаления";

РезультатЗапроса = Запрос.Выполнить().Выгрузить();

// Карта групп верхнего уровня (Ключ = Наименование, Значение = Ссылка)
КартаГрупп = Новый Соответствие;
// Таблица подчиненных элементов для поиска с учетом родителя
ТаблицаЭлементов = Новый ТаблицаЗначений;
ТаблицаЭлементов.Колонки.Добавить("Ссылка", Новый ОписаниеТипов("СправочникСсылка.СтатьиЗатрат"));
ТаблицаЭлементов.Колонки.Добавить("Наименование", Новый ОписаниеТипов("Строка", , Новый КвалификаторыСтроки(150)));
ТаблицаЭлементов.Колонки.Добавить("Родитель", Новый ОписаниеТипов("СправочникСсылка.СтатьиЗатрат"));
ТаблицаЭлементов.Индексы.Добавить("Родитель, Наименование");

Для Каждого Стр Из РезультатЗапроса Цикл
    Если Стр.ЭтоГруппа И Стр.Родитель.Пустая() Тогда
        КартаГрупп.Вставить(ВРег(СокрЛП(Стр.Наименование)), Стр.Ссылка);
    ИначеЕсли НЕ Стр.ЭтоГруппа Тогда
        Нов = ТаблицаЭлементов.Добавить();
        Нов.Ссылка = Стр.Ссылка;
        Нов.Наименование = ВРег(СокрЛП(Стр.Наименование));
        Нов.Родитель = Стр.Родитель;
    КонецЕсли;
КонецЦикла;

Программный обход таблицы загрузки

Теперь реализуем логику «конечного автомата»: сохраняем текущего родителя и ищем подчиненные элементы строго внутри контекста этой группы.


// ТаблицаЗагрузки содержит колонки: ИсходныйТекст, ПланГод, ПланМесяц, Факт
ТаблицаЗагрузки.Колонки.Добавить("ТекущийРодитель");
ТаблицаЗагрузки.Колонки.Добавить("Ссылка1С");
ТаблицаЗагрузки.Колонки.Добавить("ЭтоГруппа", Новый ОписаниеТипов("Булево"));

ТекущаяГруппаСсылка = Справочники.СтатьиЗатрат.ПустаяСсылка();
ОтборПоиска = Новый Структура("Родитель, Наименование");

Для Каждого СтрФайла Из ТаблицаЗагрузки Цикл
    ТекстОчищенный = НормализоватьСтроку(СтрФайла.ИсходныйТекст);
    ТекстКлюч = ВРег(ТекстОчищенный);
    
    // Проверим, не является ли строка новой группой
    НайденнаяГруппа = КартаГрупп[ТекстКлюч];
    
    Если НайденнаяГруппа <> Неопределено Тогда
        // Зафиксировали смену контекста группы
        ТекущаяГруппаСсылка = НайденнаяГруппа;
        СтрФайла.ЭтоГруппа = Истина;
        СтрФайла.Ссылка1С = НайденнаяГруппа;
    Иначе
        // Строка считается подчиненным элементом текущей группы
        СтрФайла.ЭтоГруппа = Ложь;
        СтрФайла.ТекущийРодитель = ТекущаяГруппаСсылка;
        
        Если ЗначениеЗаполнено(ТекущаяГруппаСсылка) Тогда
            ОтборПоиска.Родитель = ТекущаяГруппаСсылка;
            ОтборПоиска.Наименование = ТекстКлюч;
            
            СтрокиПоиска = ТаблицаЭлементов.НайтиСтроки(ОтборПоиска);
            Если СтрокиПоиска.Количество() > 0 Тогда
                СтрФайла.Ссылка1С = СтрокиПоиска[0].Ссылка;
            КонецЕсли;
        КонецЕсли;
    КонецЕсли;
КонецЦикла;

Дополнительные признаки распознавания структуры Excel

Если визуально файл выглядит плоским, рекомендуем программно проверить неочевидные свойства ячеек Excel при чтении через COM-соединение:

Свойство IndentLevel: в некоторых файлах иерархия задается отступом ячейки. Проверьте целочисленное значение отступа — строки-группы часто имеют нулевой уровень вложенности, а элементы — значение от 1 и выше.
Свойство HasFormula: строки-папки часто содержат расчетные формулы суммирования подчиненных строк (функция СУММ), в то время как подчиненные строки заполнены прямыми числовыми значениями. Наличие формулы в числовой колонке служит надежным индикатором группы.

Организация ручного досопоставления и кэширование

После завершения автоматического сопоставления отфильтруем строки, где колонка Ссылка1С осталась незаполненной, и выведем их пользователю для ручного выбора. Чтобы оператору не приходилось заново сопоставлять одни и те же ошибочные наименования при каждой загрузке, сохраняйте результат ручного выбора в отдельный регистр сведений соответствий. В качестве измерений используйте текст из файла и найденную группу-родителя, а в качестве ресурса — выбранную ссылку 1С.

Описанный подход позволяет свести рутинную ручную работу к минимуму и гарантирует точную загрузку данных в иерархический справочник даже из нестандартно отформатированных файлов Excel.