При разработке отчетов и выгрузок в 1С часто возникает задача: сформировать несколько табличных документов и объединить их в один файл Excel, где каждый отчет будет расположен на отдельном листе. Стандартный метод платформы ТабличныйДокумент.Записать() умеет сохранять данные только в отдельный файл и не поддерживает многостраничную запись в один документ.
Разберем основные причины сложностей при решении этой задачи, детально рассмотрим рабочий программный код на базе COM-объекта Excel.Application, а также выясним альтернативные способы оптимизации, если табличные документы имеют огромный объем и вызывают ошибку переполнения памяти.
Проанализируем, почему стандартные подходы могут приводить к сбоям:
ТабличныйДокумент.Записать() формирует файл формата XLS или XLSX строго с одним рабочим листом.Copy() и Paste() всего диапазона ячеек задействует системный буфер Windows. На больших объемах данных это приводит к блокировкам, потере форматирования или зависанию процесса.Рассмотрим надежный вариант работы с COM-объектом Excel: сначала сохраняем каждый табличный документ во временный файл средствами 1С, а затем создаем результирующую книгу и переносим листы напрямую методом Copy() коллекции Worksheets, не задействуя буфер обмена ячеек.
Посмотрим на пример кода, выполняющего объединение трех табличных документов:
// Предположим, у нас уже сформированы три табличных документа: ТабДок1, ТабДок2, ТабДок3
ВременныйКаталог = КаталогВременныхФайлов();
ПутьФайл1 = ВременныйКаталог + "РазОтчет.xls";
ПутьФайл2 = ВременныйКаталог + "ДваОтчет.xls";
ПутьФайл3 = ВременныйКаталог + "ТриОтчет.xls";
ИтоговыйФайл = "C:\Users\Home\Desktop\ИтоговыйОтчет.xls";
ТабДок1.Записать(ПутьФайл1, ТипФайлаТабличногоДокумента.XLS);
ТабДок2.Записать(ПутьФайл2, ТипФайлаТабличногоДокумента.XLS);
ТабДок3.Записать(ПутьФайл3, ТипФайлаТабличногоДокумента.XLS);
СписокФайлов = Новый ТаблицаЗначений;
СписокФайлов.Колонки.Добавить("Путь");
СписокФайлов.Колонки.Добавить("ИмяЛиста");
Стр = СписокФайлов.Добавить();
Стр.Путь = ПутьФайл1;
Стр.ИмяЛиста = "Отчет 1";
Стр = СписокФайлов.Добавить();
Стр.Путь = ПутьФайл2;
Стр.ИмяЛиста = "Отчет 2";
Стр = СписокФайлов.Добавить();
Стр.Путь = ПутьФайл3;
Стр.ИмяЛиста = "Отчет 3";
Попытка
Эксель = Новый COMОбъект("Excel.Application");
Исключение
ВызватьИсключение "Не удалось инициализировать Excel.Application. Убедитесь, что Microsoft Excel установлен.";
КонецПопытки;
Эксель.Visible = 0;
Эксель.DisplayAlerts = 0;
// Создаем новую результирующую книгу
ИтоговаяКнига = Эксель.Workbooks.Add();
Для Каждого СтрокаФайла Из СписокФайлов Цикл
Попытка
ВременнаяКнига = Эксель.Workbooks.Open(СтрокаФайла.Путь);
Исключение
Продолжить;
КонецПопытки;
ЛистИсточник = ВременнаяКнига.Worksheets(1);
ЛистИсточник.Name = СтрокаФайла.ИмяЛиста;
// Копируем лист целиком перед первым листом итоговой книги
ЛистИсточник.Copy(ИтоговаяКнига.Worksheets(1));
ВременнаяКнига.Close(Ложь);
// Удаляем временный файл с диска
УдалитьФайлы(СтрокаФайла.Путь);
КонецЦикла;
// Удаляем пустые листы, созданные Excel по умолчанию при создании новой книги
Пока ИтоговаяКнига.Worksheets.Count > СписокФайлов.Количество() Цикл
ИтоговаяКнига.Worksheets(ИтоговаяКнига.Worksheets.Count).Delete();
КонецЦикла;
// Сохраняем готовую книгу
ИтоговаяКнига.SaveAs(ИтоговыйФайл);
ИтоговаяКнига.Close(Ложь);
Эксель.Quit();
// Очищаем переменные COM для освобождения памяти
ИтоговаяКнига = Неопределено;
Эксель = Неопределено;
Если один из отчетов содержит десятки тысяч строк, поочередное заполнение ячеек или перенос через временные файлы может замедлять систему. Рассмотрим правила оптимизации работы с Excel.Application:
ScreenUpdating и Calculation.Cells(i, j) формируется двумерный массив COMSafeArray, который передается целиком в свойство Value выбранного диапазона Range.Quit() и обнуляем ссылки на COM-объекты для вызова сборщика мусора.Посмотрим на пример пакетной вставки данных через массив без предварительного создания табличного документа:
// Пример прямой пакетной выгрузки данных таблицы значений в лист Excel
Эксель = Новый COMОбъект("Excel.Application");
Эксель.Visible = 0;
Эксель.DisplayAlerts = 0;
Эксель.ScreenUpdating = 0;
Эксель.Calculation = -4135; // xlCalculationManual
Книга = Эксель.Workbooks.Add();
Лист = Книга.Worksheets(1);
Лист.Name = "БольшиеДанные";
КоличествоСтрок = ТаблицаДанных.Количество();
КоличествоКолонок = ТаблицаДанных.Колонки.Количество();
// Создаем двумерный SafeArray для быстрой вставки
МассивДанных = Новый COMSafeArray("VT_VARIANT", КоличествоСтрок, КоличествоКолонок);
Для НомерСтроки = 0 По КоличествоСтрок - 1 Цикл
СтрокаДанных = ТаблицаДанных[НомерСтроки];
Для НомерКолонки = 0 По КоличествоКолонок - 1 Цикл
МассивДанных.SetValue(НомерСтроки, НомерКолонки, СтрокаДанных[НомерКолонки]);
КонецЦикла;
КонецЦикла;
// Заполняем диапазон одной командой
ЯчейкаНачала = Лист.Cells(1, 1);
ЯчейкаКонца = Лист.Cells(КоличествоСтрок, КоличествоКолонок);
Лист.Range(ЯчейкаНачала, ЯчейкаКонца).Value = МассивДанных;
Эксель.Calculation = -4105; // xlCalculationAutomatic
Эксель.ScreenUpdating = 1;
Книга.SaveAs("C:\Users\Home\Desktop\БыстрыйОтчет.xlsx");
Книга.Close(Ложь);
Эксель.Quit();
Лист = Неопределено;
Книга = Неопределено;
Эксель = Неопределено;
Если на сервере или рабочей станции не установлен Microsoft Excel или выгрузка падает из-за ограничений 32-разрядной памяти, разберем альтернативные решения:
ЗаписьXML или ЗаписьТекста. Внутри XML-файла создается несколько блоков <Worksheet ss:Name="...">. Excel открывает такие файлы как полноценную многостраничную книгу. При этом память сервера не перегружается, так как запись идет потоком..xlsx представляет собой ZIP-архив с XML-файлами, можно выгрузить три табличных документа в отдельные файлы .xlsx штатным методом 1С, после чего средствами ЧтениеZipФайла и ЗаписьZipФайла перенести таблицы sheet1.xml в общий архив и обновить связующие файлы workbook.xml и [Content_Types].xml.