Multiple Looping PHPExcel

public function excel($tahun, $program_id)

{

$pejabat = $this->db->get_where('sird_contact', array('contact_id' => 1))->row();

$tahun = $tahun;

if ($program_id == 'all') {

$listProgram = $this->db->get_where('sird_program', array('program_year' => $tahun))->result();

} else {

$listProgram = $this->db->get_where('sird_program', array('program_id' => $program_id))->result();

}

$objPHPExcel = new PHPExcel();

$objPHPExcel->getProperties()->setCreator("Jama' Rochmad Muttaqin")

->setLastModifiedBy("Laporan Realisasi Capaian Kegiatan")

->setTitle("Data Laporan Realisasi Capaian Kegiatan")

->setSubject("Data Laporan Realisasi Capaian Kegiatan");

$objPHPExcel->setActiveSheetIndex(0);

$objPHPExcel->getActiveSheet()->setCellValue('A6', "NO")

->setCellValue('B6', "PROGRAM DAN KEGIATAN")

->setCellValue('C6', "PAGU ANGGARAN (Rp)")

->setCellValue('D6', "REALISASI ANGGARAN (Rp)")

->setCellValue('E6', "SISA ANGGARAN (Rp)")

->setCellValue('F6', "TARGET")

->setCellValue('G6', "REALISASI")

->setCellValue('H6', "KETERANGAN");

$dataArray = array();

$no = 0;

foreach ($listProgram as $r) {

$no++;

$row_array['no'] = '';

$row_array['program'] = $r->program_name;

$row_array['c'] = '';

$row_array['d'] = '';

$row_array['e'] = '';

$row_array['f'] = '';

$row_array['g'] = '';

$row_array['h'] = '';

array_push($dataArray, $row_array);

// Sub Program

$nosub = 0;

$program_id = $r->program_id;

$listSubProgram = $this->db->get_where('sird_subprogram', array('program_id' => $program_id))->result();

foreach ($listSubProgram as $s) {

$subprogram_id = $s->subprogram_id;

$nosub++;

$row_array['no'] = $nosub;

$row_array['program'] = $s->subprogram_name;

$row_array['c'] = '';

$row_array['d'] = '';

$row_array['e'] = '';

$row_array['f'] = '';

$row_array['g'] = '';

$row_array['h'] = '';

array_push($dataArray, $row_array);

// Kegiatan

$nokegiatan = 0;

$listKegiatan = $this->db->get_where('sird_kegiatan', array('program_id' => $program_id, 'subprogram_id' => $subprogram_id))->result();

foreach ($listKegiatan as $k) {

$nokegiatan++;

$row_array['no'] = '';

$row_array['program'] = ucwords(strtolower($k->kegiatan_name));

$row_array['c'] = number_format($k->kegiatan_pagu, 0, '', ',');

$row_array['d'] = number_format($k->kegiatan_real, 0, '', ',');

$row_array['e'] = number_format($k->kegiatan_sisa, 0, '', ',');

$row_array['f'] = ucwords(strtolower($k->kegiatan_target));

$row_array['g'] = ucwords(strtolower($k->kegiatan_realisasi));

$row_array['h'] = ucwords(strtolower($k->kegiatan_keterangan));

array_push($dataArray, $row_array);

}

$nox = $no + 6;

$objPHPExcel->getActiveSheet()->fromArray($dataArray, null, 'A7');

// Set page orientation and size

$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);

$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

$objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.50);

$objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.50);

$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.50);

$objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.50);

$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');

$objPHPExcel->getActiveSheet()->getStyle('A6:H6')->getFont()->setBold(true);

$objPHPExcel->getActiveSheet()->getStyle('A6:H6')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);

$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);

$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);

$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);

$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);

$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);

$objPHPExcel->setActiveSheetIndex(0);

$sharedStyle1 = new PHPExcel_Style();

$sharedStyle2 = new PHPExcel_Style();

$sharedStyle1->applyFromArray(

array(

'borders' => array(

'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),

'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),

'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN),

'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN),

),

));

$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A6:H$nox");

$objPHPExcel->getActiveSheet()->getRowDimension('6')->setRowHeight(40);

$objPHPExcel->getActiveSheet()->getStyle('A6:H6')->applyFromArray(

array(

'font' => array(

'bold' => true,

),

'alignment' => array(

'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,

'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,

),

'borders' => array(

'top' => array(

'style' => PHPExcel_Style_Border::BORDER_THIN,

),

)

);

$objPHPExcel->getActiveSheet()->getStyle('C6:E6')->getAlignment()->setWrapText(true);

$objPHPExcel->getActiveSheet()->getStyle('A6:A1000')->getAlignment()->setWrapText(true);

$objPHPExcel->getActiveSheet()->getStyle('B6:B1000')->getAlignment()->setWrapText(true);

$objPHPExcel->getActiveSheet()->getStyle('A4:H1000')->getFont()->setName('Times New Roman');

$objPHPExcel->getActiveSheet()->getStyle('A4:H1000')->getFont()->setSize(10);

// Merge cells

$objPHPExcel->getActiveSheet()->mergeCells('A2:H2');

$objPHPExcel->getActiveSheet()->setCellValue('A2', "REALISASI CAPAIAN KINERJA KEGIATAN TAHUN ANGGARAN " . $tahun);

$objPHPExcel->getActiveSheet()->mergeCells('A3:H3');

$objPHPExcel->getActiveSheet()->setCellValue('A3', "DINAS PERUMAHAN RAKYAT DAN KAWASAN PERMUKIMAN KABUPATEN JEPARA");

$objPHPExcel->getActiveSheet()->mergeCells('A4:H4');

// $objPHPExcel->getActiveSheet()->setCellValue('A4', "TAHUN : " . $Tahun1 . " s/d " . $Tahun2);

$objPHPExcel->getActiveSheet()->getStyle()->getAlignment()->setWrapText(true);

$objPHPExcel->getActiveSheet()->getStyle('A2:H4')->getFont()->setName('Times New Roman');

$objPHPExcel->getActiveSheet()->getStyle('A2:H4')->getFont()->setSize(10);

$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setSize(10);

$objPHPExcel->getActiveSheet()->getStyle('A4')->getFont()->setSize(10);

$objPHPExcel->getActiveSheet()->getStyle('A2:H6')->getFont()->setBold(true);

$objPHPExcel->getActiveSheet()->getStyle('A2:H6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('A7:A1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('B7:B1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

$objPHPExcel->getActiveSheet()->getStyle('C7:C1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

$objPHPExcel->getActiveSheet()->getStyle('D7:D1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

$objPHPExcel->getActiveSheet()->getStyle('E7:E1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

$objPHPExcel->getActiveSheet()->getStyle('F7:F1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

$objPHPExcel->getActiveSheet()->getStyle('G7:G1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

$objPHPExcel->getActiveSheet()->getStyle('H7:H1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

$objPHPExcel->getActiveSheet()->getStyle('A7:A1000')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

$objPHPExcel->getActiveSheet()->getStyle('C7:C1000')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

$objPHPExcel->getActiveSheet()->getStyle('D7:D1000')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

$objPHPExcel->getActiveSheet()->getStyle('E7:E1000')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

$objPHPExcel->getActiveSheet()->getStyle('F7:F1000')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

$objPHPExcel->getActiveSheet()->getStyle('G7:G1000')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

$objPHPExcel->getActiveSheet()->getStyle('H7:H1000')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

$date = date('Y-m-d');

$time = time();

$objWriter->save('download/Data_Laporan_' . $date . '_' . $time . '.xlsx');

redirect(base_url('download/Data_Laporan_' . $date . '_' . $time . '.xlsx'));

}