Как вытащить данные из XLSX с помощью построителя запроса в 1С

← На главную

Загрузка данных из внешних файлов формата Excel (XLSX) — одна из наиболее часто встречающихся задач в практике разработчика 1С. Существует множество способов реализации этого механизма: через COM-объекты, прямое чтение XML-структуры файла или использование встроенных средств платформы. В данной статье мы подробно разберем один из наиболее элегантных и универсальных способов — использование объекта ПостроительЗапроса в связке с ТабличнымДокументом. Этот метод позволяет быстро преобразовать данные из ячеек Excel в структурированную ТаблицуЗначений для дальнейшей обработки.

Зачем использовать построитель запроса для Excel?

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

Шаг 1: Загрузка файла во временное хранилище и чтение в ТабличныйДокумент

Прежде чем данные попадут в построитель, нам необходимо прочитать файл XLSX и поместить его содержимое в объект ТабличныйДокумент. Рассмотрим, как это происходит в современных управляемых формах. Сначала мы выбираем файл через диалоговое окно и передаем его на сервер.


// На клиенте
Диалог = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
Диалог.Фильтр = "Excel файлы (*.xlsx)|*.xlsx";
Если Диалог.Выбрать() Тогда
    АдресФайла = ПоместитьВоВременноеХранилище(Новый ДвоичныеДанные(Диалог.ПолноеИмяФайла));
    ЗагрузитьДанныеНаСервере(АдресФайла);
КонецЕсли;

На стороне сервера мы получаем двоичные данные из хранилища и записываем их во временный файл, чтобы ТабличныйДокумент мог его прочитать. После этого мы используем метод Прочитать().


// На сервере
Процедура ЗагрузитьДанныеНаСервере(АдресФайла)
    ИмяВременногоФайла = ПолучитьИмяВременногоФайла("xlsx");
    ДанныеФайла = ПолучитьИзВременногоХранилища(АдресФайла);
    ДанныеФайла.Записать(ИмяВременногоФайла);
    
    ТабД = Новый ТабличныйДокумент;
    ТабД.Прочитать(ИмяВременногоФайла);
    
    // Теперь данные в ТабД, переходим к построителю
КонецПроцедуры

Шаг 2: Использование ПостроителяЗапроса для формирования таблицы

Теперь, когда данные находятся в объекте ТабличныйДокумент, мы можем задействовать ПостроительЗапроса. Его ключевая роль здесь — преобразовать неструктурированную область ячеек в объект РезультатЗапроса, из которого легко выгрузить стандартную ТаблицуЗначений.

Проанализируем ключевой фрагмент кода, который выполняет эту трансформацию:


// Создаем экземпляр построителя
ПЗ = Новый ПостроительЗапроса;

// Определяем область данных (по умолчанию вся заполненная область)
ОбластьДанных = ТабД.Область();

// Инициализируем описание источника данных на основе области табличного документа
ПЗ.ИсточникДанных = Новый ОписаниеИсточникаДанных(ОбластьДанных);

// Отключаем автоматическое добавление представлений для ускорения и чистоты данных
ПЗ.ДобавлениеПредставлений = ТипДобавленияПредставлений.НеДобавлять;

// Заполняем настройки и выполняем построитель
ПЗ.ЗаполнитьНастройки();
ПЗ.Выполнить();

// Получаем искомую таблицу значений
ТаблицаЗначений = ПЗ.Результат.Выгрузить();

Разберем подробнее, что происходит в этом блоке. Объект ОписаниеИсточникаДанных является связующим звеном. Когда мы передаем в него Область табличного документа, 1С пытается интерпретировать первую строку этой области как заголовки будущей таблицы значений. Если в первой строке Excel-файла написаны имена колонок (например, "Номенклатура", "Количество", "Цена"), построитель автоматически создаст колонки с соответствующими именами в итоговой ТаблицеЗначений.

Шаг 3: Тонкости типизации и очистки данных

После того как мы получили ТаблицуЗначений, важно понимать, как именно платформа типизировала данные. Зачастую при чтении из Excel через ТабличныйДокумент все значения воспринимаются как строки. Чтобы корректно «размазать» данные по реквизитам справочников или документов, нам необходимо выполнить явное приведение типов. Для быстрого переноса информации в базу без написания кода сопоставления подойдёт универсальная обработка для импорта данных из Excel в справочники 1С.

Рассмотрим пример обхода полученной таблицы с обработкой типов:


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

Важно обратить внимание на следующие нюансы, а если файлы поступают из внешних источников, можно автоматизировать сбор прайс-листов с почты, FTP и HTTP перед их обработкой:

Пустые строки: В Excel часто остаются "невидимые" строки с форматированием (границы ячеек, цвет фона). Построитель запроса может включить их в результат. Перед обработкой строки всегда проверяйте, заполнена ли хотя бы одна ключевая колонка. Для более сложных сценариев автоматизации без использования API применяется платформа для создания программных роботов OneRPA.
Заголовки: Если в ячейках заголовков Excel присутствуют пробелы или спецсимволы, 1С может заменить их на символы подчеркивания. Проверяйте фактические имена колонок в отладчике после выполнения метода Выгрузить().
Формат дат: Если в Excel используется сложный формат даты, 1С может прочитать его как число (количество дней с начала эпохи Excel). В таких случаях потребуется дополнительная математическая конвертация в тип Дата.

Альтернативный современный подход

Выясним, стоит ли всегда использовать ПостроительЗапроса. Начиная с версии платформы 8.3.6, разработчикам доступен более прямой путь. Поскольку ТабличныйДокумент уже содержит в себе все данные после метода Прочитать(), мы можем обойти его циклом напрямую, минуя построитель:


Для НомерСтроки = 2 По ТабД.ВысотаТаблицы Цикл
    ТоварНаименование = ТабД.Область(НомерСтроки, 1).Текст;
    Количество = ТабД.Область(НомерСтроки, 2).Текст;
    // Обработка данных
КонецЦикла;

Этот способ работает быстрее для очень больших файлов, так как не тратит ресурсы на создание промежуточных метаданных источника. Однако метод с построителем запроса остается незаменимым, когда структура Excel-файла заранее известна и совпадает с техническими именами, что позволяет получить готовую типизированную таблицу одной командой. В частности, для задач бухгалтерии можно загрузить отчёты о продажах маркетплейсов в БП 3 из Excel в автоматизированном режиме.

Подводя итог, использование ПостроителяЗапроса — это надежный "старой школы" метод, который до сих пор актуален благодаря своей предсказуемости и простоте реализации для средних объемов данных. Мы рассмотрели весь путь: от выбора файла пользователем до получения готовой таблицы значений, готовой к загрузке в базу данных.