Calculate all values in the column and add it below as total sum-CodePudding

I want to calculate all values in the column C and place it at the bottom on the same Column C as total sum.

I'm generating excel with data from database - some orders and their amount. Want to calculate the total amount.

My script is

$excel = new PHPExcel();    
$excel->setActiveSheetIndex(0); 
    
$i = 1;
$excel->getActiveSheet()->setCellValue('A'.$i, '#');
$excel->getActiveSheet()->setCellValue('B'.$i, 'Date');
$excel->getActiveSheet()->setCellValue('C'.$i, 'Amount');
$excel->getActiveSheet()->setCellValue('D'.$i, '');

$stmt = $mysqli->prepare("SELECT  id, date, order_sum, SUM(order_sum) as totalSum FROM orders ");
$stmt->execute();
$stmt->bind_result($id, $date, $order_sum, $totalSum );
    
while ($stmt->fetch()) 
{
        $i  ;       
    $excel->getActiveSheet()->setCellValue('A'.$i, $id);
    $excel->getActiveSheet()->setCellValue('B'.$i, $date);
    $excel->getActiveSheet()->setCellValue('C'.$i, $order_sum);
    $excel->getActiveSheet()->setCellValue('D'.$i,  '=SUM(C2:C'.($totalSum -1).')' );
}
$stmt->close();

header('Content-Type: text/html; charset=UTF8');
header('Content-disposition: attachment; filename=orders.xls');
header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Cache-Control: max-age=0');
$file = PHPExcel_IOFactory::createWriter($excel,'Excel2007');   
$file->setPreCalculateFormulas(true);
$file->save('php://output');

But got error

PHP Fatal error: Uncaught PHPExcel_Calculation_Exception: Worksheet!D2 -> undefined variable '.'

CodePudding user response：

I have got it to work like this:

$column = "D";
$column_total = "C";
$row = $i 1;
$excel->getActiveSheet()->setCellValue($column_total.$row, 'Total: ');
$excel->getActiveSheet()->setCellValue($column.$row , '=SUM(C3:C'.$i.')');

CodePudding user response：

I'm not a PHP Excel Pro and don't have it available to me at the time of writing but this script should work I think

<?
$excel = new PHPExcel();    
$excel->setActiveSheetIndex(0); 
    
$i = 1;
$generalSum = 0;

$excel->getActiveSheet()->setCellValue('A'.$i, '#');
$excel->getActiveSheet()->setCellValue('B'.$i, 'Date');
$excel->getActiveSheet()->setCellValue('C'.$i, 'Amount');

$stmt = $mysqli->prepare("SELECT  id, date, order_sum, SUM(order_sum) as totalSum FROM orders ");
$stmt->execute();
$stmt->bind_result($id, $date, $order_sum, $totalSum );
    
while ($stmt->fetch()) 
{
  $i  ;       
  $excel->getActiveSheet()->setCellValue('A'.$i, $id);
  $excel->getActiveSheet()->setCellValue('B'.$i, $date);
  $excel->getActiveSheet()->setCellValue('C'.$i, $order_sum);

  // Incrementation Sum
  $generalSum = bcadd($generalSum, $order_sum);
}

// Incrementation
$i  ;    

// Display Bottom Line with Sum
$excel->getActiveSheet()->setCellValue('A'.$i,0);
$excel->getActiveSheet()->setCellValue('B'.$i,0);
$excel->getActiveSheet()->setCellValue('C'.$i,$generalSum);

$stmt->close();

header('Content-Type: text/html; charset=UTF8');
header('Content-disposition: attachment; filename=orders.xls');
header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Cache-Control: max-age=0');
$file = PHPExcel_IOFactory::createWriter($excel,'Excel2007');   
$file->setPreCalculateFormulas(true);
$file->save('php://output');
?>