Как прочитать данные из сложного или защищенного Excel-файла в 1С?

← На главную

В процессе работы часто возникает задача загрузки данных из файлов Microsoft Excel. Стандартный метод ТабличныйДокумент.Прочитать() в 1С отлично справляется с простыми файлами, но что делать, если он выдает ошибку? Такая ситуация может возникнуть, если файл имеет сложную структуру, защищен от редактирования или был создан в стороннем приложении. Давайте подробно разберем, почему так происходит и какие существуют альтернативные способы для чтения таких файлов.

Проанализируем типичный сценарий. Мы пытаемся прочитать файл с расширением .xls. Первое, что приходит в голову — возможно, проблема в старом формате. А что если просто переименовать файл, изменив расширение на .xlsx? К сожалению, это не решение. Простое переименование меняет только имя файла, но не его внутреннюю структуру (формат данных). В результате Excel и другие программы, включая 1С, скорее всего, не смогут его открыть, так как будут ожидать увидеть структуру формата .xlsx (архив с XML-файлами), а по факту получат бинарную структуру старого .xls.

Основная задача в таких случаях — не конвертировать файл, а надежно извлечь из него данные в ТаблицуЗначений для дальнейшей обработки. Рассмотрим несколько рабочих методов, которые помогут это сделать, когда стандартные средства бессильны.

Метод 1: Использование технологии ADO (ActiveX Data Objects)

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

Что для этого нужно?

Для работы этого метода необходимо, чтобы в системе были установлены специальные драйверы для доступа к данным. Их можно скачать с официального сайта Microsoft:

Для старых форматов .xls (Excel 97-2003) используется драйвер Microsoft Jet OLE DB 4.0. Обычно он уже есть в Windows.
Для новых форматов .xlsx (Excel 2007 и новее) требуется Microsoft Access Database Engine (ранее ACE OLEDB Provider). Важно установить версию (x32 или x64), соответствующую разрядности процесса 1С, который будет выполнять код.

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

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


// Функция для чтения данных из Excel файла через ADO
// Возвращает ТаблицуЗначений
Функция ПрочитатьExcelЧерезADO(ПолноеИмяФайла)

    // 1. Определяем, старый или новый формат файла, чтобы сформировать правильную строку подключения
    Расширение = НРег(Прав(ПолноеИмяФайла, 4));
    ЭтоСтарыйФормат = (Расширение = ".xls");

    Попытка
        // Создаем COM-объект для подключения
        ADODBConnection = Новый COMОбъект("ADODB.Connection");
    Исключение
        Сообщить("Не удалось создать объект ADODB.Connection. Проверьте, зарегистрирована ли компонента в системе.");
        Возврат Неопределено;
    КонецПопытки;

    // 2. Формируем строку подключения
    Если ЭтоСтарыйФормат Тогда
        // Строка для .xls (Excel 97-2003)
        СтрокаПодключения = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ПолноеИмяФайла + ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";";
    Иначе
        // Строка для .xlsx (Excel 2007 и новее)
        СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПолноеИмяФайла + ";Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1"";";
    КонецЕсли;

    // 3. Открываем соединение
    Попытка
        ADODBConnection.Open(СтрокаПодключения);
    Исключение
        ОписаниеОшибки = ОписаниеОшибки();
        Сообщить("Не удалось подключиться к файлу Excel! " + ОписаниеОшибки);
        Сообщить("Возможные причины: не установлен драйвер Microsoft Access Database Engine или указан неверный путь к файлу.");
        Возврат Неопределено;
    КонецПопытки;

    // 4. Получаем схему данных, чтобы узнать имена листов
    Попытка
        ADODBRecordSetShema = ADODBConnection.OpenSchema(20); // 20 - adSchemaTables
        ИмяПервогоЛиста = ADODBRecordSetShema.Fields("TABLE_NAME").Value;
        ADODBRecordSetShema.Close();
    Исключение
        Сообщить("Не удалось получить список листов из файла Excel.");
        ADODBConnection.Close();
        Возврат Неопределено;
    КонецПопытки;

    // 5. Выполняем SQL-запрос к первому листу
    ТекстЗапроса = "SELECT * FROM [" + ИмяПервогоЛиста + "]";
    ADODBRecordSet = Новый COMОбъект("ADODB.Recordset");
    ADODBRecordSet.Open(ТекстЗапроса, ADODBConnection);

    // 6. Создаем и заполняем ТаблицуЗначений
    ТЗ = Новый ТаблицаЗначений;
    
    // Создаем колонки
    Для Каждого Поле Из ADODBRecordSet.Fields Цикл
        ТЗ.Колонки.Добавить(Поле.Name);
    КонецЦикла;

    // Заполняем строки
    Пока НЕ ADODBRecordSet.EOF Цикл
        НоваяСтрока = ТЗ.Добавить();
        Для Каждого Колонка Из ТЗ.Колонок Цикл
            НоваяСтрока[Колонка.Имя] = ADODBRecordSet.Fields(Колонка.Имя).Value;
        КонецЦикла;
        ADODBRecordSet.MoveNext();
    КонецЦикла;

    // 7. Закрываем соединения
    ADODBRecordSet.Close();
    ADODBConnection.Close();

    Возврат ТЗ;

КонецФункции

Важные параметры в строке подключения:

HDR=Yes: указывает, что первая строка на листе — это заголовок. Драйвер будет использовать значения из этой строки как имена полей (колонок). Если заголовка нет, используйте HDR=No.
IMEX=1: (Import Mixed Types) — режим "импорта смешанных данных". Он заставляет драйвер рассматривать столбцы со смешанными типами данных (например, числа и текст) как текстовые. Это помогает избежать потерь данных, когда в числовом столбце вдруг встречается текстовое значение.

Этот метод отлично подходит для чтения "сырых" данных и часто игнорирует защиту листа от редактирования, так как он не взаимодействует с файлом через интерфейс Excel, а читает его напрямую как источник данных.

Метод 2: Использование внешних компонент или библиотек OpenXML

Если работа с ADO кажется слишком громоздкой или требует установки драйверов, что не всегда возможно, на помощь приходят современные решения.

Внешние компоненты (Native API)

Существует множество готовых внешних компонент для 1С (платных и бесплатных), которые предназначены специально для работы с Excel. Они написаны на C++, C# или других языках и предоставляют очень простой и удобный программный интерфейс. Как правило, работа с ними сводится к нескольким строчкам кода:


// Примерный код, синтаксис зависит от конкретной компоненты
КомпонентаExcel = Новый("AddIn.ExcelReader"); // Подключаем компоненту
Книга = КомпонентаExcel.ОткрытьФайл("C:\МоиДокументы\отчет.xlsx");
Лист = Книга.ПолучитьЛист(0); // Получаем первый лист по индексу
ЗначениеЯчейки = Лист.ПолучитьЗначение("A1");
ТЗ = Лист.ВыгрузитьВТаблицуЗначений("A1:D100"); // Часто есть готовые методы для выгрузки

Преимущества этого подхода очевидны: высокая скорость, отсутствие зависимости от Microsoft Excel и драйверов ADO, а также удобный API, который скрывает всю сложность парсинга файла.

Библиотеки для работы с OpenXML

Формат .xlsx — это, по сути, ZIP-архив, содержащий XML-файлы с данными, стилями, структурой и т.д. Можно работать с этими файлами напрямую. В 1С это можно сделать, например, через .NET-библиотеки (если 1С работает на Windows) или используя встроенные объекты для работы с архивами и XML. Этот способ требует глубокого понимания структуры формата OpenXML, но дает полный контроль над процессом.

Метод 3: Конвертация через сторонние утилиты

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

Идея в том, чтобы перед чтением в 1С сконвертировать проблемный файл в более простой формат, например, CSV. Для этого можно использовать консольные утилиты.

LibreOffice/OpenOffice в "безголовом" режиме (headless). Эти офисные пакеты можно запустить из командной строки с параметрами, которые заставят его открыть Excel-файл и сохранить его в другом формате (например, CSV) без отображения пользовательского интерфейса.
Специализированные утилиты. Существуют утилиты, например, xls2csv, которые делают ровно одну вещь: конвертируют Excel в CSV.

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

Что делать с защитой файла?

Важно различать типы защиты в Excel:

Защита паролем на открытие файла. Если файл зашифрован и требует пароль для открытия, то ни один из методов не сработает без этого пароля. В строке подключения ADO или в методах компонент обычно есть параметр для передачи пароля.
Защита листа или книги от изменений. Этот тип защиты обычно не мешает чтению данных программными средствами, такими как ADO или внешние компоненты. Защита нацелена на интерактивного пользователя, работающего в Excel, а не на программы, читающие "сырые" данные. Именно поэтому ADO является отличным решением для файлов, которые "защищены" от редактирования.

Подведем итог. Если вы столкнулись с ошибкой при чтении Excel-файла стандартным ТабДок.Прочитать(), не спешите сдаваться. Сначала проанализируйте ситуацию и попробуйте альтернативные, более мощные методы. В большинстве случаев связка 1С и ADO поможет вам надежно извлечь данные даже из самых проблемных файлов, не требуя при этом установки самого Microsoft Excel.