phpxlsphpspreadsheet

PHPSpreadsheet export xls file with complex formulas


I am using a laravel package "Maatwebsite" to export my data into a xls format. As my data are complex and requires some complex formula as well. I need to CONCATENATE two cells using LOOKUP formula and the formula looks like this =IF(B10<>"",CONCATENATE(LOOKUP(2,1/($A$2:A10<>""),$A$2:A10),"_",B10),"") But the problem is when ever i try to export into the xls the xls file only contains static value but not with formula.

What is want is when ever there is a change in A10 and B10 it should reflect it in C10 as well. But It doesn't .

My Code looks like this

 $formula = '=IF(B10<>"",CONCATENATE(LOOKUP(2,1/($A$2:A10<>""),$A$2:A10),"_",B10),"")';
$sheet->setCellValueExplicit("C10", $formula, DataType::TYPE_FORMULA);

$sheet is from "AfterSheet" events.

but when i try using simple formula like

$formula = '=SUM(A10,B10)';
$sheet->setCellValueExplicit("C10", $formula, DataType::TYPE_FORMULA);

It works perfectly as expected.


Solution

  • For this to work properly, you need to be generating an XLSX file, not a legacy XLS file.