При выгрузке данных в Excel перед разработчиками часто встает задача: сохранить исходное сложное форматирование (ширину колонок, объединения ячеек, шрифты, границы) и при этом быстро заполнить документ данными. Идея вручную переносить все свойства каждого объекта Excel.Application в цикле неэффективна и сложна в реализации, так как платформа 1С не поддерживает прямую рефлексию COM-объектов.
В этой статье мы подробно разберем, как правильно организовать работу с шаблонами Excel в 1С. Мы рассмотрим методы хранения файлов без изменения конфигурации, научимся быстро копировать области листов, оптимизируем работу COM-соединения для ускорения вывода данных в сотни раз, а также разберем современную альтернативу, не требующую наличия установленного Microsoft Office на сервере.
Если вы работаете в закрытой или типовой конфигурации (например, «Альфа-Авто»), которую нежелательно снимать с поддержки, встает вопрос размещения самого файла шаблона. Рассмотрим два наиболее удобных варианта.
Вариант А. Макет во внешней обработке (Двоичные данные)
Это самый автономный способ. Вы можете внедрить файл Excel непосредственно в вашу внешнюю обработку (.epf) или внешний отчет (.erf). Для этого выполните следующие шаги:
При выполнении кода обработки мы извлекаем двоичные данные во временный файл на диске пользователя, заполняем его и открываем для редактирования или сохраняем в нужное место. Разберем пример кода:
// Получаем макет двоичных данных из внешней обработки
МакетШаблона = ПолучитьМакет("ШаблонExcel");
// Формируем путь во временном каталоге
ПутьКФайлу = ПолучитьИмяВременногоФайла("xlsx");
// Записываем двоичные данные во временный файл
МакетШаблона.Записать(ПутьКФайлу);
// Открываем файл через COM-объект для заполнения
ExcelApp = Новый COMОбъект("Excel.Application");
Книга = ExcelApp.Workbooks.Open(ПутьКФайлу);
Вариант Б. Использование типового справочника «Присоединенные файлы»
Если шаблон должен периодически обновляться пользователями без привлечения программиста, макет во внешней обработке не подойдет. В этом случае мы можем использовать стандартный механизм БСП (Библиотеки Стандартных Подсистем), который присутствует в большинстве конфигураций.
Создайте служебный элемент в справочнике (например, привязанный к определенной организации, договору или специальной папке файлов) и прикрепите туда ваш Excel-шаблон. В коде обработки мы будем находить этот элемент по коду или уникальному идентификатору, получать двоичные данные прикрепленного файла и выгружать их на диск аналогично первому варианту.
Построчное форматирование ячеек через COM-соединение работает крайне медленно. Вместо этого гораздо эффективнее скопировать готовую форматированную строку (или группу строк) из шаблона и вставить ее ниже, сдвигая последующие строки. Разберем, как реализовать такое копирование.
// Предположим, что ExcelApp и Sheet (Лист) уже инициализированы
// Копируем строки шаблона с 5 по 10
Sheet.Rows("5:10").Select();
ExcelApp.Selection.Copy();
// Выбираем строку, куда будем вставлять скопированную область
Sheet.Rows("11:11").Select();
// Вставляем скопированные строки со сдвигом вниз (-4121 — константа Excel xlShiftDown)
ExcelApp.Selection.Insert(-4121);
После того как структура документа создана, необходимо заполнить ячейки данными. Прямое обращение к каждой ячейке вида Sheet.Cells(Ряд, Колонка).Value = Значение при больших объемах информации вызывает «зависание» процесса. Для решения этой проблемы воспользуемся объектом COMSafeArray. Он позволяет передать весь массив данных в Excel за одно обращение.
// Создаем двумерный массив безопасного типа VT_VARIANT
// Параметры: тип данных, количество колонок, количество строк
КоличествоКолонок = 5;
КоличествоСтрок = 100;
COMArray = Новый COMSafeArray("VT_VARIANT", КоличествоКолонок, КоличествоСтрок);
// Заполняем массив данными (индексация начинается с 0)
Для СтрокаИндекс = 0 По КоличествоСтрок - 1 Цикл
Для КолонкаИндекс = 0 По КоличествоКолонок - 1 Цикл
Значение = "Данные_" + СтрокаИндекс + "_" + КолонкаИндекс;
COMArray.SetValue(КолонкаИндекс, СтрокаИндекс, Значение);
КонецЦикла;
КонецЦикла;
// Определяем область (Range) на листе для вставки данных
// Координаты должны строго соответствовать размеру COMSafeArray
НомерСтрокиНачало = 11;
НомерКолонкиНачало = 1;
НомерСтрокиКонец = НомерСтрокиНачало + КоличествоСтрок - 1;
НомерКолонкиКонец = НомерКолонкиНачало + КоличествоКолонок - 1;
НачальнаяЯчейка = Sheet.Cells(НомерСтрокиНачало, НомерКолонкиНачало);
КонечнаяЯчейка = Sheet.Cells(НомерСтрокиКонец, НомерКолонкиКонец);
// Передаем весь массив за один такт
Sheet.Range(НачальнаяЯчейка, КонечнаяЯчейка).Value = COMArray;
Если полностью отказаться от обращения к отдельным ячейкам не удается, критически важно отключить визуальные эффекты и внутренние обработчики событий Microsoft Excel на время работы скрипта. Это сократит время генерации файла в несколько раз.
Проанализируем ключевые параметры оптимизации, которые необходимо установить сразу после инициализации объекта Excel.Application:
ExcelApp = Новый COMОбъект("Excel.Application");
// 1. Отключаем обновление экрана (пользователь не видит мигания и перерисовки)
ExcelApp.ScreenUpdating = Ложь;
// 2. Отключаем автоматический расчет формул (формулы пересчитаются один раз в конце)
// -4135 соответствует константе xlCalculationManual
ExcelApp.Calculation = -4135;
// 3. Отключаем обработку событий макросов (предотвращает зависания при наличии кода в шаблоне)
ExcelApp.EnableEvents = Ложь;
// 4. Подавляем вывод системных предупреждений (например, о перезаписи файла)
ExcelApp.DisplayAlerts = Ложь;
Важное правило: После завершения всех манипуляций с данными обязательно верните настройки в исходное состояние и корректно освободите память. Неправильное закрытие COM-объектов приводит к накоплению «зависших» процессов EXCEL.EXE в диспетчере задач операционной системы.
// Включаем обратно расчет формул перед сохранением
ExcelApp.Calculation = -4105; // -4105 соответствует xlCalculationAutomatic
ExcelApp.ScreenUpdating = Истина;
ExcelApp.EnableEvents = Истина;
ExcelApp.DisplayAlerts = Истина;
// Закрываем книгу с сохранением изменений
Книга.SaveAs(ПутьКФайлуРезультату);
Книга.Close(Ложь);
// Завершаем работу приложения Excel
ExcelApp.Quit();
// Принудительно очищаем ссылки в обратном порядке для сборщика мусора 1С
Лист = Неопределено;
Книга = Неопределено;
ExcelApp = Неопределено;
Начиная с современных версий платформы 1С v8.3, разработчикам доступен мощный инструмент — прямая работа с файлами формата .xlsx через встроенный объект ТабличныйДокумент. Этот метод полностью решает проблему скорости, стабильности и не требует наличия установленного Excel на сервере или локальном компьютере.
Выясним, как прочитать шаблон, заполнить его данными и сохранить обратно в оригинальном формате:
// Создаем табличный документ
ТабДок = Новый ТабличныйДокумент;
// Читаем готовый файл шаблона напрямую с сохранением всего оформления
ТабДок.Прочитать(ПутьКШаблону, СпособЧтенияТабличногоДокумента.XLSX);
// Заполняем ячейки, обращаясь к ним по координатам
// При этом сохраняются шрифты, цвета границ и ширина колонок шаблона
ТабДок.Область("R5C2").Текст = "Значение для строки 5, колонки 2";
ТабДок.Область("R6C2").Текст = "Значение для строки 6, колонки 2";
// Также мы можем использовать параметры, если они были настроены в шаблоне
// ТабДок.Параметры.ИмяПараметра = "Значение";
// Сохраняем итоговый документ обратно в формат XLSX
ТабДок.Записать(ПутьКФайлуРезультату, ТипФайлаТабличногоДокумента.XLSX);
Использование ТабличныйДокумент является наиболее предпочтительным в современных реалиях 1С-разработки. Оно гарантирует высокую отказоустойчивость системы и бесперебойную работу в фоновых заданиях на серверах под управлением ОС Linux.