Mmeyers Mmeyers - 12 days ago 7
PHP Question

LaravelExcel export generate too many brackets

This is my first post in this forum, be patient with me if u can ;)

First of all the context: i'm developping a web page that import an excel file (.xlsx) with two sheets, and edit the first one with some data from database. I'm using LaravelExcel and I encounter an issue with formula.

I'm trying to write an existing formula in a cell. This is my code so far:

Excel::load('files/template_v02.xlsx', function($reader) use ($clients) {
$sheet = $reader->setActiveSheetIndex(0);
$sheet->setCellValue('A7', "=IFNA(VLOOKUP(\$C7;$'Billable items'.\$C$58:\$D$61;2;0); 0)+IF(D7=\"Yes\"; $'Billable items'.\$D$63;0)");
})->store('xlsx', public_path('files/test'));


The problem is the second '$', just before the first call on 'Billable items' that is the name of the second sheet. I don't understand why this char generate this result when I open the file modified :

=IFNA(VLOOKUP($C7;$'Billable items'.$C$58:$D$61;2;0); 0)+IF(D7="Yes"; $'Billable items'.$D$63;0)))


Two brackets appear at the end of the formula (one for each call on the second sheet with '$') in the cell, and I can't figure out why (I'm encoding in UTF-8).

Someone have an idea perhaps ?

Thanks in advance !

Answer

Forget to answer when I figured out what was the problem, if some people get the same problem:

First, the dollar symbol before sheetname

$\'Billable items\'

Should not be there, LaravelExcel doesn't like it, don't know why.

And finally, the real problem was the IFNA function of Excel, must be replace by IFERROR, in this case, IFNA wasn't seems to work with this Library.

Comments