Как исправить ошибку «Номер за пределами значения» при загрузке данных из Excel в 1С 7.7

← На главную

При реализации загрузки прайс-листов, накладных или каталогов товаров из файлов MS Excel в 1С 7.7 разработчики часто сталкиваются с ошибкой «Номер за пределами значения». Разберем типичную ситуацию: алгоритм выполняет построчный обход строк листа Excel, проверяет ячейки на заполненность и переносит значения в объект ТаблицаЗначений. В определенный момент выполнение аварийно прерывается.

Проанализируем причины возникновения этой проблемы, логические ошибки в коде и рассмотрим правильные варианты реализации загрузки с учетом особенностей платформы 1С 7.7 и COM-соединения с Excel.

Почему возникает ошибка «Номер за пределами значения»

Рассмотрим исходный фрагмент кода, вызвавший сбой:


Для Стр = 1 По Строк Цикл
    Если ПустоеЗначение(E.Sheets(Лист).Cells(Стр + 10, 10).Value) = 1 Тогда
        Запомнить = Запомнить + 1;
        Если Запомнить > 1 Тогда
            Прервать;
        Иначе
            Продолжить;
        КонецЕсли;
    Иначе
        ТЗ.НоваяСтрока(Стр);
        ТЗ.УстановитьЗначение(Стр, "ШтрихКод", СокрЛП(E.Sheets(Лист).Cells(Стр + 10, 10).Value));
        ТЗ.УстановитьЗначение(Стр, "ЦЗак", СокрЛП(E.Sheets(Лист).Cells(Стр + 10, 12).Value));
        ТЗ.УстановитьЗначение(Стр, "ЦОпт", СокрЛП(E.Sheets(Лист).Cells(Стр + 10, 14).Value));
    КонецЕсли;
КонецЦикла;

В этом коде кроются сразу две критические ошибки:

Рассинхронизация номера строки цикла и количества строк в ТаблицеЗначений. Метод ТЗ.НоваяСтрока(Позиция) пытается вставить строку в указанную позицию. Если параметр Позиция превышает текущее количество строк в таблице плюс один, строка просто добавляется в конец. Допустим, на 3-й итерации цикла ячейка оказалась пустой, и сработал оператор Продолжить. В ТаблицаЗначений остались только 2 строки. На 4-й итерации (Стр = 4) метод ТЗ.НоваяСтрока(4) добавляет третью по счету строку, но следом вызывается ТЗ.УстановитьЗначение(4, ...). Поскольку 4-й строки в таблице физически нет, платформа генерирует исключение «Номер за пределами значения».
Ошибочный алгоритм подсчета подряд идущих пустых строк. Переменная-счетчик Запомнить инкрементируется при каждой пустой строке, но при обнаружении непустой строки значение счетчика не сбрасывается обратно в 0. В результате загрузка прервется при обнаружении любых двух пустых строк в файле, даже если между ними были сотни заполненных позиций.

Вариант 1. Использование прямого обращения к реквизитам текущей строки (рекомендуемый)

Самый простой, читаемый и надежный способ заполнения ТаблицаЗначений в 1С 7.7 — добавление строки методом НоваяСтрока() без параметров с последующим обращением к колонкам через точку или через свойство НомерСтроки. Также не забываем сбрасывать счетчик пустых строк в ветке Иначе.

Посмотрим на исправленный код:


Лист = 1;
E = СоздатьОбъект("Excel.Application");
E.WorkBooks.Open(ФайлСоотв);

// Определяем последнюю строку
Строк = E.Sheets(Лист).Cells.SpecialCells(11).Row;

ТЗ = СоздатьОбъект("ТаблицаЗначений");
ТЗ.НоваяКолонка("ШтрихКод");
ТЗ.НоваяКолонка("ЦЗак");
ТЗ.НоваяКолонка("ЦОпт");

Запомнить = 0;

Для Стр = 1 По Строк Цикл
    ЗначениеЯчейки = СокрЛП(E.Sheets(Лист).Cells(Стр + 10, 10).Value);
    
    Если ПустоеЗначение(ЗначениеЯчейки) = 1 Тогда
        Запомнить = Запомнить + 1;
        Если Запомнить > 1 Тогда
            // Встретили 2 пустые строки подряд — прерываем обработку
            Прервать;
        Иначе
            // Пропускаем одиночную пустую строку
            Продолжить;
        КонецЕсли;
    Иначе
        // Обязательно сбрасываем счетчик, так как строка не пустая
        Запомнить = 0;
        
        ТЗ.НоваяСтрока();
        ТЗ.ШтрихКод = ЗначениеЯчейки;
        ТЗ.ЦЗак     = СокрЛП(E.Sheets(Лист).Cells(Стр + 10, 12).Value);
        ТЗ.ЦОпт     = СокрЛП(E.Sheets(Лист).Cells(Стр + 10, 14).Value);
    КонецЕсли;
КонецЦикла;

Вариант 2. Использование УстановитьЗначение с позиционированием по НомерСтроки

Если по стандартам разработки или особенностям алгоритма требуется использовать метод УстановитьЗначение(), в качестве первого параметра необходимо передавать свойство ТЗ.НомерСтроки (номер только что добавленной текущей строки), а не переменную итератора цикла Стр:


ТЗ.НоваяСтрока();
ТЗ.УстановитьЗначение(ТЗ.НомерСтроки, "ШтрихКод", ЗначениеЯчейки);
ТЗ.УстановитьЗначение(ТЗ.НомерСтроки, "ЦЗак", СокрЛП(E.Sheets(Лист).Cells(Стр + 10, 12).Value));
ТЗ.УстановитьЗначение(ТЗ.НомерСтроки, "ЦОпт", СокрЛП(E.Sheets(Лист).Cells(Стр + 10, 14).Value));

Важные рекомендации при работе с Excel в 1С 7.7

Разберем дополнительные нюансы, которые предотвратят зависание процессов в системе и искажение загружаемых данных:

Освобождение COM-объекта и выгрузка EXCEL.EXE из памяти. Если не закрыть книгу и приложение явным образом, процесс EXCEL.EXE останется висеть в диспетчере задач Windows, блокируя файл. Всегда оборачивайте завершение работы в блок очистки:
```
E.WorkBooks.Close();
E.Quit();
E = 0;
```
Чтение текстовых данных и длинных штрихкодов (.Text вместо .Value). При обращении к свойству .Value ячейки со штрихкодом Excel может вернуть число в экспоненциальном виде (например, 4,60123E+12) или отрезать лидирующие нули. Для штрихкодов и строковых кодов безопаснее считывать свойство .Text: ТЗ.ШтрихКод = СокрЛП(E.Sheets(Лист).Cells(Стр + 10, 10).Text);
Корректное определение последней заполненной строки. Метод SpecialCells(11) (константа xlCellTypeLastCell) возвращает последнюю ячейку, когда-либо изменявшуюся на листе, даже если данные из нее были удалены. Если требуется найти фактический конец данных по заполненной колонке, эффективнее использовать метод End(xlUp):
```
// -4162 соответствует константе xlUp
Строк = E.Sheets(Лист).Cells(65536, 10).End(-4162).Row;
```