Using PHPExcel in Yii

phpexcel-yii.php

<?php

// PHPExcel is extracted into protected/extensions/ folder
// protected/extensions/PHPExcel.php, protected/extensions/PHPExcel/

class MyExcel
{
	private static $objPhpExcel;

	public static function genFile( $file_name = 'data.xlsx' )
	{
		ini_set( 'memory_limit', '256M' );

		// include PHPExcel classes:
		$phpExcelPath = Yii::getPathOfAlias( 'ext' );
		spl_autoload_unregister( array( 'YiiBase', 'autoload' ) );
		$p = $phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php';
		include( $p );
		spl_autoload_register( array( 'YiiBase', 'autoload' ) );

		// create new workbook, set sheet title:
		self::$objPhpExcel = new PHPExcel();
		$worksheet         = self::$objPhpExcel->getSheet()->setTitle( "My WorkSheet" );

		// write column header:
		$column_titles = [ 'Name', 'Sex', 'Age', 'Position', ];
		foreach ( $column_titles as $col_idx => $col_title )
			$worksheet->setCellValueByColumnAndRow( $col_idx, 1, $col_title );

		// get data from db:
		$cmd = Yii::app()->db->createCommand( "SELECT name, sex, age, position FROM people" );

		// get reader object:
		$dataReader = $cmd->query();
		$idx        = 2; // start with 2nd row, because in 1st row there is column titles!

		// read records line by line:
		while ( ( $rec = $dataReader->read() ) !== false )
		{
			$worksheet->setCellValueByColumnAndRow( 0, $idx, $rec[ 'name' ] )
				->setCellValueByColumnAndRow( 1, $idx, $rec[ 'sex' ] )
				->setCellValueByColumnAndRow( 2, $idx, $rec[ 'age' ] )
				->setCellValueByColumnAndRow( 3, $idx, $rec[ 'position' ] );
			++$idx;
		}

		// save worksheet:
		PHPExcel_IOFactory::createWriter( self::$objPhpExcel, 'Excel2007' )->save( $file_name );

		// free resources:
		self::$objPhpExcel->disconnectWorksheets();
		self::$objPhpExcel = null;
	}
}

// example:
MyExcel::genFile( 'data/myfile.xlsx' );