Перевод старых баз и обработок с платформы 1С 7.7 на 1С 8 — стандартная задача для многих разработчиков. Однако в процессе миграции часто выявляются неожиданные проблемы с производительностью. Одной из самых распространенных и неприятных ситуаций является катастрофическое замедление формирования файлов Excel через COM-соединение. Простой отчет, который в «семерке» выгружался за 10–15 минут, в «восьмерке» на том же сервере и с тем же объемом данных начинает выполняться часами. Замер производительности при этом упрямо указывает на методы вставки строк, такие как Insert.
В этой статье мы подробно разберем, почему один и тот же код на одной и той же операционной системе работает с разной скоростью на двух поколениях платформы 1С, детально проанализируем внутреннее поведение Excel при операциях вставки и рассмотрим лучшие практические способы оптимизации кода — от быстрых настроек до архитектурного изменения логики выгрузки.
Проанализируем ситуацию по шагам и найдем наиболее эффективное решение.
Разница в скорости работы вызвана не ухудшением механизмов самой платформы 1С, а серьезными архитектурными различиями между версиями 7.7 и 8.x, а также изменениями в операционной системе и разрядности приложений. Рассмотрим подробнее основные факторы:
dllhost.exe. Каждый вызов свойства или метода ячейки в цикле порождает колоссальные накладные расходы на уровне ОС.Добавим к этому особенности самого табличного процессора Excel. Метод Insert (вставка строки со сдвигом вниз) имеет вычислительную сложность порядка O(N²). Каждый раз, когда мы вставляем строку посреди документа, Excel вынужден:
Когда строк несколько тысяч, операция вставки каждой новой строки начинает занимать все больше и больше времени, превращая процесс генерации в бесконечное ожидание.
Теперь рассмотрим практические способы решения этой проблемы.
Это самый простой шаг («быстрая победа»), который не требует глобальной переработки алгоритма, но способен ускорить выполнение кода в разы. Перед началом формирования документа необходимо временно перевести Excel в «тихий» режим: отключить отрисовку интерфейса, обработку событий и автоматический пересчет формул. Формулы пересчитаем один раз — в самом конце, перед сохранением файла.
Разберем пример кода, реализующий этот подход:
// Создаем объект Excel
ExcelApp = Новый COMОбъект("Excel.Application");
ExcelApp.Visible = Ложь; // Отключаем видимость окна
// Отключаем обновление экрана (убирает перерисовку при изменениях)
ExcelApp.ScreenUpdating = Ложь;
// Отключаем обработку событий макросов и листов
ExcelApp.EnableEvents = Ложь;
// Переводим расчет формул в ручной режим (константа xlCalculationManual = -4135)
ExcelApp.Calculation = -4135;
Попытка
// Здесь располагается ваш основной цикл формирования документа
// Например, копирование и вставка строк
// После завершения всех манипуляций возвращаем настройки обратно
ExcelApp.Calculation = -4105; // xlCalculationAutomatic = -4105
ExcelApp.ScreenUpdating = Истина;
ExcelApp.EnableEvents = Истина;
Исключение
// В случае ошибки обязательно возвращаем настройки и закрываем процесс
ExcelApp.Calculation = -4105;
ExcelApp.ScreenUpdating = Истина;
ExcelApp.EnableEvents = Истина;
ExcelApp.Quit();
ВызватьИсключение;
КонецПопытки;
Важно: Всегда используйте блоки Попытка...Исключение. Если в процессе выполнения произойдет сбой, а настройки Excel не будут возвращены в исходное состояние, процесс EXCEL.EXE может «зависнуть» в памяти сервера, монопольно занимая ресурсы процессора.
Если вам необходимо заполнить большое количество ячеек значениями, делать это в цикле по одной ячейке через COM — самая грубая ошибка производительности. Вместо тысяч последовательных вызовов через границу процессов мы можем передать всю таблицу данных за один-единственный вызов с помощью специального объекта COMSafeArray.
Рассмотрим, как это реализовать на практике:
// Допустим, нам нужно заполнить таблицу из 1000 строк и 5 колонок
КоличествоСтрок = 1000;
КоличествоКолонок = 5;
// Создаем двумерный SafeArray типа Variant (VT_VARIANT = 12)
// Индексы SafeArray в 1С начинаются с 0
МассивДанных = Новый COMSafeArray("VT_VARIANT", КоличествоСтрок, КоличествоКолонок);
// Заполняем массив данными на стороне 1С (это происходит мгновенно в оперативной памяти)
Для Стр = 0 По КоличествоСтрок - 1 Цикл
МассивДанных.SetValue(Стр, 0, "Товар " + Строка(Стр));
МассивДанных.SetValue(Стр, 1, Стр * 10); // Количество
МассивДанных.SetValue(Стр, 2, 150.50); // Цена
МассивДанных.SetValue(Стр, 3, Стр * 10 * 150.50); // Сумма
МассивДанных.SetValue(Стр, 4, "=RC[-2]*RC[-1]"); // Текст формулы в формате R1C1
КонецЦикла;
// Получаем нужный диапазон ячеек в Excel (адресация начинается с 1)
// Диапазон должен точно соответствовать размерности нашего SafeArray
РабочийЛист = Книга.Sheets(1);
Диапазон = РабочийЛист.Range(РабочийЛист.Cells(1, 1), РабочийЛист.Cells(КоличествоСтрок, КоличествоКолонок));
// Передаем весь массив за ОДИН вызов COM-объекта
Диапазон.Value = МассивДанных;
Использование COMSafeArray сокращает время заполнения листа с нескольких десятков минут до долей секунды. Этот метод идеально подходит для выгрузки плоских таблиц данных.
Если в вашем отчете используется сложный шаблон, содержащий расчетные формулы и специфическое форматирование, необязательно копировать и вставлять строки по одной. Более эффективным подходом будет предварительное создание пустых строк нужного формата и последующее массовое заполнение формулами.
Для работы с формулами лучше всего использовать свойство FormulaR1C1. Этот стиль адресации (строка-колонка) позволяет задавать относительные формулы единообразно для всего диапазона, и Excel автоматически скорректирует ссылки для каждой строки.
Рассмотрим пример заполнения формулами целого столбца без циклов:
// Нам нужно прописать формулу умножения цены на количество для диапазона строк со 2 по 1001
// В стиле R1C1 формула "=RC[-2]*RC[-1]" означает: умножить значение в ячейке той же строки
// на 2 колонки левее на значение ячейки той же строки на 1 колонку левее.
РабочийЛист = Книга.Sheets(1);
ДиапазонФормул = РабочийЛист.Range("D2:D1001");
// Записываем формулу сразу во весь диапазон одной строкой кода
ДиапазонФормул.FormulaR1C1 = "=RC[-2]*RC[-1]";
Excel обработает этот вызов на внутреннем оптимизированном уровне C++, что произойдет практически мгновенно.
В современных версиях платформы 1С v8.3 реализован мощный механизм трансляции формул при сохранении объекта ТабличныйДокумент в формат XLSX. Это позволяет полностью отказаться от использования нестабильного и медленного COM-объекта Excel на сервере.
Выясним, как работает этот механизм. В ячейку табличного документа можно программно записать свойство Формула, указав текст формулы в формате Excel. При сохранении файла платформа самостоятельно преобразует эти данные в корректный XLSX-архив.
Посмотрим на пример создания такого документа:
ТабДок = Новый ТабличныйДокумент;
// Создаем область макета или заполняем ячейки вручную
Для НомерСтроки = 1 По 1000 Цикл
// Заполняем исходные данные
ЯчейкаКол = ТабДок.Область("R" + Строка(НомерСтроки) + "C1");
ЯчейкаКол.Содержимое = ТипСодержимогоЯчейкиТабличногоДокумента.Число;
ЯчейкаКол.Значение = 10;
ЯчейкаЦена = ТабДок.Область("R" + Строка(НомерСтроки) + "C2");
ЯчейкаЦена.Содержимое = ТипСодержимогоЯчейкиТабличногоДокумента.Число;
ЯчейкаЦена.Значение = 150;
// Задаем формулу для третьей колонки
ЯчейкаСумма = ТабДок.Область("R" + Строка(НомерСтроки) + "C3");
// Указываем формулу в формате Excel
ЯчейкаСумма.Формула = "A" + Строка(НомерСтроки) + "*B" + Строка(НомерСтроки);
КонецЦикла;
// Записываем готовый файл на диск без участия Microsoft Office
ПутьКФайлу = "C:\Reports\Report.xlsx";
ТабДок.Записать(ПутьКФайлу, ТипФайлаТабличногоДокумента.XLSX);
Этот способ имеет колоссальные преимущества перед COM-технологией:
Применяя данные методы оптимизации, вы сможете добиться надежной и быстрой генерации отчетов любой сложности, сохранив при этом точность расчетов и форматирование исходных шаблонов.