skip to Main Content

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 ‘.’

2

Answers


  1. Chosen as BEST ANSWER

    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.')');
    

  2. 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');
    ?>
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search