Как записать ТабличныйДокумент в новый лист существующего файла Excel в 1С?

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

В этой статье мы разберем, почему возникает такое ограничение и как с помощью COM-технологий объявить и сформировать многостраничный файл Excel, перенеся туда данные из объектов ТабличныйДокумент.

Выясним причину ограничений встроенного метода

Рассмотрим, как устроен объект ТабличныйДокумент в платформе 1С:

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

Решение через промежуточные файлы и COM-объект Excel

Для создания многостраничной книги мы сначала сохраняем каждый ТабличныйДокумент во временный файл формата Excel, после чего с помощью COM-объекта Excel.Application копируем листы из временных файлов в итоговую книгу и переименовываем их.

Рассмотрим по шагам реализацию этого алгоритма:

1. Сохраняем сформированные печатные формы во временные файлы на диске методом ТабличныйДокумент.Записать().
2. Инициализируем COM-соединение с приложением Excel через Новый COMОбъект("Excel.Application").
3. Открываем основной (целевой) файл Excel.
4. В цикле открываем каждый временный файл, находим его первый лист и копируем его в основную книгу с помощью метода Copy().
5. Присваиваем скопированному листу требуемое наименование.
6. Сохраняем изменения в основной книге, закрываем временные файлы и удаляем их с диска.

Разберем пример кода на языке 1С:

// 1. Предположим, что временные файлы уже сохранены, и их пути записаны в массив МассивПутейФайловXLS
ИмяОсновногоФайла = "C:\Reports\Реестр.xls";

ДокЭксель = Новый COMОбъект("Excel.Application");
ДокЭксель.DisplayAlerts = 0; // Отключаем предупреждающие диалоги Excel

// Открываем итоговую книгу
ОбщаяКнига = ДокЭксель.WorkBooks.Open(ИмяОсновногоФайла);

// Настраиваем первый лист
ЛистСДанными = ОбщаяКнига.Sheets(1);
ЛистСДанными.Select();
ЛистСДанными.Name = "Титульный";

Сч = 1;
Для каждого ПутьКФайлуXLS Из МассивПутейФайловXLS Цикл
    // Открываем очередную временную книгу
    Книга = ДокЭксель.WorkBooks.Open(ПутьКФайлуXLS.Значение);
    ЛистСДанными = Книга.Sheets(1);
    ЛистСДанными.Select();
    
    // Получаем имя для листа из имени файла
    ИмяЛиста = СтрЗаменить(Книга.Name, ".xls", "");
    ЛистСДанными.Name = ИмяЛиста;
    
    // Копируем лист в основную книгу
    ЛистСДанными.Copy(ОбщаяКнига.Sheets(Сч));
    
    Сч = Сч + 1;
    Книга.Close();
КонецЦикла; 

// Настраиваем отображение ярлыков листов и активируем первый лист
ДокЭксель.ActiveWindow.TabRatio = 0.5;
ЛистСДанными = ОбщаяКнига.Sheets(1);
ЛистСДанными.Select();

// Сохраняем и закрываем итоговый файл
ОбщаяКнига.Save();
ОбщаяКнига.Close();
ДокЭксель.Quit();

ДокЭксель = Неопределено;

// Удаляем временные файлы
Для каждого ПутьКФайлуXLS Из МассивПутейФайловXLS Цикл
    УдалитьФайлы(СокрЛП(ПутьКФайлуXLS.Значение));
КонецЦикла;

Альтернативные способы и практические рекомендации

В зависимости от архитектуры вашей системы и инфраструктуры, рассмотрим дополнительные варианты решения задачи:

1. Прямая запись в Excel через COM без временных файлов:

   Можно не сохранять ТабличныйДокумент во временный файл, а напрямую открывать целевую книгу через COMОбъект("Excel.Application"), создавать новый лист методом Worksheets.Add() и заполнять ячейки данными из 1С.

2. Оптимизация скорости выгрузки (SafeArray):

   Если вы выгружаете таблицы большого размера через COM напрямую, поячеечное заполнение будет работать медленно. Для ускорения процесса рекомендуется выгружать данные из 1С в двумерный массив COMSafeArray и записывать его в диапазон Range.Value за один вызов.

3. Работа в Linux и на сервере без установленного MS Office:

   Важно помнить, что COMОбъект("Excel.Application") работает только в операционной системе Windows при наличии установленного Microsoft Excel. Для работы на серверах под управлением Linux применяются сторонние библиотеки и компоненты (например, на Python, .NET или C++), либо выполняется прямая сборка структуры ZIP/XML файла формата XLSX средствами 1С.