Почему Excel возвращает лишние листы при загрузке данных через ADOX в 1С и как это исправить

← На главную

При программной загрузке документов Excel в 1С с использованием технологий ADO/ADOX разработчики часто сталкиваются с ситуацией, когда коллекция таблиц axCatalog.Tables содержит больше элементов, чем визуально отображается листов в файле. Из-за этого при обходе всех листов происходит задвоение или повторное считывание одной и той же информации.

В этой статье мы разберем причину такого поведения провайдера OLEDB, проанализируем варианты решения проблемы в коде 1С и рассмотрим альтернативные подходы к загрузке файлов Excel.

Причина проблемы: особенности работы OLEDB и ADOX с файлами Excel

Рассмотрим, как работает провайдер Microsoft.ACE.OLEDB.12.0 (или Microsoft.Jet.OLEDB.4.0). Для библиотеки ADO любой файл Excel представляется как база данных, а его содержимое — как наборы таблиц. При этом таблицами считаются не только физические рабочие листы книги, но и следующие объекты:

Автофильтры и диапазоны фильтрации: При установке фильтра в Excel создается скрытый системный именованный диапазон, содержащий в имени строку _xlnm#_FilterDatabase.
Пользовательские именованные диапазоны (Named Ranges): Любая область ячеек, которой через «Диспетчер имен» было присвоено собственное имя.
Системные области печати: Области _xlnm#Print_Area и сквозные строки _xlnm#Print_Titles.

Поскольку физический лист книги и установленный на нем фильтр содержат одни и те же данные, последовательное чтение всех элементов из ADOX.Catalog.Tables приводит к считыванию строк с одного и того же листа дважды.

Способ 1. Исключение системных имен автофильтров в коде 1С

Если в вашей обработке уже используется объект ADOX.Catalog, проще всего добавить проверку наименования выбираемой таблицы перед выполнением SQL-запроса SELECT.

Рассмотрим пример кода, который исключает таблицы автофильтров:


КаталогКом = Новый COMОбъект("ADOX.Catalog");
КаталогКом.ActiveConnection = Connection;

Для НомерЛиста = 0 По КаталогКом.Tables.Count - 1 Цикл
    ИмяЛиста = КаталогКом.Tables(НомерЛиста).Name;
    
    // Пропускаем системные таблицы автофильтрации
    Если Найти(ИмяЛиста, "_xlnm#_FilterDatabase") > 0 Или Найти(ИмяЛиста, "ФильтрБазы") > 0 Тогда
        Продолжить;
    КонецЕсли;

    // Формируем текст запроса к реальному листу
    СтрЗапроса = "SELECT * FROM [" + ИмяЛиста + "]";
    
    Попытка
        Выборка = Connection.Execute(СтрЗапроса);
    Исключение
        ВызватьИсключение "Не удалось прочитать данные с листа: " + ИмяЛиста;
    КонецПопытки;

    Пока НЕ Выборка.EOF Цикл
        // Обработка строк данных
        Выборка.MoveNext();
    КонецЦикла;
КонецЦикла;

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

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

В OLEDB имена реальных листов всегда оканчиваются символом $ (или $', если имя листа содержит пробелы или спецсимволы). Именованные диапазоны и системные области (включая фильтры) этого символа на конце не имеют.

Проанализируем пример проверки имени таблицы на наличие символа $:


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

    // Далее следует чтение данных листа...
КонецЦикла;

Альтернативное решение: Отказ от ADO в пользу штатных средств 1С

Использование COM-объектов ADODB.Connection и ADOX.Catalog влечет за собой ряд ограничений: зависимость от разрядности установленного драйвера OLEDB (32/64 бит), проблемы с неверным определением типов данных в колонках и ошибки с именованными диапазонами.

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

Разберем пример штатного чтения файла Excel:


ТабДок = Новый ТабличныйДокумент;

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

// Обход ячеек табличного документа
ВысотаТаблицы = ТабДок.ВысотаТаблицы;
ШиринаТаблицы = ТабДок.ШиринаТаблицы;

Для НомерСтроки = 1 По ВысотаТаблицы Цикл
    Для НомерКолонки = 1 По ШиринаТаблицы Цикл
        ЗначениеЯчейки = ТабДок.Область(НомерСтроки, НомерКолонки).Текст;
    КонецЦикла;
КонецЦикла;

Применение объекта ТабличныйДокумент гарантирует, что программа прочитает исключительно видимые данные листов и полностью исключит дублирование строк из-за скрытых системных автофильтров Excel.