При интеграции 1С с Microsoft Excel программисты часто сталкиваются с задачей обработки данных, которые пользователь (например, бухгалтер) предварительно отфильтровал в файле. Стандартные подходы, такие как проверка свойства Hidden или анализ высоты строки RowHeight, могут давать непредсказуемые результаты, особенно если скрытие произошло в результате работы сложных фильтров или условного форматирования. Разберем подробно, как корректно решить эту задачу и какие технические нюансы следует учитывать.
Рассмотрим стандартную ситуацию: в файле Excel применен автофильтр или фильтр по цвету, настроенный через условное форматирование. Программист пытается в цикле проверить каждую строку:
Для НомерСтроки = 1 По КоличествоСтрок Цикл
Если Лист.Rows(НомерСтроки).Hidden Тогда
// Строка скрыта
КонецЕсли;
КонецЦикла;
Проанализируем, почему это может не работать. В некоторых версиях Excel и при определенных типах фильтрации свойство Hidden на уровне объекта Rows может не обновляться мгновенно для COM-соединения. Еще сложнее ситуация с RowHeight: если бухгалтер вручную или через макрос установил высоту строки в крайне малое значение (например, 0.1), визуально строка скрыта, но логически RowHeight не равен нулю, а свойство Hidden остается в значении False. Поэтому проверка RowHeight = 0 часто оказывается неэффективной.
Самый быстрый и надежный способ получить только видимые данные, не перебирая тысячи строк в цикле — это использование метода SpecialCells. Этот метод позволяет выбрать ячейки, соответствующие определенному критерию. Для получения видимых ячеек используется константа xlCellTypeVisible, значение которой равно 12.
Разберем по шагам, как применить этот метод в коде 1С:
// Получаем диапазон используемых ячеек
ИспользуемыйДиапазон = Лист.UsedRange;
// Выделяем только видимые ячейки (параметр 12)
Попытка
ВидимыеЯчейки = ИспользуемыйДиапазон.SpecialCells(12);
Исключение
// Если видимых ячеек нет или произошла ошибка
ВидимыеЯчейки = Неопределено;
КонецПопытки;
Важное уточнение: Результат метода SpecialCells(12) возвращает объект Range, который может состоять из нескольких несвязанных областей. Это происходит, если скрытые строки разрывают сплошной диапазон данных.
Выясним причину, по которой простая итерация по результату SpecialCells может привести к потере данных. Если ваш фильтр скрыл строки 5, 6 и 10, то видимый диапазон будет состоять из трех «островов»: строки 1-4, строки 7-9 и строки 11-конец. В объектной модели Excel это называется Areas.
Проанализируем правильный алгоритм обхода таких данных:
Если ВидимыеЯчейки <> Неопределено Тогда
Для Каждого Область Из ВидимыеЯчейки.Areas Цикл
Для Каждого СтрокаОбласти Из Область.Rows Цикл
НомерСтроки = СтрокаОбласти.Row;
// Здесь выполняем обработку данных видимой строки
Сообщить("Обработка видимой строки № " + НомерСтроки);
КонецЦикла;
КонецЦикла;
КонецЕсли;
Такой подход в десятки раз быстрее, чем перебор всех строк листа с проверкой каждой на скрытость, так как 1С обращается к COM-объекту Excel значительно реже.
Если по каким-то причинам вы все же вынуждены использовать цикл по всем строкам, рекомендуется обращаться к свойству Hidden не напрямую через Rows(имя), а через EntireRow. Это принуждает Excel актуализировать статус строки для внешнего интерфейса.
Посмотрим на пример кода:
Для н = 1 По 100 Цикл
ТекущаяСтрока = Лист.Cells(н, 1).EntireRow;
Если ТекущаяСтрока.Hidden Тогда
Продолжить; // Пропускаем скрытую фильтром строку
КонецЕсли;
// Дополнительная проверка на "почти нулевую" высоту
Если ТекущаяСтрока.RowHeight < 1 Тогда
Продолжить;
КонецЕсли;
КонецЦикла;
Иногда Excel "замораживает" состояние видимости строк для COM-объекта, если файл был сохранен в процессе изменения фильтра. Выясним, как гарантировать актуальность данных. Перед началом чтения данных полезно проверить свойство FilterMode объекта Worksheet. Если оно установлено в True, значит, на листе применен фильтр.
В исключительных случаях может потребоваться принудительный пересчет листа или обновление экранного отображения, чтобы свойства строк актуализировались:
ExcelПриложение.ScreenUpdating = Истина;
Лист.Calculate();
Рассмотрим ситуацию, когда методы VBA или COM по какой-то причине недоступны или работают нестабильно из-за специфики настройки безопасности системы. Можно использовать так называемый «костыль», который, тем не менее, является очень надежным. В Excel существует функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (или SUBTOTAL), которая умеет игнорировать скрытые строки.
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103; A1), где 103 — это код функции СЧЁТЗ, игнорирующей скрытые строки.Хотя этот метод требует изменения файла, он работает абсолютно всегда, независимо от того, как именно была скрыта строка: фильтром, группировкой или вручную.
Подведем итог: для эффективной работы в 1С со скрытыми строками Excel лучше всего комбинировать метод SpecialCells(12) с обходом коллекции Areas. Это обеспечит максимальную производительность. Если же важна абсолютная точность при ручном скрытии строк разной высоты, используйте проверку EntireRow.Hidden в сочетании с анализом RowHeight < 1.