При интеграции данных из внешних систем, особенно из файлов Excel, разработчики часто сталкиваются с форматом таблиц, который удобен для восприятия человеком, но совершенно не подходит для хранения в базе данных. Типичный пример — «широкая» таблица, где периоды (даты) развернуты по горизонтали в заголовках колонок. В 1С же табличные части документов обычно имеют «узкую» структуру, где каждый факт (количество по конкретной дате) должен занимать отдельную строку. Рассмотрим подробнее, как эффективно решить задачу трансформации таких данных и загрузить их в табличную часть документа.
Проанализируем ситуацию: в исходном файле Excel у нас есть аналитические разрезы (Бренд, Страна, Размер) и несколько колонок с датами (например, 01.08.2020, 01.09.2020). На пересечении строки и колонки стоит количество. Нам необходимо выполнить процедуру «разворота» (Unpivot), чтобы для каждой заполненной ячейки с количеством создавалась новая строка в документе 1С, куда переносились бы общие данные строки и значение даты из заголовка колонки.
Прежде чем приступать к алгоритму трансформации, выясним, как лучше прочитать файл. Традиционный способ через COMObject("Excel.Application") постепенно уходит в прошлое, так как он требует установленного Microsoft Office на сервере и работает медленно. Мы рекомендуем использовать объект ТабличныйДокумент. Этот метод встроен в платформу, работает быстро и позволяет открывать файлы .xlsx напрямую.
Рассмотрим пример кода для открытия файла:
ТабДок = Новый ТабличныйДокумент;
Попытка
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
Сообщить("Не удалось прочитать файл: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
Основная идея решения заключается в использовании двух циклов. Внешний цикл будет перебирать строки таблицы (начиная со строки данных), а внутренний — колонки, в которых указаны периоды. Разберем этот процесс по шагам.
Шаг 1: Определение границ данных. Нам нужно знать, в какой строке находится шапка, с какой колонки начинаются даты и сколько всего строк в файле.
Шаг 2: Создание временной таблицы значений. Чтобы ускорить процесс и иметь возможность предварительно проверить данные, лучше сначала загрузить всё в ТаблицаЗначений, а затем одним методом Загрузить() перенести данные в документ.
Шаг 3: Реализация вложенных циклов. Посмотрим на пример реализации алгоритма:
// Предположим, колонки 1-3 это Бренд, Страна, Размер.
// Колонки 4 и далее — это даты.
ВсегоСтрок = ТабДок.ВысотаТаблицы;
ВсегоКолонок = ТабДок.ШиринаТаблицы;
ТЗ_Результат = Новый ТаблицаЗначений;
ТЗ_Результат.Колонки.Добавить("Бренд");
ТЗ_Результат.Колонки.Добавить("Страна");
ТЗ_Результат.Колонки.Добавить("Размер");
ТЗ_Результат.Колонки.Добавить("Период", Новый ОписаниеТипов("Дата"));
ТЗ_Результат.Колонки.Добавить("Количество", Новый ОписаниеТипов("Число"));
// 1. Читаем даты из шапки (4-я колонка и до конца)
СоответствиеПериодов = Новый Соответствие;
Для НомКолонки = 4 По ВсегоКолонок Цикл
ЗначениеЗаголовка = ТабДок.Область(1, НомКолонки).Текст;
Попытка
ДатаПериода = Дата(ЗначениеЗаголовка); // Требуется осторожность с форматом
СоответствиеПериодов.Вставить(НомКолонки, ДатаПериода);
Исключение
// Если в заголовке не дата, пропускаем колонку
КонецПопытки;
КонецЦикла;
// 2. Основной цикл по строкам данных (начиная со 2-й строки)
Для НомСтроки = 2 По ВсегоСтрок Цикл
ТекБренд = ТабДок.Область(НомСтроки, 1).Текст;
ТекСтрана = ТабДок.Область(НомСтроки, 2).Текст;
ТекРазмер = ТабДок.Область(НомСтроки, 3).Текст;
// Внутренний цикл по колонкам с периодами
Для каждого Элемент Из СоответствиеПериодов Цикл
ИндексКолонки = Элемент.Ключ;
ТекПериод = Элемент.Значение;
ТекКоличество = ТабДок.Область(НомСтроки, ИндексКолонки).Текст;
// Преобразуем строку в число, удаляя лишние пробелы
ЧислоКоличество = Число(СтрЗаменить(ТекКоличество, Символы.НПП, ""));
// Записываем только если есть количество
Если ЧислоКоличество <> 0 Тогда
НоваяСтрока = ТЗ_Результат.Добавить();
НоваяСтрока.Бренд = ТекБренд;
НоваяСтрока.Страна = ТекСтрана;
НоваяСтрока.Размер = ТекРазмер;
НоваяСтрока.Период = ТекПериод;
НоваяСтрока.Количество = ЧислоКоличество;
КонецЕсли;
КонецЦикла;
КонецЦикла;
При реализации загрузки мы должны учитывать несколько критических моментов, которые часто приводят к ошибкам в реальных проектах:
СтрЗаменить(Значение, Символы.НПП, "").ТабличныйДокумент.Прочитать() с параметром Значение 1С попытается сама определить тип. Если же вы читаете текст, используйте функции парсинга даты, учитывая локальные настройки формата (ДД.ММ.ГГГГ).Объект.Товары.Загрузить(ТЗ_Результат) работает значительно быстрее, чем добавление каждой строки в цикле через Добавить(). Однако это затрет существующие строки в документе. Если нужно добавить данные к уже существующим, используйте цикл по ТЗ_Результат с методом ЗаполнитьЗначенияСвойств().Если ваша конфигурация построена на базе БСП (УТ 11, ERP, КА 2), проанализируем возможность использования подсистемы «Загрузка данных из файла». Это мощный инструмент, который предоставляет готовый интерфейс для пользователя. Разработчику достаточно описать ожидаемую структуру колонок, а система сама предложит пользователю сопоставить их с данными Excel. Для реализации сложной трансформации (как в нашем случае с периодами) в БСП можно переопределить обработку результата загрузки в модуле менеджера или через расширение.
Трансформация данных из Excel требует внимательного подхода к архитектуре циклов. Мы выяснили, что наиболее эффективный путь — это использование вложенного цикла (строка -> колонка-период) с предварительной загрузкой в таблицу значений. Такой подход позволяет не только корректно распределить данные по строкам табличной части, но и внедрить проверки на дубли или некорректные значения еще до записи данных в базу 1С.