Code Optimization

Topics: Developer Forum
Sep 12, 2011 at 7:28 AM

<?php session_start();
if (@$HTTP_SESSION_VARS["login_status"] <> "login"){
	header("Location: flogin.php");
	die();
}

/** Error reporting */
error_reporting(E_ALL);

require_once 'config.php';
require_once 'functions.php';
require_once 'GradeBook/gradefunctions.php';


/** PHPExcel */
require_once dirname(__FILE__) . '/Classes/PHPExcel.php';
/** PHPExcel_IOFactory */
require_once '/Classes/PHPExcel/IOFactory.php';
// This is to pre-empt the maximum execution time problem
//set_time_limit(0);

ini_set('memory_limit','1024M');
//ini_set('max_execution_time','600');

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set properties
//echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("Paul Mimbi")
							 ->setLastModifiedBy("Paul Mimbi")
							 ->setTitle("stgr Academic Report")
							 ->setSubject("Studies Chart")
							 ->setDescription("Document generated using PHP classes.")
							 ->setKeywords("office 2007 openxml php")
							 ->setCategory("File for Decision Making Purposes");

//colour schemes
$light_green = 'ACE600';
$deep_green = '004900';
$amber = 'FF6600';
$red = 'FF0000';
$purple = 'D9007E';
$purple_blue = '8800CC';
$deep_blue = '2200CC';
$sky_blue1 = '0099CC';
$sky_blue2 = '33FFCC';
$sky_blue3 = '33CCFF';
$grey = 'C3C3C3';
$white= 'FFFFFF';

// read data from recordset...
$qryf10 = "SELECT DISTINCT st.StudentId AS sID, c.CourseId, c.CourseCode, c.Course, t.Score AS tScore, t.MaxScore AS tMax, e.MaxScore AS eMax,
		  m.Marks AS mScore, m.ExamId AS ExamId, c.ProgramId, c.SemesterId, s.SemesterDescription, st.CivilStatusId AS cStatus
			FROM courses c 
			LEFT JOIN exams e ON c.CourseId = e.CourseId
			LEFT JOIN marks m ON e.ExamId = m.ExamId
            LEFT JOIN transcripts t ON c.CourseId = t.CourseId
			INNER JOIN students st ON t.StudentId = st.StudentId OR m.StudentId = st.StudentId
			LEFT JOIN semesters s ON c.SemesterId = s.SemesterId
			WHERE c.ProgramId IN (1, 3) 
			AND st.CivilStatusId IN (1, 2)
			ORDER BY st.CivilStatusId, c.ProgramId, c.SemesterId, st.StudentId, c.CourseId  ASC";
/* This one also works...
$qryf10 = "SELECT st.StudentId AS sID, c.CourseId, c.CourseCode, c.Course, t.Score AS tScore, t.MaxScore AS tMax, e.MaxScore AS eMax,
		   m.Marks AS mScore, m.ExamId AS ExamId, c.ProgramId, c.SemesterId, s.SemesterDescription, st.CivilStatusId AS cStatus
			FROM courses c
            LEFT JOIN semesters s ON c.SemesterId = s.SemesterId
			LEFT JOIN exams e ON c.CourseId = e.CourseId
			LEFT JOIN marks m ON e.ExamId = m.ExamId
			LEFT JOIN transcripts t ON c.CourseId = t.CourseId
			LEFT JOIN students st ON t.StudentId = st.StudentId OR m.StudentId = st.StudentId
			WHERE c.ProgramId IN (1, 3) 
			AND st.CivilStatusId IN (1, 2)
			ORDER BY st.CivilStatusId, c.ProgramId, c.SemesterId, st.StudentId, c.CourseId  ASC";
*/			
$rstf10 = mysql_query($qryf10, $ServerConnect) or die(mysql_error());
$numf10 = mysql_num_rows($rstf10);
//mysql_data_seek ($rstf10 , 0 );

// Query to fetch the individual courses per civil status, per program per semester
$query_subjects = 'SELECT DISTINCT c.CourseId, c.Course, c.CourseCode, c.ProgramId, c.SemesterId,
			s.SemesterDescription, st.CivilStatusId AS cStatus
			FROM courses c 
			LEFT JOIN transcripts t ON c.CourseId = t.CourseId
			LEFT JOIN semesters s ON c.SemesterId = s.SemesterId
			LEFT JOIN exams e ON c.CourseId = e.CourseId
			LEFT JOIN marks m ON e.ExamId = m.ExamId
			INNER JOIN students st ON t.StudentId = st.StudentId OR m.StudentId = st.StudentId
			WHERE c.ProgramId = %d
			AND st.CivilStatusId= %d
			AND c.SemesterId = %d 
			ORDER BY st.CivilStatusId, c.ProgramId, c.SemesterId, c.CourseId  ASC';

/*OLD QUERY...DOES NOT WORK WHEN MARKS TABLE IS EMPTY...
	$query_subjects = 'SELECT DISTINCT c.CourseId, c.Course, c.CourseCode, c.ProgramId, c.SemesterId,
			s.SemesterDescription, st.CivilStatusId AS cStatus
			FROM courses c 
			LEFT JOIN transcripts t ON c.CourseId = t.CourseId
			LEFT JOIN exams e ON c.CourseId = e.CourseId
			LEFT JOIN marks m ON e.ExamId = m.ExamId
			LEFT JOIN students st ON m.StudentId = st.StudentId
			LEFT JOIN semesters s ON c.SemesterId = s.SemesterId
			WHERE c.ProgramId = %d
			AND st.CivilStatusId= %d
			AND c.SemesterId = %d 
			ORDER BY st.CivilStatusId, c.ProgramId, c.SemesterId, c.CourseId  ASC';
*/			/*
			*NB: I have removed AND st.StudentId IS NOT NULL from both
			*the queries; checked that it was not really necessary...
			*/

// function for setting title colors...
function getTitleColorArray($semester_id){
	$color = '';
	switch($semester_id) {
		case 1:
			$color = '99FF33';
			break;
		case 2:
			$color = 'FFFF33';
			break;
		case 3:
			$color = 'FF9933';
			break;
		case 4:
			$color = 'FF3333';
			break;
		default:
		   $color = 'FFFFFF';
	}
	
	return array(
		'font'    => array(
			'bold'      => true
		),
		'alignment' => array(
			'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
		),
		'borders' => array(
			'top'     => array(
				'style' => PHPExcel_Style_Border::BORDER_THIN
			)
		),
		'fill' => array(
			'type'       => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
			'rotation'   => 90,
			'startcolor' => array(
				'rgb' => $color
			),
			'endcolor'   => array(
				'rgb' => 'FFFFFF'
			)
		)
	);
}
	
// Array to hold the report structure to be dumped to Excel
$report  = array();

// Initialize local temporary variable
$civil_status = -1; 	// Keeps track of the current civil status
$status_count = -1; 	//Counter for the civil status
$program_count = -1; 	// Counter for the program
$prog = -1;             // Keeps track of program id
$semester_count = -1; 	// Counter for the semesters
$student_count = -1;	// Counter to keep track of the number of students per status,program and semester
$student_id= -1; 		// Keeps track of the current student during the fetch
$course_id = -1; 		//Keeps track of the current course/subject during the fetch
$semester = -1; 		// Keeps track of the current semester during the fetch

while ($rowf10 = mysql_fetch_assoc($rstf10)) {				
	// Get the civil status
	if ($civil_status != $rowf10['cStatus']) {
		$status_count++;
		$civil_status = $rowf10['cStatus'];
		
		$status_name = "";
		switch ($civil_status) {
			case 1:
				$status_name = "n";
				break;
			case 2:
				$status_name = "agd";
				break;
			case 3:
				$status_name = "s";
				break;
			default:
				$status_name = "DiP";
		}
		$report[$status_count]['status_id'] = $civil_status;
		$report[$status_count]['civil_status'] = $status_name;
	}
	// Get the program	
	if ($prog != $rowf10['ProgramId']) {
		$program_count++;
		
		$prog = $rowf10['ProgramId'];						
		$program   = getProgramName($prog, 1);
		
		$report[$status_count]['program'][$program_count]['id'] = $prog;
		$report[$status_count]['program'][$program_count]['name'] = $program;
	}
	
	// Get the semester
	if ($semester != $rowf10['SemesterId']) {
		$semester_count++;
		$semester= $rowf10['SemesterId'];
		
		$report[$status_count]['program'][$program_count]['semester'][$semester_count]['semester_id'] = $semester;
	}
	
	// Get the student(s)
	if ($student_id != $rowf10['sID']) {
		$student_count++;
		$student_id = $rowf10['sID'];
		
		$report[$status_count]['program'][$program_count]['semester'][$semester_count]['student'][$student_count]['student_id'] = $student_id;
		$report[$status_count]['program'][$program_count]['semester'][$semester_count]['student'][$student_count]['codes'] = array();
		$report[$status_count]['program'][$program_count]['semester'][$semester_count]['student'][$student_count]['examid'] = array();
		
		//if ($student_id == 26) echo $program."-".$semester."<br/>";
	}
	
	//Get the subject/course and mark
	if ($course_id != $rowf10['CourseId']) {
		$course_id = $rowf10['CourseId'];
	}
		
	if($rowf10['mScore'] != NULL){
		$marks = $rowf10['mScore'];
	} else {
		$marks = $rowf10['tScore'];
	}						
	
	//$marks = $rowf10['mScore'];
	
	$temp_array = array($course_id => array($rowf10['CourseCode'] => $marks));
	$exam_id_array = array($course_id => $rowf10['ExamId']);
	
	$report[$status_count]['program'][$program_count]['semester'][$semester_count]['student'][$student_count]['codes'][] = $temp_array;
	$report[$status_count]['program'][$program_count]['semester'][$semester_count]['student'][$student_count]['examid'][] = $exam_id_array;
	
 } //end while $rowf10 ...
 
//die();
//print_r($report[0]['program'][0]['semester']);
//die();

// add data to worksheet
$shtCount = 0;
foreach ($report as $status) {
	// Get the civil status
	// Create the work sheet for each civil status at this stage
	$civil_status_id = $status['status_id'];
	$cstatus =  $status['civil_status'];
	
	//count for sheet lable
	//$shtCount = 0;
	
	foreach ($status['program'] as $program => $program_data) {
		// This is to pre-empt the maximum execution time problem
		//set_time_limit(240);
					
		// initialize/reset the statistics array (for subjects done..)
		$student_statistics = array();

        // Get the programs under each civil status
		$program_id = $program_data['id'];
				
		$progname = substr($program_data['name'],22);
		$lblSheet = $progname. ' ('. $cstatus.')';
		
		$objPHPExcel->createSheet();
		$objPHPExcel->setActiveSheetIndex($shtCount);
		$objPHPExcel->getActiveSheet()->setTitle($lblSheet);
		
		// Start cell for the semester title
		//$start_cell_char = "B";
		$start_col_index = 0;

		// Start column for the displaying the subject codes; subject codes are displayed below the title for the semester
        // When the loop moves to the next program, a new worksheet is created and the counter is reset to 1
		$column_index = 1;
		
		$rowCount = 3;
		
		// Lookup dictionary for the students
		$student_lookup_dictionary = array();
		
		foreach ($program_data['semester'] as $semester=>$semester_data) {
			// This is to pre-empt the maximum execution time problem
			set_time_limit(240);
            /**
             * Get the semesters under each program
             * ...but first count how many subjects returned per semester and store it in 
             * $columncount...
             */
            
            // Get the semester id
			$semester_id = $semester_data['semester_id'];
            
            // Generate the subject fetch query
            $temp_subjects_query = sprintf($query_subjects, $program_id, $civil_status_id, $semester_id);
  
            // Execute the query and store the results
			$rstSubjects = mysql_query($temp_subjects_query) or die(mysql_error());
                            
			// Get the number of records returned - used to determine the no. of cells to merge
			$column_count = mysql_num_rows($rstSubjects) or die(mysql_error());

			/** Next, determine the start and end cell for the semester title
			  * This shall have to be computed by the script e.g. if the first cell for semester 1 is B1
			  * and semester 1 has 16 subjects, then the last cell shall be B17. The title for semester 2 
			  * shall however start at B18. There is therefore a need to have a variable that keeps track of 
			  * the last cell so as to ensure the titles appear in the correct cell ranges.
			  */
			$title = ''; // variable to hold title per semester
			$style = 1; // variable to hold color and style scheme
			switch ($semester_id) {
				case 1:
					// set first year title
					$title = 'Annus I';
					$style = 1;
					break;
				case 2:
					// set second year title
					$title = 'Annus II';
					$style = 2;
					$column_count = $column_count - 1; // to compensate for shifting of $end_col_index...
					break;
				case 3:
					// set 3rd year title
					$title = 'Annus III';
					$style = 3;
					$column_count = $column_count - 1;
					break;
				case 4:
					// set 4th year title
					$title = 'Annus IV';
					$style = 4;
					$column_count = $column_count - 1;
					break;
			}
			// Determine the end cell for the semester title
			//$end_cell_char = chr(ord($start_cell_char) + $column_count );
			$end_col_index = $start_col_index + $column_count;

			$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($start_col_index, 1, $end_col_index, 1);
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($start_col_index, 1, $title);
			$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($start_col_index, 1, $end_col_index, 1)->applyFromArray(getTitleColorArray($style));
			
			// Set the start cell to the end cell
			//$start_cell_char = chr(ord($end_cell_char) + 1);
			$start_col_index = $end_col_index + 1;

			/** 
			 * Create the columns for each subject in the semester at this stage
			 * NOTE: This will however require the course codes for the semester to have been independently 
			 *fetched in advance
			 * 
			 */		
			
			// Lookup buffer for the positioning of the marks
			$lookup_dictionary = array();
			
			while ($rowSubjects = mysql_fetch_assoc($rstSubjects)) {
				// Store the column position of each subject
				$lookup_dictionary[$rowSubjects['CourseCode']] = $column_index;
				
				// Set column values (subject headings) for row 2
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index, 2, $rowSubjects['CourseCode']);
				
				// Increment the column count
				$column_index++;
			}
			
			// Explicitly free the result handle though the MySQL engine implictly does this after a record fetch
            @mysql_free_result($rstSubjects) or die(mysql_error());
			
			foreach ($semester_data['student'] as $student=>$student_data) {
				/**
                  * Get the students under each semester
                  * It is in this iteration that the row count for the report is incremented
                  */
                $name = getStudentName($student_data['student_id']);
				$student_id = $student_data['student_id'];
				
				// Check if the student id exists in the dictionary
				if ( ! array_key_exists($student_id, $student_lookup_dictionary)){
					$student_lookup_dictionary[$student_id] = $rowCount;
				}
								
				$objPHPExcel->setActiveSheetIndex($shtCount);
				
				// Get the row index for the student's data
				// This is necessary so that the data for students who appear across semesters within the same program
				// appears in the same row in the Excel report
				$row_index = $student_lookup_dictionary[$student_id];

				// Enter name of student in first column
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row_index, $name);
				
				// set hyperlink for retriving f10s...
				$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(0, 
					$row_index)->getHyperlink()->setUrl('http://localhost/istudium/admin_view_transcript.php?studentID='.$student_id);

				$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(0, 
					$row_index)->getHyperlink()->setTooltip('Click to download f10');
					
				// Get the row_index (or student_id) in order to determine the row in which the student appears in the excel report;
				// when $student_id is used in the array, the values at the bottom of the xls sheet are truncated?!
				if ( ! array_key_exists($row_index, $student_statistics)) {
					$student_statistics[$row_index] = array();
				}

				foreach ($student_data['codes'] as $course_code=>$course_data) {
					// This is to pre-empt the maximum execution time problem
					//set_time_limit(240);
                    // Get the subjects/codes and marks under each student
					foreach ($course_data as $course_id=>$course_id_data) {
                        $item_counter = 0;
				
                        foreach ($course_id_data as $course_code=>$mark) {
							
							if (array_key_exists($course_code, $lookup_dictionary)) {
								$mark_column = $lookup_dictionary[$course_code];
								
								// Get the exam ID for the current subject
								If (isset($student_data['examid'][$item_counter][($item_counter+1)])){
									$cur_exam_id = $student_data['examid'][$item_counter][($item_counter+1)];
								}
								// Enter mark; the column co-ordinate corresponds to the subjects above...
								$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($mark_column,
									$row_index, $mark);
									
								// Determine the no. of subjects sat for by the student using the $students_statistics array...
								$student_codes = $student_statistics[$row_index];
								
								if (isset($mark) && $mark > 0 && !in_array($course_code, $student_codes)) {
								//if (isset($mark) && $mark > 0 && !array_search($course_code, $student_codes)) {
									array_push($student_codes, $course_code);
									$student_statistics[$row_index] = $student_codes;
								}

								// shade the cells...								
								$cellColor = '';
								
								if ($mark >= 70) {
									$cellColor = $light_green;									
								} elseif ($mark >= 50 && $mark <=69) {
									$cellColor = $amber;
								} elseif ($mark >= 1 && $mark <=49) {
									$cellColor = $red;
								} else {
									// just leave it blank
									$cellColor = $white;													
								}							
								
								
								$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($mark_column, 
									$row_index)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
									
								$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($mark_column, 
									$row_index)->getFill()->getStartColor()->setRGB($cellColor);
									
								$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($mark_column, 
									$row_index)->getFont()->getColor()->setRGB($cellColor);
									
								// set hyperlink to folders that contain actae...
								//$today = date('Y-m-d');
								//$date = explode("-", $today);
								//obtain course name to use in tooltip...
								$course_name = getCourse($course_id);
								
								$objPHPExcel->getActiveSheet()->getCellByColumnAndRow($mark_column, 
									$row_index)->getHyperlink()->setUrl('http://localhost/istudium/result_summary2.php?courseID='.$course_id.'&examID='.$cur_exam_id);
								$objPHPExcel->getActiveSheet()->getCellByColumnAndRow($mark_column, 
									$row_index)->getHyperlink()->setTooltip('Click to view the ['.$course_name.'] Acta');

								// Setting borders...
								$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($mark_column, 
									$row_index)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
								
								// Set border colors
								$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($mark_column, 
									$row_index)->getBorders()->getAllBorders()->getColor()->setARGB('FF993300');
								
								// Set column width
								//$objPHPExcel->getActiveSheet()->getColumnDimension($mark_column)->setWidth(12);
								$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
							}
							$item_counter++;
						}
					}
					
				}
				
				$rowCount ++;
			}
			
		}

         //print_r($student_statistics);
         //die;
		
		//Calculate the total number of subjects/courses per program...
		$query_subj = "SELECT * FROM courses WHERE ProgramId = $program_id ORDER BY Course ASC";
		$result_subj = mysql_query($query_subj);
		$num_subjects = mysql_num_rows($result_subj);
		
		// Set the headings for the stats...
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index, 2, 'Done');
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($column_index, 2)
			->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index + 1, 2, 'Pending');
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($column_index + 1, 2)
			->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		
		foreach($student_statistics as $key => $value) {
			// Output subjects done...in last column...
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index, $key, count($value));
            
            // Caclculate the no. of subjects pending
            $subjects_pending = $num_subjects - count($value);
            
			// Output subjects pending...in next column...
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index + 1, $key, $subjects_pending);
		}
		
		// Increment the sheet count
		$shtCount++;
	}
}
//die;
// fetch recordset for the key sheet
$query_key = "SELECT c.CourseId, c.CourseCode, c.Course, s.SpecializationId, s.SName 
			FROM courses c 
			LEFT JOIN specializations s ON c.SpecializationId = s.SpecializationId
			WHERE c.ProgramId IN (1, 3) 
			ORDER BY c.CourseId ASC ";

$rst_key = mysql_query($query_key) or die(mysql_error());
// set the key sheet
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($shtCount);
$objPHPExcel->getActiveSheet()->setTitle("Report Key");
// set row count for the values...
$row_count = 3;
while ($row_key = mysql_fetch_assoc($rst_key)) {
	// Set code and subject headings...
	$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Code');
	$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Subject');
	$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Credits');
	$objPHPExcel->getActiveSheet()->getStyle('E8')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	$objPHPExcel->getActiveSheet()->getStyle('E9')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	$objPHPExcel->getActiveSheet()->getStyle('E10')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	$objPHPExcel->getActiveSheet()->getStyle('E8')->getFill()->getStartColor()->setRGB($light_green);
	$objPHPExcel->getActiveSheet()->getStyle('E9')->getFill()->getStartColor()->setRGB($amber);
	$objPHPExcel->getActiveSheet()->getStyle('E10')->getFill()->getStartColor()->setRGB($red);
	// setting borders...
	$objPHPExcel->getActiveSheet()->getStyle('E8')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	$objPHPExcel->getActiveSheet()->getStyle('E9')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	$objPHPExcel->getActiveSheet()->getStyle('E10')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	// Set border colors
	$objPHPExcel->getActiveSheet()->getStyle('E8')->getBorders()->getAllBorders()->getColor()->setARGB('FF993300');
	$objPHPExcel->getActiveSheet()->getStyle('E8')->getBorders()->getAllBorders()->getColor()->setARGB('FF993300');
	$objPHPExcel->getActiveSheet()->getStyle('E8')->getBorders()->getAllBorders()->getColor()->setARGB('FF993300');
	// set the values 
	$objPHPExcel->getActiveSheet()->setCellValue('F8', 'Exam done successfully');
	$objPHPExcel->getActiveSheet()->setCellValue('F9', 'Exam pending');
	$objPHPExcel->getActiveSheet()->setCellValue('F10', 'Resit');

	// populate the code, subject and credit values from recordset..
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row_count, $row_key['CourseCode']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row_count, $row_key['Course']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row_count, $row_key['SName']);
	// set column 'B' to autosize...
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
	//$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(14);
	
	// Increment the row count
	$row_count++;
}

//Set up the document for display
$sheet = 0;
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
	$objPHPExcel->setActiveSheetIndex($sheet);
	$objPHPExcel->getActiveSheet()->getPageSetup()->setHorizontalCentered(true);
	$objPHPExcel->getActiveSheet()->getPageSetup()->setVerticalCentered(false);
	//$objPHPExcel->getActiveSheet()->setTitle('Change Report');
	$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Calibri');
	$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
	$objPHPExcel->getActiveSheet()->freezePaneByColumnAndRow(1, 3);
	$sheet++;
}


//Flush the MySQL resource handle used for the data fetch
mysql_free_result($rstf10) or die(mysql_error());
//mysql_free_result($rstSubjects) or die(mysql_error());... already done above.
mysql_free_result($rst_key) or die(mysql_error());

//print_r($report[0]['program'][0]['semester'][0]);
//die;
//print_r($report[0]['program'][0]['semester'][0]['student'][53]);
//die;
//print_r($report);
//die;

				
//Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
//var_dump(iconv('UTF-8', 'UTF-16LE', 'x'));


//Redirect output to a client�s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="StatusReport-'.date('dMY').'.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
//$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
//exit;
				
/*
// or save to disc..
$writer = new PHPExcel_Writer_Excel2007($objPHPExcel);
$writer->save('Reports/Report-'. date('dMY').'.xlsx');
            
echo date('H:i:s') . " Download Complete\n";
*/
// clear workbook from memory...
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);
//Delete the arrays
unset($report);
unset($lookup_dictionary);
unset($student_lookup_dictionary);
unset ($student_statistics);
exit;
?>
I am a new user of this great library.  The following is the code I have developed to output student data from a recordset to an xlsx file.  I have a strange feeling that it is somewhat cumbersome for it takes sometime to download.  Could anyone help to optimize it?

 


Sep 25, 2011 at 6:45 PM

I may have placed the query at the end of the code snippet...reason for no reply so far.  It takes a bit of time to download...could anyone help in optimizing it?