Как выгрузить данные из 1С в шаблон Excel с сохранением формул?

← На главную

При автоматизации отчетности часто возникает задача (поможет выгрузка данных из запросов 1С во внешние файлы Excel): необходимо не просто выгрузить табличный документ из 1С в файл, а заполнить уже существующий, строго регламентированный шаблон Excel. При этом критически важно, чтобы в итоговом файле сохранились и автоматически пересчитались заложенные в шаблоне формулы. Стандартный метод ТабличныйДокумент.Записать() здесь не подходит, так как он полностью перезаписывает файл или создает новый, теряя сложные настройки шаблона.

В этой статье мы подробно разберем, как решить эту задачу, используя механизм COM-соединения (OLE Automation). Мы рассмотрим алгоритм открытия шаблона, точечного заполнения ячеек данными и корректного сохранения результата.

Постановка задачи и выбор метода

Рассмотрим ситуацию, описанную автором: имеется файл-шаблон Excel с фиксированным количеством строк и колонок. В файле настроены формулы, которые суммируют или преобразуют введенные данные. Задача состоит в том, чтобы взять данные из 1С (например, выполнить экспорт реквизитов справочников в Excel с фильтрами или получить результат запроса) и поместить их в ячейки, не содержащие формул. После этого Excel должен самостоятельно пересчитать итоговые значения.

Проанализируем доступные варианты решения:

Стандартная запись MXL в XLS. Не подходит, так как мы потеряем формулы и форматирование исходного шаблона.
Использование макетов ActiveDocument. Устаревший и сложный в поддержке метод.
Прямая работа с XML (OpenXML). Отличный быстрый вариант, не требующий установленного Excel, но сложный в реализации (нужно разбирать структуру xlsx-архива).
Работа через COM-объект (Excel.Application). Самый распространенный и понятный способ для задач небольшого объема (как в нашем случае — около 43 строк). Он позволяет управлять Excel так, будто это делает пользователь: открывать книги, писать в ячейки, сохранять.

Остановимся на четвертом варианте, так как он наиболее нагляден и проще всего реализуется средствами встроенного языка 1С, при условии, что на компьютере, где выполняется код, установлен Microsoft Excel.

Алгоритм работы с COM-объектом Excel

Давайте разберем процесс пошагово. Весь код должен быть обернут в конструкцию Попытка ... Исключение, чтобы в случае ошибки процесс Excel не остался висеть в оперативной памяти.

1. Инициализация приложения

Сначала нам нужно создать экземпляр приложения Excel. Для этого используем Новый COMОбъект. Важно сразу отключить вывод сообщений пользователю (чтобы Excel не спрашивал подтверждения при сохранении) и, при необходимости, скрыть окно приложения.


Попытка
    Эксель = Новый COMОбъект("Excel.Application");
    Эксель.DisplayAlerts = Ложь; // Отключаем предупреждения
    Эксель.Visible = Ложь;       // Работаем в фоновом режиме
    // Эксель.ScreenUpdating = Ложь; // Можно отключить обновление экрана для ускорения
Исключение
    Сообщить("Не удалось запустить MS Excel. Возможно, он не установлен.");
    Возврат;
КонецПопытки;

2. Открытие шаблона

Далее мы открываем наш файл-шаблон. Рекомендуем использовать копию шаблона или сразу после открытия сохранять файл под новым именем, чтобы случайно не испортить исходник.


ПутьКШаблону = "C:\Templates\Otchet_Template.xlsx";
Попытка
    Книга = Эксель.Workbooks.Open(ПутьКШаблону);
    // Получаем доступ к первому листу
    Лист = Книга.Worksheets(1); 
Исключение
    Эксель.Quit();
    Сообщить("Ошибка при открытии файла шаблона: " + ОписаниеОшибки());
    Возврат;
КонецПопытки;

3. Заполнение данных

Это самый ответственный этап. В отличие от других задач, где может потребоваться выгрузка журнала регистрации в Excel с группировкой, здесь мы не выгружаем табличный документ целиком. Вместо этого мы проходим циклом по нашим данным в 1С и точечно записываем значения в нужные ячейки Excel.

Предположим, у нас есть выборка из результата запроса ВыборкаДетали, и мы знаем, что данные должны начинаться с 5-й строки Excel.


НомерСтрокиExcel = 5;

Пока ВыборкаДетали.Следующий() Цикл
    // Записываем данные в ячейки (Строка, Колонка)
    // Важно: в ячейки с формулами писать НЕ НУЖНО. Excel пересчитает их сам.
    
    // Колонка 1 - Наименование
    Лист.Cells(НомерСтрокиExcel, 1).Value = ВыборкаДетали.Номенклатура;
    
    // Колонка 2 - Количество (число)
    Лист.Cells(НомерСтрокиExcel, 2).Value = ВыборкаДетали.Количество;
    
    // Колонка 3 - Цена (число)
    Лист.Cells(НомерСтрокиExcel, 3).Value = ВыборкаДетали.Цена;
    
    // Допустим, в Колонке 4 стоит формула "=B5*C5" (Количество * Цена).
    // Мы пропускаем запись в Cells(НомерСтрокиExcel, 4), формула сработает автоматически.
    
    НомерСтрокиExcel = НомерСтрокиExcel + 1;
КонецЦикла;

Важный нюанс: Обращение к Cells происходит по координатам (R, C), где R — номер строки, а C — номер колонки. Это удобно при работе в циклах.

4. Сохранение и завершение работы

После того как данные записаны, необходимо сохранить файл. Если мы используем шаблон, лучше сохранить его как новый файл методом SaveAs.

Критически важно корректно закрыть COM-объект. Если этого не сделать, процесс excel.exe останется висеть в диспетчере задач, потребляя ресурсы сервера или компьютера пользователя.


ПутьКРезультату = "C:\Reports\Otchet_" + Формат(ТекущаяДата(), "ДФ=yyyyMMdd_HHmmss") + ".xlsx";

Попытка
    // Сохраняем как новый файл (формат xlsx - 51)
    Книга.SaveAs(ПутьКРезультату, 51); 
    Книга.Close(0); // Закрываем книгу
    
    Сообщить("Файл успешно сформирован: " + ПутьКРезультату);
Исключение
    Сообщить("Ошибка при сохранении файла: " + ОписаниеОшибки());
КонецПопытки;

// Обязательное завершение работы приложения
Эксель.Quit();
Эксель = Неопределено;

Особенности и подводные камни

Давайте проанализируем важные аспекты, о которых стоит помнить при реализации этого метода.

Выполнение на Сервере или Клиенте?

В конфигурации ЗКГУ (как и в других управляемых приложениях) основной код выполняется на сервере — поможет массовая выгрузка данных в Excel-шаблоны и загрузка в 1С. Однако Microsoft не рекомендует (а иногда и запрещает лицензией) запускать Office Automation на серверной стороне. Это может привести к зависанию сеанса 1С, если Excel попытается вывести диалоговое окно, которое никто не увидит.

Рекомендация: Если пользователей немного, выполняйте формирование Excel на стороне Клиента. Для этого:

Сформируйте данные на Сервере и поместите их во временное хранилище или передайте массивом структур на Клиент.
В клиентской процедуре создайте COM-объект и заполните файл.

Если же необходимо формировать файлы в фоновом задании на сервере, убедитесь, что под учетной записью, от имени которой запущен сервер 1С, установлен Excel и выполнены необходимые настройки DCOM — задача решается через формирование файлов XLS без установки Microsoft Excel.

Скорость работы

Запись данных по одной ячейке (через Cells(r,c).Value) работает довольно медленно из-за накладных расходов на каждый вызов COM. Если у вас всего 43 строки, как у автора вопроса, это не критично — отчет сформируется за секунду. Но если строк тысячи, процесс может занять минуты.

Для ускорения больших отчетов используется запись через Range и массивы SafeArray (COMSafeArray). В этом случае вы готовите массив данных в 1С и передаете его в Excel одной командой:


// Примерный подход для ускорения (псевдокод)
Диапазон = Лист.Range(Лист.Cells(5, 1), Лист.Cells(48, 3));
Диапазон.Value = ВашCOMSafeArray;

Альтернатива: Если Excel не установлен

Если у заказчика (или на сервере) нет лицензии на MS Office, а стоит, например, LibreOffice или вообще ничего, метод COM работать не будет. Для этой задачи есть выгрузка данных из 1С в Excel без использования MS Office. В такой ситуации часто требуется импорт данных из различных форматов (xlsx, ods, csv) для предварительной обработки или загрузки в базу.

В этом случае рассмотрим другие варианты:

Табличный документ 1С. Если формулы простые, можно создать макет в самой 1С, прописать формулы там (1С поддерживает формулы типа R1C1), и сохранять результат в xlsx. Но это требует перерисовки шаблона в конфигураторе.
NativeExcel или работа с XML. Существуют решения, позволяющие выполнять универсальную загрузку из Excel в любой справочник даже без наличия установленного Excel на компьютере. Библиотеки, работающие с XML напрямую, позволяют открыть шаблон, подменить значения в sharedStrings.xml или sheet1.xml внутри архива xlsx и сохранить файл. Это самый надежный и быстрый способ для серверной автоматизации.

Резюме

Для решения задачи автора наиболее целесообразно использовать COM-соединение с Excel. Это позволяет:

Открыть готовый шаблон с жесткой структурой.
Программно внести данные только в ячейки ввода.
Позволить Excel автоматически пересчитать все формулы.
Сохранить результат, не нарушая дизайн отчета.

Используйте приведенный выше код как базу.