При автоматизации бизнес-процессов разработчики часто сталкиваются с необходимостью загрузки данных из файлов Microsoft Excel. Традиционный путь — использование COM-объекта Excel.Application. Однако этот подход имеет существенные недостатки: он требует наличия платной лицензии MS Office на каждом рабочем месте или RDP-сервере, работает медленно и недоступен на серверах под управлением ОС Linux.
В этой статье мы подробно разберем, как организовать чтение файлов xls и xlsx абсолютно бесплатно, не нарушая лицензионную политику Microsoft и минимизируя необходимость переписывания существующего кода.
Платформа 1С:Предприятие обладает мощным встроенным механизмом для работы с электронными таблицами. Объект ТабличныйДокумент позволяет напрямую читать файлы форматов XLS, XLSX и ODS без привлечения стороннего программного обеспечения.
Рассмотрим пример использования метода Прочитать(). Важно помнить, что данный метод недоступен на тонком клиенте. Попытка вызвать его напрямую в модуле формы тонкого клиента приведет к ошибке «Метод недоступен на тонком клиенте». Для обхода этого ограничения мы должны передать файл на сервер через временное хранилище.
Разберем по шагам правильную реализацию этого алгоритма:
&НаКлиенте
Процедура ЗагрузитьФайлНаКлиенте(Команда)
ДиалогОткрытия = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
ДиалогОткрытия.Фильтр = "Файлы Excel (*.xls;*.xlsx)|*.xls;*.xlsx";
ДиалогОткрытия.МножественныйВыбор = Ложь;
Если ДиалогОткрытия.Выбрать() Тогда
ВыбранныйФайл = ДиалогОткрытия.ПолноеИмяФайла;
// Помещаем файл во временное хранилище на клиенте
ДвоичныеДанныеФайла = Новый ДвоичныеДанные(ВыбранныйФайл);
АдресХранилища = ПоместитьВоВременноеХранилище(ДвоичныеДанныеФайла, УникальныйИдентификатор);
// Вызываем серверный метод для обработки файла
ЗагрузитьФайлНаСервере(АдресХранилища);
КонецЕсли;
КонецПроцедуры
&НаСервере
Процедура ЗагрузитьФайлНаСервере(АдресХранилища)
// Получаем двоичные данные из хранилища
ДвоичныеДанные = ПолучитьИзВременногоХранилища(АдресХранилища);
// Создаем временный файл на диске сервера
ИмяВременногоФайла = ПолучитьИмяВременногоФайла("xlsx");
ДвоичныеДанные.Записать(ИмяВременногоФайла);
// Читаем файл в ТабличныйДокумент
ТабДок = Новый ТабличныйДокумент;
Попытка
ТабДок.Прочитать(ИмяВременногоФайла, СпособЧтенияЗначенийТабличногоДокумента.Значение);
УдалитьФайлы(ИмяВременногоФайла);
Исключение
УдалитьФайлы(ИмяВременногоФайла);
ВызватьИсключение "Ошибка чтения файла: " + ОписаниеОшибки();
КонецПопытки;
// Обрабатываем прочитанные данные
// Например, обходим ячейки в цикле
КоличествоСтрок = ТабДок.ВысотаТаблицы;
КоличествоКолонок = ТабДок.ШиринаТаблицы;
Для Строка = 1 По КоличествоСтрок Цикл
Для Колонка = 1 По КоличествоКолонок Цикл
ЗначениеЯчейки = ТабДок.Область(Строка, Колонка).Текст;
// Выполняем полезную работу с данными...
КонецЦикла;
КонецЦикла;
КонецПроцедуры
Параметр СпособЧтенияЗначенийТабличногоДокумента.Значение позволяет сохранить типы данных (числа, даты) при загрузке, предотвращая их автоматическое преобразование в строку.
Если файл Excel содержит большое количество строк, стандартный обход ячеек в цикле может работать медленно. Чтобы ускорить процесс, мы можем преобразовать область табличного документа в стандартную ТаблицаЗначений с помощью объекта ПостроительЗапроса. Проанализируем этот прием подробнее:
&НаСервере
Функция ПолучитьТаблицуИзТабДока(ТабДок) Экспорт
Построитель = Новый ПостроительЗапроса;
// Определяем область данных (исключая пустые области)
ОбластьДанных = ТабДок.Область(1, 1, ТабДок.ВысотаТаблицы, ТабДок.ШиринаТаблицы);
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ОбластьДанных);
Построитель.Выполнить();
Возврат Построитель.Результат.Выгрузить();
КонецФункции
Этот код мгновенно вернет готовую ТаблицуЗначений, с которой работать гораздо проще и быстрее, нежели перебирать миллионы ячеек вручную.
Еще один популярный способ чтения файлов Excel без запуска самого приложения Excel — использование интерфейса ADODB.Connection. В этом случае файл Excel рассматривается как база данных, а листы — как таблицы.
Пример программного кода для подключения через 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);
RecordSet = Новый COMОбъект("ADODB.Recordset");
// Обращаемся к первому листу файла
RecordSet.Open("SELECT * FROM [Лист1$]", Connection, 3, 1);
Пока НЕ RecordSet.EOF() Цикл
// Обработка полей записи
ЗначениеПоля = RecordSet.Fields.Item(0).Value;
RecordSet.MoveNext();
КонецЦикла;
RecordSet.Close();
Connection.Close();
Исключение
ВызватьИсключение "Ошибка подключения через ADO: " + ОписаниеОшибки();
КонецПопытки;
При использовании ADO разработчики часто сталкиваются с исключением ненайденного провайдера. Выясним причину этой ошибки. Она заключается в несовпадении разрядности платформы 1С и установленных в операционной системе драйверов базы данных.
Если ваша служба «1С:Предприятие» работает в 64-битной версии, то стандартные 32-битные драйверы Microsoft Office не будут видны серверным процессам. Для решения этой проблемы выполните следующие шаги:
AccessDatabaseEngine_X64.exe для 64-битного сервера).Microsoft.ACE.OLEDB станет доступен для COM-соединения из кода 1С.Важное ограничение: метод чтения через ADO накладывает жесткие рамки на структуру файла. Он требует строгой табличной структуры. Если на листе содержатся сложные шапки, объединенные ячейки или макросы, данный метод использовать не рекомендуется.
Если на сервере или клиентских машинах установлен бесплатный пакет LibreOffice, мы можем использовать его в качестве COM-сервера вместо MS Excel. Для этого используется системный объект com.sun.star.ServiceManager.
Посмотрим на пример инициализации и открытия файла:
ServiceManager = Новый COMОбъект("com.sun.star.ServiceManager");
Desktop = ServiceManager.createInstance("com.sun.star.frame.Desktop");
// Преобразуем путь к файлу в формат URL, требуемый для LibreOffice
ПутьКФайлуURL = "file:///" + СтрЗаменить(ПутьКФайлу, "\", "/");
ПараметрыОткрытия = Новый COMSafeArray("VT_DISPATCH", 1);
// Настраиваем скрытый режим работы (без открытия графического окна)
PropertyValue = ServiceManager.createInstance("com.sun.star.beans.PropertyValue");
PropertyValue.Name = "Hidden";
PropertyValue.Value = Истина;
ПараметрыОткрытия.SetValue(0, PropertyValue);
Document = Desktop.loadComponentFromURL(ПутьКФайлуURL, "_blank", 0, ПараметрыОткрытия);
Sheets = Document.getSheets();
Sheet = Sheets.getByIndex(0); // Получаем первый лист
// Читаем значение конкретной ячейки (колонка 0, строка 0)
Cell = Sheet.getCellByPosition(0, 0);
Значение = Cell.getString();
// Закрываем документ
Document.close(Истина);
Внимание: объектная модель LibreOffice (UNO API) кардинально отличается от объектной модели MS Excel. Заменить одну строчку инициализации не получится — придется полностью переписать все методы обращения к ячейкам, листам и областям данных. Этот вариант подходит только в том случае, если вы готовы к масштабному рефакторингу кода.
Подводя итог, выделим оптимальные стратегии работы: