При работе с внешними данными, особенно при загрузке информации из файлов Excel в систему 1С, мы часто сталкиваемся с необходимостью обрабатывать документы, имеющие сложную структуру. Одной из распространенных проблем является наличие многострочной "шапки" – нескольких верхних строк, которые содержат общую информацию, заголовки, пояснения, но не являются частью самих данных, предназначенных для импорта. Стандартный подход с использованием метода ТабличныйДокумент.Прочитать загружает весь лист Excel целиком, а затем, при попытке сформировать из него таблицу значений через ПостроительЗапроса, первая строка документа (часто часть шапки) некорректно интерпретируется как заголовки колонок, что приводит к ошибкам или неполной загрузке данных. Давайте рассмотрим, как эффективно решить эту проблему, пропустив шапку и загрузив только полезные данные.
ТабличныйДокумент.Прочитать и его ограниченийДля начала давайте разберем, как работает метод ТабличныйДокумент.Прочитать. Когда мы используем его для чтения файла Excel, система 1С загружает содержимое указанного листа в объект ТабличныйДокумент. Этот объект является представлением листа Excel в 1С и содержит все ячейки со значениями и форматированием. Важно понимать, что ТабличныйДокумент.Прочитать не занимается анализом структуры данных, не пытается определить, где находится шапка, а где начинаются данные. Он просто переносит содержимое ячеек из внешнего файла. Если на листе Excel есть сложная шапка, занимающая, например, первые 9 строк, то все эти 9 строк будут частью объекта ТабличныйДокумент.
Проблема возникает, когда мы пытаемся использовать этот загруженный ТабличныйДокумент как источник для ПостроителяЗапроса. По умолчанию, ПостроительЗапроса анализирует первую строку предоставленного ему источника данных, чтобы определить имена и порядок колонок. Если этой первой строкой оказывается часть сложной шапки (например, объединенная ячейка или строка с общим заголовком), ПостроительЗапроса может некорректно идентифицировать колонки, или же увидит только одну колонку (как было описано в сообщении 4), а остальные данные проигнорирует, поскольку не сможет найти соответствующие заголовки. Таким образом, нам необходимо "показать" ПостроителюЗапроса только ту часть табличного документа, которая содержит фактические данные и корректные заголовки.
Область() табличного документаДля того чтобы пропустить ненужные строки шапки и загрузить только актуальные данные, мы будем использовать метод ТабличныйДокумент.Область(). Этот метод позволяет нам выделить конкретный прямоугольный диапазон ячеек внутри уже загруженного ТабличныйДокумент и получить новый объект типа ОбластьЯчеекТабличногоДокумента.
Синтаксис метода выглядит так:
ТабличныйДокумент.Область(НачальнаяСтрока, НачальнаяКолонка, КонечнаяСтрока, КонечнаяКолонка)
НачальнаяСтрока: Номер строки, с которой начинается нужная нам область данных. Это будет первая строка после вашей шапки.НачальнаяКолонка: Номер колонки, с которой начинается область (обычно 1, если данные начинаются с первой колонки).КонечнаяСтрока: Номер строки, на которой заканчивается область данных. Мы можем использовать свойство ТаблДок.ВысотаТаблицы, чтобы указать, что данные идут до конца документа.КонечнаяКолонка: Номер колонки, на которой заканчивается область данных. Аналогично, можно использовать ТаблДок.ШиринаТаблицы для охвата всех колонок.Таким образом, вместо того чтобы передавать в ПостроительЗапроса весь объект ТабличныйДокумент, мы передадим ему именно эту "обрезанную" ОбластьЯчеекТабличногоДокумента. ПостроительЗапроса будет работать только с этим выделенным диапазоном, игнорируя все, что находится за его пределами. Это гарантирует, что первая строка в переданной области будет корректной строкой с заголовками или данными, которую ПостроительЗапроса сможет правильно интерпретировать.
Давайте пошагово рассмотрим, как реализовать этот подход в коде 1С.
ТабличныйДокумент.На первом этапе мы, как обычно, считываем содержимое файла Excel в объект ТабличныйДокумент. На этом этапе мы еще не заботимся о шапке, просто переносим все данные из файла в память 1С.
Для этого нам понадобится полный путь к файлу Excel (ИмяФайлаНаСервере) и указание способа чтения и типа файла. Используем СпособЧтенияЗначенийТабличногоДокумента.Значение для чтения только значений ячеек и ТипФайлаТабличногоДокумента.XLS (или XLSX, в зависимости от формата вашего файла).
ТаблДок = Новый ТабличныйДокумент;
ТаблДок.Прочитать(ИмяФайлаНаСервере, СпособЧтенияЗначенийТабличногоДокумента.Значение, ТипФайлаТабличногоДокумента.XLS);
После выполнения этого кода в переменной ТаблДок будет находиться весь лист Excel, включая шапку, данные и, возможно, подвалы.
Это самый важный шаг. Здесь мы указываем, с какой строки реально начинаются наши данные. Если, например, шапка занимает первые 9 строк, то данные начинаются с 10-й строки. Мы задаем номер этой строки в переменной НомерПервойСтрокиДанных. Остальные параметры – начальная/конечная колонка и конечная строка – можно определить динамически, используя свойства ТаблДок.ШиринаТаблицы и ТаблДок.ВысотаТаблицы, чтобы охватить все колонки и все строки до конца документа.
// Предположим, что шапка занимает первые 9 строк, и данные начинаются с 10-й
НомерПервойСтрокиДанных = 10;
// Если данные начинаются с первой колонки
НомерПервойКолонкиДанных = 1;
// До конца документа по высоте
НомерПоследнейСтрокиДанных = ТаблДок.ВысотаТаблицы;
// До конца документа по ширине
НомерПоследнейКолонкиДанных = ТаблДок.ШиринаТаблицы;
// Получаем область табличного документа, содержащую только нужные данные
ОбластьДанных = ТаблДок.Область(
НомерПервойСтрокиДанных,
НомерПервойКолонкиДанных,
НомерПоследнейСтрокиДанных,
НомерПоследнейКолонкиДанных
);
Переменная ОбластьДанных теперь содержит новый объект типа ОбластьЯчеекТабличногоДокумента, который представляет собой только тот диапазон ячеек, который мы указали. Все, что было выше или левее этого диапазона, исключено из этой области. Именно этот объект мы и будем использовать дальше.
ПостроителяЗапроса.Теперь, когда у нас есть объект ОбластьДанных, содержащий только необходимые для импорта данные (без шапки), мы можем передать его в ПостроительЗапроса. Это является ключевым моментом решения, который позволяет ПостроителюЗапроса правильно определить структуру колонок.
ПЗ = Новый ПостроительЗапроса;
ПЗ.ИсточникДанных = Новый ОписаниеИсточникаДанных(ОбластьДанных);
ПЗ.ДобавлениеПредставлений = ТипДобавленияПредставлений.НеДобавлять; // Или Автоматически, если нужны представления
ПЗ.ЗаполнитьНастройки(); // Это действие анализирует ПЕРВУЮ СТРОКУ в ОбластьДанных
ПЗ.Выполнить();
ТЗ = ПЗ.Результат.Выгрузить();
Обратите внимание, что в ПЗ.ИсточникДанных мы передаем именно ОбластьДанных, а не весь ТаблДок. Метод ПЗ.ЗаполнитьНастройки() проанализирует теперь первую строку именно этой области (которая, по нашему замыслу, содержит заголовки колонок или является первой строкой данных), и на ее основе построит правильную структуру полей для запроса. В результате выполнения ПЗ.Выполнить() и ПЗ.Результат.Выгрузить() мы получим таблицу значений (ТЗ), которая будет содержать только нужные нам данные, без лишней шапки.
Давайте соберем все шаги в единый программный блок, который вы сможете использовать в своих обработках или модулях.
// Определим путь к файлу Excel.
// В реальных условиях путь может быть получен от пользователя или из настроек.
ИмяФайлаНаСервере = "C:\МоиДокументы\ДанныеДляИмпорта.xls"; // Пример пути
// 1. Создаем новый объект ТабличныйДокумент и считываем весь файл Excel в него
ТаблДок = Новый ТабличныйДокумент;
Попытка
ТаблДок.Прочитать(ИмяФайлаНаСервере, СпособЧтенияЗначенийТабличногоДокумента.Значение, ТипФайлаТабличногоДокумента.XLS);
Исключение
Сообщить("Ошибка при чтении файла Excel: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
// 2. Определяем параметры для выделения нужной области данных.
// Предположим, что шапка занимает первые 9 строк, и данные начинаются с 10-й строки.
// Если шапка занимает другое количество строк, измените это значение.
НомерПервойСтрокиДанных = 10;
// Обычно данные начинаются с первой колонки
НомерПервойКолонкиДанных = 1;
// Конечная строка - до конца документа
НомерПоследнейСтрокиДанных = ТаблДок.ВысотаТаблицы;
// Конечная колонка - до конца документа
НомерПоследнейКолонкиДанных = ТаблДок.ШиринаТаблицы;
// Проверяем, что заданная начальная строка не превышает высоту таблицы
Если НомерПервойСтрокиДанных > ТаблДок.ВысотаТаблицы Тогда
Сообщить("Указанная начальная строка данных (" + НомерПервойСтрокиДанных +
") превышает общую высоту таблицы (" + ТаблДок.ВысотаТаблицы + "). Проверьте параметры.");
Возврат;
КонецЕсли;
// Получаем объект "ОбластьЯчеекТабличногоДокумента", содержащий только наши данные
ОбластьДанных = ТаблДок.Область(
НомерПервойСтрокиДанных,
НомерПервойКолонкиДанных,
НомерПоследнейСтрокиДанных,
НомерПоследнейКолонкиДанных
);
// 3. Создаем ПостроительЗапроса и передаем ему нашу выделенную область данных
ПЗ = Новый ПостроительЗапроса;
ПЗ.ИсточникДанных = Новый ОписаниеИсточникаДанных(ОбластьДанных);
// Настройка, определяющая, нужно ли добавлять представления для полей
ПЗ.ДобавлениеПредставлений = ТипДобавленияПредставлений.НеДобавлять;
// Заполняем настройки построителя запроса.
// Важно: ПостроительЗапроса анализирует первую строку ОбластьДанных для определения колонок.
ПЗ.ЗаполнитьНастройки();
// Выполняем запрос
ПЗ.Выполнить();
// Выгружаем результат в таблицу значений
ТЗ = ПЗ.Результат.Выгрузить();
// Теперь переменная ТЗ содержит данные из Excel, начиная с 10-й строки, без шапки.
// Вы можете продолжить дальнейшую обработку ТЗ:
// - Перебирать строки ТЗ в цикле
// - Проверять и преобразовывать типы данных
// - Записывать данные в справочники, документы или регистры 1С
Сообщить("Данные успешно загружены в таблицу значений. Количество строк: " + ТЗ.Количество());
Для Каждого СтрокаТЗ Из ТЗ Цикл
// Пример обработки каждой строки
// Сообщить("Данные строки: " + СтрокаТЗ.Колонка1 + ", " + СтрокаТЗ.Колонка2);
// ... ваш код для обработки данных
КонецЦикла;
При использовании этого подхода мы получаем мощный и гибкий инструмент для импорта данных. Однако, есть несколько нюансов, на которые стоит обратить внимание:
ПостроительЗапроса использует первую строку своего источника данных для определения структуры колонок. Следовательно, очень важно, чтобы первая строка в указанной вами ОбластиДанных содержала корректные заголовки колонок или хотя бы была первой строкой данных с последовательной структурой. Если там будут объединенные ячейки или нерелевантная информация, ПостроительЗапроса может снова некорректно определить поля.
НомерПервойСтрокиДанных = 10. В более сложных сценариях, когда количество строк шапки может меняться, можно реализовать более гибкий алгоритм. Например, программно искать в документе ключевое слово, которое однозначно отмечает начало данных (например, "Код", "Наименование" или "№ п/п"). Однако, это значительно усложнит код. Для большинства типовых задач с фиксированным форматом Excel-файлов, ручное указание номера строки достаточно.
ТабличныйДокумент.Прочитать поддерживает не только формат XLS, но и XLSX, CSV и другие. Выбор нужного типа указывается в параметре ТипФайлаТабличногоДокумента.
Попытка...Исключение при работе с внешними файлами, чтобы корректно обрабатывать ситуации, когда файл не найден, поврежден или имеет неверный формат.
ТаблицуЗначений (ТЗ), вы можете выполнять с ними любую необходимую логику: проверять на дубликаты, приводить типы данных, выполнять сопоставление с существующими справочниками, записывать в документы или регистры системы 1С.
Хотя использование ТабличныйДокумент.Область() является наиболее эффективным и рекомендуемым способом для данной задачи, существуют и другие методы, каждый со своими плюсами и минусами:
ТабличныйДокумент 1С не справляется (например, из-за сильно объединенных ячеек в данных, специфического форматирования, множества листов с разными структурами), можно использовать прямой доступ к Excel через COM-обобъект. Этот метод предоставляет полный контроль над Excel-файлом, позволяя программно открывать его, переходить по листам, читать содержимое конкретных ячеек или диапазонов. Однако, он более ресурсоемкий, требует установки Microsoft Office на сервере или клиентской машине и сложнее в разработке и отладке. В нашем случае, он был бы избыточен.
ТабличныйДокумент, программно удалить ненужные строки с шапкой с помощью метода ТаблДок.УдалитьОбласть(). После удаления, оставшуюся часть документа можно было бы передать в ПостроительЗапроса. Однако, этот метод менее эффективен, чем сразу выбрать нужную область. Он создает временную операцию удаления, которая может быть излишней при больших объемах данных, и по своей сути является "обходным маневром", а не прямым решением.
В заключение, использование метода ТабличныйДокумент.Область() в связке с ПостроителемЗапроса — это элегантное, производительное и наиболее подходящее решение для задачи импорта данных из Excel с пропуском многострочной шапки. Оно позволяет нам четко определить рабочий диапазон данных, избегая некорректной интерпретации структуры файла и значительно упрощая дальнейшую обработку.