Разработчики 1С часто сталкиваются с необходимостью выгрузки отчетов или документов в формат Microsoft Excel. И если выгрузка в стандартные форматы .xlsx или .xls не вызывает трудностей, то задача сохранения табличного документа в формат .xlsm (книга Excel с поддержкой макросов) напрямую встроенными средствами платформы не решается. Системное перечисление ТипФайлаТабличногоДокумента попросту не содержит формата XLSM.
Попытка принудительно указать расширение файла при сохранении, например, вызвав метод ТабДок.Записать("Выгрузка.xlsm", "xlsm") или указав тип файла ТипФайлаТабличногоДокумента.XLSX с изменением расширения в имени файла, приведет к тому, что Excel выдаст ошибку о повреждении структуры документа при попытке его открыть. Это происходит потому, что файл с поддержкой макросов имеет специфические связи и бинарные компоненты внутри своей XML-структуры, которые платформа 1С формировать по умолчанию не умеет.
В этой статье мы подробно рассмотрим и разберем три проверенных на практике способа обхода этого ограничения в зависимости от используемой операционной системы и архитектуры вашего решения.
Если сервер 1С или клиентский компьютер, на котором выполняется код выгрузки, работает под управлением ОС Windows и на нем установлен пакет Microsoft Office Excel, мы можем применить автоматизацию через COM-соединение. Этот способ наиболее прост в реализации.
Суть метода заключается в следующем: мы сохраняем наш табличный документ во временный файл формата .xlsx средствами 1С, затем программно открываем его через Excel и пересохраняем в требуемый формат .xlsm. Для этого в методе сохранения Excel необходимо передать специальный числовой идентификатор формата — 52 (константа xlOpenXMLWorkbookMacroEnabled).
Давайте разберем пример кода, реализующий этот алгоритм:
// 1. Сформируем и запишем стандартный XLSX во временную директорию
ВременныйПутьXLSX = ПолучитьИмяВременногоФайла("xlsx");
ТабличныйДокумент.Записать(ВременныйПутьXLSX, ТипФайлаТабличногоДокумента.XLSX);
// Укажем путь, куда необходимо положить итоговый файл .xlsm
ИтоговыйПутьXLSM = "C:\Выгрузка\ОтчетСМакросами.xlsm";
// 2. Инициализируем COM-соединение с Microsoft Excel для конвертации
Попытка
ExcelПриложение = Новый COMОбъект("Excel.Application");
ExcelПриложение.Visible = Ложь;
ExcelПриложение.DisplayAlerts = Ложь; // Отключаем вывод предупреждений и диалоговых окон
// Открываем ранее сохраненный временный файл XLSX
Книга = ExcelПриложение.Workbooks.Open(ВременныйПутьXLSX);
// Выполняем сохранение в формате XLSM (числовой код 52)
Книга.SaveAs(ИтоговыйПутьXLSM, 52);
// Закрываем книгу и приложение Excel
Книга.Close(Ложь);
ExcelПриложение.Quit();
// Удаляем временный XLSX-файл за собой
УдалитьФайлы(ВременныйПутьXLSX);
Исключение
// Обязательно обрабатываем исключения и корректно закрываем процесс Excel в случае сбоя
Если ЗначениеЗаполнено(ExcelПриложение) Тогда
ExcelПриложение.Quit();
КонецЕсли;
ЗаписьЖурналаРегистрации("Сохранение в XLSM", УровеньЖурналаРегистрации.Ошибка, , ,
"Ошибка конвертации файла через COM: " + ПодробноеПредставлениеОшибки(ИнформацияОбОшибке()));
ВызватьИсключение "Не удалось сохранить файл в формате XLSM. Обратитесь к администратору.";
КонецПопытки;
Важное примечание: Если вам требуется не просто поменять формат, а программно внедрить код макроса прямо «на лету», вы можете использовать объектную модель VBA через COM-соединение. Для этого предварительно выгрузите текст макроса в текстовый файл и импортируйте его в проект книги:
// Пример импорта макроса из внешнего файла
Книга.VBProject.VBComponents.Import("C:\Templates\MyMacroCode.txt");
Учтите, что для успешного программного импорта кода VBA в настройках безопасности Microsoft Excel должна быть включена опция «Доверять доступ к объектной модели проектов VBA».
Этот способ является наиболее надежным и предпочтительным, когда структура макросов известна заранее и не меняется динамически. Нам не нужно генерировать макросы программно — мы просто создаем готовый пустой файл-шаблон с нужными макросами и помещаем его внутрь конфигурации 1С.
Разберем этот процесс по шагам:
.xlsm (например, Template.xlsm).Template.xlsm.Посмотрим на пример реализации данного метода:
// Извлекаем двоичные данные шаблона из макета конфигурации
ДвоичныеДанныеШаблона = УправлениеОтчетами.ПолучитьМакет("НашШаблонСМакросами"); // Пример обращения к общему макету
ВременныйФайлШаблона = ПолучитьИмяВременногоФайла("xlsm");
ДвоичныеДанныеШаблона.Записать(ВременныйФайлШаблона);
Попытка
ExcelПриложение = Новый COMОбъект("Excel.Application");
ExcelПриложение.Visible = Ложь;
ExcelПриложение.DisplayAlerts = Ложь;
// Открываем временную копию нашего шаблона
Книга = ExcelПриложение.Workbooks.Open(ВременныйФайлШаблона);
Лист = Книга.Worksheets(1); // Работаем с первым листом
// Заполняем шаблон данными. Например, выведем шапку и строки
Лист.Cells(5, 2).Value = "Отчет по продажам за " + Формат(ТекущаяДата(), "ДФ=dd.MM.yyyy");
// Предположим, у нас есть выборка данных
СтрокаНомер = 8;
Пока ВыборкаДанных.Следующий() Цикл
Лист.Cells(СтрокаНомер, 2).Value = ВыборкаДанных.Номенклатура;
Лист.Cells(СтрокаНомер, 3).Value = ВыборкаДанных.Количество;
Лист.Cells(СтрокаНомер, 4).Value = ВыборкаДанных.Сумма;
СтрокаНомер = СтрокаНомер + 1;
КонецЦикла;
// Сохраняем заполненный файл под целевым именем
Книга.SaveAs("D:\Reports\ИтоговыйОтчет.xlsm", 52);
Книга.Close(Истина);
ExcelПриложение.Quit();
// Очищаем временный файл шаблона
УдалитьФайлы(ВременныйФайлШаблона);
Исключение
Если ЗначениеЗаполнено(ExcelПриложение) Тогда
ExcelПриложение.Quit();
КонецЕсли;
ЗаписьЖурналаРегистрации("Заполнение Шаблона XLSM", УровеньЖурналаРегистрации.Ошибка,,, ПодробноеПредставлениеОшибки(ИнформацияОбОшибке()));
ВызватьИсключение "Ошибка при заполнении шаблона макросов: " + КраткоеПредставлениеОшибки(ИнформацияОбОшибке());
КонецПопытки;
Преимущество этого метода заключается в том, что структура макросов, стили оформления, логотипы компании и формулы Excel уже настроены визуально и не загромождают код 1С. Мы лишь транслируем сырые данные в подготовленные ячейки.
Что делать, если сервер приложений 1С развернут на операционной системе семейства Linux? Использовать COM-объекты Windows (COMОбъект("Excel.Application")) в данной среде невозможно. Тем не менее, задача автоматической генерации файлов с макросами остается актуальной.
Для решения этой проблемы мы можем прибегнуть к ручной сборке структуры файла формата OpenXML. Файл с расширением .xlsm (как и .xlsx) представляет собой обычный ZIP-архив, содержащий XML-файлы разметки данных, стилей, метаданных и связей. Главное отличие формата .xlsm от .xlsx заключается в наличии бинарного файла vbaProject.bin внутри папки xl/ архива, а также в регистрации соответствующих типов связей в метаданных XML.
Проанализируем пошаговый алгоритм решения этой задачи кроссплатформенными методами:
.xlsm. Смените расширение файла на .zip и распакуйте его.xl/ файл с именем vbaProject.bin. Скопируйте его — именно в этом бинарном файле скомпилирован ваш макрос VBA. Добавьте данный файл в макет конфигурации 1С как двоичные данные..xlsx с помощью стандартного метода ТабличныйДокумент.Записать(ПутьКФайлу, ТипФайлаТабличногоДокумента.XLSX).ЧтениеZipФайла, распакуйте созданный .xlsx файл в отдельный временный каталог на сервере.vbaProject.bin в распакованный каталог в подпапку xl/.[Content_Types].xml (находится в корне архива) внутри тега <Types> добавьте строку: <Override PartName="/xl/vbaProject.bin" ContentType="application/vnd.ms-office.vbaProject"/>xl/_rels/workbook.xml.rels добавьте связь с файлом макроса: <Relationship Id="rIdVBA" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/vbaProject" Target="vbaProject.bin"/>ЗаписьZipФайла и присвойте итоговому архиву расширение .xlsm.Ниже приведен пример функции на встроенном языке 1С, выполняющей интеграцию файла макроса в готовый XLSX-архив:
Функция ВнедритьМакросыВАрхив(ПутьКФайлуXLSX, ПутьКФайлуXLSM, ДвоичныеДанныеVBA) Экспорт
// Создаем временную директорию для распаковки структуры XML
ПутьРаспаковки = КаталогВременныхФайлов() + "ExcelOpenXML_" + СтрЗаменить(Строка(Новый УникальныйИдентификатор), "-", "");
СоздатьКаталог(ПутьРаспаковки);
// 1. Распаковываем XLSX архив
АрхивЧтение = Новый ЧтениеZipФайла(ПутьКФайлуXLSX);
АрхивЧтение.ИзвлечьВсе(ПутьРаспаковки, РежимВосстановленияПутейФайловZIP.Восстанавливать);
АрхивЧтение.Закрыть();
// 2. Помещаем бинарный файл макросов vbaProject.bin в папку xl/
ПутьКПапкеXL = ПутьРаспаковки + "/xl";
СоздатьКаталог(ПутьКПапкеXL);
ДвоичныеДанныеVBA.Записать(ПутьКПапкеXL + "/vbaProject.bin");
// 3. Модифицируем файл [Content_Types].xml
ПутьContentTypes = ПутьРаспаковки + "/[Content_Types].xml";
ТекстXML = Новый ТекстовыйДокумент;
ТекстXML.Прочитать(ПутьContentTypes, КодировкаТекста.UTF8);
ПолныйТекстXML = ТекстXML.ПолучитьТекст();
// Проверим, нет ли уже зарегистрированного vbaProject
Если Найти(ПолныйТекстXML, "vbaProject.bin") = 0 Тогда
СтрокаПоиска = "</Types>";
СтрокаВставки = "<Override PartName=""/xl/vbaProject.bin"" ContentType=""application/vnd.ms-office.vbaProject""/>" + Символы.ПС + "</Types>";
ПолныйТекстXML = СтрЗаменить(ПолныйТекстXML, СтрокаПоиска, СтрокаВставки);
ТекстXML.УстановитьТекст(ПолныйТекстXML);
ТекстXML.Записать(ПутьContentTypes, КодировкаТекста.UTF8);
КонецЕсли;
// 4. Модифицируем файл связей xl/_rels/workbook.xml.rels
ПутьRels = ПутьРаспаковки + "/xl/_rels/workbook.xml.rels";
ТекстRels = Новый ТекстовыйДокумент;
ТекстRels.Прочитать(ПутьRels, КодировкаТекста.UTF8);
ПолныйТекстRels = ТекстRels.ПолучитьТекст();
Если Найти(ПолныйТекстRels, "vbaProject.bin") = 0 Тогда
СтрокаПоискаRels = "</Relationships>";
СтрокаВставкиRels = "<Relationship Id=""rIdVBA"" Type=""http://schemas.openxmlformats.org/officeDocument/2006/relationships/vbaProject"" Target=""vbaProject.bin""/>" + Символы.ПС + "</Relationships>";
ПолныйТекстRels = СтрЗаменить(ПолныйТекстRels, СтрокаПоискаRels, СтрокаВставкиRels);
ТекстRels.УстановитьТекст(ПолныйТекстRels);
ТекстRels.Записать(ПутьRels, КодировкаТекста.UTF8);
КонецЕсли;
// 5. Запаковываем всё обратно в архив с расширением XLSM
НовыйАрхив = Новый ЗаписьZipФайла(ПутьКФайлуXLSM);
НовыйАрхив.Добавить(ПутьРаспаковки + "/*", РежимСохраненияПутейФайловZIP.СохранятьОтносительныеПути, РежимВключенияПодкаталоговZIP.Рекурсивно);
НовыйАрхив.Записать();
// 6. Удаляем временную папку распаковки
УдалитьФайлы(ПутьРаспаковки);
Возврат Истина;
КонецФункции
Этот способ требует более глубокого понимания формата Microsoft OpenXML, однако он полностью автономен. Ему не требуются установленные на сервере офисные пакеты программ, он не создает зависших фоновых процессов Excel в диспетчере задач операционной системы и стабильно работает на серверах под управлением ОС Linux.
Подводя итоги анализа проблемы сохранения табличных документов в формате .xlsm с макросами в 1С, сформулируем основные рекомендации для разработчиков: