Member 13941305 Ответов: 1

Нужно создать новый лист в phpexcel для каждого цикла поиска

**Привет, я не новичок в этой программе. Я хотел бы знать, как создать новый лист для каждого результата поиска.
Ниже приведен мой текущий код.
Однако он показывает только результат для последнего массива. Пожалуйста, помогите мне в этом.**

Что я уже пробовал:

$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);

Mohibur Rashid

вы создаете объект excel для каждой строки. но писать ее надо один раз. идеал?

Member 13941305

Привет, Рашид. Этот метод я нашел в google для создания excel с помощью phpexcel

1 Ответов

Рейтинг:
0

Mohibur Rashid 

<?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);