Метка: формула

Задача: при формировании файла формата 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(ВрФайл); // А теперь магия! Вставим формулу..