PHPExcel: Formulas removed from file

Question

I searched a lot but couldn't find an answer, so here we go:

I need to create some sheets which will pull data from other two. I've already created these two, but the others are giving me headache...

Here is the code:

foreach ($result_user as $user)
{
    $objPHPExcel->getActiveSheet()
                ->setCellValue('A' . $i, iconv('ISO-8859-1','UTF-8',$user['name']))
                ->setCellValue('B' . $i, iconv('ISO-8859-1','UTF-8',$user['email']))
                ->setCellValue('C' . $i, '=VLOOKUP(' . $user['manager'] . ';source_users!A:B;2;FALSE)')
                ->setCellValue('D' . $i, '=COUNTIF(source_shops!A2:A' . $total_shops . ';A' . $i . ')')
                ->setCellValue('E' . $i, '=source_users!B' . $i)
                ->setCellValue('F' . $i, '=G' . $i . '*100/H' . $i);
    $i++;
}

*$total_shops is the size of the "source_shops" sheet.

The C and D columns simply do not work. When the created xlsx file is opened, I receive this error:

"Excel found unreadable content in 'filename.xlsx'. Do you want to recover the contents of "this workbook? If you trust the source of this workbook, click Yes."

And after click "Yes", this one shows up:

"Excel was able to open the file by repairing or removing the unreadable content. Removed Records: Formula from /xl/worksheets/sheet3.xml part."

When the file finnaly opens, these two columns are filled with "0"s. If the equal sign is removed from the string, the file is generated, opens without errors and the rest of the formula is there! And if You put the equal sign back (in Excel), everything works.

I've found similar threads, but nothing seems to solve my problem...

you're building =VLOOKUP(fred;source_users!....). "fred" by itself is invalid. if it's a string, then it needs to be quoted: =VLOOKUP("fred", ....) — Marc B

Mark Baker Mark Baker · Accepted Answer · 2014-08-07T16:26:34

As per section 4.6.4 of the developer documentation:

Write a formula into a cell Inside the Excel file, formulas are always stored as they would appear in an English version of Microsoft Office Excel, and PHPExcel handles all formulae internally in this format. This means that the following rules hold:

Decimal separator is '.' (period)

Function argument separator is ',' (comma)

Matrix row separator is ';' (semicolon)

English function names must be used

This is regardless of which language version of Microsoft Office Excel may have been used to create the Excel file.

The only exception is if you use locale settings to handle conversion between English/US formulae and localised formulae names with ; separators and locale-language function names

You're using ; as a function argument separator, you're not quoting your strings (e.g. $user['manager'] in your VLOOKUP)

PHPExcel: Formulas removed from file

1 Answers