public function actionExcel()
{
//Validasi Raport gan!
$bulan = date('m');
if ($bulan < 7) {
$smt = 2;
//jika th 2(1-6) maka tahun-1/tahun-2 2012/2013
$th_ajar = date('Y') - 1 . '/' . date('Y');
} elseif ($bulan < 13 && $bulan > 6) {
$smt = 1;
//jika th 1(7-12) maka tahun/tahun+1 2012/2013
$th_ajar = date('Y') . '/' . (date('Y') + 1);
}
Yii::import('ext.phpexcel.Classes.PHPExcel', true);
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Nur Rochim")->setLastModifiedBy("Nur Rochim")->setTitle("Raport")->setSubject("Raport I-SIMS Penilaian")->setDescription("Raport I-SIMS Penilaian")->setKeywords("Raport I-SIMS Penilaian")->setCategory("Raport I-SIMS Penilaian");
$styleBold = array('font' => array('bold' => true), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => 'FF9900')));
$styleBackground = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => 'FF9900')), 'font' => array('name' => 'Arial', 'size' => 12, 'bold' => true));
$styleArray = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT, 'wrap' => PHPExcel_Style_Alignment::VERTICAL_TOP), 'borders' => array('horizontal' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
$tengah = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY, 'wrap' => PHPExcel_Style_Alignment::VERTICAL_CENTER), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
$tebalgaristengah = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'wrap' => PHPExcel_Style_Alignment::VERTICAL_CENTER), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'font' => array('bold' => true), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => 'FF9900')));
$tebalgariskanan = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT, 'wrap' => PHPExcel_Style_Alignment::VERTICAL_CENTER), 'font' => array('bold' => true));
//Di aliaskan
$ocim = $objPHPExcel->setActiveSheetIndex();
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
//Set Judul di merge
$ocim->setCellValue("A1", 'LAPORAN HASIL BELAJAR SISWA')->getStyle("A1:E1")->applyFromArray($styleBackground);
$ocim->mergeCells("A1:E1");
$ocim->getStyle("A1:E1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$ocim->getRowDimension(1)->setRowHeight(22);
$ocim->getRowDimension(2)->setRowHeight(20);
//Set Widthnya gan!
/*$ocim->getColumnDimension("A")->setWidth(5);
$ocim->getColumnDimension("B")->setWidth(40);
$ocim->getColumnDimension("C")->setWidth(10);
$ocim->getColumnDimension("D")->setWidth(10);
$ocim->getColumnDimension("E")->setWidth(10);*/
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
//Nilai Raport Mulai
$nilai = Nilai::model()->findAll(array('condition' => "nis='" . $_GET['id'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "'", 'order' => 'kode_mapel ASC'));
$arraynilai = array();
foreach ($nilai as $nil) {
$arraynilai[] = array('nama' => $nil->nis0->nama_lengkap, 'kode_mapel' => $nil->kodeMapel->kodeMapel->mapel, 'kkm' => $nil->kodeMapel->kodeMapel->kkm, 'na' => $nil->na);
}
//Header info siswa mulai
$objPHPExcel->getActiveSheet()->getStyle('A3:E6')->applyFromArray($styleArray);
$ocim->setCellValue("A3", "Nama : " . $nil->nis0->nama_lengkap);
$ocim->mergeCells("A3:B3");
$ocim->setCellValue("A4", "Nomor Induk :" . $nil->nis);
$ocim->mergeCells("A4:B4");
$ocim->setCellValue("A5", "Nama Sekolah : " . Yii::app()->params['title']);
$ocim->mergeCells("A5:B5");
$ocim->setCellValue("A6", "Alamat Sekolah : " . Yii::app()->params['subtitle']);
$ocim->mergeCells("A6:E7");
$ocim->getStyle("A6:E7")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$ocim->getStyle("A6:E7")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$ocim->setCellValue("D3", "Kelas : " . $nil->kelas . " " . $nil->lokal);
$ocim->mergeCells("D3:E3");
$ocim->setCellValue("D4", "Semester : " . $nil->semester);
$ocim->mergeCells("D4:E4");
$ocim->setCellValue("D5", "Tahun Pelajaran : " . $nil->th_ajar);
$ocim->mergeCells("D5:E5");
//Header info siswa selesai
$ocim->setCellValue("A9", "No")->setCellValue("B9", "Mata Pelajaran")->setCellValue("C9", "KKM*")->setCellValue("D9", "Nilai")->setCellValue("E9", "NRK**")->getStyle("A9:E9")->applyFromArray($styleBold);
foreach ($arraynilai as $k => $u) {
$ocim->setCellValue('A' . ($k + 10), $k + 1)->setCellValue('B' . ($k + 10), $u['kode_mapel'])->setCellValue('C' . ($k + 10), $u['kkm'])->setCellValue('D' . ($k + 10), $u['na'])->setCellValue('E' . ($k + 10), $u['kkm']);
//cetak kkm
}
$akhir = Yii::app()->db->createCommand("select count(nis) from nilai where nis='" . $_GET['id'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "';")->queryScalar();
//Jumlah nilai akhir
$ocim->setCellValue('A' . ($akhir + 12), "Jumlah Nilai Prestasi Hasil Belajar ")->mergeCells("A" . ($akhir + 12) . ":B" . ($akhir + 12));
$objPHPExcel->getActiveSheet()->getCell('C' . ($akhir + 12))->setValue('=SUM(D10:D23)');
//Jumlah Rata-rata Hasil Belajar:
$ocim->setCellValue('A' . ($akhir + 13), "Rata-rata Hasil Belajar ")->mergeCells("A" . ($akhir + 13) . ":B" . ($akhir + 13));
$objPHPExcel->getActiveSheet()->getCell('C' . ($akhir + 13))->setValue("=SUM(D10:D23)/" . $akhir . "");
foreach ($nilai as $i => $ii) {
$model = new Nilai();
$rata2 = Yii::app()->db->createCommand("select sum(na)/count(na) from nilai where kode_mapel='" . $ii['kode_mapel'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "';")->queryScalar();
echo $rata2 . "<br>";
$ocim->setCellValue('E' . ($i + 10), $rata2);
}
//Nilai Raport Selesai
//Berikan Border All
$semua = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
$ocim->getStyle('A9:E' . ($akhir + 10))->applyFromArray($semua);
//Ekstra Mulai
$ekstra = Trekstra::model()->findAll(array('condition' => "nis='" . $_GET['id'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "'"));
$arrayekstra = array();
foreach ($ekstra as $eks) {
$arrayekstra[] = array('nama_ekstra' => $eks->idEkstra->nama_ekstra, 'nilai_ekstra' => $eks->nilai_ekstra);
}
$ocim->setCellValue("A" . ($akhir + 15), "No")->setCellValue("B" . ($akhir + 15), "KEGIATAN EKSTRAKURIKULER")->setCellValue("E" . ($akhir + 15), "NILAI")->getStyle("A" . ($akhir + 15) . ":E" . ($akhir + 15))->applyFromArray($styleBold);
$ocim->mergeCells("B" . ($akhir + 15) . ":D" . ($akhir + 15));
foreach ($arrayekstra as $oc => $im) {
$ocim->setCellValue('A' . ($akhir + 16 + $oc), $oc + 1)->setCellValue('B' . ($akhir + 16 + $oc), $im['nama_ekstra'])->setCellValue('E' . ($akhir + 16 + $oc), $im['nilai_ekstra'])->mergeCells("B" . ($akhir + 16 + $oc) . ":D" . ($akhir + 16 + $oc));
}
$ocim->getStyle('A' . ($akhir + 15) . ':E' . ($akhir + 16 + $oc))->applyFromArray($semua);
//Ekstra Selesai
//Pribadi Mulai
$akhir2 = Yii::app()->db->createCommand("select count(nis) from tr_ekstra where nis='" . $_GET['id'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "';")->queryScalar();
$pribadi = Trpribadi::model()->findAll(array('condition' => "nis='" . $_GET['id'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "' and id_pribadi not in (4,5)"));
$arraypribadi = array();
foreach ($pribadi as $pri) {
$arraypribadi[] = array('nama_pribadi' => $pri->idPribadi->nama_pribadi, 'nilai_pribadi' => $pri->nilai_pribadi, 'catatan' => $pri->catatan);
}
$ocim->setCellValue("A" . ($akhir + 17 + $akhir2), "No")->setCellValue("B" . ($akhir + 17 + $akhir2), "KEPRIBADIAN")->setCellValue("C" . ($akhir + 17 + $akhir2), "NILAI")->getStyle("A" . ($akhir + 17 + $akhir2) . ":C" . ($akhir + 17 + $akhir2))->applyFromArray($styleBold);
foreach ($arraypribadi as $oc => $im) {
$ocim->setCellValue('A' . ($akhir + 18 + $akhir2 + $oc), $oc + 1)->setCellValue('B' . ($akhir + 18 + $akhir2 + $oc), $im['nama_pribadi'])->setCellValue('C' . ($akhir + 18 + $akhir2 + $oc), $im['nilai_pribadi']);
//cetak kkm
}
$ocim->getStyle('A' . ($akhir + 17 + $akhir2) . ':E' . ($akhir + 18 + $akhir2 + $oc))->applyFromArray($semua);
//Pribadi Selesai
//Keterangan KKM dan NRK
$ocim->setCellValue('A' . ($akhir + 20 + $akhir2 + $oc), "*) Kriteria Ketuntasan Minimal")->setCellValue('A' . ($akhir + 21 + $akhir2 + $oc), "**) Nilai Rata-Rata Kelas")->mergeCells("A" . ($akhir + 20 + $akhir2 + $oc) . ":E" . ($akhir + 20 + $akhir2 + $oc))->mergeCells("A" . ($akhir + 21 + $akhir2 + $oc) . ":E" . ($akhir + 21 + $akhir2 + $oc));
$ocim->getStyle("A" . ($akhir + 20 + $akhir2 + $oc) . ":E" . ($akhir + 21 + $akhir2 + $oc))->getFont()->setSize(8);
//Presensi Mulai
$akhir2 = Yii::app()->db->createCommand("select count(nis) from tr_ekstra where nis='" . $_GET['id'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "';")->queryScalar();
$pribadi = Trpribadi::model()->findAll(array('condition' => "nis='" . $_GET['id'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "' and id_pribadi not in (4,5)"));
$arraypribadi = array();
foreach ($pribadi as $pri) {
$arraypribadi[] = array('nama_pribadi' => $pri->idPribadi->nama_pribadi, 'nilai_pribadi' => $pri->nilai_pribadi, 'catatan' => $pri->catatan);
}
$ocim->setCellValue("D" . ($akhir + 17 + $akhir2), "KETIDAKHADIRAN")->setCellValue("E" . ($akhir + 17 + $akhir2), "HARI")->getStyle("D" . ($akhir + 17 + $akhir2) . ":E" . ($akhir + 17 + $akhir2))->applyFromArray($styleBold);
$sakit = Presensi::model()->countByAttributes(array('nis' => $_GET['id'], 'status' => 'S', 'th_ajar' => $th_ajar, 'semester' => (string) $smt));
$izin = Presensi::model()->countByAttributes(array('nis' => $_GET['id'], 'status' => 'I', 'th_ajar' => $th_ajar, 'semester' => (string) $smt));
$alpha = Presensi::model()->countByAttributes(array('nis' => $_GET['id'], 'status' => 'A', 'th_ajar' => $th_ajar, 'semester' => (string) $smt));
$ocim->setCellValue("D" . ($akhir + 18 + $akhir2), "Sakit")->setCellValue("D" . ($akhir + 19 + $akhir2), "Izin")->setCellValue("D" . ($akhir + 20 + $akhir2), "Alpha")->setCellValue("E" . ($akhir + 18 + $akhir2), $sakit)->setCellValue("E" . ($akhir + 19 + $akhir2), $izin)->setCellValue("E" . ($akhir + 20 + $akhir2), $alpha);
//Presensi Selesai
//Tentang Mulai
$objPHPExcel->getActiveSheet()->getStyle('G4:L9')->applyFromArray($tengah);
$ocim->setCellValue("G3", 'CATATAN TENTANG PENGEMBANGAN DIRI')->mergeCells("G3:L3")->getStyle("G3:L3")->applyFromArray($tebalgaristengah);
$pribadi2 = Trpribadi::model()->findAll(array('condition' => "nis='" . $_GET['id'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "' and id_pribadi=5"));
$arraypribadi2 = array();
foreach ($pribadi2 as $pri2) {
$arraypribadi2[] = array($ocim->setCellValue("G4", $pri2->catatan), $ocim->mergeCells("G4:L9"));
}
//Tentang selesai
//Catatan
$objPHPExcel->getActiveSheet()->getStyle('G12:L17')->applyFromArray($tengah);
$ocim->setCellValue("G11", 'CATATAN')->mergeCells("G11:L11")->getStyle("G11:L11")->applyFromArray($tebalgaristengah);
$pribadi2 = Trpribadi::model()->findAll(array('condition' => "nis='" . $_GET['id'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "' and id_pribadi=4"));
$arraypribadi2 = array();
foreach ($pribadi2 as $pri2) {
$arraypribadi2[] = array($ocim->setCellValue("G12", $pri2->catatan), $ocim->mergeCells("G12:L17"));
}
//Catatan selesai
//Tanggal
$tanggalbawah = date('d F Y');
$ocim->setCellValue("G19", 'Karanganyar, ' . $tanggalbawah)->mergeCells("G19:L19")->getStyle("G19:L19")->applyFromArray($tebalgariskanan);
//TTD
$raport = Nilai::model()->find(array('condition' => "nis='" . $_GET['id'] . "'"));
$ocim->setCellValue("K20", 'Wali Kelas')->mergeCells("K20:L20")->getStyle("G20:L20")->applyFromArray($tebalgariskanan);
$ocim->setCellValue("G20", 'Orang Tua/Wali')->mergeCells("G20:H20")->getStyle("G20:H20")->applyFromArray($tebalgariskanan);
$ocim->setCellValue("K24", $raport->kodeGuru->nama_guru)->mergeCells("K24:L24")->getStyle("G24:L24")->applyFromArray($tebalgariskanan);
//NIP
$ocim->setCellValue("K25", "NIP." . $raport->kodeGuru->nip)->mergeCells("K25:L25")->getStyle("G25:L25")->applyFromArray($tebalgariskanan);
$ocim->setCellValue("G24", '(_____________)')->mergeCells("G24:H24")->getStyle("G24:H24")->applyFromArray($tebalgariskanan);
$objPHPExcel->setActiveSheetIndex(0);
ob_end_clean();
ob_start();
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="ocim.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
}
/**
* Returns the data model based on the primary key given in the GET variable.
* If the data model is not found, an HTTP exception will be raised.
* @param integer the ID of the model to be loaded
*/
public function loadModel($id)
{
$model = Trpribadi::model()->findByPk($id);
if ($model === null) {
throw new CHttpException(404, 'The requested page does not exist.');
}
return $model;
}