При решении задач автоматизации и построения аналитической отчетности в среде «1С:Предприятие 8.3» программисты часто сталкиваются с необходимостью выгрузки справочника Номенклатура в плоскую таблицу для последующего анализа в Microsoft Excel. Стандартное иерархическое представление в виде дерева групп и элементов отлично подходит для интерфейса программы, но крайне неудобно для работы в электронных таблицах. Конечные пользователи хотят видеть плоский список, в котором каждая родительская группа (от верхнего уровня до непосредственного родителя) расположена в своем собственном отдельном столбце. Например: Группа 1 | Группа 1.1 | Товар 1.
В этой статье мы детально проанализируем различные способы решения этой задачи, оценим их производительность, разберем конкретные алгоритмы и предоставим готовые примеры кода, готовые к внедрению в ваши конфигурации (например, в «1С:Розница 2.2» или любую другую систему на управляемых формах).
Рассмотрим наиболее производительный и универсальный подход, который решает задачу за один запрос к базе данных без рекурсивного перебора объектов. Этот метод отлично подходит для справочников с произвольной, заранее неизвестной глубиной вложенности групп.
Основой данного метода является использование конструкции запроса ИТОГИ ПО ... ТОЛЬКО ИЕРАРХИЯ. СУБД возвращает выборку данных, в которой группы и элементы упорядочены по их иерархическому положению. Для распределения названий групп по колонкам мы применим специальную структуру данных — стек (на базе объекта Массив). В процессе обхода выборки мы будем отслеживать уровень вложенности записи с помощью метода Выборка.Уровень(). Если уровень увеличивается, мы заносим имя группы в стек. Если уменьшается — удаляем элементы из стека до текущего уровня. При позиционировании на детальной записи (товаре) мы выводим накопленный стек в ячейки табличного документа.
Разберем по шагам программный код, реализующий этот алгоритм на стороне сервера:
&НаСервереБезКонтекста
Функция ПолучитьИерархиюВСтолбцы() Экспорт
// Создаем объект ТабличныйДокумент, который в дальнейшем сохраним в Excel
ДокументРезультат = Новый ТабличныйДокумент;
// Инициализируем запрос к базе данных
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Ссылка КАК Ссылка,
| Номенклатура.Представление КАК Представление
|ИЗ
| Справочник._ДемоНоменклатура КАК Номенклатура
|ГДЕ
| Номенклатура.ЭтоГруппа = ЛОЖЬ
|ИТОГИ ПО
| Ссылка ТОЛЬКО ИЕРАРХИЯ
|АВТОУПОРЯДОЧИВАНИЕ";
// Выполняем запрос и получаем выборку
Выборка = Запрос.Выполнить().Выбрать();
НомерСтроки = 0;
ТекущийУровень = 0;
СтекРодителей = Новый Массив;
// Обходим выборку последовательно
Пока Выборка.Следующий() Цикл
УровеньЗаписи = Выборка.Уровень;
// Шаг 1. Корректируем стек родителей в зависимости от уровня вложенности текущей записи
Пока УровеньЗаписи < ТекущийУровень Цикл
ТекущийУровень = ТекущийУровень - 1;
СтекРодителей.Удалить(ТекущийУровень);
КонецЦикла;
// Шаг 2. Анализируем тип текущей записи в выборке
Если Выборка.ТипЗаписи = ТипЗаписиЗапроса.ДетальнаяЗапись Тогда
// Если перед нами товар (детальная запись), увеличиваем счетчик строк табличного документа
НомерСтроки = НомерСтроки + 1;
// Выводим все родительские группы из стека в отдельные колонки
Для НомерКолонки = 1 По ТекущийУровень Цикл
Ячейка = ДокументРезультат.Область(НомерСтроки, НомерКолонки);
Ячейка.Текст = СтекРодителей[НомерКолонки - 1];
КонецЦикла;
// Выводим представление самого элемента в следующую за родителем колонку
ЯчейкаЭлемента = ДокументРезультат.Область(НомерСтроки, ТекущийУровень + 1);
ЯчейкаЭлемента.Текст = Выборка.Представление;
Иначе
// Если перед нами группа, фиксируем ее представление в стеке и увеличиваем текущий уровень
ТекущийУровень = ТекущийУровень + 1;
СтекРодителей.Добавить(Выборка.Представление);
КонецЕсли;
КонецЦикла;
Возврат ДокументРезультат;
КонецФункции
Обратим внимание на работу свойства Выборка.ТипЗаписи. Оно принимает значение из системного перечисления ТипЗаписиЗапроса. Когда мы обходим группировки иерархии, свойство равно значению ТипЗаписиЗапроса.ИтогПоГруппировке, что позволяет нам вовремя наполнять массив-стек актуальными именами родительских папок. Этот алгоритм работает со скоростью прямого чтения из СУБД и потребляет минимальное количество оперативной памяти, так как в памяти единовременно хранится только цепочка родителей текущего элемента, а не вся иерархия справочника.
Выясним, как упростить решение, если в вашей базе данных структура иерархии справочника строго ограничена и известна заранее (например, глубина вложенности папок не превышает 3 уровней). В этом случае мы можем отказаться от сложного анализа выборки и написать простой плоский запрос, в котором обратимся к родителям разной степени вложенности напрямую через точку.
Посмотрим на пример такого запроса, рассчитанного на максимальную вложенность в 3 уровня:
&НаСервереБезКонтекста
Функция ПолучитьПлоскуюТаблицуФиксированнойИерархии() Экспорт
ТабличныйДокумент = Новый ТабличныйДокумент;
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ВЫБОР
| КОГДА Номенклатура.Родитель.Родитель.Родитель.Ссылка ЕСТЬ NULL ТОГДА
| ВЫБОР
| КОГДА Номенклатура.Родитель.Родитель.Ссылка ЕСТЬ NULL ТОГДА
| ВЫБОР КОГДА Номенклатура.Родитель.Ссылка ЕСТЬ NULL ТОГДА """" ИНАЧЕ Номенклатура.Родитель.Представление КОНЕЦ
| ИНАЧЕ Номенклатура.Родитель.Родитель.Представление
| КОНЕЦ
| ИНАЧЕ Номенклатура.Родитель.Родитель.Родитель.Представление
| КОНЕЦ КАК Группа1,
| ВЫБОР
| КОГДА Номенклатура.Родитель.Родитель.Родитель.Ссылка ЕСТЬ NULL ТОГДА
| ВЫБОР
| КОГДА Номенклатура.Родитель.Родитель.Ссылка ЕСТЬ NULL ТОГДА """"
| ИНАЧЕ Номенклатура.Родитель.Представление
| КОНЕЦ
| ИНАЧЕ Номенклатура.Родитель.Родитель.Представление
| КОНЕЦ КАК Группа2,
| ВЫБОР
| КОГДА Номенклатура.Родитель.Родитель.Родитель.Ссылка ЕСТЬ NULL ТОГДА """"
| ИНАЧЕ Номенклатура.Родитель.Представление
| КОНЕЦ КАК Группа3,
| Номенклатура.Ссылка КАК Ссылка,
| Номенклатура.Представление КАК НоменклатураНаименование
|ИЗ
| Справочник.Номенклатура КАК Номенклатура
|ГДЕ
| Номенклатура.ЭтоГруппа = ЛОЖЬ";
ТаблицаДанных = Запрос.Выполнить().Выгрузить();
// Выводим полученную плоскую таблицу в табличный документ
Построитель = Новый ПостроительОтчета;
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТаблицаДанных);
Построитель.Вывести(ТабличныйДокумент);
Возврат ТабличныйДокумент;
КонецФункции
Проанализируем этот подход. Конструкция ВЫБОР КОГДА... позволяет корректно распределить названия групп по колонкам начиная с левого края (с самого верхнего уровня иерархии). Если элемент лежит непосредственно в корне справочника, все колонки групп останутся пустыми. Главное преимущество данного способа — возможность моментальной выгрузки результата запроса в ТаблицаЗначений и последующий автоматический вывод с помощью объекта ПостроительОтчета. Недостаток очевиден: при добавлении пользователями нового уровня вложенности структура данных нарушится, и самые глубокие группы не попадут в отчет.
Чтобы преодолеть ограничение фиксированной вложенности и сохранить скорость выполнения плоских запросов к СУБД, мы можем применить динамическое конструирование текста запроса. Перед формированием отчета мы программно определим фактический максимальный уровень вложенности элементов справочника Номенклатура.
Давайте разберем этот процесс детально:
Уровень среди всех элементов, не являющихся группами.Родитель.Родитель...).Реализуем данный алгоритм в коде 1С:
&НаСервереБезКонтекста
Функция ПолучитьДинамическуюТаблицуИерархии() Экспорт
// Определяем максимальный уровень вложенности элементов в ИБ
ЗапросМаксУровень = Новый Запрос;
ЗапросМаксУровень.Текст =
"ВЫБРАТЬ
| МАКСИМУМ(Номенклатура.Уровень) КАК МаксУровень
|ИЗ
| Справочник.Номенклатура КАК Номенклатура
|ГДЕ
| Номенклатура.ЭтоГруппа = ЛОЖЬ";
ВыборкаМакс = ЗапросМаксУровень.Выполнить().Выбрать();
МаксимальныйУровень = 0;
Если ВыборкаМакс.Следующий() И ЗначениеЗаполнено(ВыборкаМакс.МаксУровень) Тогда
МаксимальныйУровень = ВыборкаМакс.МаксУровень;
КонецЕсли;
Если МаксимальныйУровень = 0 Тогда
// Справочник пуст или не имеет групп, выводим стандартный запрос
Возврат Новый ТабличныйДокумент;
КонецЕсли;
// Динамически собираем текст полей выбора для запроса
СтрокаПолейЗапроса = "";
Для ТекущийШаг = 1 По МаксимальныйУровень Цикл
// Формируем цепочку обращений через точки
ПутьКРодителю = "Номенклатура";
Для Глубина = 1 По (МаксимальныйУровень - ТекущийШаг + 1) Цикл
ПутьКРодителю = ПутьКРодителю + ".Родитель";
КонецЦикла;
СтрокаПолейЗапроса = СтрокаПолейЗапроса +
"ВЫБОР КОГДА " + ПутьКРодителю + ".Ссылка ЕСТЬ NULL ТОГДА """" ИНАЧЕ " + ПутьКРодителю + ".Представление КОНЕЦ КАК Группа" + ТекущийШаг + ", " + Символы.ПС;
КонецЦикла;
// Собираем итоговый текст запроса
ТекстИтоговогоЗапроса =
"ВЫБРАТЬ" + Символы.ПС +
СтрокаПолейЗапроса +
" Номенклатура.Ссылка КАК Ссылка,
| Номенклатура.Представление КАК Товар
|ИЗ
| Справочник.Номенклатура КАК Номенклатура
|ГДЕ
| Номенклатура.ЭтоГруппа = ЛОЖЬ";
ЗапросРезультат = Новый Запрос(ТекстИтоговогоЗапроса);
ТаблицаДанных = ЗапросРезультат.Выполнить().Выгрузить();
ТабличныйДокумент = Новый ТабличныйДокумент;
Построитель = Новый ПостроительОтчета;
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТаблицаДанных);
Построитель.Вывести(ТабличныйДокумент);
Возврат ТабличныйДокумент;
КонецФункции
Данное решение сочетает в себе максимальную гибкость и высокую скорость работы СУБД. Нам не нужно вручную прописывать жесткие ограничения на глубину иерархии — программа сама подстроится под любые изменения структуры каталога товаров. При необходимости можно использовать инструмент для интерактивного формирования текста запроса для получения родителей всех уровней иерархии.
Если отчет планируется использовать непосредственно в интерфейсе программы 1С с возможностью интерактивной настройки пользователем, лучшим решением будет применение Системы компоновки данных (СКД). Нам не потребуется писать сложные программные циклы обхода, так как СКД имеет развитые встроенные механизмы для работы с иерархическими связями.
Для настройки такого отчета выполним следующие шаги:
Ссылка, приемником — поле Родитель. В параметре «Условие связи» укажем соответствие родительских связей.УровеньРодителя. В качестве выражения этого поля применим специальную агрегатную функцию СКД: ВычислитьВыражение("Номенклатура.Представление", , "Иерархия"). Эта функция позволяет СКД автоматически обращаться к иерархическому дереву и извлекать родителей для каждой детальной строки.Использование СКД сводит объем написания ручного программного кода к минимуму. Система самостоятельно сформирует оптимальный пакет запросов и выведет готовую иерархию в табличный документ, который пользователь сможет выгрузить в Excel одной кнопкой.
В редких случаях технические или производственные справочники в информационных базах могут обладать экстремально глубокой вложенностью (десятки и сотни уровней). Попытка выполнить рекурсивный обход в памяти или сформировать гигантский динамический запрос на сотни колонок может привести к переполнению стека сервера 1С или аварийному завершению сессии СУБД.
Для решения подобных специфических задач мы рекомендуем использовать метод пакетной выборки порциями. При обходе элементов мы запрашиваем информацию о родителях ограниченными пакетами (например, по 5 уровней за раз): Номенклатура.Родитель, Номенклатура.Родитель.Родитель и так далее. Если на крайнем уровне обнаруживается непустая ссылка на вышестоящую группу, мы выполняем дополнительный шаг цикла, передавая этот элемент в качестве новой опорной точки. Это гарантирует стабильное и предсказуемое потребление ресурсов оперативной памяти даже на базах данных с экстремально сложной структурой хранения информации.
После того как мы сформировали объект ТабличныйДокумент одним из вышеописанных способов (например, Способом 1), нам необходимо предоставить пользователю возможность сохранить файл. Рассмотрим, как программно записать полученную таблицу на диск в формате XLSX:
&НаКлиенте
Процедура СохранитьТаблицуВExcel(ТабличныйДокумент, ПутьДляСохранения)
// Проверяем заполненность табличного документа
Если ТабличныйДокумент.ВысотаТаблицы = 0 Тогда
Предупреждение("Таблица пуста. Нечего сохранять!");
Возврат;
КонецЕсли;
Попытка
// Записываем документ в формате Excel 2007+ (XLSX)
ТабличныйДокумент.Записать(ПутьДляСохранения, ТипФайлаТабличногоДокумента.XLSX);
ПоказатьПредупреждение(, "Файл успешно сохранен по пути: " + ПутьДляСохранения);
Исключение
ВызватьИсключение "Не удалось сохранить файл! Ошибка: " + ОписаниеОшибки();
КонецПопытки;
КонецПроцедуры
Использование встроенного метода Записать() с параметром ТипФайлаТабличногоДокумента.XLSX позволяет платформе 1С генерировать файлы, полностью совместимые с современными версиями табличных процессоров, без необходимости установки Microsoft Excel на стороне сервера или клиента. Если требуется готовое решение без программирования, подойдёт обработка для экспорта реквизитов справочников в Excel с фильтрами.
Для облегчения выбора оптимального подхода приведем сравнительный анализ всех рассмотренных методов:
Рассмотрев все возможные варианты, мы рекомендуем использовать Способ 1 (Иерархический запрос со стеком) в качестве основного стандарта разработки, так как он обеспечивает наилучший баланс между гибкостью, производительностью СУБД и потреблением оперативной памяти сервера приложений.