I can't create excel by some function Pls help me

[noombkk]

$sheet->setCellValue('B5', '=SUMIF($X10:$X37,"=0",D10:D37)');
and
$sheet->setCellValue('B9', '=A5-(A10+A9)');
When I use this, the file cannot be created successfully.
Thank you very much for your help.

[oleibman]

What happens when you use it? Do you have a file you can upload?

[noombkk]

Auto_excel.zip
you can use public function t_excel() and public function t2_excel()
Thank you very much

[oleibman]

No problem at all when I execute your code. Is it possible that your system issues some output before the constructor, or in the constructor, or after your routine has finished? Here's the code I am using, which eliminates all those parts:

require __DIR__ . '/PhpSpreadsheet' . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
//use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Color;
//-->>use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat; // Add this line

class issue4251 
{
    public function t_excel() {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $spreadsheet->getProperties()
            ->setCreator("Test")
            ->setTitle("Consolidate Financial Report");
  
        $sheet->setCellValue('X10', '0'); 
        $sheet->setCellValue('X11', '9');
        $sheet->setCellValue('X12', '0');
        $sheet->setCellValue('X13', '0');
        $sheet->setCellValue('X14', '9');
        $sheet->setCellValue('X15', '9');
        $sheet->setCellValue('X16', '0');
        $sheet->setCellValue('X17', '0');

        $sheet->setCellValue('D10', '100'); 
        $sheet->setCellValue('D11', '150');
        $sheet->setCellValue('D12', '50');
        $sheet->setCellValue('D13', '200');
        $sheet->setCellValue('D14', '180');
        $sheet->setCellValue('D15', '90');
        $sheet->setCellValue('D16', '120');
        $sheet->setCellValue('D17', '220');

        $sheet->setCellValue('B10', '=SUMIF($X10:$X17,"=0",D10:D17)');    //Error
        //$sheet->setCellValue('B10', '=SUM(D10:D17)');    //Can use

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="cons_test.xlsx"');
        header('Cache-Control: max-age=0');    

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }
        
    public function t2_excel() {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $spreadsheet->getProperties()
            ->setCreator("Test")
            ->setTitle("Consolidate Financial Report");
  
        $sheet->setCellValue('A5', '1000'); 
        $sheet->setCellValue('A10', '150');
        $sheet->setCellValue('A9', '300');

        $sheet->setCellValue('B9', '=A5-(A10+A9)');     //Error
        //    $sheet->setCellValue('B9', '=A5-A10+A9');     //Without ( ) Can use

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="cons_test.xlsx"');
        header('Cache-Control: max-age=0');    

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }
}

$issue4251 = new issue4251();
//$issue4251->t_excel();
$issue4251->t2_excel();

When I run t_excel, cell B10 has the calculated value 690. When I run t2_excel, cell B9 has the value 450.

[noombkk]

I have off on error in php
Everything is working normally.

Thank you very much