При разработке интеграций и выгрузке отчетов мы часто сталкиваемся с требованием заказчика: сформированный в Системе Компоновки Данных (СКД) отчет должен автоматически сохраняться в уже существующий файл-шаблон формата Excel. При этом первые страницы (листы) файла должны оставаться неизменными, так как они могут содержать строгий формат, макросы или вводные данные для сторонних систем, а сам отчет должен ложиться строго на определенный лист (например, на третий). Проанализируем ситуацию и разберем по шагам все возможные способы решения этой технической задачи.
Самый старый и распространенный подход — это использование технологии OLE/COM. Посмотрим на пример алгоритма. Программно переносить данные по одной ячейке из 1С в Excel невероятно долго, поэтому мы применим хитрость с копированием целого листа.
Записать.Sheet и его метод Copy), чтобы скопировать лист из временного файла и вставить его третьим листом в книгу-шаблон.Рассмотрим примерную реализацию сохранения во временный файл:
// Сохраняем результат СКД во временный файл
ПутьКВременномуФайлу = ПолучитьИмяВременногоФайла("xlsx");
ТабличныйДокументРезультат.Записать(ПутьКВременномуФайлу, ТипФайлаТабличногоДокумента.XLSX);
// Дальнейшая работа с Excel.Application ...
Важно: Выясним причину, почему этот метод не идеален. Во-первых, компания Microsoft категорически не рекомендует использовать COM-объекты Office на стороне сервера. Во-вторых, если код будет выполняться регламентным заданием на сервере 1С, вам придется долго настраивать права DCOM для пользователя USR1CV82 (или аналогичного). Кроме того, в памяти сервера часто остаются «зависшие» скрытые процессы Excel, которые со временем съедают всю оперативную память.
В современных релизах платформы 1С:Предприятие 8.3 функционал работы с документами существенно расширен. Теперь мы можем загружать и сохранять файлы Excel по листам, используя объект ПакетОтображаемыхДокументов. Разберем по шагам, как это работает.
Метод объекта ТабличныйДокумент под названием Прочитать позволяет нам считать конкретный лист из Excel. Мы можем прочитать первый и второй лист шаблона в два разных табличных документа. Затем мы создаем пакет, помещаем туда эти два шаблона, а третьим элементом добавляем наш свежий отчет СКД.
Пакет = Новый ПакетОтображаемыхДокументов;
// Читаем первый лист шаблона
ТабДокЛист1 = Новый ТабличныйДокумент;
ТабДокЛист1.Прочитать(ПутьКШаблону, СпособЧтенияЗначенийТабличногоДокумента.Текст, 1);
Пакет.Состав.Добавить(ТабДокЛист1);
// Читаем второй лист шаблона
ТабДокЛист2 = Новый ТабличныйДокумент;
ТабДокЛист2.Прочитать(ПутьКШаблону, СпособЧтенияЗначенийТабличногоДокумента.Текст, 2);
Пакет.Состав.Добавить(ТабДокЛист2);
// Добавляем наш сформированный отчет СКД
Пакет.Состав.Добавить(ТабличныйДокументРезультатСКД);
// Сохраняем итоговый файл
Пакет.Записать(ПутьКНовомуФайлу, ТипФайлаПакетаОтображаемыхДокументов.XLSX);
Недостаток подхода: Платформа 1С отлично читает базовые ячейки, но может потерять сложную логику оригинального шаблона. Если на первых двух листах были сложные диаграммы, сводные таблицы, макросы или специфическое условное форматирование — при чтении и обратной записи через пакет они, скорее всего, исчезнут. Этот метод подходит, если первые два листа достаточно простые (текст, рамки, формулы).
Если нам нужно выгрузить только сырые данные (таблицу) из СКД без красивого оформления (рамочек, группировок, цветов фона), мы можем рассмотреть подключение к файлу Excel через объект ADODB.Connection.
Используя провайдер Microsoft.ACE.OLEDB.12.0, платформа 1С может обращаться к файлу .xlsx точно так же, как к таблице базы данных SQL. Мы просто пишем запросы INSERT INTO [Лист3$] и построчно выгружаем результаты нашего отчета.
Главный плюс: на сервере 1С не требуется устанавливать полноценный пакет MS Office, достаточно поставить бесплатный движок Access Database Engine. Процесс работает очень быстро и не оставляет зависших фоновых приложений, а структура всего файла-шаблона вообще не нарушается.
Если сервер 1С работает под управлением операционной системы Linux или в компании действует политика импортозамещения и Microsoft Office отсутствует, мы можем задействовать свободное ПО. Подключение к LibreOffice осуществляется через технологию OLE/UNO с использованием сервиса com.sun.star.ServiceManager.
Логика работы здесь полностью аналогична Метову 1 (с COM-объектом Excel): мы открываем скрытый процесс LibreOffice Calc, загружаем шаблон, загружаем временный файл с нашим отчетом, копируем лист и сохраняем результат. Синтаксис методов будет другим, но концептуально задача решается точно так же.
Посмотрим на самое изящное и высокотехнологичное решение, которое не требует стороннего ПО и гарантирует полную сохранность шаблона. Как мы знаем, современный файл .xlsx — это обычный ZIP-архив, содержащий набор XML-файлов. Формат (OpenXML) полностью открыт и задокументирован.
Проанализируем алгоритм:
ЧтениеZipФайла и распаковываем как временный файл, так и наш файл-шаблон во временные каталоги./xl/worksheets/sheet1.xml (это наш отчет). Также нам может понадобиться файл словаря строк sharedStrings.xml.sheet3.xml (предварительно удалив старый, если он был).[Content_Types].xml и workbook.xml в корне архива шаблона, чтобы прописать там наличие нового третьего листа. Это делается штатными средствами работы с XML (объекты ЧтениеXML и ЗаписьXML).ЗаписьZipФайла, мы запаковываем папку с шаблоном обратно и меняем расширение на .xlsx.Этот способ требует хорошего знания структуры OpenXML и умения аккуратно объединять XML-документы (диффинг). Однако, если вы один раз напишете такую обработку, она будет работать молниеносно на любой операционной системе и сервере, не повреждая исходные макросы, графики и сложное оформление первых двух страниц шаблона.
Заключение: Выбор метода зависит от ваших условий. Если сервер на Windows, а данные нужно выгрузить «на вчера» — используйте COM. Если шаблон простой — смело применяйте ПакетОтображаемыхДокументов. А если вы стремитесь к идеальной архитектуре без зависимости от стороннего ПО — потратьте время на изучение работы с OpenXML через ZIP-архивы.