asked on

Adding Summary / Total row in PHPExcel

I need to add a row that has the totals for 'count' and 'revenue' at the bottom. In my case count is 1 every time.

$objPHPExcel = new PHPExcel();

$result = domain_funcs::HHH_rev_details($from, $to, $domain, &$db);
$data_title = "HHH_details_$domain-$from-$to.xls";

// Set properties
$objPHPExcel->getProperties()->setCreator("$domain HHH Report")
							 ->setTitle("HHH Report");
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle("HHH Report");
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(50); //Place
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20); //Date
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15); //Trans Count (1)
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15); //Revenue	 

$objPHPExcel->getActiveSheet()
				->setCellValue("A1", "Place")
				->setCellValue("B1", "Date")
				->setCellValue("C1", "Transaction Count")
				->setCellValue("D1", "Revenue");
$objPHPExcel->getActiveSheet()->getStyle("A1:D1")->getFont()->setBold(TRUE)->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle("A1:D1")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('E5E8C7');
$excel_row = 2;
	

$count = mysql_num_rows($result);
if(mysql_num_rows($result) > 0)
{		
	while($row = $db->get_next_row($result))
	{
	    $school = $row['place'];
	    $thedate = $row['ldate'];
		$domain_name = $row['domain_name'];
		$Count = 1;
		$revenue = $row['revenue'];
		
		
		$objPHPExcel->getActiveSheet()
					->setCellValue("A$excel_row", $place)
					->setCellValue("B$excel_row", $thedate)
					->setCellValue("C$excel_row", $count)
					->setCellValue("D$excel_row", $revenue);
			
		$excel_row++;
	}
}

$header_url = $data_title;
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Redirect output to a client's web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment; filename=\"".$header_url."\";" ); 
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

Open in new window

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ima Jedi

ASKER

YES Rgonzo1971! that did it. The only thing I had to change, was to remove the first semi-colon.

	$objPHPExcel->getActiveSheet()
	->setCellValue('C'.$excel_row,  '=COUNT(C2:C'.($excel_row -1).')' )
	->setCellValue('D'.$excel_row,  '=SUM(D2:D'.($excel_row -1).')' );

Open in new window

For people looking at this solution,
I used this code on the outside of the while loop so it prints it on the last row.