При работе с большими объемами данных в Excel (например, файлы на 25 000 строк и более) часто возникает задача загрузки не всех данных, а только тех, которые пользователь предварительно отобрал с помощью Автофильтра. Проблема заключается в том, что при обычном переборе строк через COMОбъект программа читает все ячейки подряд, игнорируя настройки видимости в самом приложении Excel. В этой статье мы подробно разберем несколько эффективных методов, которые позволят нам обрабатывать только видимые строки, значительно экономя время и ресурсы системы.
Самый прямой и интуитивно понятный способ — это проверка свойства Hidden (Скрыто) у конкретной строки. Когда в Excel срабатывает фильтр, строки, не попавшие в критерий, не удаляются, а просто скрываются. Мы можем проанализировать это состояние программно.
Рассмотрим по шагам, как реализовать этот алгоритм. Мы будем перебирать строки в цикле и проверять, является ли строка «скрытой». Если свойство Hidden возвращает Ложь (или 0), значит, строка видна пользователю, и мы должны ее прочитать.
// Предположим, что ЭксельЛист уже инициализирован
ВсегоСтрок = ЭксельЛист.UsedRange.Rows.Count;
Для НомерСтроки = 1 По ВсегоСтрок Цикл
// Проверяем, скрыта ли вся строка целиком
Если ЭксельЛист.Rows(НомерСтроки).EntireRow.Hidden = 0 Тогда
// Строка видима — читаем данные ячеек
ЗначениеКолонки1 = ЭксельЛист.Cells(НомерСтроки, 1).Value;
ЗначениеКолонки2 = ЭксельЛист.Cells(НомерСтроки, 2).Value;
// Здесь выполняется логика обработки данных
Сообщить("Обработка строки: " + НомерСтроки);
КонецЕсли;
КонецЦикла;
Важное замечание: Несмотря на простоту, этот метод может работать медленно на очень больших файлах (десятки тысяч строк). Это связано с тем, что каждое обращение к свойству Hidden через COM-соединение требует времени на пересылку запроса между 1С и Excel.
Для ускорения процесса мы можем использовать мощный инструмент Excel — метод SpecialCells. Параметр 12 (соответствует константе xlCellTypeVisible) позволяет мгновенно получить ссылку только на те ячейки, которые видимы на данный момент.
Проанализируем ситуацию: если отфильтрованные строки идут не подряд (например, строки 1, 5, 10), Excel создает объект, состоящий из нескольких несвязанных областей (Areas). Чтобы корректно прочитать данные, нам нужно использовать вложенный цикл: сначала по областям, а затем по ячейкам внутри каждой области.
// Получаем только видимые ячейки в используемом диапазоне
ВидимыеОбласти = ЭксельЛист.UsedRange.SpecialCells(12);
// Перебираем каждую непрерывную область видимых строк
Для Каждого Область Из ВидимыеОбласти.Areas Цикл
КоличествоСтрокВОбласти = Область.Rows.Count;
КоличествоКолонокВОбласти = Область.Columns.Count;
Для Стр = 1 По КоличествоСтрокВОбласти Цикл
// Читаем данные первой колонки текущей области
Данные = Область.Cells(Стр, 1).Value;
Если ЗначениеЗаполнено(Данные) Тогда
// Обработка данных
КонецЕсли;
КонецЦикла;
КонецЦикла;
Этот способ работает на порядок быстрее, так как мы полностью исключаем из обработки скрытые диапазоны еще на этапе обращения к объектной модели Excel.
В практике разработки на 1С часто оказывается выгоднее отказаться от COM в пользу встроенного объекта ТабличныйДокумент. Метод Прочитать() поддерживает файлы .xlsx и при чтении сохраняет информацию о видимости строк.
Выясним причину, почему это эффективно: при загрузке файла Excel в ТабличныйДокумент скрытые строки получают свойство Видимость = Ложь. Мы можем перебрать строки табличного документа в памяти 1С, что будет в десятки раз быстрее, чем работа через COM-интерфейс.
ТабДок = Новый ТабличныйДокумент;
Попытка
ТабДок.Прочитать(ИмяФайла, СпособЧтенияЗначений.Значение);
Исключение
Сообщить("Ошибка чтения файла!");
Возврат;
КонецПопытки;
КоличествоСтрок = ТабДок.ВысотаТаблицы;
Для НомерСтроки = 1 По КоличествоСтрок Цикл
// Проверяем видимость строки в 1С
Если ТабДок.Область(НомерСтроки, 1, НомерСтроки, 1).Видимость Тогда
// Читаем данные ячейки
Значение = ТабДок.Область(НомерСтроки, 1).Текст;
// Ваша логика...
КонецЕсли;
КонецЦикла;
Если нам необходимо не просто прочитать то, что выбрал пользователь, но и самим наложить фильтр перед чтением, мы используем объект AutoFilter. Разберем по шагам, как считать параметры текущего фильтра или применить новый.
Например, чтобы узнать, по какому критерию сейчас отфильтрована первая колонка, мы можем обратиться к коллекции Filters:
Попытка
Если ЭксельЛист.AutoFilterMode Тогда
ТекущийКритерий = ЭксельЛист.AutoFilter.Filters.Item(1).Criteria1;
Сообщить("Фильтр установлен на значение: " + ТекущийКритерий);
КонецЕсли;
Исключение
// Фильтр может быть включен, но критерий не задан
КонецПопытки;
Для программной установки фильтра на определенный диапазон (например, с 1 по 10 колонку) используется метод AutoFilter:
// Устанавливаем фильтр в первой колонке по значению "Товар"
ЭксельЛист.Range(ЭксельЛист.Cells(1, 1), ЭксельЛист.Cells(1, 10)).AutoFilter(1, "Товар");
При работе через COM всегда следует придерживаться правил «хорошего тона», чтобы избежать зависания системы и утечек памяти:
Excel.ScreenUpdating = 0; перед началом обработки.Excel.DisplayAlerts = 0; позволит избежать модальных окон Excel, которые могут остановить выполнение кода 1С.Книга.Close(0); и завершайте процесс Excel.Quit(); в блоке Попытка...Исключение...КонецПопытки.Copy и последующего чтения этого листа целиком без проверок.Подводя итог, выбор метода зависит от конкретной задачи. Для быстрой и разовой проверки свойство Hidden вполне подходит. Однако для профессиональной разработки и обработки файлов объемом в 25 000 строк лучше использовать SpecialCells(12) или загрузку через ТабличныйДокумент, так как это обеспечит максимальную скорость работы вашей обработки.