Задача: при формировании файла формата xlsx из заполненного макета, необходимо вставить формулы.
Решение: в макете формулы вставить не возможно, из-за ограничений платформы 1С. Однако есть другой пусть — вставить формулы уже после формирования файла xlsx. Файл этого формата, по сути zip архив, в котором содержаться файлы xml с данными листов. Поэтому вполне возможно, проставив в макете в нужные формулы в текстовом формате, заменить их на «настоящие» уже в самом файле
Примечание: Задача решена на основе разработок описанных тут и тут. К себе на сайт утащил наработки, потому что-то с «интернетом творится что-то странное» — сегодня статьи есть, а завтра их уже снесли или разместили в платный раздел.
Всю работу по преобразованию разместил в отдельны модуль:
// Процедура - Извлечь архив, распаковывает ZIP-архив в указанный каталог
Процедура ИзвлечьАрхив(ПутьФайла, ПутьКаталога) Экспорт
ДДФайла = Новый ДвоичныеДанные(ПутьФайла);
Поток = Новый ПотокВПамяти(ПолучитьБуферДвоичныхДанныхИзДвоичныхДанных(ДДФайла));
Попытка
Архив = Новый ЧтениеZipФайла(Поток);
Архив.ИзвлечьВсе(ПутьКаталога, РежимВосстановленияПутейФайловZIP.Восстанавливать);
Архив.Закрыть();
Исключение
Сообщить("Ошибка при извлечении архива: " + ПутьФайла);
КонецПопытки;
Поток.Закрыть();
КонецПроцедуры
// Процедура - Собрать архив, упаковывает файлы из каталога в ZIP-архив
Процедура СобратьАрхив(ПутьКаталога, ПутьФайла) Экспорт
Архив = Новый ЗаписьZipФайла(ПутьФайла);
Архив.Добавить(ПутьКаталога + "\*.*",
РежимСохраненияПутейZIP.СохранятьОтносительныеПути,
РежимОбработкиПодкаталоговZIP.ОбрабатыватьРекурсивно);
Архив.Записать();
КонецПроцедуры
// Функция - Получить DOM-документ из XML-файла, возвращает XML-файл как DOM-модель
Функция ПолучитьDOMДокументИзXMLФайла(ПутьXMLФайла) Экспорт
ЧтениеXML = Новый ЧтениеXML;
ЧтениеXML.ОткрытьФайл(ПутьXMLФайла);
ПостроительDOM = Новый ПостроительDOM;
ДокументDOM = ПостроительDOM.Прочитать(ЧтениеXML);
ЧтениеXML.Закрыть();
Возврат ДокументDOM;
КонецФункции
// Процедура - Сохранить DOM-документ как XML-файл, записывает DOM в указанный файл
Процедура СохранитьDOMДокументКАКXMLФайл(ДокументDOM, ПутьXMLФайла) Экспорт
ЗаписьXML = Новый ЗаписьXML();
ЗаписьXML.ОткрытьФайл(ПутьXMLФайла);
ЗаписьDOM = Новый ЗаписьDOM();
ЗаписьDOM.Записать(ДокументDOM, ЗаписьXML);
ЗаписьXML.Закрыть();
КонецПроцедуры
// Точка входа - непосредственно на входе - имя файла, где необходимо вставить
// формулы
Процедура ЗаполнитьФормулыВФайлеXLSX(ПутьДоФайла) экспорт
ВременныйКаталог = КаталогВременныхФайлов() + "\excel_temp\";
СоздатьКаталог(ВременныйКаталог);
ИзвлечьАрхив(ПутьДоФайла, ВременныйКаталог);
ПутьДоXMLФайлаЛист1 = ВременныйКаталог + "xl\worksheets\sheet1.xml";
// Чтение распакованного xml-файла листа 1 в ДокументDOM
ДокументDOM = ПолучитьDOMДокументИзXMLФайла(ПутьДоXMLФайлаЛист1);
СтроковыеЗначения = ПолучитьDOMДокументИзXMLФайла(ВременныйКаталог + "xl\sharedStrings.xml");
ВсеЯчейки = ДокументDOM.ПолучитьЭлементыПоИмени("c");
ВсеСтроки = ДокументDOM.ПолучитьЭлементыПоИмени("row");
ВсегоСтрок = ВсеСтроки.Количество();
ВсеСтроковыеЗначения = СтроковыеЗначения.ЭлементДокумента.ДочерниеУзлы;
// Обход ячеек и определение формул по произвольному алгоритму
Для Каждого Ячейка Из ВсеЯчейки Цикл
ИмяЯчейки = Ячейка.ПолучитьАтрибут("r");
Если ИмяЯчейки = Неопределено Тогда Продолжить;КонецЕсли;
ИмяСтолбца = Сред(ИмяЯчейки, 1, 1);
ИмяСтроки = Сред(ИмяЯчейки, 2);
НомерСтроки = Число(ИмяСтроки);
ТипЯчейки = Ячейка.ПолучитьАтрибут("t");
Если ТипЯчейки="s" Тогда
Если Ячейка.ПервыйДочерний.ИмяУзла = "v" Тогда // здесь лежит какое-то значение
// надо найти значение в строках SharedStrings
ЗначениеЯчейки = ВсеСтроковыеЗначения[Число(Ячейка.ПервыйДочерний.ТекстовоеСодержимое)];
ЭтоФормула=СтрНачинаетсяС(ЗначениеЯчейки.ТекстовоеСодержимое,"="); // Формула начинается с "="
Если ЭтоФормула Тогда
формула=ЗначениеЯчейки.ТекстовоеСодержимое;
формула=СтрЗаменить(формула,"=","");
Пока Ячейка.ЕстьДочерниеУзлы() Цикл
Ячейка.УдалитьДочерний(Ячейка.ПервыйДочерний);
КонецЦикла;
// Заполнение формулы
ЭлФормула = ДокументDOM.СоздатьЭлемент("f");
ЭлФормула.ТекстовоеСодержимое = Формула;
Ячейка.ДобавитьДочерний(ЭлФормула);
КонецЕсли;
КонецЕсли;
КонецЕсли;
КонецЦикла;
// Запись модифицированного объекта ДокументDOM в xml-файл листа 1
СохранитьDOMДокументКАКXMLФайл(ДокументDOM, ПутьДоXMLФайлаЛист1);
УдалитьФайлы(ПутьДоФайла);
СобратьАрхив(ВременныйКаталог, ПутьДоФайла);
УдалитьФайлы(ВременныйКаталог);
КонецПроцедуры
Как использовать:
ТабДок=Новый ТабличныйДокумент;
Макет=ПолучитьОбщийМакет("ШаблонЛКУКЛС");
...
ОбластьТушка=Макет.ПолучитьОбласть("Тушка");
ОбластьТушка.Параметры.Разница="=G"+(ГСч+1)+"-H"+(ГСч+1); // = означает что тут формула в ячейке
ТабДок.Вывести(ОбластьТушка);
...
ВрФайл=ПолучитьИмяВременногоФайла("xlsx");
ТабДок.Записать(ВрФайл,ТипФайлаТабличногоДокумента.XLSX);
СК_ГР_ФормулыВмакете.ЗаполнитьФормулыВФайлеXLSX(ВрФайл); // А теперь магия! Вставим формулу..
