daftar_nilai_ujian_xls.php

<?php
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=hasil_ujian_".date("d-m-Y").".xls");
include_once "include/koneksi.php";
$id_mp = $_REQUEST['id_mp'];
$nama_mp = mysql_result(mysql_query("SELECT nama_mp FROM mapel WHERE id_mp='".$id_mp."'"),0);
$sql_head = "SELECT id_ujian,nama_ujian FROM ujian where id_mp='".$id_mp."'";
$sql_head_exe = mysql_query($sql_head);
$head_id_ujian = array();
$nama_ujian=array();
$sql_nilai='';
while($head = mysql_fetch_assoc($sql_head_exe)){
  array_push($head_id_ujian,$head['id_ujian']);
  $sql_nilai.="SUM(IF(d.id_ujian=".$head['id_ujian'].",d.nilai,0)) AS nilai_".$head['id_ujian'].",";
  array_push($nama_ujian,$head['nama_ujian']);
}
?>
<div style="width:80%;margin:0 auto" class="daftar_pelajaran">
<fieldset>
<legend><center> YAYASAN PENDIDIKAN CENDRAWASIH PALU </center></legend>
<legend><center> AKADEMI KEBIDANAN PALU </center></legend>
<legend><center> SULAWESI TENGAH </center></legend>
<legend><center>Daftar Nilai Ujian Mata Pelajaran <?php echo ucwords($nama_mp) ?> </center></legend>
<table class='listing' cellpadding='0' cellspacing='0'>
<tr>
<th>No</th><th>NIS</th><th>Nama</th><th><?php echo implode("</th><th>",$nama_ujian)?></th><th>Rerata</th>
</tr>
<?php
if(count($head_id_ujian) > 0){
  $sql="SELECT
          a.nis,
          a.nama,"
          .$sql_nilai
         ."SUM(IF(ISNULL(d.nilai),0,d.nilai))/".count($head_id_ujian)." as rerata
        FROM siswa a
          LEFT JOIN (
            SELECT b.*
            FROM nilai b
            LEFT JOIN ujian c ON b.id_ujian=c.id_ujian
            WHERE c.id_mp='".$id_mp."'
          ) d  ON a.nis=d.id_user
        GROUP BY a.nis
        ORDER BY rerata DESC";
  $result = mysql_query($sql);
  $no=1;
  while($data = mysql_fetch_assoc($result)){
    echo "<tr>";
    echo "<td>".$no++."</td>";
    echo "<td class='nis'>".$data['nis']."</td>";
    echo "<td>".$data['nama']."</td>";
    for($i = 0;$i < count($head_id_ujian); $i++){
      echo "<td class='nilai' title='".$head_id_ujian[$i]."'>"
           .(empty($data['nilai_'.$head_id_ujian[$i]])?"--":number_format($data['nilai_'.$head_id_ujian[$i]],1))
           ."</td>";
    }
    echo "<td>".number_format($data['rerata'],3)."</td>";
    echo "</tr>";
  }
}
else {
  echo "<tr><td colspan='5'>Belum ada ujian yang dibuat</td></tr>";
}
?>
</table>