Как прочитать данные из объединенных по вертикали ячеек Excel в 1С?

← На главную

При интеграции 1С с Microsoft Excel разработчики часто сталкиваются с файлами сложной структуры, где в первой колонке ячейки объединены по вертикали (например, подразделение, группа товаров или поставщик), а во второй колонке расположены детальные записи. В результате одной ячейке первой колонки соответствует несколько строк второй колонки.

При стандартном построчном обходе через COM-соединение возникает проблема: данные считываются только из первой строки, а для последующих строк проверка значения дает пустой результат, из-за чего детальные строки пропускаются. Выясним причину такого поведения и подробно разберем рабочие способы решения этой задачи.

Почему Excel возвращает пустоту для объединенных ячеек?

Проанализируем особенности объектной модели Microsoft Excel. Физически значение объединенного диапазона хранится только в одной ячейке — крайней левой верхней (главной ячейке диапазона). Все остальные ячейки, визуально входящие в этот блок, имеют пустое значение (Empty). Когда цикл в коде переходит на следующую строку и обращается к свойству Value ячейки из первой колонки, возвращается пустое значение, проверка равенства подразделению не срабатывает, и подчиненные данные из второй колонки не попадают в результирующую таблицу значений или табличную часть.

Способ 1. Определение параметров объединенной области через MergeArea

Рассмотрим прямое решение с использованием объектной модели Excel. У каждого объекта ячейки (Range) существует свойство MergeArea, которое возвращает ссылку на всю область объединения, в которую входит текущая ячейка. Если ячейка не объединена, свойство MergeArea указывает на саму эту ячейку.

Для определения количества строк, входящих в объединенный блок, используется свойство MergeArea.Rows.Count. Разберем по шагам, как построить чтение:

Определяем, объединена ли ячейка, или сразу получаем число строк блока через ЛистExcel.Cells(К, НачКолонка).MergeArea.Rows.Count.
Если значение в заголовке блока соответствует искомому подразделению, внутренним циклом считываем все строки второй колонки, относящиеся к этому блоку.
Сдвигаем счетчик внешнего цикла на количество обработанных строк блока, чтобы не делать лишних итераций.

Посмотрим на пример реализации данного алгоритма:


К = НачСтрока;
Пока К <= КонСтрока Цикл
    
    ЯчейкаШапки = ЛистExcel.Cells(К, НачКолонка);
    ЗначениеПодразделения = СокрЛП(ЯчейкаШапки.Value);
    
    // Получаем количество строк в объединенном блоке
    КоличествоСтрокВБлоке = ЯчейкаШапки.MergeArea.Rows.Count;
    
    Если ЗначениеПодразделения = СокрЛП(Объект.Подразделение) Тогда
        
        // Обходим все строки детальных записей, входящие в блок
        Для Смещение = 0 По КоличествоСтрокВБлоке - 1 Цикл
            
            ИндексСтроки = К + Смещение;
            ЗначениеВторойКолонки = СокрЛП(ЛистExcel.Cells(ИндексСтроки, НачКолонка + 1).Value);
            
            Если Не ПустаяСтрока(ЗначениеВторойКолонки) Тогда
                НовСтрока = ТЧданныеФайла.Добавить();
                НовСтрока.Подразделение = ЗначениеПодразделения;
                НовСтрока.Значение = ЗначениеВторойКолонки;
            КонецЕсли;
            
        КонецЦикла;
        
    КонецЕсли;
    
    // Переходим сразу к следующему блоку
    К = К + КоличествоСтрокВБлоке;
    
КонецЦикла;

Способ 2. Получение значения из верхней ячейки объединения

Рассмотрим альтернативную ситуацию: что делать, если структуру цикла менять не хочется и требуется построчный обход? В этом случае для каждой строки мы можем гарантированно получить значение заголовка, обратившись к первой ячейке диапазона MergeArea.Cells(1, 1). Если ячейка объединена, это вернет значение из верхней левой ячейки блока, а если нет — значение текущей ячейки.

Разберем пример:


Для К = НачСтрока По КонСтрока Цикл
    
    // Даже на второй и третьей строке объединения Cells(1, 1) вернет заголовок блока
    ЗначениеКолонки1 = СокрЛП(ЛистExcel.Cells(К, НачКолонка).MergeArea.Cells(1, 1).Value);
    
    Если ЗначениеКолонки1 <> СокрЛП(Объект.Подразделение) Тогда
        Продолжить;
    КонецЕсли;
    
    ЗначениеКолонки2 = СокрЛП(ЛистExcel.Cells(К, НачКолонка + 1).Value);
    
    НовСтрока = ТЧданныеФайла.Добавить();
    НовСтрока.Подразделение = ЗначениеКолонки1;
    НовСтрока.Значение = ЗначениеКолонки2;
    
КонецЦикла;

Способ 3. Алгоритмическое запоминание («протягивание» значения)

Постоянные вызовы свойств COM-объекта (таких как MergeArea) на каждой строке вызывают существенные накладные расходы по времени из-за межпроцессного взаимодействия OLE. Самый производительный подход заключается в запоминании последнего непустого значения в служебной переменной.

Разберем по шагам, как работает этот метод:

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

Посмотрим на реализацию в коде:


ТекущееПодразделение = "";

Для К = НачСтрока По КонСтрока Цикл
    
    ЗначениеЯчейки1 = СокрЛП(ЛистExcel.Cells(К, НачКолонка).Value);
    
    // Если ячейка не пустая — начался новый блок
    Если Не ПустаяСтрока(ЗначениеЯчейки1) Тогда
        ТекущееПодразделение = ЗначениеЯчейки1;
    КонецЕсли;
    
    // Проверяем фильтр по текущему сохраненному подразделению
    Если ТекущееПодразделение <> СокрЛП(Объект.Подразделение) Тогда
        Продолжить;
    КонецЕсли;
    
    ЗначениеЯчейки2 = СокрЛП(ЛистExcel.Cells(К, НачКолонка + 1).Value);
    
    НовСтрока = ТЧданныеФайла.Добавить();
    НовСтрока.Подразделение = ТекущееПодразделение;
    НовСтрока.Значение = ЗначениеЯчейки2;
    
КонецЦикла;

Альтернатива: загрузка через ТабличныйДокумент

Важно помнить об ограничениях технологии COM: она требует установленного Microsoft Excel на клиентском компьютере, работает медленно и не поддерживается на серверах под управлением Linux. Если стоит задача быстрой и кроссплатформенной загрузки файла, рекомендуется использовать встроенный объект платформы 1С: ТабличныйДокумент.

Метод ТабличныйДокумент.Прочитать(ПутьКФайлу) загружает Excel-файл напрямую в память без использования сторонних приложений. После загрузки обход ячеек выполняется в десятки раз быстрее, а логика «протягивания» непустых значений из Способа 3 полностью сохраняется.