Почему из Excel загружаются не все строки в 1С и как это исправить?

← На главную

Проблема частичной загрузки данных из файлов Excel в 1С — это классическая трудность, с которой сталкиваются разработчики при написании собственных обработок импорта. Особенно часто это проявляется при работе через COM-объект (Excel.Application). В данной статье мы подробно разберем, почему программа может «видеть» только часть строк, и рассмотрим надежные методы программного получения всех данных из файла.

Причина 1: Некорректное использование свойства UsedRange

Чаще всего для определения границ данных программисты используют свойство UsedRange. Однако это свойство имеет свои особенности. Рассмотрим ситуацию: если в файле были данные в 6000 строках, но затем часть из них удалили простым нажатием клавиши Delete (без удаления самих строк листа), Excel может продолжать считать эти строки «использованными». Бывает и обратная ситуация, когда из-за особенностей форматирования UsedRange возвращает не всю область.

Проанализируем нюансы работы с этим объектом. Свойство UsedRange.Rows.Count возвращает количество строк в используемом диапазоне, но оно не всегда соответствует номеру последней строки. Например, если данные начинаются с 10-й строки и занимают 100 строк, UsedRange.Rows.Count вернет 100, в то время как последняя значимая строка будет 110-й.

Чтобы избежать этой ошибки, рекомендуется использовать следующий подход для определения последней строки:


// Правильное определение границ через UsedRange
ПоследняяСтрока = WS.UsedRange.Row + WS.UsedRange.Rows.Count - 1;

Причина 2: Проблемы при передаче больших массивов (Value)

В сообщениях форума упоминается, что при 500 строках загрузка проходит успешно, а при 6000 — обрывается. Это часто связано с методом получения данных через массив UsedRange.Value. Когда мы пытаемся выгрузить огромный диапазон данных за один раз, COM-сервер Excel может столкнуться с нехваткой оперативной памяти или таймаутом ответа.

Рассмотрим пример кода, который часто приводит к ошибкам на больших объемах данных:


Попытка    
    Ex = новый COMОбъект("Excel.Application");
    WB = Ex.Workbooks.Open(ИмяФайла);
    WS = WB.WorkSheets(1);
    // Попытка засунуть 6000 строк в массив одним махом
    Arr = WS.UsedRange.Value; 
    WB.Close(0);
Исключение
    Предупреждение("Не удалось загрузить данные");
КонецПопытки;

Если в данных встречаются спецсимволы, некорректные формулы (например, #REF!) или просто очень длинные текстовые строки, метод Value может вернуть пустой результат или оборвать чтение на проблемной строке.

Решение 3: Использование метода CurrentRegion

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

Выясним, как это реализовать в коде:


// Использование текущей области данных от ячейки A1
КолВоСтрок = WS.Cells(1, 1).CurrentRegion.Rows.Count;

Этот метод работает стабильнее, если ваша таблица имеет четкую структуру заголовков и данных без разрывов.

Решение 4: Построчный обход с проверкой на «пустоту»

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

Разберем по шагам, как реализовать такой алгоритм:

Устанавливаем начальную строку (например, пропускаем заголовок).
Запускаем бесконечный цикл или цикл до заведомо большого числа.
Внутри цикла проверяем значение в самой важной колонке (например, «Артикул» или «Наименование»).
Если встретили 3-5 пустых строк подряд — прекращаем чтение (запас в несколько строк нужен на случай случайных пропусков в данных).

Пример кода для построчного чтения:


НомерСтроки = 1;
ПустыхПодряд = 0;
Пока ПустыхПодряд < 5 Цикл
    ЗначениеЯчейки = СокрЛП(WS.Cells(НомерСтроки, 1).Text);
    Если ЗначениеЗаполнено(ЗначениеЯчейки) Тогда
        // Обработка данных строки
        ПустыхПодряд = 0;
    Иначе
        ПустыхПодряд = ПустыхПодряд + 1;
    КонецЕсли;
    НомерСтроки = НомерСтроки + 1;
КонецЦикла;

Решение 5: Современный метод «Прочитать()» (рекомендуется)

Если вы работаете на платформе 8.3, самым быстрым и стабильным способом будет отказ от COM-объекта в пользу встроенных средств 1С. Объект ТабличныйДокумент умеет читать файлы .xlsx напрямую, что исключает проблемы с установленными версиями Excel и разрядностью систем (32/64 бит).

Посмотрим на пример этого метода:


ТабДок = Новый ТабличныйДокумент;
Попытка
    // Используем способ чтения Direct для скорости и надежности
    ТабДок.Прочитать(ИмяФайла, СпособЧтенияЗначенийТабличногоДокумента.Значение);
    
    ВсегоСтрок = ТабДок.ВысотаТаблицы;
    Для Стр = 1 По ВсегоСтрок Цикл
        // Читаем данные из ячеек таблицы 1С
        Артикул = ТабДок.Область(Стр, 1).Текст;
        // ... остальная логика
    КонецЦикла;
Исключение
    Сообщить("Ошибка чтения файла: " + ОписаниеОшибки());
КонецПопытки;

Преимущества данного метода:

Не требуется наличие установленного Microsoft Excel на сервере или компьютере пользователя.
Корректная обработка всех строк, независимо от «глюков» UsedRange.
Высокая скорость работы с большими файлами (свыше 10 000 строк).

Подведение итогов

Проанализировав ситуацию, мы видим, что неполная загрузка строк — это не мистика, а следствие особенностей работы интерфейса COM. Для небольших файлов достаточно подправить расчет индекса последней строки через UsedRange.Row + UsedRange.Rows.Count. Однако для серьезных задач и больших объемов данных (от 5000 строк и выше) правильным решением будет использование метода ТабДок.Прочитать() или предварительная конвертация данных в формат .csv.