При автоматизации загрузки данных из внешних источников часто возникает задача обработки файлов Excel (в форматах .xls и .xlsx) на сервере, где не установлен пакет Microsoft Office. Установка полноценного офисного пакета на сервер считается плохой практикой из-за проблем с безопасностью, лицензированием и стабильностью COM-объектов. В этой статье мы подробно разберем, как решить эту задачу максимально быстро и эффективно, используя различные подходы.
Если вам привычно работать с данными через SQL-запросы, использование технологии ADO (ActiveX Data Objects) является отличным вариантом. Для этого не нужен Excel, но необходимо установить специальный драйвер от Microsoft.
Для работы с современными форматами .xlsx (начиная с Office 2007) потребуется Microsoft Access Database Engine, хотя для большинства задач достаточно настроить импорт данных в форматах xls, xlsx, ods и csv. Рассмотрим по шагам процесс настройки:
/passive.Проанализируем пример строки подключения через ADO:
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу + ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";";
Connection = Новый COMОбъект("ADODB.Connection");
Connection.Open(ConnectionString);
Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = Connection;
Command.CommandText = "SELECT * FROM [Лист1$]"; // Обратите внимание на знак $
Recordset = Command.Execute();
// Далее перебираем Recordset и получаем данные
Параметр HDR=YES указывает на то, что первая строка файла содержит заголовки колонок. Параметр IMEX=1 (Intermixed) критически важен: он заставляет драйвер читать данные разных типов в одной колонке как текст, что предотвращает потерю данных.
Начиная с версий платформы 8.3.10 и выше, 1С умеет нативно читать файлы форматов .xls, .xlsx и .ods без использования каких-либо внешних библиотек или COM-соединений. Это позволяет реализовать удобную загрузку данных из табличного документа на управляемых формах. Это самый рекомендуемый и кроссплатформенный метод, который работает в том числе на Linux.
Разберем ситуацию, когда в файле несколько листов. В 1С объект ТабличныйДокумент не имеет прямой сущности «Лист». Если вам требуется загрузка файлов Excel с чтением всех страниц, учитывайте, что при чтении все листы объединяются в одну длинную таблицу, а границы листов становятся именованными областями.
Выясним, как прочитать конкретный лист:
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
// Имена листов превращаются в имена областей
Для Каждого Область Из ТабДок.Области Цикл
Сообщить("Найден лист (область): " + Область.Имя);
КонецЦикла;
Важно: 1С автоматически корректирует имена листов, чтобы они соответствовали правилам именования переменных (например, пробелы заменяются на подчеркивания). Если вам нужно прочитать только первый лист, вы можете просто ограничить чтение первой найденной областью.
Если файл представляет собой «плоскую» таблицу (первая строка — заголовки, далее — данные), можно скомбинировать ТабличныйДокумент и ПостроительЗапроса. Это позволяет мгновенно выгрузить данные в ТаблицуЗначений.
Рассмотрим пример кода:
&НаСервере
Процедура ПрочитатьExcelЧерезПостроитель(ПутьКФайлу)
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу);
// Создаем построитель и назначаем источником область табличного документа
ПЗ = Новый ПостроительЗапроса;
ПЗ.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабДок.Область());
// Заполняем настройки и выполняем
ПЗ.ДобавлениеПредставлений = ТипДобавленияПредставлений.НеДобавлять;
ПЗ.ЗаполнитьНастройки();
ПЗ.Выполнить();
// Получаем готовую ТЗ с колонками из первой строки Excel
ТаблицаРезультат = ПЗ.Результат.Выгрузить();
КонецПроцедуры
Этот метод демонстрирует впечатляющую скорость: файл объемом более 350 000 строк может быть прочитан менее чем за 2 секунды. Основное время при такой загрузке будет тратиться уже не на чтение, а на последующую обработку и запись данных в базу 1С.
Проанализируем ситуацию с файлами .xlsx. Технически это ZIP-архив, внутри которого находятся файлы XML. Если вам требуется максимально возможная производительность и минимальное потребление оперативной памяти, можно использовать низкоуровневое чтение.
Алгоритм действий следующий:
ЧтениеZipФайла для распаковки файла во временный каталог.xl/worksheets/sheet1.xml.xl/sharedStrings.xml.ЧтениеXML для потокового чтения данных.Этот метод наиболее сложен в реализации, так как требует ручного сопоставления индексов строк и работы с форматами дат, но он позволяет обрабатывать файлы практически любого размера, которые не «пролезают» в память через ТабличныйДокумент.
Если ваш сервер 1С работает совместно с MS SQL Server, вы можете задействовать мощь СУБД. Файл Excel можно подключить как Linked Server (связанный сервер) и выполнять к нему запросы прямо из консоли SQL или через ADO, обращаясь к SQL Server.
Это бывает полезно, когда нужно объединить данные из Excel с данными из других таблиц базы данных еще на этапе выборки, не перегоняя лишние мегабайты в 1С.
Выбор метода зависит от ваших условий:
ТабличныйДокумент.Прочитать(). Это самый простой и надежный способ.ПостроительЗапроса.Помните, что при использовании внешних драйверов (ADO) необходимо всегда учитывать разрядность операционной системы и сервера 1С, а также права доступа учетной записи, под которой запущена служба сервера 1С, к временным папкам и файлам данных.