Large Arrays cause Excel Files to Corrupt

Topics: Developer Forum, Project Management Forum
Dec 21, 2012 at 12:04 PM

Greetings to All!

I am running a school management system.  The following code creates a simple array to hold a report structure which is then dumped to an Excel file. It works flawlessly with small arrays (small data-sets) but when the array gets larger, the numbers in the first row are repeated in all subsequent cells below it...somewhat annoying... It could be an issue of output buffering but no improvement has been noted on 'tweaking' the buffer size.  On the other hand I do not think that 50 columns of about 100 rows each is excessively large...

[php version: 5.3.0; PHPExcel version: 1.7.6]

Here's the offending code snippet (sorry for the size):

 

<!--?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('mysql.connect_timeout', 600);
ini_set('default_socket_timeout', 600);
//ini_set('max_execution_time','600');

//setting mem cache...
//$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;
$cacheSettings = array( 'memcacheServer'  => 'localhost',
                        'memcachePort'    => 11211,
                        'cacheTime'       => 600
                      );
if (!PHPExcel_Settings::setCacheStorageMethod($cacheMethod,$cacheSettings))
   die('CACHEING ERROR');

// 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';

/* set hyperlink for retriving f10s, Actae, etc...
by getting the absolute path of the page, independent from the site position (local machine or server)
and from the server OS - works both on Unix systems and Windows systems.*/
$conflen=strlen('istudium');
$B=substr(__FILE__,0,strrpos(__FILE__,'/'));
$A=substr($_SERVER['DOCUMENT_ROOT'], strrpos($_SERVER['DOCUMENT_ROOT'], $_SERVER['PHP_SELF']));
$C=substr($B,strlen($A));
$posconf=strlen($C)-$conflen-1;
$D=substr($C,1,$posconf);
$host='http://'.$_SERVER['SERVER_NAME'].'/'.$D;

// 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 st.ProgramId IN (1, 3)
			AND 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."
"; } //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; // if ($rowf10['CourseCode'] == '8107') { // echo sprintf("Student count: %d, marks: %s
", $student_count, $marks); // } } //end while $rowf10 ... //die(); //print_r($report[0]['program'][0]['semester']); //die(); // add data to worksheet //count for sheet lable $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']; foreach ($status['program'] as $program => $program_data) { // 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; $course_id = $rowSubjects['CourseId']; // Set column values (subject headings) for row 2 $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column_index, 2, $rowSubjects['CourseCode']); // Set url for retrieving students who have not yet done the course $url_course = $host . 'istudium/ca_list.php?courseID='.$course_id.'&programID='.$program_id. '&civilstatusID='.$civil_status_id; //specifying course, program and civil status... $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column_index, 2)->getHyperlink()->setUrl($url_course); $course_name = getCourse($course_id); // get course name to use in tooltip... $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column_index, 2)->getHyperlink()->setTooltip('Click to view students who have not yet done ['.$course_name.']'); // 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); // Get the url for downloading students F10... $url = $host . 'istudium/admin_view_transcript.php?studentID='.$student_id; $objPHPExcel->getActiveSheet()->getCellByColumnAndRow(0, $row_index)->getHyperlink()->setUrl($url); $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...(increment the no. of subjects sat for by the student) $student_codes = $student_statistics[$row_index]; if (isset($mark) && $mark > 0 && !in_array($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); //obtain url for Acta //$url2 = "http://" . $_SERVER['HTTP_HOST'] . '/istudium/result_summary2.php?courseID='.$course_id.'&examID='.$cur_exam_id; $url2 = $host . 'istudium/result_summary3.php?courseID='.$course_id.'&programID='.$program_id. '&civilstatusID='.$civil_status_id; //specifying course, program and civil status... //$url2 = $host . 'istudium/result_summary3.php?courseID='.$course_id; $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($mark_column, $row_index)->getHyperlink()->setUrl($url2); $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++; } // foreach $course_id_data } // end foreach $course_data } //end foreach $student_data $rowCount ++; } // foreach $semester_data } // foreach program_data //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); } /** * // retains $student_stats (subjects done by n) for custom_reports2.php... * if ($program_id == 3 && $civil_status_id == 1){ * //$student_stats = array(); // new variable to keep just the subjects for civil status n... * //$student_stats = $student_statistics; * $_SESSION['student_stats'] = $student_stats; * //var_dump($_SESSION['student_stats']); * //print_r($_SESSION['student_stats']); * //print_r(count($student_stats)); * //die; * } * //print_r($student_statistics); * //die; */ // Increment the sheet count $shtCount++; } // end foreach $status... } // end foreach $report //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')); // clear buffer ob_end_clean(); //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'); //Clean (erase) the output buffer and turn off output buffering ob_end_clean(); $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; ?>

fidelnamisi@gmail.com