Принцип работы: Формируем и сохраняем книгу Excel, открываем файл как архив, записываем формулы в соответствующие xml файлы листов, снова формируем архив и сохраняем как файл Excel.
Решение:
1. Создадим 2 макета для разных листов книги (если нужно несколько листов):
Формулы пишем прямо в макет как в файле Excel.
Создаем реквизиты:
2. Формируем 2 табличных документа из макетов и сохраняем формулы в Таблицу значений.
Обработка = РеквизитФормыВЗначение("Объект");
Макет = Обработка.ПолучитьМакет("Лист1");
Область = Макет.ПолучитьОбласть("ОбластьТаблица");
СтруктураФормулы = Новый Структура("Лист, Ячейка, Формула");
Для Строка = 1 По Область.ВысотаТаблицы Цикл
Для Колонка = 1 По Область.ШиринаТаблицы Цикл
ТекстЯчейки = Область.Область(Строка, Колонка).Текст;
Если СтрНачинаетсяС(ТекстЯчейки, "=") Тогда
СтруктураФормулы.Лист = 1;
СтруктураФормулы.Формула = СтрЗаменить(ТекстЯчейки, "=", "");
СтруктураФормулы.Ячейка = Символ(64 + Колонка) + Строка(Строка);
ЗаполнитьЗначенияСвойств(Формулы.Добавить(), СтруктураФормулы);
КонецЕсли;
КонецЦикла;
КонецЦикла;
Таблица1.Вывести(Область);
Макет = Обработка.ПолучитьМакет("Лист2");
Область = Макет.ПолучитьОбласть("ОбластьТаблица");
Для Строка = 1 По Область.ВысотаТаблицы Цикл
Для Колонка = 1 По Область.ШиринаТаблицы Цикл
ТекстЯчейки = Область.Область(Строка, Колонка).Текст;
Если СтрНачинаетсяС(ТекстЯчейки, "=") Тогда
СтруктураФормулы.Лист = 2;
СтруктураФормулы.Формула = СтрЗаменить(ТекстЯчейки, "=", "");
СтруктураФормулы.Ячейка = Символ(64 + Колонка) + Строка(Строка);
ЗаполнитьЗначенияСвойств(Формулы.Добавить(), СтруктураФормулы);
КонецЕсли;
КонецЦикла;
КонецЦикла;
Таблица2.Вывести(Область);
3. Сохраняем в файл Excel
ВременныйКаталог = КаталогВременныхФайлов();
ИмяВременногоФайла= ПолучитьИмяВременногоФайла(".xlsx");
Книга = Новый ПакетОтображаемыхДокументов;
Лист1 = Книга.Состав.Добавить();
Лист1.Данные = ПоместитьВоВременноеХранилище(Таблица1);
Лист1.Наименование = "Лист 1";
Лист2 = Книга.Состав.Добавить();
Лист2.Данные = ПоместитьВоВременноеХранилище(Таблица2);
Лист2.Наименование = "Лист 2";
Книга.Записать(ИмяВременногоФайла, ТипФайлаПакетаОтображаемыхДокументов.XLSX);
4. Разархивируем
Архив = Новый ЧтениеZipФайла(ИмяВременногоФайла, "");
Архив.ИзвлечьВсе(ВременныйКаталог + "\xlsfrom1C", РежимВосстановленияПутейФайловZIP.Восстанавливать);
Архив.Закрыть();
5. Меняем в нужных ячейках данные на формулы
// Меняем в нужных ячейках на формулы
ЗаменитьПараметры(ВременныйКаталог + "\xlsfrom1C\xl\worksheets\sheet1.xml", 1);
ЗаменитьПараметры(ВременныйКаталог + "\xlsfrom1C\xl\worksheets\sheet2.xml", 2);
Процедура ЗаменитьПараметры(ИмяФайлаXML, НомерЛиста)
Текст = Новый ТекстовыйДокумент;
Текст.Прочитать(ИмяФайлаXML, "windows-1251");
СтрокаXML = Текст.ПолучитьТекст();
ФормулыЛиста = Формулы.НайтиСтроки(Новый Структура("Лист", НомерЛиста));
Для Каждого Формула Из ФормулыЛиста Цикл
ПозицияЯчейки = СтрНайти(СтрокаXML, "<c r=""" + Формула.Ячейка +"""");
Если ПозицияЯчейки = 0 Тогда Продолжить; КонецЕсли;
НачалоФормулы = СтрНайти(СтрокаXML, "<v>",,ПозицияЯчейки);
Если НачалоФормулы = 0 Тогда Продолжить; КонецЕсли;
КонецФормулы = СтрНайти(СтрокаXML, "</v>",,НачалоФормулы);
Если КонецФормулы = 0 Тогда Продолжить; КонецЕсли;
СтрокаXML = Лев(СтрокаXML, НачалоФормулы - 1) + "<f>" + Формула.Формула + "</f>" + Прав(СтрокаXML, СтрДлина(СтрокаXML) - КонецФормулы - 3);
КонецЦикла;
Текст.УстановитьТекст(СтрокаXML);
Текст.Записать(ИмяФайлаXML, "windows-1251");
КонецПроцедуры
6. Заново формируем файл Excel
АрхивРезультат = Новый ЗаписьZipФайла(ИмяВременногоФайла);
АрхивРезультат.Добавить(ВременныйКаталог + "\xlsfrom1C\*.*", РежимСохраненияПутейZIP.СохранятьОтносительныеПути, РежимОбработкиПодкаталоговZIP.ОбрабатыватьРекурсивно);
АрхивРезультат.Записать();
Итоговый код обработки:
//Обработка сохраняет 2 макета с формулами как книгу Excel 2мя листами без использования Com.
//Можно использовать как образец, при решении подобных задач.
&НаСервере
Процедура ПриСозданииНаСервере(Отказ, СтандартнаяОбработка)
Обработка = РеквизитФормыВЗначение("Объект");
Макет = Обработка.ПолучитьМакет("Лист1");
Область = Макет.ПолучитьОбласть("ОбластьТаблица");
СтруктураФормулы = Новый Структура("Лист, Ячейка, Формула");
Для Строка = 1 По Область.ВысотаТаблицы Цикл
Для Колонка = 1 По Область.ШиринаТаблицы Цикл
ТекстЯчейки = Область.Область(Строка, Колонка).Текст;
Если СтрНачинаетсяС(ТекстЯчейки, "=") Тогда
СтруктураФормулы.Лист = 1;
СтруктураФормулы.Формула = СтрЗаменить(ТекстЯчейки, "=", "");
СтруктураФормулы.Ячейка = Символ(64 + Колонка) + Строка(Строка);
ЗаполнитьЗначенияСвойств(Формулы.Добавить(), СтруктураФормулы);
КонецЕсли;
КонецЦикла;
КонецЦикла;
Таблица1.Вывести(Область);
Макет = Обработка.ПолучитьМакет("Лист2");
Область = Макет.ПолучитьОбласть("ОбластьТаблица");
Для Строка = 1 По Область.ВысотаТаблицы Цикл
Для Колонка = 1 По Область.ШиринаТаблицы Цикл
ТекстЯчейки = Область.Область(Строка, Колонка).Текст;
Если СтрНачинаетсяС(ТекстЯчейки, "=") Тогда
СтруктураФормулы.Лист = 2;
СтруктураФормулы.Формула = СтрЗаменить(ТекстЯчейки, "=", "");
СтруктураФормулы.Ячейка = Символ(64 + Колонка) + Строка(Строка);
ЗаполнитьЗначенияСвойств(Формулы.Добавить(), СтруктураФормулы);
КонецЕсли;
КонецЦикла;
КонецЦикла;
Таблица2.Вывести(Область);
КонецПроцедуры
&НаСервере
Процедура СохранитьВExcelНаСервере()
ВременныйКаталог = КаталогВременныхФайлов();
ИмяВременногоФайла= ПолучитьИмяВременногоФайла(".xlsx");
// Временно, для удобства тестирования пишем на сервер в каталог C:\Test
// В дальнейшем можно убрать и сделать передачу файла на клиента
ВременныйКаталог = "C:\Test";
ИмяВременногоФайла = "C:\Test\Test.xlsx";
//
Книга = Новый ПакетОтображаемыхДокументов;
Лист1 = Книга.Состав.Добавить();
Лист1.Данные = ПоместитьВоВременноеХранилище(Таблица1);
Лист1.Наименование = "Лист 1";
Лист2 = Книга.Состав.Добавить();
Лист2.Данные = ПоместитьВоВременноеХранилище(Таблица2);
Лист2.Наименование = "Лист 2";
Книга.Записать(ИмяВременногоФайла, ТипФайлаПакетаОтображаемыхДокументов.XLSX);
Архив = Новый ЧтениеZipФайла(ИмяВременногоФайла, "");
Архив.ИзвлечьВсе(ВременныйКаталог + "\xlsfrom1C", РежимВосстановленияПутейФайловZIP.Восстанавливать);
Архив.Закрыть();
// Меняем в нужных ячейках на формулы
ЗаменитьПараметры(ВременныйКаталог + "\xlsfrom1C\xl\worksheets\sheet1.xml", 1);
ЗаменитьПараметры(ВременныйКаталог + "\xlsfrom1C\xl\worksheets\sheet2.xml", 2);
АрхивРезультат = Новый ЗаписьZipФайла(ИмяВременногоФайла);
АрхивРезультат.Добавить(ВременныйКаталог + "\xlsfrom1C\*.*", РежимСохраненияПутейZIP.СохранятьОтносительныеПути, РежимОбработкиПодкаталоговZIP.ОбрабатыватьРекурсивно);
АрхивРезультат.Записать();
КонецПроцедуры
&НаКлиенте
Процедура СохранитьВExcel(Команда)
СохранитьВExcelНаСервере();
КонецПроцедуры
&НаСервере
Процедура ЗаменитьПараметры(ИмяФайлаXML, НомерЛиста)
Текст = Новый ТекстовыйДокумент;
Текст.Прочитать(ИмяФайлаXML, "windows-1251");
СтрокаXML = Текст.ПолучитьТекст();
ФормулыЛиста = Формулы.НайтиСтроки(Новый Структура("Лист", НомерЛиста));
Для Каждого Формула Из ФормулыЛиста Цикл
ПозицияЯчейки = СтрНайти(СтрокаXML, "<c r=""" + Формула.Ячейка +"""");
Если ПозицияЯчейки = 0 Тогда Продолжить; КонецЕсли;
НачалоФормулы = СтрНайти(СтрокаXML, "<v>",,ПозицияЯчейки);
Если НачалоФормулы = 0 Тогда Продолжить; КонецЕсли;
КонецФормулы = СтрНайти(СтрокаXML, "</v>",,НачалоФормулы);
Если КонецФормулы = 0 Тогда Продолжить; КонецЕсли;
СтрокаXML = Лев(СтрокаXML, НачалоФормулы - 1) + "<f>" + Формула.Формула + "</f>" + Прав(СтрокаXML, СтрДлина(СтрокаXML) - КонецФормулы - 3);
КонецЦикла;
Текст.УстановитьТекст(СтрокаXML);
Текст.Записать(ИмяФайлаXML, "windows-1251");
КонецПроцедуры
Вступайте в нашу телеграмм-группу Инфостарт