private function mbatik($data, $title) { $worksheet = new PHPExcel_Worksheet(); $worksheet->setTitle($title); // Siswa Start $worksheet->mergeCells('A3:R3'); $worksheet->setCellValue('A3', 'Data Siswa'); $worksheet->getColumnDimension('A')->setAutoSize(true); $worksheet->SetCellValue('A4', 'No. Pendaftaran'); $worksheet->getColumnDimension('B')->setAutoSize(true); $worksheet->SetCellValue('B4', 'NISN'); $worksheet->getColumnDimension('C')->setAutoSize(true); $worksheet->SetCellValue('C4', 'Nama'); $worksheet->getColumnDimension('D')->setAutoSize(true); $worksheet->SetCellValue('D4', 'Ikhwan/Akhwat'); $worksheet->getColumnDimension('E')->setAutoSize(true); $worksheet->SetCellValue('E4', 'Sekolah Asal'); $worksheet->getColumnDimension('F')->setAutoSize(true); $worksheet->SetCellValue('F4', 'Program'); $worksheet->getColumnDimension('G')->setAutoSize(true); $worksheet->SetCellValue('G4', 'TTL'); $worksheet->getColumnDimension('H')->setAutoSize(true); $worksheet->SetCellValue('H4', 'Alamat'); $worksheet->getColumnDimension('I')->setAutoSize(true); $worksheet->SetCellValue('I4', 'Keluarga'); $worksheet->getColumnDimension('J')->setAutoSize(true); $worksheet->SetCellValue('J4', 'Kewarganegaraan'); $worksheet->getColumnDimension('K')->setAutoSize(true); $worksheet->SetCellValue('K4', 'Agama'); $worksheet->getColumnDimension('L')->setAutoSize(true); $worksheet->SetCellValue('L4', 'Tinggi'); $worksheet->getColumnDimension('M')->setAutoSize(true); $worksheet->SetCellValue('M4', 'Berat'); $worksheet->getColumnDimension('N')->setAutoSize(true); $worksheet->SetCellValue('N4', 'Riwayat Penyakit'); $worksheet->getColumnDimension('O')->setAutoSize(true); $worksheet->SetCellValue('O4', 'Kelainan Jasmani'); $worksheet->getColumnDimension('P')->setAutoSize(true); $worksheet->SetCellValue('P4', 'Tinggal Bersama'); $worksheet->getColumnDimension('Q')->setAutoSize(true); $worksheet->SetCellValue('Q4', 'Hobi'); $worksheet->getColumnDimension('R')->setAutoSize(true); $worksheet->SetCellValue('R4', 'Prestasi'); // End Siswa // Start Ayah $worksheet->mergeCells('S3:AF3'); $worksheet->setCellValue('S3', 'Data Ayah'); $worksheet->getColumnDimension('S')->setAutoSize(true); $worksheet->SetCellValue('S4', 'Nama'); $worksheet->getColumnDimension('T')->setAutoSize(true); $worksheet->SetCellValue('T4', 'Status'); $worksheet->getColumnDimension('U')->setAutoSize(true); $worksheet->SetCellValue('U4', 'TTL'); $worksheet->getColumnDimension('V')->setAutoSize(true); $worksheet->SetCellValue('V4', 'Alamat'); $worksheet->getColumnDimension('W')->setAutoSize(true); $worksheet->SetCellValue('W4', 'No. Telp'); $worksheet->getColumnDimension('X')->setAutoSize(true); $worksheet->SetCellValue('X4', 'Hubungan dengan pendaftar'); $worksheet->getColumnDimension('Y')->setAutoSize(true); $worksheet->SetCellValue('Y4', 'Kewarganegaraan'); $worksheet->getColumnDimension('Z')->setAutoSize(true); $worksheet->SetCellValue('Z4', 'Agama'); $worksheet->getColumnDimension('AA')->setAutoSize(true); $worksheet->SetCellValue('AA4', 'Tingkat Pendidikan'); $worksheet->getColumnDimension('AB')->setAutoSize(true); $worksheet->SetCellValue('AB4', 'Pekerjaan'); $worksheet->getColumnDimension('AC')->setAutoSize(true); $worksheet->SetCellValue('AC4', 'Jabatan'); $worksheet->getColumnDimension('AD')->setAutoSize(true); $worksheet->SetCellValue('AD4', 'Instansi'); $worksheet->getColumnDimension('AE')->setAutoSize(true); $worksheet->SetCellValue('AE4', 'Penghasilan'); $worksheet->getColumnDimension('AF')->setAutoSize(true); $worksheet->SetCellValue('AF4', 'Jumlah Tanggungan'); // End Ayah // Start Ibu $worksheet->mergeCells('AG3:AT3'); $worksheet->setCellValue('AG3', 'Data Ibu'); $worksheet->getColumnDimension('AG')->setAutoSize(true); $worksheet->SetCellValue('AG4', 'Nama'); $worksheet->getColumnDimension('AH')->setAutoSize(true); $worksheet->SetCellValue('AH4', 'Status'); $worksheet->getColumnDimension('AI')->setAutoSize(true); $worksheet->SetCellValue('AI4', 'TTL'); $worksheet->getColumnDimension('AJ')->setAutoSize(true); $worksheet->SetCellValue('AJ4', 'Alamat'); $worksheet->getColumnDimension('AK')->setAutoSize(true); $worksheet->SetCellValue('AK4', 'No. Telp'); $worksheet->getColumnDimension('AL')->setAutoSize(true); $worksheet->SetCellValue('AL4', 'Hubungan dengan pendaftar'); $worksheet->getColumnDimension('AM')->setAutoSize(true); $worksheet->SetCellValue('AM4', 'Kewarganegaraan'); $worksheet->getColumnDimension('AN')->setAutoSize(true); $worksheet->SetCellValue('AN4', 'Agama'); $worksheet->getColumnDimension('AO')->setAutoSize(true); $worksheet->SetCellValue('AO4', 'Tingkat Pendidikan'); $worksheet->getColumnDimension('AP')->setAutoSize(true); $worksheet->SetCellValue('AP4', 'Pekerjaan'); $worksheet->getColumnDimension('AQ')->setAutoSize(true); $worksheet->SetCellValue('AQ4', 'Jabatan'); $worksheet->getColumnDimension('AR')->setAutoSize(true); $worksheet->SetCellValue('AR4', 'Instansi'); $worksheet->getColumnDimension('AS')->setAutoSize(true); $worksheet->SetCellValue('AS4', 'Penghasilan'); $worksheet->getColumnDimension('AT')->setAutoSize(true); $worksheet->SetCellValue('AT4', 'Jumlah Tanggungan'); // End Ibu // Start Wali $worksheet->mergeCells('AU3:BH3'); $worksheet->setCellValue('AU3', 'Data Wali'); $worksheet->getColumnDimension('AU')->setAutoSize(true); $worksheet->SetCellValue('AU4', 'Nama'); $worksheet->getColumnDimension('AV')->setAutoSize(true); $worksheet->SetCellValue('AV4', 'Status'); $worksheet->getColumnDimension('AW')->setAutoSize(true); $worksheet->SetCellValue('AW4', 'TTL'); $worksheet->getColumnDimension('AX')->setAutoSize(true); $worksheet->SetCellValue('AX4', 'Alamat'); $worksheet->getColumnDimension('AY')->setAutoSize(true); $worksheet->SetCellValue('AY4', 'No. Telp'); $worksheet->getColumnDimension('AZ')->setAutoSize(true); $worksheet->SetCellValue('AZ4', 'Hubungan dengan pendaftar'); $worksheet->getColumnDimension('BA')->setAutoSize(true); $worksheet->SetCellValue('BA4', 'Kewarganegaraan'); $worksheet->getColumnDimension('BB')->setAutoSize(true); $worksheet->SetCellValue('BB4', 'Agama'); $worksheet->getColumnDimension('BC')->setAutoSize(true); $worksheet->SetCellValue('BC4', 'Tingkat Pendidikan'); $worksheet->getColumnDimension('BD')->setAutoSize(true); $worksheet->SetCellValue('BD4', 'Pekerjaan'); $worksheet->getColumnDimension('BE')->setAutoSize(true); $worksheet->SetCellValue('BE4', 'Jabatan'); $worksheet->getColumnDimension('BF')->setAutoSize(true); $worksheet->SetCellValue('BF4', 'Instansi'); $worksheet->getColumnDimension('BG')->setAutoSize(true); $worksheet->SetCellValue('BG4', 'Penghasilan'); $worksheet->getColumnDimension('BH')->setAutoSize(true); $worksheet->SetCellValue('BH4', 'Jumlah Tanggungan'); // End Wali // Start Pembayaran $worksheet->mergeCells('BI3:BJ3'); $worksheet->setCellValue('BI3', 'Data Pembayaran'); $worksheet->getColumnDimension('BI')->setAutoSize(true); $worksheet->SetCellValue('BI4', 'Infaq Pendidikan'); $worksheet->getColumnDimension('BJ')->setAutoSize(true); $worksheet->SetCellValue('BJ4', 'SPP'); // End Pembayaran // Start Mbatik Isi $row = 5; foreach ($data as $registrant) { //$registrant = new RegistrantEntity(); $rData = $registrant->getRegistrantData(); // Registrant Data $worksheet->SetCellValue('A' . $row, $registrant->getId()); $worksheet->SetCellValue('B' . $row, $registrant->getNisn()); $worksheet->SetCellValue('C' . $row, $registrant->getName()); $worksheet->SetCellValue('D' . $row, $registrant->getGender() == 'L' ? 'Ikhwan' : 'Akhwat'); $worksheet->SetCellValue('E' . $row, $registrant->getPreviousSchool()); $worksheet->SetCellValue('F' . $row, $registrant->getProgram()); if (!empty($rData)) { $worksheet->SetCellValue('G' . $row, ucfirst($rData->getBirthPlace()) . ', ' . tgl_indo($rData->getBirthDate()->format('Y m d'))); $worksheet->SetCellValue('H' . $row, $rData->getAddress()); $worksheet->SetCellValue('I' . $row, ucwords($rData->getFamilyCondition())); $worksheet->SetCellValue('J' . $row, strtoupper($rData->getNationality())); $worksheet->SetCellValue('K' . $row, ucfirst($rData->getReligion())); $worksheet->SetCellValue('L' . $row, $rData->getHeight()); $worksheet->SetCellValue('M' . $row, $rData->getWeight()); $worksheet->SetCellValue('N' . $row, $rData->getHospitalSheets(false)); $worksheet->SetCellValue('O' . $row, $rData->getPhysicalAbnormalities(false)); $worksheet->SetCellValue('P' . $row, ucwords($rData->getStayWith())); $worksheet->SetCellValue('Q' . $row, $rData->getHobbies(false)); $worksheet->SetCellValue('R' . $row, $rData->getAchievements(false)); } // Registrant Payment $worksheet->SetCellValue('BI' . $row, $registrant->getInitialCost()); $worksheet->SetCellValue('BJ' . $row, $registrant->getSubscriptionCost()); // Registrant Father $fData = $registrant->getFather(); if (!empty($fData)) { $worksheet->SetCellValue('S' . $row, $fData->getName()); $worksheet->SetCellValue('T' . $row, $fData->getStatus()); $worksheet->SetCellValue('U' . $row, ucfirst($fData->getBirthPlace()) . ', ' . tgl_indo($fData->getBirthDate()->format('Y m d'))); $worksheet->SetCellValue('V' . $row, $fData->getAddress()); $worksheet->SetCellValue('W' . $row, $fData->getContact()); $worksheet->SetCellValue('X' . $row, ucwords($fData->getRelation())); $worksheet->SetCellValue('Y' . $row, strtoupper($fData->getNationality())); $worksheet->SetCellValue('Z' . $row, ucwords($fData->getReligion())); $worksheet->SetCellValue('AA' . $row, $fData->getEducationLevel()); $worksheet->SetCellValue('AB' . $row, $fData->getJob()); $worksheet->SetCellValue('AC' . $row, $fData->getPosition()); $worksheet->SetCellValue('AD' . $row, $fData->getCompany()); $worksheet->SetCellValue('AE' . $row, number_format($fData->getIncome(), 0, ',', '.')); $worksheet->SetCellValue('AF' . $row, $fData->getBurdenCount()); } // Registrant Mother $mData = $registrant->getMother(); if (!empty($mData)) { $worksheet->SetCellValue('AG' . $row, $mData->getName()); $worksheet->SetCellValue('AH' . $row, $mData->getStatus()); $worksheet->SetCellValue('AI' . $row, ucfirst($mData->getBirthPlace()) . ', ' . tgl_indo($mData->getBirthDate()->format('Y m d'))); $worksheet->SetCellValue('AJ' . $row, $mData->getAddress()); $worksheet->SetCellValue('AK' . $row, $mData->getContact()); $worksheet->SetCellValue('AL' . $row, ucwords($mData->getRelation())); $worksheet->SetCellValue('AM' . $row, strtoupper($mData->getNationality())); $worksheet->SetCellValue('AN' . $row, ucwords($mData->getReligion())); $worksheet->SetCellValue('AO' . $row, $mData->getEducationLevel()); $worksheet->SetCellValue('AP' . $row, $mData->getJob()); $worksheet->SetCellValue('AQ' . $row, $mData->getPosition()); $worksheet->SetCellValue('AR' . $row, $mData->getCompany()); $worksheet->SetCellValue('AS' . $row, number_format($mData->getIncome(), 0, ',', '.')); $worksheet->SetCellValue('AT' . $row, $mData->getBurdenCount()); } // Registrant Mother $gData = $registrant->getGuardian(); if (!empty($gData)) { $worksheet->SetCellValue('AU' . $row, $gData->getName()); $worksheet->SetCellValue('AV' . $row, $gData->getStatus()); $worksheet->SetCellValue('AW' . $row, ucfirst($gData->getBirthPlace()) . ', ' . tgl_indo($gData->getBirthDate()->format('Y m d'))); $worksheet->SetCellValue('AX' . $row, $gData->getAddress()); $worksheet->SetCellValue('AY' . $row, $gData->getContact()); $worksheet->SetCellValue('AZ' . $row, ucwords($gData->getRelation())); $worksheet->SetCellValue('BA' . $row, strtoupper($gData->getNationality())); $worksheet->SetCellValue('BB' . $row, ucwords($gData->getReligion())); $worksheet->SetCellValue('BC' . $row, $gData->getEducationLevel()); $worksheet->SetCellValue('BD' . $row, $gData->getJob()); $worksheet->SetCellValue('BE' . $row, $gData->getPosition()); $worksheet->SetCellValue('BF' . $row, $gData->getCompany()); $worksheet->SetCellValue('BG' . $row, number_format($gData->getIncome(), 0, ',', '.')); $worksheet->SetCellValue('BH' . $row, $gData->getBurdenCount()); } // Iteration of Rows $row++; } // End Mbatik Isi $this->excel->addSheet($worksheet); }