PhpExcel SUM（）返回0

Question

So as said in the title, the SUM() function I use in my PHP always displays a "0" in the generated document.

What is weird is that when I do the same formula directly in the doc, by selecting the cell etc.., the result is good :/

Here is the code :

public function generateDocument($name, $description, $params, $criteres, $options) {

    // création du doc au format xsl
    $doc = new \PHPExcel();
    $doc->getProperties()->setCreator(UASG_NAME)
        ->setTitle($name)
        ->setDescription($description);

    $doc->setActiveSheetIndex(0);
    $worksheet = $doc->getActiveSheet();
    // création du nom de l'onglet pour les datas
    $worksheet->setTitle($this->createSheetTitle($name));

    $dataService = new DataService($this->pdo, $this->context, $this->logger);
    $table=$dataService->search($params, $criteres);

    $this->startTimer("GENEREDATAS");
    $headRow=null;$totalRow=null;
    $this->currencyColumns = array();

    $lineNumber = 1;

    // ajout du titre en haut du fichier
    if(isset($options->heading) && $options->heading!=""){
        $worksheet->mergeCells('A1:Z2');
        $worksheet->setCellValue('A1', $options->heading);
        $worksheet->getStyle('A1')->getFont()->setSize(13);
        $worksheet->getStyle('A1')->getFont()->setBold(true);
        $previousLineNumber=4;
    }else{
        $previousLineNumber=0;
    }

    foreach ($table['rows'] as $rowindex => $row) {
        $nbColumn=count($row['cells']);
        foreach ($row['cells'] as $column => $cell) {
            // header
            if ($rowindex == 0) {
                $title = $table['headers'][$column];
                $this->setCellValue($worksheet, $column, $previousLineNumber + 1, $title);
                $headRow = $previousLineNumber + 1;
            }
            // cellule
            if ($row['level'] == 0) {
                if ($column == 0) {
                    $lineNumber++;
                }
                $columnCell=$column;
                $this->setCellValue($worksheet, $columnCell, $lineNumber + $previousLineNumber, $cell);
            }
            $worksheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        }
    }

    if(isset($options->totalPrice) && $options->totalPrice == 1){
        $borderStyle= array(
            'borders' => array(
                'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THICK
                )
            )
        );
        $worksheet->getStyleByColumnAndRow($nbColumn-1, $lineNumber+$previousLineNumber+2)->applyFromArray($borderStyle);
        $worksheet->setCellValueByColumnAndRow($nbColumn-1, $lineNumber+$previousLineNumber+2, '=SUM(E2:E100)');
        // '=SUM(F'.($previousLineNumber+2).':F'.($lineNumber+2).')');
    }

    if (isset($nbColumn)) {
        $lastColumn = $nbColumn - 1;
        // mise en forme des titres
        $range = \PHPExcel_Cell::stringFromColumnIndex(0) . $headRow . ':' . \PHPExcel_Cell::stringFromColumnIndex($lastColumn) . $headRow;
        $worksheet->getStyle($range)->applyFromArray($this->getHeadStyle());

        $range = \PHPExcel_Cell::stringFromColumnIndex(0) . ($previousLineNumber) . ':' . \PHPExcel_Cell::stringFromColumnIndex($nbColumn) . ($lineNumber + $previousLineNumber + 1);
        $worksheet->getStyle($range)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        // mise en forme des colonnes des devises
        if (count($this->currencyColumns)>0) {
            foreach($this->currencyColumns as $column) {
                $range = \PHPExcel_Cell::stringFromColumnIndex($column) . ($previousLineNumber) . ':' . \PHPExcel_Cell::stringFromColumnIndex($column) . ($lineNumber + $previousLineNumber + 1);
                $worksheet->getStyle($range)->getNumberFormat()->setFormatCode('#,##0.00_-[$' . $this->currencySymbol . ' ]');
            }
        }

        unset($nbColumn);
    }



    $this->stopTimer("GENEREDATAS");


    $doc->setActiveSheetIndex(0);
    $this->startTimer("WRITE");
    $writer = \PHPExcel_IOFactory::createWriter($doc, 'Excel2007');
    //$writer->setPreCalculateFormulas(false);
    $writer->save('php://output');
    $this->stopTimer("WRITE");


}

I thought it might be a problem about languages. Because I'm opening the doc in an English Ubuntu so that may be the problem, but I don't know how to pass through that easily..

If someone could help that would be awesome.

Answer 1

You need adjust this $writer->setPreCalculateFormulas(true);