Почему OLE DB и ADOX.Catalog неверно определяют количество листов в Excel при загрузке в 1С?

← На главную

При использовании COM-объектов ADODB.Connection и ADOX.Catalog для чтения файлов Excel разработчики часто сталкиваются с ситуацией, когда коллекция axCatalog.Tables возвращает больше объектов, чем физически отображается листов в файле (например, 2 листа вместо 1). В результате алгоритм загрузки позиций отрабатывает циклически несколько раз, приводя к задвоению данных в 1С.

Рассмотрим подробно, почему возникают лишние «фантомные» листы и как правильно доработать код загрузки.

Причины возникновения лишних таблиц в OLE DB

Проанализируем механику работы драйверов OLE DB (Microsoft Jet / Microsoft ACE). Когда драйвер открывает документ Excel, он воспринимает файл не просто как набор вкладок, а как реляционную базу данных. В коллекцию axCatalog.Tables попадают не только физические рабочие листы, но и следующие объекты:

Поименованные области (диапазоны): Если на листе создан поименованный диапазон через «Диспетчер имен» или умную таблицу Excel, драйвер регистрирует её как отдельную таблицу.
Служебные системные диапазоны: При установке параметров печати в документе автоматически создаётся диапазон Print_Area. При использовании автофильтра создаётся диапазон _FilterDatabase. Они также считываются драйвером как отдельные объекты.
Скрытые и «очень скрытые» листы: В Excel лист может иметь состояние видимости xlSheetVeryHidden (значение -1), которое не отображается в стандартном меню «Отобразить лист», но доступно в редакторе VBA. OLE DB видит такие листы наравне с обычными.

Способ 1. Проверка имени таблицы на символ `$`

Выясним главный признак, позволяющий отличить реальный рабочий лист Excel от поименованного диапазона или служебной области. В драйвере OLE DB имена физических листов книги всегда оканчиваются на символ $ (например, Лист1$ или 'Мой Лист$'). Поименованные области и фильтры этого символа на конце не имеют.

Разберем доработку обхода коллекции axCatalog.Tables с фильтрацией по конечному символу:


Для Каждого Лист ИЗ axCatalog.Tables Цикл
    ИмяТаблицы = СокрЛП(Лист.Name);
    
    // Удаляем кавычки, если имя листа содержит пробелы
    ИмяТаблицы = СтрЗаменить(ИмяТаблицы, "'", "");
    
    // Проверяем, оканчивается ли имя на $
    Если Прав(ИмяТаблицы, 1) <> "$" Тогда
        // Это поименованный диапазон, FilterDatabase или Print_Area - пропускаем
        Продолжить;
    КонецЕсли;
    
    // Дополнительно исключаем системные области, если знак $ присутствовал в имени диапазона
    Если Найти(ИмяТаблицы, "Print_Area") > 0 Или Найти(ИмяТаблицы, "FilterDatabase") > 0 Тогда
        Продолжить;
    КонецЕсли;
    
    // Далее выполняется чтение данных реального листа
    // ...
КонецЦикла;

Способ 2. Использование метода OpenSchema вместо ADOX.Catalog

Вместо использования библиотеки ADOX можно запросить схему таблиц непосредственно из объекта ADODB.Connection с помощью метода OpenSchema. Это позволяет проанализировать тип таблицы (поле TABLE_TYPE), где реальные листы обозначаются как TABLE, а системные структуры — как SYSTEM TABLE.

Рассмотрим пример получения списка таблиц через схему:


// 20 соответствует константе adSchemaTables
Recordset = Connection.OpenSchema(20); 

Пока НЕ Recordset.EOF Цикл
    ИмяТаблицы = Recordset.Fields("TABLE_NAME").Value;
    ТипТаблицы = Recordset.Fields("TABLE_TYPE").Value;
    
    ИмяТаблицыОчищенное = СтрЗаменить(СокрЛП(ИмяТаблицы), "'", "");
    
    Если ТипТаблицы = "TABLE" И Прав(ИмяТаблицыОчищенное, 1) = "$" Тогда
        // Обработка только физических листов
    КонецЕсли;
    
    Recordset.MoveNext();
КонецЦикла;
Recordset.Close();

Способ 3. Переход на встроенный объект 1С «ТабличныйДокумент»

Использование COM-объектов OLE DB / ADO имеет ряд существенных ограничений:

Работает исключительно в операционной системе Windows.
Требует установленного драйвера Microsoft Access Database Engine подходящей разрядности (32 или 64 бит).
Не гарантирует порядок следования листов (таблицы возвращаются по алфавиту или по дате создания).

Для решения этих проблем в современной платформе 1С:Предприятие рекомендуется использовать встроенный объект ТабличныйДокумент. Метод Прочитать() позволяет загружать файлы форматов .xls и .xlsx нативно, кроссплатформенно и без вызова сторонних драйверов.

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


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

// Табличный документ содержит только данные активного/первого листа файла
КоличествоСтрок = ТабДок.ВысотаТаблицы;
КоличествоКолонок = ТабДок.ШиринаТаблицы;

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

Использование проверки символа $ в именах таблиц при работе через ADOX или переход на ТабличныйДокумент полностью исключают проблему с фантомными листами и задвоением загружаемых позиций.