Good day!

Cells E8: E15 and F8: F15 contain numbers. When writing the formula = SUMPRODUCT (E8: E15, F8: F15) to cell C4 using PhpSpreadsheet , an error appears in Microsoft Excel 2010 VALUE! . If you click on the "Change in the formula bar" item in the drop-down menu and just press Enter, the error disappears and the correct value appears in its place. In LibreOffice and OpenOffice, everything works fine.

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load("tmpl.xls"); $worksheet = $spreadsheet->getActiveSheet(); for($i = 8; $i < 16; $i++) { $worksheet->getCell('F'.$i)->setValue(1); $worksheet->getCell('E'.$i)->setValue(2); } $worksheet->getCell("C4")->setValue('=SUMPRODUCT(E8:E15,F8:F15)'); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="file.xls"'); header('Cache-Control: max-age=0'); $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls'); $writer->save('php://output');

Also, everything works correctly if I write to the xlsx format or write to the formula, not ranges, but individual cells.

Can anyone know how to fix this?

UPD.

I tried to add value

 $val = $worksheet->getCell("C4")->getCalculatedValue(); $worksheet->getCell("C4")->setCalculatedValue($val); $worksheet->getCell("D4")->setValue($val);

The correct value is written in the neighboring cell D4, and an error is still output in C4.

  • Perhaps, besides the formula, you need to write the value - Qwertiy ♦
  • @Qwertiy I tried to add a value using setCalculatedValue, did not help. - prot1vogas
  • funny, I never thought that in a non-Russian locale, arguments in Excel formulas are separated by a comma, not a semicolon :) (or rather, a system separator of list elements) - teran

0