Click here to Skip to main content
15,902,635 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
**Hi, I'm quit new for this programming. I would like to know how to create new sheet for every search result.
Below is my current code.
However, its only showing the result for the last array. Please help me on this.**

What I have tried:

PHP
$arr = array();
$main="select Distinct location from ige_international where city = 'UAE' and mental_grade != ''";
$resultmain = mysqli_query($MySQLi_CON, $main) or die(mysqli_error($MySQLi_CON));
while($row1 = mysqli_fetch_assoc($resultmain)){
array_push($arr, $row1['location']);


}


$length = count($arr);
for ($i = 0; $i < $length; $i++) {

$sql="SELECT location, CONCAT('IGEMA-0',MONTH(exam_date),YEAR(exam_date),'-',id) AS stud_id, 
UPPER(std_name)as std_name, mental_grade , UPPER(result) as result , 
DATE_FORMAT(exam_date, '%d/%m/%Y') as date from ige_international where city = 'UAE' and 
result not in ( 'Fail' ) and mental_grade != '' and location = '$arr[$i]' ";
$result = mysqli_query($MySQLi_CON, $sql) or die(mysqli_error($MySQLi_CON));

if (mysqli_num_rows($result) > 0) {


$objPHPExcel = new PHPExcel();
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Final Result.xlsx"');
header('Cache-Control: max-age=0');
    $s=0;
    if ($s < $length) {
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($s);
$objPHPExcel->getActiveSheet()->setTitle($arr[$i]);
	$objPHPExcel->getActiveSheet()->SetCellValue('A1', "NO");
    $objPHPExcel->getActiveSheet()->SetCellValue('B1', "CENTRE");
	$objPHPExcel->getActiveSheet()->SetCellValue('C1', "EXAMINATION CODE");
    $objPHPExcel->getActiveSheet()->SetCellValue('D1', "STUDENT NAME");
	$objPHPExcel->getActiveSheet()->SetCellValue('E1', "MENTAL GRADE");
	$objPHPExcel->getActiveSheet()->SetCellValue('E1', "RESULT");
	$objPHPExcel->getActiveSheet()->SetCellValue('E1', "DATE");
	
	
$rowCount = 2;
$n = 1;

while($row = mysqli_fetch_assoc($result)){
    $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $n);
    $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $row['location']);
	$objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, $row['stud_id']);
    $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, $row['std_name']);
	$objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, $row['mental_grade']);
	$objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, $row['result']);
	$objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, $row['date']);
	$rowCount++;
	$n++;
	
}

$s++;
}
}
}
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
Posted
Updated 7-Aug-18 21:26pm
v2
Comments
Mohibur Rashid 7-Aug-18 19:15pm    
you are creating excel object on every row. but writing it once. ideal?
Member 13941305 7-Aug-18 22:48pm    
Hi Rashid. This method i found in google to create a excel using phpexcel

1 solution

PHP
<?php
$arr = array();
$main="select Distinct location from ige_international where city = 'UAE' and mental_grade != ''";
$resultmain = mysqli_query($MySQLi_CON, $main) or die(mysqli_error($MySQLi_CON));
/*
while($row1 = mysqli_fetch_assoc($resultmain)) {
  array_push($arr, $row1['location']);
}
*/
$arr = mysqli_fetch_row($resultmain);

$length = count($arr);
for ($i = 0; $i < $length; $i++) {
  $sql="SELECT location, CONCAT('IGEMA-0',MONTH(exam_date),YEAR(exam_date),'-',id) AS stud_id, UPPER(std_name)as std_name, mental_grade , UPPER(result) as result , DATE_FORMAT(exam_date, '%d/%m/%Y') as date from ige_international where city = 'UAE' and result not in ( 'Fail' ) and mental_grade != '' and location = '$arr[$i]' "; //location = '$arr[$i]' can be dangerous. take a look at prepare statement http://php.net/manual/en/mysqli-stmt.prepare.php
  // this query on loop is a poor idea. You can write a single query to grab entire set of data. 
  $result = mysqli_query($MySQLi_CON, $sql) or die(mysqli_error($MySQLi_CON)); 
  if (mysqli_num_rows($result) > 0) {
    $objPHPExcel = new PHPExcel();                        // now this is where things are going wrong; with every new location this is getting resetted 
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="Final Result.xlsx"');
    header('Cache-Control: max-age=0');
    $s=0;                                                // with every new location this is getting resetted
    if ($s < $length) {   // this is always true no matter what
      $objPHPExcel->createSheet();
      $objPHPExcel->setActiveSheetIndex($s);
      $objPHPExcel->getActiveSheet()->setTitle($arr[$i]);
      $objPHPExcel->getActiveSheet()->SetCellValue('A1', "NO");
      $objPHPExcel->getActiveSheet()->SetCellValue('B1', "CENTRE");
      $objPHPExcel->getActiveSheet()->SetCellValue('C1', "EXAMINATION CODE");
      $objPHPExcel->getActiveSheet()->SetCellValue('D1', "STUDENT NAME");
      $objPHPExcel->getActiveSheet()->SetCellValue('E1', "MENTAL GRADE");
      $objPHPExcel->getActiveSheet()->SetCellValue('E1', "RESULT");
      $objPHPExcel->getActiveSheet()->SetCellValue('E1', "DATE");
      $rowCount = 2;
      $n = 1;
      while($row = mysqli_fetch_assoc($result)) {
        $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $n);
        $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $row['location']);
        $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, $row['stud_id']);
        $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, $row['std_name']);
        $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, $row['mental_grade']);
        $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, $row['result']);
        $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, $row['date']);
        $rowCount++;
        $n++;
      }
      $s++; // you are increasing s here, but resetting above
    }
  }
}
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900