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
I have got it to work like this:
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