Как в 1С проверить строку Excel на пустоту и корректно определить последнюю строку

← На главную

При интеграции 1С с файлами Microsoft Excel разработчики часто сталкиваются со ситуацией, когда процедура загрузки данных подтягивает «фантомные» пустые строки. Попытки определить границу таблицы штатным свойством UsedRange приводят к тому, что система считывает сотни визуально пустых ячеек. Разберем по шагам, почему это происходит, как проверить всю строку на пустоту без медленного обхода каждой ячейки и какие способы оптимизации чтения существуют.

Почему UsedRange считает пустые строки заполненными

Выясним причину, по которой стандартное обращение к используемому диапазону листа через UsedRange возвращает некорректный номер последней строки. Посмотрим на типовой код определения границы файла:


Функция НомерПоследнейЗаполненнойСтрокиЛиста(ФайлExcel)
    
    Результат = ФайлExcel.ActiveSheet.UsedRange.Row + ФайлExcel.ActiveSheet.UsedRange.Rows.Count - 1;
    
    Если Не ЗначениеЗаполнено(Результат) Тогда
        Результат = 1;
    КонецЕсли; 
    
    Возврат Результат;
    
КонецФункции

Свойство UsedRange включает в себя все ячейки, с которыми когда-либо совершались действия в редакторе Excel. Строка будет считаться «используемой», если в ней присутствуют:

Оформление и форматирование (установлены границы ячеек, заливка фона, шрифт или числовой формат).
Формулы, вычисляемое значение которых возвращает пустую строку "".
Ячейки, из которых значение было удалено клавишей Delete, но сама структура ячеек не была очищена.

В результате цикл чтения данных из 1С продолжается по визуально пустым строкам, замусоривая результат загрузки.

Способы проверки строки на пустоту без поячеистого обхода

Рассмотрим, как можно сразу проверить всю строку на наличие данных, не выполняя обращение к каждой ячейке по отдельности через COM (что крайне негативно сказывается на производительности).

1. Использование встроенной функции Excel CountA

Через COM-соединение можно вызвать функцию Excel WorksheetFunction.CountA (в русской версии — СЧЁТЗ). Она возвращает количество непустых ячеек в переданном диапазоне:


Функция СтрокаПустая(ExcelПриложение, ЛистExcel, НомерСтроки)
    
    КоличествоЗаполненных = ExcelПриложение.WorksheetFunction.CountA(ЛистExcel.Rows(НомерСтроки));
    Возврат (КоличествоЗаполненных = 0);
    
КонецФункции

Если функция возвращает 0, значит, в строке нет ни одного значения, и её можно пропустить или удалить.

2. Поиск реальной последней строки через метод Find

Чтобы не обрабатывать лишние строки, найдём реальную последнюю заполненную ячейку снизу вверх с помощью метода Find, настроенного на поиск по значениям (игнорируя форматирование):


Функция НайтиРеальнуюПоследнююСтроку(ЛистExcel)
    
    // -4163 соответствует константе xlValues (поиск по значениям)
    // 2 соответствует xlPrevious (поиск в обратном порядке)
    Ячейка = ЛистExcel.Cells.Find("*", ЛистExcel.Cells(1, 1), -4163, 2, 1, 2);
    
    Если Ячейка = Неопределено Тогда
        Возврат 1;
    КонецЕсли;
    
    Возврат Ячейка.Row;
    
КонецФункции

3. Быстрое считывание всего диапазона в массив 1С

Обращение к COM-объекту Excel на каждой итерации цикла работает медленно. Для ускорения считываем весь диапазон листа в память за один вызов, получив двухмерный массив COMSafeArray:


// Получаем двухмерный массив значений диапазона
МассивДанных = ЛистExcel.Range(ЛистExcel.Cells(1, 1), ЛистExcel.Cells(ВсегоСтрок, ВсегоКолонок)).Value;

// Обход массива выполняем уже средствами 1С
Для НомерСтроки = 1 По ВсегоСтрок Цикл
    СтрокаПустая = Истина;
    Для НомерКолонки = 1 По ВсегоКолонок Цикл
        ЗначениеЯчейки = МассивДанных.GetValue(НомерСтроки, НомерКолонки);
        Если ЗначениеЗаполнено(СокрЛП(ЗначениеЯчейки)) Тогда
            СтрокаПустая = Ложь;
            Прервать;
        КонецЕсли;
    КонецЦикла;
    
    Если СтрокаПустая Тогда
        Продолжить; // Пропускаем пустую строку
    КонецЕсли;
    
    // Обработка заполненных данных...
КонецЦикла;

Правильное удаление пустых строк

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


ВсегоСтрок = НайтиРеальнуюПоследнююСтроку(ЛистExcel);

Для НомерСтроки = ВсегоСтрок По 1 Шаг -1 Цикл
    Если СтрокаПустая(ExcelПриложение, ЛистExcel, НомерСтроки) Тогда
        ЛистExcel.Rows(НомерСтроки).Delete();
    КонецЕсли;
КонецЦикла;

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

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


ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу);

ВсегоСтрок = ТабДок.ВысотаТаблицы;
ВсегоКолонок = ТабДок.ШиринаТаблицы;

Для НомерСтроки = 1 По ВсегоСтрок Цикл
    
    ПустаяСтрока = Истина;
    Для НомерКолонки = 1 По ВсегоКолонок Цикл
        ТекстЯчейки = СокрЛП(ТабДок.Область(НомерСтроки, НомерКолонки).Текст);
        Если Не ПустаяСтрока(ТекстЯчейки) Тогда
            ПустаяСтрока = Ложь;
            Прервать;
        КонецЕсли;
    КонецЦикла;
    
    Если ПустаяСтрока Тогда
        Продолжить;
    КонецЕсли;
    
    // Логика загрузки строки из ТабличногоДокумента
    
КонецЦикла;

Использование ТабличныйДокумент.Прочитать() работает значительно быстрее COM-соединения, не оставляет зависших процессов в оперативной памяти и корректно работает во всех операционных системах.