function export_xls_fik() { //get submit parameters $col_name = $this->input->get_post("ckod"); $cou_id = $this->input->get_post("kursus"); $current_sem = $this->input->get_post("semtr"); $current_year = $this->input->get_post("tahun"); //get values $list_fik = $this->m_report->result_fik($col_name, $cou_id, $current_sem, $current_year); //for debug purpose only, safe to delete /*echo"<pre>"; print_r($list_fik); echo"</pre>"; die();*/ //load our new PHPExcel library $this->load->library('excel'); //activate worksheet number 1 $this->excel->setActiveSheetIndex(0); //name the worksheet $this->excel->getActiveSheet()->setTitle("Fail Induk Keputusan"); //save our workbook as this file name $filename = 'Fail_Induk_Keputusan.xls'; //set the informations $this->excel->getActiveSheet()->setCellValue('B2', 'Fail Induk Nama'); $this->excel->getActiveSheet()->mergeCells('B2:Z2'); //style for the above information $styleArray = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); $this->excel->getActiveSheet()->getStyle('B1:B3')->applyFromArray($styleArray); //apply the style to the cells $highlightCells = array(); //header $excel_header = array('BIL', 'NAMA CALON', 'MYKAD', 'ANGKA GILIRAN', 'KOD KURSUS', 'JANTINA', 'KAUM', 'AGAMA'); $ttl = 0; $columnCount = 65; $arysubb = array(); //$sucount=$list_fik[0]->count_subj; foreach ($list_fik as $stuk) { $keyk = count($stuk->moduletaken); array_push($arysubb, $keyk); } $uniqcodesub = array_unique($arysubb); arsort($uniqcodesub); for ($i = 1; $i <= $uniqcodesub[0]; $i++) { array_push($excel_header, 'KOD_MP' . $i . " ", 'NAMA_MP' . $i . " ", 'JK_MP' . $i . " ", 'GRED_MP' . $i . " ", 'NM_MP' . $i . " ", 'MK_MP' . $i . " ", 'STATUS_MP' . $i . " "); } array_push($excel_header, 'PNGA ', 'PNGKA ', 'PNGV ', 'PNGKV ', 'PNGK ', 'PNGKK ', 'JUM_MP ', 'JUM_JK ', 'JUM_MN ', 'STATUS'); //data $index = 1; $excel_data = array(); foreach ($list_fik as $rowData) { $r = array(); array_push($r, $index); $namapelajar = $rowData->stu_name; $icpalajar = $rowData->stu_mykad; $angkagiliran = $rowData->stu_matric_no; $kodkursus = $rowData->cou_course_code; $jantina = $rowData->stu_gender; $bangsa = $rowData->stu_race; $agama = $rowData->stu_religion; if ("NULL" == $agama) { $agama = "-"; } array_push($r, " " . strval(name_strtoupper($namapelajar)), " " . $icpalajar . " ", " " . strval(name_strtoupper($angkagiliran)), " " . strval(name_strtoupper($kodkursus)), " " . strval(name_strtoupper($jantina)), " " . strval(name_strtoupper($bangsa)), " " . strval(name_strtoupper($agama))); $credittotal = 0; $nilaMata = 0; $jummod = 0; $nilaMatatot = 0; foreach ($rowData->moduletaken as $moduldiambil) { $jummod++; $credittotal += $moduldiambil->mod_credit_hour; $nilaMata = $moduldiambil->mod_credit_hour * $moduldiambil->grade_value; $nilaMatatot += $nilaMata; if (0 == $nilaMata) { $nilaMata = "0"; } $kodmodule = $moduldiambil->mod_paper; $namamodul = $moduldiambil->mod_name; $jamkredit = $moduldiambil->mod_credit_hour; $jenisgred = $moduldiambil->grade_type; $nilaigred = $moduldiambil->grade_value; $levelgred = $moduldiambil->grade_level; array_push($r, " " . strval(name_strtoupper($kodmodule)), " " . strval(name_strtoupper($namamodul)), $jamkredit . " ", " " . strval(name_strtoupper($jenisgred)), $nilaigred . " ", $nilaMata . " ", " " . strval(name_strtoupper($levelgred))); } $pngk = $rowData->pngk; $pngkk = $rowData->pngkk; $pngv = $rowData->pngv; $pngkv = $rowData->pngkv; $pnga = $rowData->pnga; $pngka = $rowData->pngka; $status = "1 "; array_push($r, $pnga . " ", $pngka . " ", $pngv . " ", $pngkv . " ", $pngk . " ", $pngkk . " ", $jummod . " ", $credittotal . " ", $nilaMatatot . " ", $status); array_push($excel_data, $r); //array_push($excel_data, $subjek_arr); $index++; } //for debug purpose only, safe to delete /*echo"<pre>"; print_r($gabung_arr); echo"</pre>"; die();*/ //set auto resize for all the columns for ($col = 'A'; $col != 'CL'; $col++) { $this->excel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true); } //load the header into position B4 $this->excel->getActiveSheet()->fromArray($excel_header, NULL, 'B4'); //load the data into position B5 $this->excel->getActiveSheet()->fromArray($excel_data, NULL, 'B5'); //border fill color for header $style_header = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FFC000')), 'font' => array('bold' => true)); $this->excel->getActiveSheet()->getStyle('B4:CK4')->applyFromArray($style_header); //apply the border fill //style to set border $borderStyleArray = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'inside' => array('style' => PHPExcel_Style_Border::BORDER_THIN))); $this->excel->getActiveSheet()->getStyle('B4:CK' . (3 + $index))->applyFromArray($borderStyleArray); $this->excel->getActiveSheet()->getStyle('D4:D' . (3 + $index))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER); header('Content-Type: application/vnd.ms-excel'); //mime type header('Content-Disposition: attachment;filename="' . $filename . '"'); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension) //if you want to save it as .XLSX Excel 2007 format $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5'); //force user to download the Excel file without writing it to server's HD $objWriter->save('php://output'); }
function export_xls_fik() { // $col_name = "KV Cubaan"; // $current_year = 2014; // $cou_id = 6; // $current_sem = 1; $col_name = $this->input->post('kodpusat'); $current_year = $this->input->post('mt_year'); $cou_id = $this->input->post('slct_kursus'); $current_sem = $this->input->post('semester'); $cC = explode("-", $col_name); $student = $this->m_report->result_fik_v2($cC[0], $cou_id, $current_sem, $current_year); if (!empty($student)) { //for debug purpose only, safe to delete /*echo"<pre>"; print_r($list_fik); echo"</pre>"; die();*/ //load our new PHPExcel library $this->load->library('excel'); //activate worksheet number 1 $this->excel->setActiveSheetIndex(0); //name the worksheet $this->excel->getActiveSheet()->setTitle("FIK"); //save our workbook as this file name $filename = 'Fail_Induk_Keputusan.xls'; //set the informations $this->excel->getActiveSheet()->setCellValue('A1', 'FIN'); $this->excel->getActiveSheet()->mergeCells('A1:J1'); $this->excel->getActiveSheet()->setCellValue('K1', 'PENTAKSIRAN BERTERUSAN [PB] AKADEMIK'); $this->excel->getActiveSheet()->mergeCells('K1:Q1'); $this->excel->getActiveSheet()->setCellValue('R1', 'PENILAIAN AKHIR [PA] AKADEMIK'); $this->excel->getActiveSheet()->mergeCells('R1:AB1'); $this->excel->getActiveSheet()->setCellValue('AC1', 'MARKAH PB DAN PA AKADEMIK'); $this->excel->getActiveSheet()->mergeCells('AC1:AP1'); $this->excel->getActiveSheet()->setCellValue('AQ1', 'MARKAH PB + PA AKADEMIK'); $this->excel->getActiveSheet()->mergeCells('AQ1:AW1'); $this->excel->getActiveSheet()->setCellValue('AX1', 'GRED AKADEMIK'); $this->excel->getActiveSheet()->mergeCells('AX1:BD1'); $this->excel->getActiveSheet()->setCellValue('BE1', 'PENTAKSIRAN BERTERUSAN (PB) VOKASIONAL'); $this->excel->getActiveSheet()->mergeCells('BE1:BT1'); $this->excel->getActiveSheet()->setCellValue('BU1', 'PENILAIAN AKHIR (PA) VOKASIONAL'); $this->excel->getActiveSheet()->mergeCells('BU1:CJ1'); $this->excel->getActiveSheet()->setCellValue('CK1', 'MARKAH PB VOKASIONAL'); $this->excel->getActiveSheet()->mergeCells('CK1:CR1'); $this->excel->getActiveSheet()->setCellValue('CS1', 'MARKAH PA VOKASIONAL'); $this->excel->getActiveSheet()->mergeCells('CS1:CZ1'); $this->excel->getActiveSheet()->setCellValue('DA1', 'MARKAH PB + PA VOKASIONAL'); $this->excel->getActiveSheet()->mergeCells('DA1:DH1'); $this->excel->getActiveSheet()->setCellValue('DI1', 'GRED VOKASIONAL'); $this->excel->getActiveSheet()->mergeCells('DI1:DP1'); //style for the above information $styleArray = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'F0F0F0 ')), 'font' => array('bold' => true)); $this->excel->getActiveSheet()->getStyle('A1:DP1')->applyFromArray($styleArray); //apply the style to the cells // $this->excel->getActiveSheet()->getStyle('B1:B3')->applyFromArray($styleArray); //apply the style to the cells $highlightCells = array(); //header $subjek = array(); $excel_header = array('BIL', 'NAMA CALON', 'NO. KAD PENGENALAN', 'ANGKA GILIRAN', 'KOD KURSUS', 'STATUS', 'JANTINA', 'KAUM', 'AGAMA', 'KV'); // HEADER $subjekUnie = array('BAHASA MELAYU', 'BAHASA INGGERIS', 'MATHEMATICS', 'SAINS', 'SEJARAH', 'PEND ISLAM', 'PEND MORAL'); foreach ($subjekUnie as $rowak) { array_push($excel_header, $rowak . "(100)"); } foreach ($subjekUnie as $rowak) { array_push($excel_header, $rowak . "(100)"); if ($rowak == 'SAINS') { array_push($excel_header, 'SAINS KERTAS 2 (100)', 'SAINS GABUNG'); } if ($rowak == 'PENDIDIKAN ISLAM') { array_push($excel_header, 'PEND ISLAM KERTAS 2 ', 'PEND ISLAM GABUNG'); } } foreach ($subjekUnie as $rowak) { array_push($excel_header, $rowak . "\r [30]\r [PB]", $rowak . "\r[70]\r[PA]"); } foreach ($subjekUnie as $rowak) { array_push($excel_header, $rowak . "(100)"); } foreach ($subjekUnie as $rowak) { array_push($excel_header, $rowak); } for ($i = 1; $i < 17; $i++) { if ($i > 8) { $a = $i - 8; } else { $a = $i; } array_push($excel_header, "TEORI {$a} (100)", "AMALI {$a} (100)"); } for ($i = 1; $i < 25; $i++) { if ($i > 16 && $i < 25) { $a = $i - 16; $scope = '[100]'; } elseif ($i > 8) { $a = $i - 8; $scope = '[30]'; } else { $a = $i; $scope = '[70]'; } array_push($excel_header, "M{$a} {$scope}"); } for ($i = 1; $i < 9; $i++) { array_push($excel_header, "M{$i}"); } //END OF HEADER $index = 1; $excel_data = array(); // $excel_data=array(); foreach ($student as $rowData) { $r = array(); $s = array(); $p = array(); array_push($r, $index); $namapelajar = $rowData->stu_name; $icpalajar = $rowData->stu_mykad; $angkagiliran = $rowData->stu_matric_no; $kodkursus = $rowData->cou_course_code; $jantina = $rowData->stu_gender; $bangsa = $rowData->stu_race; $agama = $rowData->stu_religion; $status = $rowData->stat_id; $col_name = $rowData->col_name; $cou_idg = $rowData->cou_id; //FIN array_push($r, " " . strval(name_strtoupper($namapelajar)), " " . $icpalajar . " ", " " . strval(name_strtoupper($angkagiliran)), " " . strval(name_strtoupper($kodkursus)), " " . strval(name_strtoupper($status)), " " . strval(name_strtoupper($jantina)), " " . strval(name_strtoupper($bangsa)), " " . strval(name_strtoupper($agama)), " " . strval(name_strtoupper($col_name))); $p = array(); //END FIN // modul AK foreach ($rowData->moduletaken_AK as $markSub) { $modl = $markSub->mod_name; $pus = array(); $sek = ''; foreach ($markSub->mark_ass as $rowm) { if ($rowm->mark_category == 'S') { $dats = $rowm->marks_value; if ($modl == "Pendidikan Moral") { array_push($s, " "); } array_push($s, " " . strval($dats)); if ($modl == "Pendidikan Islam") { array_push($s, " "); } } else { if ($modl == "Pendidikan Moral") { array_push($p, " " . strval('')); } if ($modl == "Science" && $rowm->mark_category == 'P') { if (!empty($markSub->mark_teori)) { foreach ($markSub->mark_teori as $teoriPak) { if ($teoriPak->ppr_category == "P") { array_push($p, ""); } else { array_push($p, ""); } } } else { array_push($p, "", ""); } } if ($modl == "Pendidikan Islam" && $rowm->mark_category == 'P') { if (!empty($markSub->mark_teori)) { foreach ($markSub->mark_teori as $teoriPak) { if ($teoriPak->ppr_category == "P") { array_push($p, " " . $teoriPak->mark); } } } else { // array_push($p, "",""); } } if ($modl == "Pendidikan Moral") { // array_push($p, " ".strval('')); } $datp = $rowm->marks_value; array_push($p, " " . strval($datp)); if ($modl == "Pendidikan Islam" && $rowm->mark_category == 'P') { array_push($p, ""); } } } } foreach ($s as $smark) { array_push($r, " " . strval($smark)); } foreach ($p as $pmark) { array_push($r, " " . strval($pmark)); } foreach ($rowData->moduletaken_AK as $markSub) { if ($markSub->mod_name == "Pendidikan Moral") { array_push($r, " " . strval(''), strval('')); } $ptotal = array(); foreach ($markSub->mark_ass as $rowms) { $markpa = $rowms->marks_total_mark / 100 * $rowms->marks_value; array_push($r, " " . strval($markpa)); } if ($markSub->mod_name == "Pendidikan Islam") { array_push($r, " " . strval(''), " " . strval('')); } } foreach ($rowData->moduletaken_AK as $markSubb) { if ($markSubb->mod_name == "Pendidikan Moral") { array_push($r, " " . strval('')); } array_push($r, " " . strval($markSubb->mt_full_mark)); if ($markSubb->mod_name == "Pendidikan Islam") { array_push($r, " " . strval('')); } } foreach ($rowData->moduletaken_AK as $markSubbb) { if ($markSubbb->mod_name == "Pendidikan Moral") { array_push($r, " " . strval('')); } array_push($r, " " . strval($markSubbb->grade_type)); if ($markSubbb->mod_name == "Pendidikan Islam") { array_push($r, " " . strval('')); } } $n = 0; foreach ($rowData->moduletaken_VK as $markSubvk) { $markToeri = array(); $markAmali = array(); $markToerip = array(); $markAmalip = array(); if (!empty($markSubvk->mark_teori)) { $teorisub = $markSubvk->mark_teori; foreach ($teorisub as $rowteori) { $pecahan[$rowteori->pt_category][$rowteori->assgmnt_name] = $rowteori->pt_teori; $pilih[$rowteori->pt_category][$rowteori->assgmnt_name] = $rowteori->assgmnt_score_selection; if ($rowteori->pt_category == 'S') { if ($rowteori->assgmnt_name == 'Teori') { array_push($markToeri, $rowteori->mark); } if ($rowteori->assgmnt_name == 'Amali') { array_push($markAmali, $rowteori->mark); } } elseif ($rowteori->pt_category == 'P') { if ($rowteori->assgmnt_name == 'Teori') { array_push($markToerip, $rowteori->mark); } if ($rowteori->assgmnt_name == 'Amali') { array_push($markAmalip, $rowteori->mark); } } } } arsort($markToeri); arsort($markAmali); //sekolah //teori $asSTeori = empty($pilih['S']['Teori']) ? 1 : $pilih['S']['Teori']; $asSAmali = empty($pilih['S']['Amali']) ? 1 : $pilih['S']['Amali']; $asPTeori = empty($pilih['P']['Teori']) ? 1 : $pilih['P']['Teori']; $asPAmali = empty($pilih['S']['Amali']) ? 1 : $pilih['S']['Amali']; $tos = array_slice($markToeri, 0, $asSTeori); $totalpt[$n] = ceil(array_sum($tos) / $asSTeori); // amali $ams = array_slice($markAmali, 0, $asSAmali); $totalams[$n] = ceil(array_sum($ams) / $asSAmali); //pusat // teori $totalptp[$n] = ceil(array_sum($markToerip) / $asPTeori); //amali $totalamsp[$n] = ceil(array_sum($markAmalip) / $asPAmali); if (!empty($markSubvk->mark_ass)) { foreach ($markSubvk->mark_ass as $markAss) { $maks[$markAss->mark_category][$n] = ceil($markAss->marks_value); } } $grade[$n] = $markSubvk->grade_type; $fullmark[$n] = $markSubvk->mt_full_mark; $n++; } for ($ia = 0; $ia < 8; $ia++) { array_push($r, empty($totalpt[$ia]) ? "0" : $totalpt[$ia], empty($totalams[$ia]) ? "0" : $totalams[$ia]); } for ($ib = 0; $ib < 8; $ib++) { array_push($r, empty($totalptp[$ib]) ? "0" : $totalptp[$ib], empty($totalamsp[$ib]) ? "0" : $totalamsp[$ib]); } for ($ic = 0; $ic < 8; $ic++) { array_push($r, empty($maks['S'][$ic]) ? "0" : $maks['S'][$ic]); } for ($ic = 0; $ic < 8; $ic++) { array_push($r, empty($maks['P'][$ic]) ? "0" : $maks['P'][$ic]); } for ($id = 0; $id < 8; $id++) { array_push($r, empty($fullmark[$id]) ? "0" : $fullmark[$id]); } for ($ie = 0; $ie < 8; $ie++) { array_push($r, empty($grade[$ie]) ? "0" : $grade[$ie]); } array_push($excel_data, $r); $index++; } //for debug purpose only, safe to delete //set auto resize for all the columns $co = count($excel_header); for ($col = 'A'; $col != 'DP'; $col++) { $this->excel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true); } //load the header into position B4 $this->excel->getActiveSheet()->fromArray($excel_header, NULL, 'A2'); //load the data into position B5 $this->excel->getActiveSheet()->fromArray($excel_data, NULL, 'A3'); //border fill color for header $style_header = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'DFDFDF')), 'font' => array('bold' => true)); $this->excel->getActiveSheet()->getStyle('A2:DP2')->applyFromArray($style_header); //apply the border fill //style to set border $borderStyleArray = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'inside' => array('style' => PHPExcel_Style_Border::BORDER_THIN))); // $this->excel->getActiveSheet()->getStyle('B4:CK'.(3+$index))->applyFromArray($borderStyleArray); // $this->excel->getActiveSheet()->getStyle('D4:D'.(3+$index))-> // getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER); header('Content-Type: application/vnd.ms-excel'); //mime type header('Content-Disposition: attachment;filename="' . $filename . '"'); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension) //if you want to save it as .XLSX Excel 2007 format $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5'); //force user to download the Excel file without writing it to server's HD $objWriter->save('php://output'); } else { redirect('/report/report_v2/view_fik_no'); } }
function export_xls_jadual_kedatangan() { $abc = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ', 'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ', 'CA', 'CB', 'CC', 'CD', 'CE', 'CF', 'CG', 'CH', 'CI', 'CJ', 'CK', 'CL', 'CM', 'CN', 'CO', 'CP', 'CQ', 'CR', 'CS', 'CT', 'CU', 'CV', 'CW', 'CX', 'CY', 'CZ', 'DA', 'DB', 'DC', 'DD', 'DE', 'DF', 'DG', 'DH', 'DI', 'DJ', 'DK', 'DL'); $centreCode = $this->input->get('kodpusat'); $semester = $this->input->get('semester'); $year = $this->input->get('slct_tahun'); $course = $this->input->get('slct_kursus'); $module = $this->input->get('modul'); $status = $this->input->get('statusID'); $cC = explode("-", $centreCode); $data['student'] = $this->m_result->get_module_taken($cC[0], $semester, $year, $course, $status, $module); $data['nama_kursus'] = $this->m_result->ambik_nama_kursus($course); //$data['nama_modul'] = $this->m_result->get_module_jadual_kedatangan($course); /**FDPO - Safe to be deleted**/ //echo('<pre>');print_r($module);echo('</pre>'); //die(); $index = sizeof($data['student']); //load our new PHPExcel library $this->load->library('excel'); //activate worksheet number 1 $this->excel->setActiveSheetIndex(0); //name the worksheet $this->excel->getActiveSheet()->setTitle("Jadual Kedatangan Calon"); //$highlightCells = array(); //header $excel_header = array('BIL', 'NAMA', 'ANGKA GILIRAN', 'TINDAKAN'); //$excel_jadual = array(); //$data['student'] = $this->m_result->get_module_taken($cC[0], $semester,$year,$course,$status,$module); $filename = 'Jadual Kedatangan Calon_' . $data['nama_kursus']->cou_name . '_' . $module . '.xls'; //save our workbook as this file name //load the header into position A1 $this->excel->getActiveSheet()->fromArray($excel_header, NULL, 'A10'); // die(); $ttl = 0; $columnCount = 65; //masukkkan data disini $index = 1; $excel_data = array(); //$j = 0; if (isset($data['student'])) { //array_push($r, $index); $tidakHadir = 1; $hospital = 2; foreach ($data['student'] as $p) { $r = array(); array_push($r, $index); array_push($r, strval(name_strtoupper($p->stu_name))); array_push($r, $p->stu_matric_no); if ($p->na_status == $tidakHadir) { array_push($r, "T"); } else { if ($p->na_status == $hospital) { array_push($r, "H"); } else { array_push($r, "/"); } } array_push($excel_data, $r); $index++; } } //echo "<pre>"; //print_r($excel_data); //echo "</pre>"; //die(); //load the data into position C4 $this->excel->getActiveSheet()->fromArray($excel_data, NULL, 'A11'); //set the informations $this->excel->getActiveSheet()->setCellValue('A2', 'Jadual Kedatangan Calon'); $this->excel->getActiveSheet()->mergeCells('A2:Z2'); $this->excel->getActiveSheet()->setCellValue('A3', 'Kursus : ' . $data['nama_kursus']->cou_name); $this->excel->getActiveSheet()->mergeCells('A3:Z3'); $this->excel->getActiveSheet()->setCellValue('A4', 'Semester : ' . $semester); $this->excel->getActiveSheet()->mergeCells('A4:Z4'); $this->excel->getActiveSheet()->setCellValue('A5', 'T = Tidak Hadir'); $this->excel->getActiveSheet()->mergeCells('A5:Z5'); $this->excel->getActiveSheet()->setCellValue('A6', 'H = Hospital'); $this->excel->getActiveSheet()->mergeCells('A6:Z6'); $this->excel->getActiveSheet()->setCellValue('A7', '/ = Hadir'); $this->excel->getActiveSheet()->mergeCells('A7:Z7'); if (file_exists("./uploaded/kvinfo/Logokolej_small.jpg")) { $gdImage = imagecreatefromjpeg('./uploaded/kvinfo/Logokolej_small.jpg'); $objDrawing = new PHPExcel_Worksheet_MemoryDrawing(); $objDrawing->setImageResource($gdImage); $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); // image rendering. $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT); // nanti image jadi low quality $objDrawing->setOffsetX(60); $objDrawing->setOffsetY(-5); $objDrawing->setCoordinates('C2'); $objDrawing->setWorksheet($this->excel->getActiveSheet()); } if (file_exists("./uploaded/kvinfo/Copkolej_medium.png")) { $copImage = imagecreatefrompng('./uploaded/kvinfo/Copkolej_medium.png'); $transparent = imagecolorallocate($copImage, 0, 0, 0); // set color transperent utk image // Make the background transparent imagecolortransparent($copImage, $transparent); // kalau xde ni, background jadi hitam.. $copDrawing = new PHPExcel_Worksheet_MemoryDrawing(); $copDrawing->setImageResource($copImage); $copDrawing->setOffsetX(60); $copDrawing->setOffsetY(-5); $copDrawing->setCoordinates('B27'); $copDrawing->setWorksheet($this->excel->getActiveSheet()); } //style for the above information $styleArray = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); $this->excel->getActiveSheet()->getStyle('A1:A9')->applyFromArray($styleArray); //apply thee style to the cells //center alignment $styleArrayCenter = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); $this->excel->getActiveSheet()->getStyle('D11:D500')->applyFromArray($styleArrayCenter); //apply thee style to the cells //border fill color for header $style_header = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FFC000')), 'font' => array('bold' => true)); $this->excel->getActiveSheet()->getStyle('A10:' . $abc[sizeof($excel_header) - 1] . '10')->applyFromArray($style_header); //apply the border fill //style to set border $borderStyleArray = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'inside' => array('style' => PHPExcel_Style_Border::BORDER_THIN))); $this->excel->getActiveSheet()->getStyle('A10:' . $abc[sizeof($excel_header) - 1] . (9 + $index))->applyFromArray($borderStyleArray); //style for data even row, we will set color for even rows of data $style_even_row = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FDE9D9'))); for ($i = 2; $i < $index; $i++) { if ($i % 2 == 0) { $this->excel->getActiveSheet()->getStyle('A' . ($i + 9) . ':' . $abc[sizeof($excel_header) - 1] . (9 + $i))->applyFromArray($style_even_row); } } //ni untuk on filter //$this->excel->getActiveSheet()->setAutoFilter('A1:'.$abc[(sizeof($excel_header)-1)].($index)); //$this->excel->getActiveSheet()->getCell('A1:'.$abc[(sizeof($excel_header)-1)].'1')->setAutoSize( true ); //apply the border fill //$this->excel->getActiveSheet()->getStyle(''.($index))-> //getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER); //set auto resize for all the columns for ($col = 0; $col <= sizeof($excel_header) - 1; $col++) { $this->excel->getActiveSheet()->getColumnDimension($abc[$col])->setAutoSize(true); } //$blocksList = implode (", ", $mark); $objValidation = $this->excel->getActiveSheet()->getCell('K1')->getDataValidation(); /*$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_DECIMAL); $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP); $objValidation->setOperator( PHPExcel_Cell_DataValidation::OPERATOR_LESSTHANOREQUAL); $objValidation->setAllowBlank(true); $objValidation->setShowInputMessage(true); $objValidation->setShowErrorMessage(true); $objValidation->setErrorTitle('Input error'); $objValidation->setError('Only Number is permitted!'); $objValidation->setPromptTitle('Allowed input'); $objValidation->setPrompt('Only numbers between 1.0 and 100.0 are allowed.'); $objValidation->setFormula1(1.0); $objValidation->setFormula2(100.0);*/ $this->excel->getActiveSheet()->removeColumn($abc[sizeof($excel_header) + 1], 1); $this->excel->getActiveSheet()->removeColumn($abc[sizeof($excel_header) + 2], 1); header('Content-Type: application/vnd.ms-excel'); //mime type header('Content-Disposition: attachment;filename="' . $filename . '"'); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension) //if you want to save it as .XLSX Excel 2007 format $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5'); //force user to download the Excel file without writing it to server's HD $objWriter->save('php://output'); }
function export_xls_markahStudent() { $abc = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ', 'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ', 'CA', 'CB', 'CC', 'CD', 'CE', 'CF', 'CG', 'CH', 'CI', 'CJ', 'CK', 'CL', 'CM', 'CN', 'CO', 'CP', 'CQ', 'CR', 'CS', 'CT', 'CU', 'CV', 'CW', 'CX', 'CY', 'CZ', 'DA', 'DB', 'DC', 'DD', 'DE', 'DF', 'DG', 'DH', 'DI', 'DJ', 'DK', 'DL'); $mark = array(); for ($i = 0; $i <= 100; $i++) { array_push($mark, $i); } $assmnt_id = $this->input->get("assgmnt_ID"); $semselect = $this->input->get("semesterP"); $cmID = $this->input->get("ksmid2"); $kelas = $this->input->get("slct_kelas"); $data['senaraipelajar'] = $this->m_pelajar->student_by_assmnt($cmID, $assmnt_id, $semselect, $kelas); $data['subjekconfigur'] = $this->m_subject->subject_configuratian_assgmnt($assmnt_id); $aJson = array("pelajar" => $data['senaraipelajar'], "subjek" => $data['subjekconfigur']); $index = sizeof($data['senaraipelajar']); //load our new PHPExcel library $this->load->library('excel'); //activate worksheet number 1 $this->excel->setActiveSheetIndex(0); //name the worksheet $this->excel->getActiveSheet()->setTitle("Pemarkahan mengikut semester dan modul"); $highlightCells = array(); //header $excel_header = array('BIL', 'NAMA', 'ANGKA GILIRAN'); $excel_assignment = array(); $totalAssignment = $data['subjekconfigur']->assgmnt_total; foreach ($data['subjekconfigur'] as $rsCol => $rsVal) { if ($rsCol == 'assgmnt_name' && $rsVal != "") { for ($indexAssign = 0; $indexAssign < $totalAssignment; $indexAssign++) { $assgmnt_header_name = strtoupper($rsVal); if (sizeof($totalAssignment > 1)) { $assgmnt_header_name = strtoupper($rsVal) . " " . ($indexAssign + 1); } array_push($excel_header, $assgmnt_header_name); } $assgmnt_name = strtoupper($rsVal); } if ($rsCol == 'assgmnt_id' && $rsVal != "") { for ($indexAssign = 0; $indexAssign < $totalAssignment; $indexAssign++) { array_push($excel_assignment, strtoupper($rsVal)); } } } $filename = 'Markah_pelajar_sem_' . $semselect . '_modul_' . $assgmnt_name . '.xls'; //save our workbook as this file name //load the header into position A1 $this->excel->getActiveSheet()->fromArray($excel_header, NULL, 'A1'); $ttl = 0; $columnCount = 65; //masukkkan markah disini $index = 1; $excel_data = array(); $j = 0; foreach ($data['senaraipelajar'] as $rowData) { $r = array(); array_push($r, $index); foreach ($rowData as $rsCol => $rsVal) { switch ($rsCol) { case "stu_name": array_push($r, " " . strval(name_strtoupper($rsVal))); break; case "stu_matric_no": array_push($r, " " . strval(name_strtoupper($rsVal))); break; case "marks": $j++; for ($i = 0; $i < sizeof($rsVal); $i++) { $rsA = $rsVal[$i]; $bData = false; if (isset($rsA['data'])) { $rsAD = $rsA['data']; $bData = true; if ($excel_assignment[$i] == $rsAD->assgmnt_id) { if ($rsAD->mark != "") { //echo "nilai ada: $j $i |"; if ($rsAD->mark != -99.98999999999999) { array_push($r, $rsAD->mark); } else { array_push($r, 'T'); } } else { array_push($r, 1); } } } if (!$bData) { array_push($r, 1); //echo "nilai : $j $i |"; } } //echo "<br>"; break; } } array_push($excel_data, $r); $index++; } //print_r($excel_data); //load the data into position C4 $this->excel->getActiveSheet()->fromArray($excel_data, NULL, 'A2'); //border fill color for header $style_header = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FFC000')), 'font' => array('bold' => true)); $this->excel->getActiveSheet()->getStyle('A1:' . $abc[sizeof($excel_header) - 1] . '1')->applyFromArray($style_header); //apply the border fill //style to set border $borderStyleArray = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'inside' => array('style' => PHPExcel_Style_Border::BORDER_THIN))); $this->excel->getActiveSheet()->getStyle('A1:' . $abc[sizeof($excel_header) - 1] . $index)->applyFromArray($borderStyleArray); //ni untuk on filter //$this->excel->getActiveSheet()->setAutoFilter('A1:'.$abc[(sizeof($excel_header)-1)].($index)); //$this->excel->getActiveSheet()->getCell('A1:'.$abc[(sizeof($excel_header)-1)].'1')->setAutoSize( true ); //apply the border fill $this->excel->getActiveSheet()->getStyle('D2:D' . $index)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER); //set auto resize for all the columns for ($col = 0; $col <= sizeof($excel_header) - 1; $col++) { $this->excel->getActiveSheet()->getColumnDimension($abc[$col])->setAutoSize(true); } $blocksList = implode(", ", $mark); $objValidation = $this->excel->getActiveSheet()->getCell('D2')->getDataValidation(); /*$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_DECIMAL); $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP); $objValidation->setOperator( PHPExcel_Cell_DataValidation::OPERATOR_LESSTHANOREQUAL); $objValidation->setAllowBlank(true); $objValidation->setShowInputMessage(true); $objValidation->setShowErrorMessage(true); $objValidation->setErrorTitle('Input error'); $objValidation->setError('Only Number is permitted!'); $objValidation->setPromptTitle('Allowed input'); $objValidation->setPrompt('Only numbers between 1.0 and 100.0 are allowed.'); $objValidation->setFormula1(1.0); $objValidation->setFormula2(100.0);*/ $this->excel->getActiveSheet()->removeColumn($abc[sizeof($excel_header) + 1], 1); $this->excel->getActiveSheet()->removeColumn($abc[sizeof($excel_header) + 2], 1); header('Content-Type: application/vnd.ms-excel'); //mime type header('Content-Disposition: attachment;filename="' . $filename . '"'); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension) //if you want to save it as .XLSX Excel 2007 format $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5'); //force user to download the Excel file without writing it to server's HD $objWriter->save('php://output'); }