При разработке отчетов и обработок часто возникает задача загрузить внешние данные из файла Microsoft Excel, сопоставить их со справочниками и регистрами базы данных, а затем сгруппировать результат. Когда объем файла достигает десятков или сотен тысяч строк, стандартный подход через COM-объект Excel.Application приводит к системным ошибкам или критическому падению производительности. Разберем причины возникновения ошибки COM-соединения, эффективные способы чтения больших объемов данных и передачу полученной таблицы в систему компоновки данных (СКД).
Проанализируем, почему при выполнении кода в модуле отчета или обработки возникает ошибка вызова конструктора COM-объекта:
ФаилExcel = Новый COMОбъект("Excel.Application");
Данное исключение с кодом 0x800401F3 указывает на то, что запрашиваемый COM-класс не зарегистрирован в операционной системе на том компьютере, где фактически исполняется программный код. Выделим основные причины возникновения этой проблемы:
USR1CV8) запускается в неинтерактивной сессии и по умолчанию не имеет прав на запуск интерактивных приложений Office, что требует создания специальных системных папок рабочего стола и настройки прав DCOM.Excel.Application занимает недопустимо много времени (до десятков минут) и приводит к утечкам памяти в рабочих процессах.Платформа 1С:Предприятие позволяет считывать файлы форматов .xls, .xlsx и .ods без использования сторонних библиотек и установленного Microsoft Excel. Для этого применяется объект ТабличныйДокумент.
Рассмотрим пример чтения файла и переноса данных в таблицу значений:
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать("C:\Files\Книга1.xlsx");
ТаблицаДанных = Новый ТаблицаЗначений;
ТаблицаДанных.Колонки.Добавить("Артикул", Новый ОписаниеТипов("Строка",,,, Новый КвалификаторыСтроки(50)));
ТаблицаДанных.Колонки.Добавить("Характеристика", Новый ОписаниеТипов("Строка",,,, Новый КвалификаторыСтроки(150)));
ВсегоСтрок = ТабДок.ВысотаТаблицы;
Для НомерСтроки = 2 По ВсегоСтрок Цикл
НоваяСтрока = ТаблицаДанных.Добавить();
НоваяСтрока.Артикул = ТабДок.ПолучитьОбласть("R" + XMLСтрока(НомерСтроки) + "C1").Текст;
НоваяСтрока.Характеристика = ТабДок.ПолучитьОбласть("R" + XMLСтрока(НомерСтроки) + "C2").Текст;
КонецЦикла;
Этот способ работает стабильно, не зависит от разрядности сервера и не требует наличия стороннего ПО.
Если требуется прочитать большой объем строк с высокой скоростью без загрузки всей графической таблицы в память, файл Excel можно обработать как базу данных через провайдер данных Microsoft.ACE.OLEDB. Важно помнить, что разрядность установленного драйвера ACE OLEDB должна соответствовать разрядности сервера 1С.
Разберем пример выполнения SQL-запроса к листу книги:
ИмяФайла = "C:\Files\Книга1.xlsx";
СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ИмяФайла + ";Extended Properties=""Excel 12.0 XML;HDR=YES;"";";
Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.Open(СтрокаПодключения);
Команда = Новый COMОбъект("ADODB.Command");
Команда.ActiveConnection = Соединение;
Команда.CommandText = "SELECT Артикул, Характеристика FROM [Лист1$]";
НаборЗаписей = Команда.Execute();
ТаблицаДанных = Новый ТаблицаЗначений;
ТаблицаДанных.Колонки.Добавить("Артикул", Новый ОписаниеТипов("Строка"));
ТаблицаДанных.Колонки.Добавить("Характеристика", Новый ОписаниеТипов("Строка"));
Пока НЕ НаборЗаписей.EOF() Цикл
НоваяСтрока = ТаблицаДанных.Добавить();
НоваяСтрока.Артикул = СокрЛП(НаборЗаписей.Fields("Артикул").Value);
НоваяСтрока.Характеристика = СокрЛП(НаборЗаписей.Fields("Характеристика").Value);
НаборЗаписей.MoveNext();
КонецЦикла;
НаборЗаписей.Close();
Соединение.Close();
Файлы формата .xlsx представляют собой сжатые ZIP-архивы, внутри которых содержатся XML-документы. Для сверхбыстрой загрузки 100 000 строк (в течение нескольких секунд) можно распаковать архив с помощью объекта ЧтениеZIPФайла и потоково прочитать файлы xl/sharedStrings.xml (таблица общих строк) и xl/worksheets/sheet1.xml с помощью объекта ЧтениеXML. Данный подход является самым производительным и платформонезависимым.
После получения таблицы значений передадим ее в схему компоновки данных без сохранения во временные файлы.
ВнешняяТаблица.Номенклатура, Характеристика, Количество).ПриКомпоновкеРезультата модуля отчета) передадим таблицу в структуру внешних наборов данных:
Процедура ПриКомпоновкеРезультата(ДокументРезультат, ДанныеРасшифровки, СтандартнаяОбработка)
СтандартнаяОбработка = Ложь;
// Получаем таблицу значений одним из описанных способов
ТаблицаИзФайла = ПолучитьТаблицуИзФайла();
ВнешниеНаборыДанных = Новый Структура;
ВнешниеНаборыДанных.Вставить("ВнешняяТаблица", ТаблицаИзФайла);
СхемаКомпоновки = ПолучитьМакет("ОсновнаяСхемаКомпоновкиДанных");
Настройки = КомпоновщикНастроек.ПолучитьНастройки();
КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновки, Настройки, ДанныеРасшифровки);
ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки, ВнешниеНаборыДанных, ДанныеРасшифровки);
ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВТабличныйДокумент;
ПроцессорВывода.УстановитьДокумент(ДокументРезультат);
ПроцессорВывода.Вывести(ПроцессорКомпоновки);
КонецПроцедуры
Для объединения разных строковых значений одной номенклатуры в одну общую ячейку не требуется писать сложные циклы склейки строк. СКД предоставляет встроенную функцию агрегации.
Разберем по шагам настройку схемы компоновки данных:
Характеристика).
СоединитьСтроки(Массив(Уникальные(Характеристика)), ", ")
Функция Уникальные() исключит дублирование повторяющихся значений, а СоединитьСтроки() объединит массив через указанный разделитель.Номенклатура, а в выбранные поля включим Номенклатура и ресурс Характеристика.В результате формирования отчета СКД автоматически сгруппирует данные по номенклатуре и выведет все связанные с ней характеристики в одну строку через запятую.