Зачастую перед разработчиком или аналитиком 1С встает задача сопоставить данные, которые приходят из внешних источников, например, из таблиц Excel. Использование Системы компоновки данных (СКД) для этой цели — отличное решение, так как оно позволяет задействовать мощные механизмы связей, группировок и оформления отчетов, привычные пользователям 1С. В этой статье мы подробно разберем, как загрузить два файла Excel, преобразовать их в программные объекты и объединить в одном отчете.
Поскольку СКД напрямую не «читает» файлы .xlsx или .xls как таблицы базы данных в реальном времени, нам необходимо построить мост между внешним файлом и схемой компоновки. Рассмотрим поэтапный план наших действий:
ТаблицаЗначений.Существует несколько способов прочитать Excel. Разберем наиболее современный и быстрый метод с использованием объекта ТабличныйДокумент, который не требует установленного Microsoft Office на сервере, а также классический метод через COM-объект.
Метод с использованием Табличного Документа (рекомендуемый):
Проанализируем ситуацию: метод Прочитать() объекта ТабличныйДокумент позволяет напрямую загрузить содержимое файла. Это работает быстро и стабильно.
Функция ПрочитатьExcelВТаблицу(ПутьКФайлу)
ТабДок = Новый ТабличныйДокумент;
Попытка
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
Сообщить("Ошибка при чтении файла: " + ОписаниеОшибки());
Возврат Неопределено;
КонецПопытки;
ТЗ = Новый ТаблицаЗначений;
// Допустим, в файле две колонки: Ключ и Значение
ТЗ.Колонки.Добавить("КлючСопоставления", Новый ОписаниеТипов("Строка"));
ТЗ.Колонки.Добавить("Показатель", Новый ОписаниеТипов("Число"));
// Проходим по строкам табличного документа
Для НомерСтроки = 1 По ТабДок.ВысотаТаблицы Цикл
НоваяСтрока = ТЗ.Добавить();
НоваяСтрока.КлючСопоставления = ТабДок.Область(НомерСтроки, 1).Текст;
НоваяСтрока.Показатель = Число(ТабДок.Область(НомерСтроки, 2).Текст);
КонецЦикла;
Возврат ТЗ;
КонецФункции
Важный момент: При загрузке данных крайне желательно очищать строки от лишних пробелов с помощью функции СокрЛП(), иначе сопоставление в СКД может не сработать из-за невидимых символов.
Теперь выясним, как подготовить сам отчет. В конфигураторе создадим новый отчет и откроем схему компоновки данных.
ТаблицаФайл1. В поле «Имя объекта содержащего данные» впишем точно такое же название.ТаблицеЗначений (например, КлючСопоставления и Показатель1).ТаблицаФайл2 с полями КлючСопоставления и Показатель2.Разберем настройку связи: в качестве источника связи выберем ТаблицаФайл1, в качестве приемника — ТаблицаФайл2. Укажем условие связи по полю КлючСопоставления. Если нам нужно увидеть все записи из первого файла, даже если для них нет пары во втором, используйте логику левого соединения (в СКД это настраивается параметрами связи).
Так как мы используем внешние наборы данных, стандартная кнопка «Сформировать» не сработает автоматически без кода. Нам нужно перехватить процесс формирования в модуле объекта отчета (процедура ПриКомпоновкеРезультата).
Рассмотрим пример кода, который связывает наши загруженные таблицы с механизмом СКД:
Процедура ПриКомпоновкеРезультата(ДанныеРасшифровки, СтандартнаяОбработка)
СтандартнаяОбработка = Ложь; // Отключаем стандартный вывод
// 1. Получаем данные из файлов (пути можно брать из параметров или реквизитов формы)
ТЗ_Файл1 = ПрочитатьExcelВТаблицу("C:\Files\File1.xlsx");
ТЗ_Файл2 = ПрочитатьExcelВТаблицу("C:\Files\File2.xlsx");
// 2. Создаем структуру для передачи в СКД
ВнешниеНаборыДанных = Новый Структура;
ВнешниеНаборыДанных.Вставить("ТаблицаФайл1", ТЗ_Файл1);
ВнешниеНаборыДанных.Вставить("ТаблицаФайл2", ТЗ_Файл2);
// 3. Инициализируем процесс компоновки
СхемаКомпоновкиДанных = ПолучитьМакет("ОсновнаяСхемаКомпоновкиДанных");
Настройки = КомпоновщикНастроек.ПолучитьНастройки();
КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновкиДанных, Настройки, ДанныеРасшифровки);
ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки, ВнешниеНаборыДанных, ДанныеРасшифровки);
ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВТабличныйДокумент;
ПроцессорВывода.УстановитьДокумент(Результат);
ПроцессорВывода.Вывести(ПроцессорКомпоновки);
КонецПроцедуры
Проанализируем типичные проблемы, с которыми можно столкнуться при сопоставлении двух таблиц из Excel:
ТаблицыЗначений используйте функцию ВРег() (приведение к верхнему регистру) для ключевых полей.ТаблицаЗначений всегда явно указывайте ОписаниеТипов.ТЗ.Выясним еще один профессиональный подход. Если логика сопоставления очень сложная, иногда проще использовать один «Набор данных - запрос», а внешние таблицы предварительно помещать во временные таблицы. Это позволит использовать всю мощь языка запросов 1С (UNION, сложные JOIN, вложенные запросы) прямо внутри СКД.
Для этого в схеме компоновки создается обычный запрос к несуществующим (пока) таблицам, а перед формированием отчета менеджер временных таблиц наполняется данными из загруженных ТЗ. Это требует более глубоких знаний, но дает максимальную гибкость.
Заключение: Мы рассмотрели основной путь решения задачи. Главное — помнить, что СКД является лишь инструментом визуализации и связки, а качество сопоставления полностью зависит от того, насколько аккуратно вы подготовили ТаблицыЗначений из исходных файлов Excel.