Как правильно загрузить договоры из Excel в 1С и избежать типичных ошибок COM-соединения

← На главную

Интеграция 1С с внешними файлами, в частности с таблицами Microsoft Excel, — одна из самых частых задач в практике программиста. Однако при написании кода импорта разработчики нередко сталкиваются с загадочным поведением программы: количество обрабатываемых строк внезапно уменьшается при наличии сортировки на листе, данные считываются некорректно, а незакрытые процессы Excel начинают перегружать оперативную память сервера. Кроме того, ошибки в логике поиска объектов в самой 1С могут приводить к аварийному завершению работы обработки.

В этой статье мы подробно разберем, почему возникают сбои при подсчете строк в Excel через COM-соединение, как исправить логическую ошибку проверки поиска контрагентов и как переписать код так, чтобы он работал стабильно и быстро.

Выясняем причину: почему ломается подсчет строк (проблема 53 и 1 строки)

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

КоличествоСтрок = Excel.Cells(1,1).SpecialCells(11).Row;

Использование конструкции SpecialCells(11), которая является аналогом сочетания клавиш Ctrl + End в самом Excel, — это крайне ненадежный подход по нескольким техническим причинам:

Влияние фильтров и сортировки. Если в файле Excel включен автофильтр (AutoFilter) или активна пользовательская сортировка, метод SpecialCells(11) возвращает номер последней видимой строки на момент сохранения файла. Если отфильтрованной оказалась только часть данных (например, до 53-й строки), то 1С получит число 53 и завершит цикл досрочно, полностью проигнорировав оставшиеся строки, которые фактически находятся в файле.
Кэширование "грязных" ячеек. Excel хранит в памяти так называемую "используемую область". Если пользователь когда-то отформатировал пустые строки в самом низу листа, а затем стер их содержимое, Excel все равно будет считать эти ячейки активными. В этом случае метод вернет завышенное число строк (например, несколько тысяч вместо реальных двухсот).
Проблема пустых файлов (возврат 1 строки). Если файл был только что сгенерирован программно или кэш используемой области не успел обновиться в памяти COM-объекта, метод SpecialCells(11) может вернуть значение 1.

Правильное решение: Для надежного получения индекса последней заполненной строки необходимо использовать свойство UsedRange активного листа. Разберем по шагам рабочий алгоритм определения границ данных:


Лист = Excel.Sheets(1);
// Находим реальную последнюю строку таблицы, даже если она начинается не с первой строки
КоличествоСтрок = Лист.UsedRange.Row + Лист.UsedRange.Rows.Count - 1;

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

Исправляем ошибку проверки контрагента в 1С

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

Если Контрагент = Неопределено Тогда...

Внимание: это грубая ошибка! Проанализируем особенности работы платформы 1С с поиском элементов справочников. Метод НайтиПоНаименованию() в случае неуспешного поиска возвращает не значение Неопределено, а пустую ссылку соответствующего типа (в данном случае — Справочники.Контрагенты.ПустаяСсылка()).

Если написать проверку на равенство значению Неопределено, она никогда не выполнится. Код продолжит работу и попытается создать новый договор, присвоив реквизиту Владелец пустую ссылку:

НовыйОбъект.Владелец = Контрагент; // Сюда запишется пустая ссылка контрагента

Поскольку реквизит Владелец для подчиненного справочника ДоговорыКонтрагентов является обязательным для заполнения на уровне метаданных, при вызове метода НовыйОбъект.Записать() система выдаст критическую ошибку транзакции: "Значение реквизита 'Владелец' не заполнено!".

Посмотрим на пример правильной проверки результата поиска:


Контрагент = Справочники.Контрагенты. НайтиПоНаименованию(Excel.Cells(СтрокаОтсчета, 23).Text, Истина);

Если Контрагент.Пустая() Тогда
    Сообщить("Ошибка в строке " + СтрокаОтсчета + ": контрагент '" + Excel.Cells(СтрокаОтсчета, 23).Text + "' не найден в базе 1С. Строка пропущена.");
    Продолжить; // Переходим к чтению следующей строки файла Excel
КонецЕсли;

Предотвращаем утечку памяти и зависание процессов Excel

В исходном коде автора вызов закрытия приложения Excel написан только в самом конце успешного выполнения процедуры. Если на середине выполнения цикла (например, на 100-й строке из 200) произойдет какая-либо ошибка (дублирование договора, сбой транзакции СУБД, деление на ноль), выполнение кода прервется. В результате процесс EXCEL.EXE останется "висеть" в памяти операционной системы компьютера или сервера.

При регулярном использовании такой обработки сервер быстро исчерпает свободные ресурсы RAM, что приведет к замедлению работы всей системы. Обязательно заворачивайте блок загрузки в конструкцию Попытка...Исключение и закрывайте COM-объект в любом случае.

Оптимизируем производительность: почему COM-соединение работает медленно

Построчное чтение ячеек в цикле 1С через COM-соединение с помощью обращения Excel.Cells(Строка, Колонка).Text выполняется очень медленно, поскольку каждый такой вызов инициирует ресурсоемкий межпроцессный обмен данными между 1С и Excel. При объеме файла в несколько тысяч строк загрузка может растянуться на десятки минут.

Рассмотрим два альтернативных варианта оптимизации чтения данных.

Вариант А. Чтение всего листа Excel в массив за один запрос

Вместо циклического обращения к каждой ячейке мы можем выгрузить все данные листа в двумерный массив 1С за одно обращение к COM-объекту. Это ускорит чтение данных в десятки раз:


// Получаем данные заполненной области в виде SafeArray
ДанныеЛиста = Лист.UsedRange.Value.Выгрузить();
КоличествоСтрок = ДанныеЛиста.ПолучитьДлину(1); // Количество строк в массиве

Вариант Б. Чтение файла напрямую через Табличный Документ (Рекомендуемый способ)

Начиная с версии платформы 8.3, 1С умеет читать файлы формата .xlsx напрямую, без использования установленного Microsoft Office. Это самое быстрое и безопасное решение, которое работает как на стороне клиента, так и на сервере под управлением Linux.

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


ТабДок = Новый ТабличныйДокумент;
Попытка
    ТабДок.Прочитать(Объект.Файл, СпособИмпортаТабличногоДокумента.Excel);
Исключение
    Сообщить("Не удалось прочитать файл Excel! Ошибка: " + ОписаниеОшибки());
    Возврат;
КонецПопытки;

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

Для СтрокаОтсчета = 2 По КоличествоСтрок Цикл
    // Читаем текст из 23-й колонки (наименование контрагента)
    ИмяКонтрагента = СокрЛП(ТабДок.ПолучитьОбласть(СтрокаОтсчета, 23, СтрокаОтсчета, 23).ТекущийРисунок.ПолучитьТекст()); // Или через получение области ячеек
    
    // Если используем прямое чтение текста ячейки:
    ТекстЯчейки = ТабДок.Область(СтрокаОтсчета, 23).Текст;
    
    Если ПустаяСтрока(ТекстЯчейки) Тогда
        Продолжить;
    КонецЕсли;
    
    Контрагент = Справочники.Контрагенты.НайтиПоНаименованию(ТекстЯчейки, Истина);
    Если Контрагент.Пустая() Тогда
        Сообщить("Строка " + СтрокаОтсчета + ": Контрагент '" + ТекстЯчейки + "' не найден!");
        Продолжить;
    КонецЕсли;
    
    // Создаем договор
    НовыйДоговор = Справочники.ДоговорыКонтрагентов.СоздатьЭлемент();
    НовыйДоговор.Владелец = Контрагент;
    НовыйДоговор.Организация = Объект.Организация;
    НовыйДоговор.Наименование = "ИСО_пост_долл";
    НовыйДоговор.ВидДоговора = Перечисления.ВидыДоговоровКонтрагентов.СПоставщиком;
    
    Попытка
        НовыйДоговор.Записать();
        Сообщить("Успешно создан договор для: " + Контрагент);
    Исключение
        Сообщить("Не удалось записать договор для контрагента " + Контрагент + ". Ошибка: " + ОписаниеОшибки());
    КонецПопытки;
КонецЦикла;

Итоговый надежный вариант кода с использованием COM-объекта

Если по техническим причинам вам все же необходимо использовать классическое COM-соединение, примените данный доработанный и безопасный шаблон кода. В нем решены проблемы с закрытием процессов, очисткой памяти, фильтрацией строк в Excel и корректным поиском контрагентов в базе данных 1С:


Excel = Неопределено;
Книга = Неопределено;

Попытка
    Excel = Новый COMОбъект("Excel.Application");
    Книга = Excel.WorkBooks.Open(Объект.Файл);
    Лист = Книга.Sheets(1);
Исключение
    Сообщить("Ошибка инициализации Excel или чтения файла!");
    Сообщить(ОписаниеОшибки());
    Если Excel <> Неопределено Тогда
        Excel.Quit();
        Excel = 0;
    КонецЕсли;
    Возврат;
КонецПопытки;

Попытка
    // Надежное определение количества строк таблицы через UsedRange
    НачальнаяСтрока = Лист.UsedRange.Row;
    ВсегоСтрокДанных = Лист.UsedRange.Rows.Count;
    КоличествоСтрок = НачальнаяСтрока + ВсегоСтрокДанных - 1;
    
    Для СтрокаОтсчета = 2 По КоличествоСтрок Цикл
        
        НаименованиеКонтрагента = СокрЛП(Лист.Cells(СтрокаОтсчета, 23).Text);
        
        Если ПустаяСтрока(НаименованиеКонтрагента) Тогда
            Продолжить; // Пропускаем пустые строки таблицы
        КонецЕсли;
        
        // Поиск контрагента в базе 1С
        Контрагент = Справочники.Контрагенты.НайтиПоНаименованию(НаименованиеКонтрагента, Истина);
        
        // Корректная проверка на пустую ссылку
        Если Контрагент.Пустая() Тогда
            Сообщить("Строка " + СтрокаОтсчета + ": Контрагент с наименованием '" + НаименованиеКонтрагента + "' не найден в справочнике! Договор не создан.");
            Продолжить;
        КонецЕсли;
        
        // Проверяем, существует ли уже такой договор, чтобы избежать дублирования
        ДоговорСсылка = Справочники.ДоговорыКонтрагентов.НайтиПоРеквизиту("Владелец", Контрагент);
        // Дополнительно можно проверять по наименованию договора
        
        НовыйОбъект = Справочники.ДоговорыКонтрагентов.СоздатьЭлемент();
        НовыйОбъект.Владелец = Контрагент;
        НовыйОбъект.Организация = Объект.Организация;
        НовыйОбъект.Наименование = "ИСО_пост_долл";
        НовыйОбъект.ВидДоговора = Перечисления.ВидыДоговоровКонтрагентов.СПоставщиком;
        
        Попытка
            НовыйОбъект.Записать();
            Сообщить("Создан и заполнен договор для контрагента: " + Контрагент);
        Исключение
            Сообщить("Ошибка записи договора в строке " + СтрокаОтсчета + ": " + ОписаниеОшибки());
        КонецПопытки;
        
    КонецЦикла;
    
Исключение
    Сообщить("Произошла непредвиденная ошибка в процессе обработки данных!");
    Сообщить(ОписаниеОшибки());
КонецПопытки;

// Обязательный блок закрытия процессов Excel в операционной системе
Попытка
    Если Книга <> Неопределено Тогда
        Книга.Close(Ложь); // Закрываем файл без сохранения изменений
    КонецЕсли;
    Если Excel <> Неопределено Тогда
        Excel.Quit();
        Excel = 0;
    КонецЕсли;
Исключение
    Excel = 0;
КонецПопытки;

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