PhpSpreadsheet - Amount in words formula not working after saving .xlsx template?

Question

PHP

Hello!
I use this PhpSpreadsheet library to create documents in .exel
in the original template, I have inserted the formula "Sum in words without macros" taken from this resource https://www.e-du.ru/2016/05/excel-summa-propisyu.html
It works fine, but after formatting the document through the PhpSpreadsheet library, the formula doesn't work and throws an error #NAME? and I can’t do anything, I tried to leave the cell free in the template and insert formulas through "$worksheet-> setCellValue" but all my attempts were unsuccessful.
Please help please ... I will be very grateful and will not remain in debt!
Attached is the source code I am using.

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\IWriter;


$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('templates/template.xlsx');

$worksheet = $spreadsheet->getActiveSheet();
    
$worksheet->setCellValue('A21', '=SUBSTITUTE(TEXT(TRUNC(B6,0),"# ##0_ ") & "(" & SUBSTITUTE(PROPER(INDEX(n_4,MID(TEXT(B6,n0),1,1)+1)&INDEX(n0x,MID(TEXT(B6,n0),2,1)+1,MID(TEXT(B6,n0),3,1)+1)&IF(-MID(TEXT(B6,n0),1,3),"миллиард"&VLOOKUP(MID(TEXT(B6,n0),3,1)*AND(MID(TEXT(B6,n0),2,1)-1),мил,2),"")&INDEX(n_4,MID(TEXT(B6,n0),4,1)+1)&INDEX(n0x,MID(TEXT(B6,n0),5,1)+1,MID(TEXT(B6,n0),6,1)+1)&IF(-MID(TEXT(B6,n0),4,3),"миллион"&VLOOKUP(MID(TEXT(B6,n0),6,1)*AND(MID(TEXT(B6,n0),5,1)-1),мил,2),"")&INDEX(n_4,MID(TEXT(B6,n0),7,1)+1)&INDEX(n1x,MID(TEXT(B6,n0),8,1)+1,MID(TEXT(B6,n0),9,1)+1)&IF(-MID(TEXT(B6,n0),7,3),VLOOKUP(MID(TEXT(B6,n0),9,1)*AND(MID(TEXT(B6,n0),8,1)-1),тыс,2),"")&INDEX(n_4,MID(TEXT(B6,n0),10,1)+1)&INDEX(n0x,MID(TEXT(B6,n0),11,1)+1,MID(TEXT(B6,n0),12,1)+1)),"z"," ")&IF(TRUNC(TEXT(B6,n0)),"","Ноль ")&") рубл"&VLOOKUP(MOD(MAX(MOD(MID(TEXT(B6,n0),11,2)-11,100),9),10),{0,"ь ";1,"я ";4,"ей "},2)&RIGHT(TEXT(B6,n0),2)&" копе"&VLOOKUP(MOD(MAX(MOD(RIGHT(TEXT(B6,n0),2)-11,100),9),10),{0,"йка";1,"йки";4,"ек"},2)," )",")")');

$retVal = $worksheet->getCell('A21')->getCalculatedValue();



$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setPreCalculateFormulas(false);
$writer->save('test.xlsx');

Answer 1

pass a function object, see https://github.com/PHPOffice/PhpSpreadsheet/blob/m...