При выгрузке больших объемов данных (от 10 000 строк и более) из 1С в файл Excel через механизмы COMОбъект("Excel.Application") программисты часто сталкиваются с критическим падением производительности. Процесс, который на небольших файлах занимает секунды, на крупных объемах может растянуться на долгие часы или даже дни. Ситуация дополнительно осложняется, если целевая книга защищена паролем или содержит макросы для добавления строк.
Рассмотрим подробно, почему возникает эта проблема, и какие методы позволяют ускорить выгрузку в десятки и сотни раз.
Проанализируем основные факторы, которые замедляют запись данных в Excel при классическом построчном заполнении:
Лист.Range(...).Value = Значение из среды 1С представляет собой отдельный межпроцессный вызов. Если необходимо выгрузить 10 000 строк по 30 колонок, система выполнит свыше 300 000 обращений к COM-объекту. Начиная с ОС Windows 7 механизмы безопасности и маршалинга COM функционируют с дополнительными проверками, что существенно увеличивает время каждого вызова по сравнению с устаревшими системами.Разберем базовый шаг оптимизации — временно отключить в приложении Excel все операции по перерисовыванию интерфейса, обработке событий и автоматическому пересчету формул. Это позволяет снять значительную часть нагрузки с процессора во время записи.
Посмотрим на пример настройки COM-объекта перед началом выгрузки:
Excel = Новый COMОбъект("Excel.Application");
Excel.Visible = Ложь;
Excel.ScreenUpdating = Ложь;
Excel.EnableEvents = Ложь;
Excel.DisplayAlerts = Ложь;
// Отключаем автоматический пересчет формул (xlCalculationManual = -4135)
Excel.Calculation = -4135;
После завершения заполнения данных необходимо вернуть параметры в исходное состояние, пересчитать формулы и сохранить файл:
// Включаем авторасчет (xlCalculationAutomatic = -4105)
Excel.Calculation = -4105;
Excel.ScreenUpdating = Истина;
Excel.EnableEvents = Истина;
Excel.DisplayAlerts = Истина;
Книга.Save();
Excel.Quit();
Самый эффективный способ ускорить заполнение ячеек — отказаться от передачи значений по одной ячейке и записать весь двухмерный массив данных за один вызов метода. Для этого в 1С используется объект COMSafeArray.
Рассмотрим по шагам, как сформировать массив и записать его в нужный диапазон:
COMSafeArray с типом данных VT_VARIANT.Resize и присвоим ему весь массив единым действием.Проанализируем пример кода:
КоличествоСтрок = ТаблицаДанных.Количество();
КоличествоКолонок = 7;
// Создаем двухмерный SafeArray (индексация от 0)
МассивДанных = Новый COMSafeArray("VT_VARIANT", КоличествоСтрок, КоличествоКолонок);
Для ИндексСтр = 0 По КоличествоСтрок - 1 Цикл
Стр = ТаблицаДанных[ИндексСтр];
МассивДанных.Установить(ИндексСтр, 0, Стр.ПациентНаименование);
МассивДанных.Установить(ИндексСтр, 1, Стр.ДатаРождения);
МассивДанных.Установить(ИндексСтр, 2, Строка(Стр.СерияПаспорта) + " " + Строка(Стр.НомерПаспорта));
МассивДанных.Установить(ИндексСтр, 3, Строка(Стр.КатегорияВнереализация));
МассивДанных.Установить(ИндексСтр, 4, Стр.ФИОРодственника);
МассивДанных.Установить(ИндексСтр, 5, Стр.Кратность);
МассивДанных.Установить(ИндексСтр, 6, Стр.Организация2009Наименование);
КонецЦикла;
// Получаем начальную ячейку и расширяем диапазон под размер массива
НачальнаяЯчейка = Лист3.Range("B" + Формат(СтартовыйНомерСтроки, "ЧГ=0"));
ЦелевойДиапазон = НачальнаяЯчейка.Resize(КоличествоСтрок, КоличествоКолонок);
// Записываем весь массив за одно обращение по COM
ЦелевойДиапазон.Value = МассивДанных;
Вместо десятков тысяч вызовов выполняется всего одна команда присвоения Value, что сокращает время записи с часов до нескольких секунд.
Если книга защищена и требует разметки строк перед заполнением, построчный вызов макроса Excel.Application.Run("AddRowF") создает узкое место системы. Чтобы устранить эту проблему, применяют следующие подходы:
Rows.Insert).Unprotect("Пароль") и верните её обратно методом Protect("Пароль") после окончания выгрузки. Также в макросах VBA можно использовать режим UserInterfaceOnly := True, при котором защита действует на действия пользователя через интерфейс, но не блокирует изменения от макросов и COM-запросов.Если требование заполнять строго защищенный файл от пользователя можно обойти или пересогласовать, наиболее быстрым решением со стороны 1С будет формирование обычного ТабличныйДокумент с последующим сохранением в формат Excel:
ТабДок = Новый ТабличныйДокумент;
// ... Заполнение табличного документа макетом ...
ТабДок.Записать(ИмяФайлаВыгрузки, ТипФайлаТабличногоДокумента.XLSX);
Этот метод работает штатно на сервере 1С без использования внешних COM-объектов MS Office и формирует итоговый файл за считанные секунды.