How To Dynamically Set Value On Cells Using PHPExcel

I am quite new coding using PHP. But I’ll try to write simple snap code about how to set value of cell dynamically. You may need it when you have to show some data from query result or any array to XLS / XLSX format. But on this article, I decided using xls format instead.

First of all, put PHPExcel lib to your CI project. You can follow this example. If you are done, then you ready to do the next step.

<?

#.. some code

function doPrintXLSReport($reportDate){
	//load PHPExcel Library
	$this->load->library('Excel');
 	$objPHPExcel = new PHPExcel();
		 
	// Set document properties
	$objPHPExcel->getProperties()->setCreator("Ariestania")
					 ->setLastModifiedBy("Ariestania")
					 ->setTitle("Sample Report")
					 ->setSubject("Sample Report")
					 ->setDescription("Sample Report")
					 ->setKeywords("Sample Report")
					 ->setCategory("Sample Report");
		 		 
	//set active sheet and the title. 0 means first sheet that I will use
	$objPHPExcel->setActiveSheetIndex(0);
	$objPHPExcel->getActiveSheet()->setTitle('Sample Report');

	//create style of the cell
	$styleThinBlackBorderOutline = array(
		    'borders' => array(
		        'outline' => array(
		            'style' => PHPExcel_Style_Border::BORDER_THIN,
		            'color' => array('argb' => 'FF000000'),
		        ),
		    ),
		);
	
	//get some data from DB 
	$detail = $this->dbhandler->getTrxDatas($reportDate);

	//set A1 as the first cell
	$current_col = 0;
	$current_row = 1;

	if($detail){
		foreach($detail as $det){
			//set value of cell
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($current_col, $current_row, $det['TRX_DATE']);
			//set cell style
			$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($current_col, $current_row)->applyFromArray($styleThinBlackBorderOutline);
			//move to next column
			$current_col++;
			//set value and style of the cell
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($current_col, $current_row, $det['TRX_DETAIL']);
			$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($current_col, $current_row)->applyFromArray($styleThinBlackBorderOutline);

			//move to next row
			$current_row++;
			//reset column back to A
			$current_col = 0;
		}
	}

	//do the download process
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	ob_end_clean() ;
	header('Content-Type: application/vnd.ms-excel');
	header('Content-Disposition: attachment;filename="SampleReport.xls"');
	header('Cache-Control: max-age=0');		 
	$objWriter->save('php://output');
	exit;
}

#.. some code

?>

Hope this simple sample can help. Happy coding πŸ™‚

Advertisements

2 thoughts on “How To Dynamically Set Value On Cells Using PHPExcel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s