Как скопировать лист Excel из одного файла в другой в 1С с сохранением форматирования

← На главную

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

В этой статье мы подробно разберем, почему стандартные подходы с простым переносом значений через массивы приводят к потере оформления, и рассмотрим два надежных способа решения этой задачи: классический (через COM-объект Microsoft Excel) и современный кроссплатформенный (через встроенные механизмы Табличного Документа 1С).

Выясним причины ошибок начинающих разработчиков и научимся писать стабильный код.

---

Анализ исходной проблемы и ошибок в коде

Давайте проанализируем код, который часто пытаются использовать разработчики на начальном этапе (аналогичный коду из обсуждения на форуме):


Данные = Область2.Value.Выгрузить(); // Ошибка!
Область.Value = Данные;              // Данные переносятся без форматирования

В этом подходе кроются две ключевые проблемы:

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

Для решения этой задачи нужно использовать специализированные методы копирования областей.

---

Способ 1. Копирование с форматированием через COM (Microsoft Excel)

Этот способ основан на использовании установленного в системе пакета Microsoft Office. Для того чтобы скопировать область полностью, мы будем использовать метод Copy() самого Excel. Этот метод работает аналогично привычному сочетанию клавиш Ctrl+C / Ctrl+V.

Также важным шагом является определение реального конца таблицы в файле-получателе. Метод SpecialCells(11), возвращающий последнюю ячейку, не всегда работает корректно, если из Excel удалялись строки (он продолжает считать удаленные строки «занятыми»). Мы применим более надежный метод — поиск последней строки снизу вверх (аналог нажатия Ctrl + Стрелка Вверх).

Рассмотрим пошагово готовый и протестированный код:


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

Попытка
    // 1. Открываем файл-приемник (куда будем вставлять данные)
    КнигаПриемник = Эксель.Workbooks.Open("C:\Temp\Receiver.xlsx");
    ЛистПриемник = КнигаПриемник.Worksheets(1);

    // 2. Открываем файл-источник (откуда копируем данные)
    КнигаИсточник = Эксель.Workbooks.Open("C:\Temp\Source.xlsx");
    ЛистИсточник = КнигаИсточник.Worksheets(1);

    // 3. Определяем границы заполненной области в источнике
    // Используем SpecialCells(11) для быстрого нахождения последней заполненной ячейки источника
    ПоследняяСтрокаИсточника = ЛистИсточник.Cells(1, 1).SpecialCells(11).Row;
    ПоследняяКолонкаИсточника = ЛистИсточник.Cells(1, 1).SpecialCells(11).Column;
    
    // Выделяем всю область данных источника
    ОбластьИсточник = ЛистИсточник.Range(ЛистИсточник.Cells(1, 1), ЛистИсточник.Cells(ПоследняяСтрокаИсточника, ПоследняяКолонкаИсточника));

    // 4. Находим реальную последнюю заполненную строку в файле-приемнике.
    // Значение -4162 соответствует системной константе Excel "xlUp" (поиск вверх)
    КоличествоСтрокПриемника = ЛистПриемник.Rows.Count;
    ПоследняяСтрокаПриемника = ЛистПриемник.Cells(КоличествоСтрокПриемника, 1).End(-4162).Row;

    // Определяем целевую ячейку для вставки (следующая строка после заполненных)
    ЯчейкаВставки = ЛистПриемник.Cells(ПоследняяСтрокаПриемника + 1, 1);

    // 5. Копируем область из источника в приемник с сохранением всех стилей и формул
    ОбластьИсточник.Copy(ЯчейкаВставки);

    // Опционально: Копируем ширину колонок, чтобы таблица не "съехала" по дизайну
    // Значение 8 соответствует константе "xlPasteColumnWidths"
    // ОбластьИсточник.Copy();
    // ЯчейкаВставки.PasteSpecial(8);

    // Сохраняем изменения и закрываем файлы
    КнигаПриемник.Save();
    КнигаПриемник.Close();
    КнигаИсточник.Close();
    
    Сообщить("Данные успешно объединены с сохранением форматирования!");

Исключение
    // В случае ошибки обязательно закрываем процесс Excel, чтобы он не завис в памяти сервера/компьютера
    Эксель.Quit();
    ВызватьИсключение КраткоеПредставлениеОшибки(ИнформацияОбОшибке());
КонецПопытки;

// Завершаем работу с Excel
Эксель.Quit();

Важное примечание: При работе с COM-объектами всегда оборачивайте код в конструкцию Попытка...Исключение и не забывайте вызывать метод Quit() в блоке исключения. В противном случае в диспетчере задач операционной системы будут плодиться зависшие процессы EXCEL.EXE, которые со временем перегрузят оперативную память.

---

Способ 2. Использование Табличного Документа 1С (Рекомендуемый современный метод)

Использование COM-соединения имеет ряд существенных недостатков: оно требует установленного MS Office на компьютере или сервере, не работает на ОС Linux (актуально для большинства современных серверов 1С) и работает медленно на больших объемах данных.

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

Рассмотрим, как реализовать этот подход программно:


// 1. Создаем табличный документ для файла-приемника и считываем его
ТабДокПриемник = Новый ТабличныйДокумент;
ТабДокПриемник.Прочитать("C:\Temp\Receiver.xlsx", СпособЧтенияТабличногоДокумента.XLSX);

// 2. Создаем табличный документ для файла-источника и считываем его
ТабДокИсточник = Новый ТабличныйДокумент;
ТабДокИсточник.Прочитать("C:\Temp\Source.xlsx", СпособЧтенияТабличногоДокумента.XLSX);

// 3. Находим последнюю заполненную строку в приемнике
ВысотаПриемника = ТабДокПриемник.ВысотаТаблицы;

// 4. Получаем область, которую нужно скопировать из источника (весь заполненный лист)
ВысотаИсточника = ТабДокИсточник.ВысотаТаблицы;
ШиринаИсточника = ТабДокИсточник.ШиринаТаблицы;

Если ВысотаИсточника > 0 И ШиринаИсточника > 0 Тогда
    
    // Выделяем область источника
    ОбластьИсточник = ТабДокИсточник.Область(1, 1, ВысотаИсточника, ШиринаИсточника);
    
    // Выделяем ячейку в приемнике, куда начнется вставка (строка после последней заполненной)
    ОбластьВставки = ТабДокПриемник.Область(ВысотаПриемника + 1, 1);
    
    // Вставляем область со всеми стилями, шрифтами, объединениями ячеек и рамками
    ТабДокПриемник.ВставитьОбласть(ОбластьИсточник, ОбластьВставки);
    
    // 5. Записываем обновленный файл обратно в формат XLSX
    ТабДокПриемник.Записать("C:\Temp\Receiver.xlsx", ТипФайлаТабличногоДокумента.XLSX);
    
    Сообщить("Файлы успешно объединены через Табличный Документ!");
Иначе
    Сообщить("Файл-источник оказался пустым.");
КонецЕсли;

---

Сравнение подходов: что выбрать?

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

Табличный документ 1С (Способ 2) является приоритетным. Он работает в десятки раз быстрее, не зависит от разрядности операционной системы и операционной среды (Windows/Linux), не требует лицензий на Microsoft Office и абсолютно безопасен с точки зрения утечек памяти. Используйте его во всех типовых задачах интеграции.
COM-соединение (Способ 1) может понадобиться только в тех редких случаях, когда в исходных файлах Excel зашиты сложные макросы (VBA), специфические графические элементы (диаграммы, сложные смарт-объекты) или формулы с внешними связями, которые стандартный парсер 1С не может корректно прочитать при прямом импорте.

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

← На главную