abel/shopee-apps
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
shopee-apps/application/views/cetak/PR_Excel_Sub1_DataRekapProyek.php
T
abel db327c0305 copy dari repo om Irfan
2026-06-13 16:02:07 +10:00

1344 lines
69 KiB
PHP
Raw Permalink Blame History

<?php
require(APPPATH. 'PHPExcel-1.8/Classes/PHPExcel.php');
require(APPPATH. 'PHPExcel-1.8/Classes/PHPExcel/Writer/Excel2007.php');
use PHPExcel\Style\PHPExcel_Style_Alignment;
use PHPExcel\Style\PHPExcel_Style_Fill;
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("PC Master");
$objPHPExcel->getProperties()->setLastModifiedBy("PC Master");
$objPHPExcel->getProperties()->setTitle("Laporan");
$objPHPExcel->getProperties()->setSubject("");
$objPHPExcel->getProperties()->setDescription("");
$objPHPExcel->setActiveSheetIndex(0);
if ($model==1) {
$objPHPExcel->getActiveSheet()->setTitle("$nama_kuartal");
//font
$objPHPExcel->getDefaultStyle()
->getFont()
->setName("Calibri")
->setSize(11);
//heading
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Laporan Rekap Proyek');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Periode '.$nama_kuartal.' : ['.$awal.' - '.$akhir.']');
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Sales : ('.$sales.')');
///Setting Judul
$objPHPExcel->getActiveSheet()->mergeCells("A1:I1");
$objPHPExcel->getActiveSheet()->mergeCells("A2:I2");
$objPHPExcel->getActiveSheet()->mergeCells("A3:I3");
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('A2:A3')->getFont()->setSize(16);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setRGB('9400D3');
$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->getColor()->setRGB('008080');
$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->getColor()->setRGB('B22222');
//set cell alignment
$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('I')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
//lebar kolom
$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);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
//font style
$objPHPExcel->getActiveSheet()->getStyle('A5:I5')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('A5:I5')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'No');
$objPHPExcel->getActiveSheet()->setCellValue('B5', 'Pelanggan');
$objPHPExcel->getActiveSheet()->setCellValue('C5', 'Kode');
$objPHPExcel->getActiveSheet()->setCellValue('D5', 'Nama');
$objPHPExcel->getActiveSheet()->setCellValue('E5', 'SO [Nomor PO]');
$objPHPExcel->getActiveSheet()->setCellValue('F5', 'Nota Terbit');
$objPHPExcel->getActiveSheet()->setCellValue('G5', 'Biaya Proyek');
$objPHPExcel->getActiveSheet()->setCellValue('H5', 'Nilai GP');
$objPHPExcel->getActiveSheet()->setCellValue('I5', 'Presentase');
$objPHPExcel->getActiveSheet()->getStyle('A5:I5')->getFill()->applyFromArray (
array(
'type' => 'FILL_SOLID',
'rotation' => 0,
'startcolor' => array(
'rgb' => '9fc5e8'
),
'endcolor' => array(
'argb' => 'FFFFFFFF'
)
)
);
$baris = 6;
$no = 1;
$sel = '';
$selprev = '-';
$b = 0;
$selsal = '-';
$s = 0;
$barissal = 6;
if ($laporan != null) {
if (is_array($laporan) || is_object($laporan)) {
foreach ($laporan as $key) {
$so = $key['KODE_SO']; $po = $key['PO_SO'];
if ($key['PO_SO']==NULL OR $key['PO_SO']=="") {
$sopo = "$so";
} else {
$sopo = "$so [$po]";
}
if ($key['KODE_PROYEK'] == $selprev) {
$b++;
$objPHPExcel->getActiveSheet()->setCellValue('E'.$baris, $sopo);
$objPHPExcel->getActiveSheet()->setCellValue('F'.$baris, $key['NOTA']);
$selprev = $key['KODE_PROYEK'];
} else {
if ($b > 0) {
$bb = $baris-1;
$bar = $bb-$b;
//merge heading
$objPHPExcel->getActiveSheet()->mergeCells('A'.$bar.':A'.$bb);
$objPHPExcel->getActiveSheet()->mergeCells('B'.$bar.':B'.$bb);
$objPHPExcel->getActiveSheet()->mergeCells('C'.$bar.':C'.$bb);
$objPHPExcel->getActiveSheet()->mergeCells('D'.$bar.':D'.$bb);
$objPHPExcel->getActiveSheet()->mergeCells('G'.$bar.':G'.$bb);
$objPHPExcel->getActiveSheet()->mergeCells('H'.$bar.':H'.$bb);
$objPHPExcel->getActiveSheet()->mergeCells('I'.$bar.':I'.$bb);
}
$b = 0;
$objPHPExcel->getActiveSheet()->setCellValue('A'.$baris, $no++);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$baris, $key['NAMA_PELANGGAN']);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$baris, $key['KODE_PROYEK']);
$objPHPExcel->getActiveSheet()->setCellValue('D'.$baris, $key['PO_PROYEK']);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$baris, $sopo);
$objPHPExcel->getActiveSheet()->setCellValue('F'.$baris, $key['NOTA']);
$objPHPExcel->getActiveSheet()->setCellValue('G'.$baris, $key['HPP']);
$objPHPExcel->getActiveSheet()->setCellValue('H'.$baris, $key['GP']);
$objPHPExcel->getActiveSheet()->setCellValue('I'.$baris, $key['PERCENT']);
$selprev = $key['KODE_PROYEK'];
}
$sel='I'.$baris;
$baris++;
}
}
$objPHPExcel->getActiveSheet()->getStyle('A5:'.$sel)->getBorders()->applyFromArray(
array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array(
'rgb' => '000000'
)
)
)
);
}
$bb = $baris - 1;
$bar = $bb - $b;
//merge heading
$objPHPExcel->getActiveSheet()->mergeCells('A'.$bar.':A'.$bb);
$objPHPExcel->getActiveSheet()->mergeCells('B'.$bar.':B'.$bb);
$objPHPExcel->getActiveSheet()->mergeCells('C'.$bar.':C'.$bb);
$objPHPExcel->getActiveSheet()->mergeCells('D'.$bar.':D'.$bb);
$objPHPExcel->getActiveSheet()->mergeCells('G'.$bar.':G'.$bb);
$objPHPExcel->getActiveSheet()->mergeCells('H'.$bar.':H'.$bb);
$objPHPExcel->getActiveSheet()->mergeCells('I'.$bar.':I'.$bb);
//Subtotal
$objPHPExcel->getActiveSheet()->freezePane('A6');
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':I'.$baris)->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':I'.$baris)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->mergeCells('A'.$baris.':E'.$baris);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$baris, "TOTAL");
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->setCellValue('F'.$baris, "$totNota");
$objPHPExcel->getActiveSheet()->setCellValue('G'.$baris, "$totBiaya");
$objPHPExcel->getActiveSheet()->setCellValue('H'.$baris, "$totGp");
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':I'.$baris)->getFill()->applyFromArray (
array(
'type' => 'FILL_SOLID',
'rotation' => 0,
'startcolor' => array(
'rgb' => 'cfe2f3'
),
'endcolor' => array(
'argb' => 'FFFFFFFF'
)
)
);
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':I'.$baris)->getBorders()->applyFromArray(
array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array(
'rgb' => '000000'
)
)
)
);
//Worksheet untuk Detail Barang
$index = 1;
$kode_proyek = NULL;
foreach ($laporan as $key) {
if ($kode_proyek!=$key['KODE_PROYEK']) {
$kode_proyek = $key['KODE_PROYEK'];
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($index);
$objPHPExcel->getActiveSheet()->setTitle("$index");
//font
$objPHPExcel->getDefaultStyle()
->getFont()
->setName("Calibri")
->setSize(12);
//heading
$objPHPExcel->getActiveSheet()->setCellValue('A1', ''.$key['PO_PROYEK'].'');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Pelanggan : ('.$key['NAMA_PELANGGAN'].')');
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Tanggal ['.$key['TANGGAL'].']');
//merge heading
$objPHPExcel->getActiveSheet()->mergeCells("A1:C1");
$objPHPExcel->getActiveSheet()->mergeCells("A2:C2");
$objPHPExcel->getActiveSheet()->mergeCells("A3:C3");
///font heading
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16);
$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(15);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setSize(15);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('A5:B23')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('C5:C23')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('I')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('J')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('L')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('M')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('N')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('O')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
//lebar kolom
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(4);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(4);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setAutoSize(true);
//Isi Tabel
$objPHPExcel->getActiveSheet()->getStyle('A5:B5')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('A5:C5')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A5')->getFont()->getColor()->setRGB('4C9D3B');
$objPHPExcel->getActiveSheet()->mergeCells("A5:B5");
$objPHPExcel->getActiveSheet()->getStyle('C5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'PREDIKSI PROYEK');
$objPHPExcel->getActiveSheet()->setCellValue('C5', $key['NILAIPREDI']);
$objPHPExcel->getActiveSheet()->getStyle('A6:B6')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('A6:C6')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A6')->getFont()->getColor()->setRGB('4C9D3B');
$objPHPExcel->getActiveSheet()->mergeCells("A6:B6");
$objPHPExcel->getActiveSheet()->getStyle('C6')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->setCellValue('A6', 'NILAI PROYEK');
$objPHPExcel->getActiveSheet()->setCellValue('C6', $key['NILAISOTOT']);
//HPP Proyek
$objPHPExcel->getActiveSheet()->getStyle('A8:C8')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('A8:C8')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A8')->getFont()->getColor()->setRGB('4C9D3B');
$objPHPExcel->getActiveSheet()->mergeCells("A8:C8");
$objPHPExcel->getActiveSheet()->setCellValue('A8', 'BIAYA PROYEK');
$objPHPExcel->getActiveSheet()->setCellValue('A9', '1');
$objPHPExcel->getActiveSheet()->setCellValue('B9', 'Barang SO');
$objPHPExcel->getActiveSheet()->setCellValue('C9', $key['BARANGSO']);
$objPHPExcel->getActiveSheet()->setCellValue('A10', '2');
$objPHPExcel->getActiveSheet()->setCellValue('B10', 'Barang Proyek');
$objPHPExcel->getActiveSheet()->setCellValue('C10', $key['BARANGPR']);
$objPHPExcel->getActiveSheet()->setCellValue('A11', '3');
$objPHPExcel->getActiveSheet()->setCellValue('B11', 'Biaya Fee');
$objPHPExcel->getActiveSheet()->setCellValue('C11', $key['BIAYAFEE']);
$objPHPExcel->getActiveSheet()->setCellValue('A12', '4');
$objPHPExcel->getActiveSheet()->setCellValue('B12', 'Biaya Kasbank');
$objPHPExcel->getActiveSheet()->setCellValue('C12', $key['BIAYAPR']);
$objPHPExcel->getActiveSheet()->setCellValue('A13', '5');
$objPHPExcel->getActiveSheet()->setCellValue('B13', 'Biaya Lainnya');
$objPHPExcel->getActiveSheet()->setCellValue('C13', $key['BIAYALAIN']);
$objPHPExcel->getActiveSheet()->getStyle('A14:B14')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('A14:C14')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A14')->getFont()->getColor()->setRGB('5B5B5B');
$objPHPExcel->getActiveSheet()->mergeCells("A14:B14");
$objPHPExcel->getActiveSheet()->setCellValue('A14', 'Subtotal');
$objPHPExcel->getActiveSheet()->getStyle('A14')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->setCellValue('C14', $key['HPP']);
//GP Proyek
$objPHPExcel->getActiveSheet()->getStyle('A16:B16')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('A16:C16')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A16')->getFont()->getColor()->setRGB('4C9D3B');
$objPHPExcel->getActiveSheet()->getStyle('C16')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->mergeCells("A16:B16");
$objPHPExcel->getActiveSheet()->setCellValue('A16', 'GP PROYEK');
$objPHPExcel->getActiveSheet()->setCellValue('C16', $key['GP']);
$objPHPExcel->getActiveSheet()->getStyle('A17:B17')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('A17:C17')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A17')->getFont()->getColor()->setRGB('F44336');
$objPHPExcel->getActiveSheet()->mergeCells("A17:B17");
$objPHPExcel->getActiveSheet()->setCellValue('A17', '(Nilai Proyek - HPP Proyek)');
//Presentase GP
$objPHPExcel->getActiveSheet()->getStyle('A19:B19')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('A19:C19')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A19')->getFont()->getColor()->setRGB('4C9D3B');
$objPHPExcel->getActiveSheet()->getStyle('C19')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->mergeCells("A19:B19");
$objPHPExcel->getActiveSheet()->setCellValue('A19', 'PRESENTASE GP');
$objPHPExcel->getActiveSheet()->setCellValue('C19', $key['PERCENT']);
$objPHPExcel->getActiveSheet()->getStyle('A20:B20')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('A20:C20')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A20')->getFont()->getColor()->setRGB('F44336');
$objPHPExcel->getActiveSheet()->mergeCells("A20:B20");
$objPHPExcel->getActiveSheet()->setCellValue('A20', '((GP Proyek/HPP Proyek)*100%)');
//Tabel Untuk Detail Proyek
//Start Nilai Proyek
$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setRGB('4E0090');
$objPHPExcel->getActiveSheet()->getStyle('E1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->mergeCells("E1:J1");
$objPHPExcel->getActiveSheet()->setCellValue('E1', 'NILAI PROYEK');
$objPHPExcel->getActiveSheet()->getStyle('E2:J2')->getFont()->setSize(13);
$objPHPExcel->getActiveSheet()->getStyle('E2:J2')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('E2', 'No');
$objPHPExcel->getActiveSheet()->setCellValue('F2', 'Nomor SO');
$objPHPExcel->getActiveSheet()->setCellValue('G2', 'Nomor PO');
$objPHPExcel->getActiveSheet()->setCellValue('H2', 'Nilai Nota');
$objPHPExcel->getActiveSheet()->setCellValue('I2', 'Nilai Fee');
$objPHPExcel->getActiveSheet()->setCellValue('J2', 'Subtotal');
$objPHPExcel->getActiveSheet()->getStyle('E2:J2')->getFill()->applyFromArray (
array(
'type' => 'FILL_SOLID',
'rotation' => 0,
'startcolor' => array(
'rgb' => 'D6B4F4'
),
'endcolor' => array(
'argb' => 'FFFFFFFF'
)
)
);
$baris = 3;
$start = $baris-1;
$nodat = 1;
foreach ($key['NIPRO'] as $dat) {
$objPHPExcel->getActiveSheet()->setCellValue('E'.$baris, $nodat++);
$objPHPExcel->getActiveSheet()->setCellValue('F'.$baris, $dat['KODE_SO']);
$objPHPExcel->getActiveSheet()->setCellValue('G'.$baris, $dat['PO_SO']);
$objPHPExcel->getActiveSheet()->setCellValue('H'.$baris, $dat['NILAISO']);
$objPHPExcel->getActiveSheet()->setCellValue('I'.$baris, $dat['FEE']);
$objPHPExcel->getActiveSheet()->setCellValue('J'.$baris++, $dat['NILAISUB']);
}
$objPHPExcel->getActiveSheet()->getStyle('G'.$start.':G'.$baris.'')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('H'.$start.':H'.$baris.'')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('I'.$start.':I'.$baris.'')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('J'.$start.':J'.$baris.'')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->mergeCells('E'.$baris.':I'.$baris.'');
$objPHPExcel->getActiveSheet()->setCellValue('E'.$baris, 'TOTAL');
$objPHPExcel->getActiveSheet()->setCellValue('J'.$baris, $key['NILAISOTOT']);
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris.':J'.$baris.'')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('E'.$start.':J'.$baris)->getBorders()->applyFromArray(
array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array(
'rgb' => '000000'
)
)
)
);
//End Nilai Proyek
$baris = $baris+2;
//Start Barang SO
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris)->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris)->getFont()->getColor()->setRGB('066373');
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->mergeCells('E'.$baris.':J'.$baris.'');
$objPHPExcel->getActiveSheet()->setCellValue('E'.$baris++, 'BARANG SO');
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris.':J'.$baris.'')->getFont()->setSize(13);
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris.':J'.$baris.'')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$baris, 'No');
$objPHPExcel->getActiveSheet()->setCellValue('F'.$baris, 'Jenis');
$objPHPExcel->getActiveSheet()->setCellValue('G'.$baris, 'Kode Barang');
$objPHPExcel->getActiveSheet()->setCellValue('H'.$baris, 'Kuantiti');
$objPHPExcel->getActiveSheet()->setCellValue('I'.$baris, 'DPP (Average)');
$objPHPExcel->getActiveSheet()->setCellValue('J'.$baris, 'Subtotal');
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris.':J'.$baris++.'')->getFill()->applyFromArray (
array(
'type' => 'FILL_SOLID',
'rotation' => 0,
'startcolor' => array(
'rgb' => 'c0d6e4'
),
'endcolor' => array(
'argb' => 'FFFFFFFF'
)
)
);
$start = $baris-1;
if ($key['BASO']!=NULL) {
$nodit = 1;
foreach ($key['BASO'] as $dit) {
$objPHPExcel->getActiveSheet()->setCellValue('E'.$baris, $nodit++);
$objPHPExcel->getActiveSheet()->setCellValue('F'.$baris, $dit['JENIS']);
$objPHPExcel->getActiveSheet()->setCellValue('G'.$baris, $dit['KODE']);
$objPHPExcel->getActiveSheet()->setCellValue('H'.$baris, $dit['QTY']);
$objPHPExcel->getActiveSheet()->setCellValue('I'.$baris, $dit['HARGA']);
$objPHPExcel->getActiveSheet()->setCellValue('J'.$baris++, $dit['TOTAVG']);
}
}
$objPHPExcel->getActiveSheet()->getStyle('G'.$start.':G'.$baris.'')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('I'.$start.':I'.$baris.'')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('J'.$start.':J'.$baris.'')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->mergeCells('E'.$baris.':I'.$baris.'');
$objPHPExcel->getActiveSheet()->setCellValue('E'.$baris, 'TOTAL');
$objPHPExcel->getActiveSheet()->setCellValue('J'.$baris, $key['BARANGSO']);
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris.':J'.$baris.'')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('E'.$start.':J'.$baris)->getBorders()->applyFromArray(
array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array(
'rgb' => '000000'
)
)
)
);
//End Barang SO
$baris = $baris+2;
//Start Barang Proyek
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris)->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris)->getFont()->getColor()->setRGB('F44336');
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->mergeCells('E'.$baris.':I'.$baris.'');
$objPHPExcel->getActiveSheet()->setCellValue('E'.$baris++, 'BARANG PROYEK');
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris.':I'.$baris.'')->getFont()->setSize(13);
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris.':I'.$baris.'')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$baris, 'No');
$objPHPExcel->getActiveSheet()->setCellValue('F'.$baris, 'Kode Barang');
$objPHPExcel->getActiveSheet()->setCellValue('G'.$baris, 'Kuantiti');
$objPHPExcel->getActiveSheet()->setCellValue('H'.$baris, 'DPP (Average)');
$objPHPExcel->getActiveSheet()->setCellValue('I'.$baris, 'Subtotal');
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris.':I'.$baris++.'')->getFill()->applyFromArray (
array(
'type' => 'FILL_SOLID',
'rotation' => 0,
'startcolor' => array(
'rgb' => 'F4CCCC'
),
'endcolor' => array(
'argb' => 'FFFFFFFF'
)
)
);
$start = $baris-1;
if ($key['BAPRO']!=NULL) {
$nodut = 1;
foreach ($key['BAPRO'] as $dut) {
$objPHPExcel->getActiveSheet()->setCellValue('E'.$baris, $nodut++);
$objPHPExcel->getActiveSheet()->setCellValue('F'.$baris, $dut['KODE_BARANG']);
$objPHPExcel->getActiveSheet()->setCellValue('G'.$baris, $dut['KELUAR_HQ']);
$objPHPExcel->getActiveSheet()->setCellValue('H'.$baris, $dut['AVG']);
$objPHPExcel->getActiveSheet()->setCellValue('I'.$baris++, $dut['SUBTOTPRO']);
}
}
$objPHPExcel->getActiveSheet()->getStyle('F'.$start.':F'.$baris.'')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('H'.$start.':H'.$baris.'')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('I'.$start.':I'.$baris.'')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->mergeCells('E'.$baris.':H'.$baris.'');
$objPHPExcel->getActiveSheet()->setCellValue('E'.$baris, 'TOTAL');
$objPHPExcel->getActiveSheet()->setCellValue('I'.$baris, $key['BARANGPR']);
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris.':I'.$baris.'')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('E'.$baris)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('E'.$start.':I'.$baris)->getBorders()->applyFromArray(
array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array(
'rgb' => '000000'
)
)
)
);
//End Barang Proyek
//Start Biaya Kasbank
$baris = 1;
$objPHPExcel->getActiveSheet()->getStyle('L'.$baris)->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('L'.$baris)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('L'.$baris)->getFont()->getColor()->setRGB('6AA84F');
$objPHPExcel->getActiveSheet()->getStyle('L'.$baris)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->mergeCells('L'.$baris.':N'.$baris.'');
$objPHPExcel->getActiveSheet()->setCellValue('L'.$baris++, 'BIAYA KASBANK');
$objPHPExcel->getActiveSheet()->getStyle('L'.$baris.':N'.$baris.'')->getFont()->setSize(13);
$objPHPExcel->getActiveSheet()->getStyle('L'.$baris.':N'.$baris.'')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('L'.$baris, 'No');
$objPHPExcel->getActiveSheet()->setCellValue('M'.$baris, 'Keterangan');
$objPHPExcel->getActiveSheet()->setCellValue('N'.$baris, 'Nilai');
$objPHPExcel->getActiveSheet()->getStyle('L'.$baris.':N'.$baris++.'')->getFill()->applyFromArray (
array(
'type' => 'FILL_SOLID',
'rotation' => 0,
'startcolor' => array(
'rgb' => 'B6D7A8'
),
'endcolor' => array(
'argb' => 'FFFFFFFF'
)
)
);
$start = $baris-1;
if ($key['BIPRO']!=NULL) {
$nodet = 1;
foreach ($key['BIPRO'] as $det) {
$objPHPExcel->getActiveSheet()->setCellValue('L'.$baris, $nodet++);
$objPHPExcel->getActiveSheet()->setCellValue('M'.$baris, $det['KET_DAK']);
$objPHPExcel->getActiveSheet()->setCellValue('N'.$baris++, $det['DEBET']);
}
}
$objPHPExcel->getActiveSheet()->getStyle('M'.$start.':M'.$baris.'')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('N'.$start.':N'.$baris.'')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->mergeCells('L'.$baris.':M'.$baris.'');
$objPHPExcel->getActiveSheet()->setCellValue('L'.$baris, 'TOTAL');
$objPHPExcel->getActiveSheet()->setCellValue('N'.$baris, $key['BIAYAPR']);
$objPHPExcel->getActiveSheet()->getStyle('L'.$baris.':N'.$baris.'')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('L'.$baris)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('L'.$start.':N'.$baris)->getBorders()->applyFromArray(
array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array(
'rgb' => '000000'
)
)
)
);
//End Biaya Kasbank
$baris = $baris+2;
//Start Biaya Proyek
$objPHPExcel->getActiveSheet()->getStyle('L'.$baris)->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('L'.$baris)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('L'.$baris)->getFont()->getColor()->setRGB('444444');
$objPHPExcel->getActiveSheet()->getStyle('L'.$baris)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->mergeCells('L'.$baris.':N'.$baris.'');
$objPHPExcel->getActiveSheet()->setCellValue('L'.$baris++, 'BIAYA PROYEK');
$objPHPExcel->getActiveSheet()->getStyle('L'.$baris.':N'.$baris.'')->getFont()->setSize(13);
$objPHPExcel->getActiveSheet()->getStyle('L'.$baris.':N'.$baris.'')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('L'.$baris, 'No');
$objPHPExcel->getActiveSheet()->setCellValue('M'.$baris, 'Keterangan');
$objPHPExcel->getActiveSheet()->setCellValue('N'.$baris, 'Nilai');
$objPHPExcel->getActiveSheet()->getStyle('L'.$baris.':N'.$baris++.'')->getFill()->applyFromArray (
array(
'type' => 'FILL_SOLID',
'rotation' => 0,
'startcolor' => array(
'rgb' => 'EEEEEE'
),
'endcolor' => array(
'argb' => 'FFFFFFFF'
)
)
);
$start = $baris-1;
if ($key['BILAIN']!=NULL) {
$nodut = 1;
foreach ($key['BILAIN'] as $dut) {
$objPHPExcel->getActiveSheet()->setCellValue('L'.$baris, $nodut++);
$objPHPExcel->getActiveSheet()->setCellValue('M'.$baris, $dut['NAMA_BPRY']);
$objPHPExcel->getActiveSheet()->setCellValue('N'.$baris++, $dut['NILAI']);
}
}
$objPHPExcel->getActiveSheet()->getStyle('M'.$start.':M'.$baris.'')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('N'.$start.':N'.$baris.'')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->mergeCells('L'.$baris.':M'.$baris.'');
$objPHPExcel->getActiveSheet()->setCellValue('L'.$baris, 'TOTAL');
$objPHPExcel->getActiveSheet()->setCellValue('N'.$baris, $key['BIAYALAIN']);
$objPHPExcel->getActiveSheet()->getStyle('L'.$baris.':N'.$baris.'')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('L'.$baris)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('L'.$start.':N'.$baris)->getBorders()->applyFromArray(
array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array(
'rgb' => '000000'
)
)
)
);
//End Biaya Proyek
$index++;
}
}
$filename = "Laporan Rekap Proyek (".$sales.") - ".$nama_kuartal." ". $ts. '.xlsx';
} else if ($model==2) {
$objPHPExcel->getActiveSheet()->setTitle("$nama_grup");
//font
$objPHPExcel->getDefaultStyle()
->getFont()
->setName("Calibri")
->setSize(12);
//heading
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'PO : '.$po_proyek.'');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Pelanggan : ('.$nama_pelanggan.')');
$objPHPExcel->getActiveSheet()->setCellValue('A3', ''.$nama_kuartal.'');
$objPHPExcel->getActiveSheet()->setCellValue('A4', 'Periode ['.$awal.' s/d '.$akhir.']');
//merge heading
$objPHPExcel->getActiveSheet()->mergeCells("A1:D1");
$objPHPExcel->getActiveSheet()->mergeCells("A2:D2");
$objPHPExcel->getActiveSheet()->mergeCells("A3:D3");
$objPHPExcel->getActiveSheet()->mergeCells("A4:D4");
///font heading
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16);
$objPHPExcel->getActiveSheet()->getStyle('A2:A4')->getFont()->setSize(15);
$objPHPExcel->getActiveSheet()->getStyle('A1:A4')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1:A4')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('A5:B28')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('C5:C28')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setRGB('9400D3');
$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->getColor()->setRGB('008080');
$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->getColor()->setRGB('B22222');
$objPHPExcel->getActiveSheet()->getStyle('A4')->getFont()->getColor()->setRGB('0000FF');
//lebar kolom
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(37);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(5);
//Isi Tabel
$objPHPExcel->getActiveSheet()->getStyle('A6:B6')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('A6:C6')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A6')->getFont()->getColor()->setRGB('4C9D3B');
$objPHPExcel->getActiveSheet()->mergeCells("A6:B6");
$objPHPExcel->getActiveSheet()->getStyle('C6')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->setCellValue('A6', 'NILAI PROYEK');
$objPHPExcel->getActiveSheet()->setCellValue('C6', $nilaiProyek);
$objPHPExcel->getActiveSheet()->getStyle('A8:C8')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('A8:C8')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A8')->getFont()->getColor()->setRGB('4C9D3B');
$objPHPExcel->getActiveSheet()->mergeCells("A8:C8");
$objPHPExcel->getActiveSheet()->setCellValue('A8', 'BIAYA PROYEK SEBELUMNYA');
$objPHPExcel->getActiveSheet()->setCellValue('A9', '1');
$objPHPExcel->getActiveSheet()->setCellValue('B9', 'Barang SO');
$objPHPExcel->getActiveSheet()->setCellValue('C9', $totBarangSoAwal);
$objPHPExcel->getActiveSheet()->setCellValue('D9', '+');
$objPHPExcel->getActiveSheet()->getStyle('D9')->getFont()->setSize(16);
$objPHPExcel->getActiveSheet()->getStyle('D9')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('A10', '2');
$objPHPExcel->getActiveSheet()->setCellValue('B10', 'Barang Proyek');
$objPHPExcel->getActiveSheet()->setCellValue('C10', $totBarangProyekAwal);
$objPHPExcel->getActiveSheet()->setCellValue('D10', '+');
$objPHPExcel->getActiveSheet()->getStyle('D10')->getFont()->setSize(16);
$objPHPExcel->getActiveSheet()->getStyle('D10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('A11', '3');
$objPHPExcel->getActiveSheet()->setCellValue('B11', 'Biaya Fee');
$objPHPExcel->getActiveSheet()->setCellValue('C11', $totBiayaFeeAwal);
$objPHPExcel->getActiveSheet()->setCellValue('D11', '+');
$objPHPExcel->getActiveSheet()->getStyle('D11')->getFont()->setSize(16);
$objPHPExcel->getActiveSheet()->getStyle('D11')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('A12', '4');
$objPHPExcel->getActiveSheet()->setCellValue('B12', 'Biaya Kasbank');
$objPHPExcel->getActiveSheet()->setCellValue('C12', $totBiayaKasbankAwal);
$objPHPExcel->getActiveSheet()->setCellValue('D12', '+');
$objPHPExcel->getActiveSheet()->getStyle('D12')->getFont()->setSize(16);
$objPHPExcel->getActiveSheet()->getStyle('D12')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('A13', '5');
$objPHPExcel->getActiveSheet()->setCellValue('B13', 'Biaya Lainnya');
$objPHPExcel->getActiveSheet()->setCellValue('C13', $totBiayaLainAwal);
$objPHPExcel->getActiveSheet()->setCellValue('D13', '+');
$objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setSize(16);
$objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A14:B14')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('A14:C14')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A14')->getFont()->getColor()->setRGB('5B5B5B');
$objPHPExcel->getActiveSheet()->mergeCells("A14:B14");
$objPHPExcel->getActiveSheet()->setCellValue('A14', 'Subtotal');
$objPHPExcel->getActiveSheet()->getStyle('A14')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->setCellValue('C14', $totBiayaAwal);
$objPHPExcel->getActiveSheet()->getStyle('A16:C16')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('A16:C16')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A16')->getFont()->getColor()->setRGB('4C9D3B');
$objPHPExcel->getActiveSheet()->mergeCells("A16:C16");
$objPHPExcel->getActiveSheet()->setCellValue('A16', 'BIAYA PROYEK BERJALAN');
$objPHPExcel->getActiveSheet()->setCellValue('A17', '1');
$objPHPExcel->getActiveSheet()->setCellValue('B17', 'Barang SO');
$objPHPExcel->getActiveSheet()->setCellValue('C17', $totBarangSoAkhir);
$objPHPExcel->getActiveSheet()->setCellValue('D17', '+');
$objPHPExcel->getActiveSheet()->getStyle('D17')->getFont()->setSize(16);
$objPHPExcel->getActiveSheet()->getStyle('D17')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('A18', '2');
$objPHPExcel->getActiveSheet()->setCellValue('B18', 'Barang Proyek');
$objPHPExcel->getActiveSheet()->setCellValue('C18', $totBarangProyekAkhir);
$objPHPExcel->getActiveSheet()->setCellValue('D18', '+');
$objPHPExcel->getActiveSheet()->getStyle('D18')->getFont()->setSize(16);
$objPHPExcel->getActiveSheet()->getStyle('D18')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('A19', '3');
$objPHPExcel->getActiveSheet()->setCellValue('B19', 'Biaya Fee');
$objPHPExcel->getActiveSheet()->setCellValue('C19', $totBiayaFeeAkhir);
$objPHPExcel->getActiveSheet()->setCellValue('D19', '+');
$objPHPExcel->getActiveSheet()->getStyle('D19')->getFont()->setSize(16);
$objPHPExcel->getActiveSheet()->getStyle('D19')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('A20', '4');
$objPHPExcel->getActiveSheet()->setCellValue('B20', 'Biaya Kasbank');
$objPHPExcel->getActiveSheet()->setCellValue('C20', $totBiayaKasbankAkhir);
$objPHPExcel->getActiveSheet()->setCellValue('D20', '+');
$objPHPExcel->getActiveSheet()->getStyle('D20')->getFont()->setSize(16);
$objPHPExcel->getActiveSheet()->getStyle('D20')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('A21', '5');
$objPHPExcel->getActiveSheet()->setCellValue('B21', 'Biaya Lainnya');
$objPHPExcel->getActiveSheet()->setCellValue('C21', $totBiayaLainAkhir);
$objPHPExcel->getActiveSheet()->setCellValue('D21', '+');
$objPHPExcel->getActiveSheet()->getStyle('D21')->getFont()->setSize(16);
$objPHPExcel->getActiveSheet()->getStyle('D21')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A22:B22')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('A22:C22')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A22')->getFont()->getColor()->setRGB('5B5B5B');
$objPHPExcel->getActiveSheet()->mergeCells("A22:B22");
$objPHPExcel->getActiveSheet()->setCellValue('A22', 'Subtotal');
$objPHPExcel->getActiveSheet()->getStyle('A22')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->setCellValue('C22', $totBiayaAkhir);
//GP Proyek
$objPHPExcel->getActiveSheet()->getStyle('A24:B24')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('A24:C24')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A24')->getFont()->getColor()->setRGB('4C9D3B');
$objPHPExcel->getActiveSheet()->getStyle('C24')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->mergeCells("A24:B24");
$objPHPExcel->getActiveSheet()->setCellValue('A24', 'GP PROYEK');
$objPHPExcel->getActiveSheet()->setCellValue('C24', $totGp);
$objPHPExcel->getActiveSheet()->getStyle('A25:B25')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('A25:C25')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A25')->getFont()->getColor()->setRGB('F44336');
$objPHPExcel->getActiveSheet()->mergeCells("A25:B25");
$objPHPExcel->getActiveSheet()->setCellValue('A25', '(Nilai Proyek - Akumulasi Biaya)');
//Presentase GP
$objPHPExcel->getActiveSheet()->getStyle('A27:B27')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('A27:C27')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A27')->getFont()->getColor()->setRGB('4C9D3B');
$objPHPExcel->getActiveSheet()->getStyle('C27')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->mergeCells("A27:B27");
$objPHPExcel->getActiveSheet()->setCellValue('A27', 'PRESENTASE GP');
$objPHPExcel->getActiveSheet()->setCellValue('C27', $preGp);
$objPHPExcel->getActiveSheet()->getStyle('A28:B28')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('A28:C28')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A28')->getFont()->getColor()->setRGB('F44336');
$objPHPExcel->getActiveSheet()->mergeCells("A28:B28");
$objPHPExcel->getActiveSheet()->setCellValue('A28', '((GP Proyek/Akumulasi Biaya)*100%)');
//Sheet Selanjutnya, Detail Proyek
//List SO Terbit
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setTitle("SO Terbit");
$objPHPExcel->getActiveSheet()->mergeCells("A1:G1");
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(17);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setRGB('9400D3');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->setCellValue('A1', "SO TERBIT - $nama_grup");
$objPHPExcel->getActiveSheet()->getStyle('A3:G3')->getFont()->setSize(13);
$objPHPExcel->getActiveSheet()->getStyle('A3:G3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'No');
$objPHPExcel->getActiveSheet()->setCellValue('B3', 'Nomor SO');
$objPHPExcel->getActiveSheet()->setCellValue('C3', 'Nomor PO');
$objPHPExcel->getActiveSheet()->setCellValue('D3', 'Tanggal Nota');
$objPHPExcel->getActiveSheet()->setCellValue('E3', 'Nomor Nota');
$objPHPExcel->getActiveSheet()->setCellValue('F3', 'Nilai Nota');
$objPHPExcel->getActiveSheet()->setCellValue('G3', 'Nilai Fee');
$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);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('D3')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('F3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('G3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('A3:G3')->getFill()->applyFromArray (
array(
'type' => 'FILL_SOLID',
'rotation' => 0,
'startcolor' => array(
'rgb' => 'D6B4F4'
),
'endcolor' => array(
'argb' => 'FFFFFFFF'
)
)
);
$baris = 4;
$no = 1;
foreach ($spr as $key) {
$objPHPExcel->getActiveSheet()->setCellValue('A'.$baris, $no++);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$baris, $key['KODE_SO']);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$baris, $key['PO_SO']);
$objPHPExcel->getActiveSheet()->setCellValue('D'.$baris, $key['TGLNOTA']);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$baris, $key['INV_PENJUALAN']);
$objPHPExcel->getActiveSheet()->setCellValue('F'.$baris, $key['NILAI']);
$objPHPExcel->getActiveSheet()->setCellValue('G'.$baris++, $key['FEE']);
}
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':G'.$baris)->getFill()->applyFromArray (
array(
'type' => 'FILL_SOLID',
'rotation' => 0,
'startcolor' => array(
'rgb' => 'D6B4F4'
),
'endcolor' => array(
'argb' => 'FFFFFFFF'
)
)
);
$objPHPExcel->getActiveSheet()->freezePane('A4');
$objPHPExcel->getActiveSheet()->mergeCells('A'.$baris.':E'.$baris);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$baris, "SUBTOTAL");
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':G'.$baris)->getFont()->setSize(13);
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':G'.$baris)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':G'.$baris)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->setCellValue('F'.$baris, $totSoTerbit);
if ($grup==1) {
$objPHPExcel->getActiveSheet()->setCellValue('G'.$baris, $totBiayaFeeAwal);
} else {
$objPHPExcel->getActiveSheet()->setCellValue('G'.$baris, $totBiayaFeeAkhir);
}
$objPHPExcel->getActiveSheet()->getStyle('A3:G'.$baris)->getBorders()->applyFromArray(
array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array(
'rgb' => '000000'
)
)
)
);
//List Barang SO
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(2);
$objPHPExcel->getActiveSheet()->setTitle("Barang SO");
$objPHPExcel->getActiveSheet()->mergeCells("A1:F1");
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(17);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setRGB('066373');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->setCellValue('A1', "BARANG SO - $nama_grup");
$objPHPExcel->getActiveSheet()->getStyle('A3:F3')->getFont()->setSize(13);
$objPHPExcel->getActiveSheet()->getStyle('A3:F3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'No');
$objPHPExcel->getActiveSheet()->setCellValue('B3', 'Jenis');
$objPHPExcel->getActiveSheet()->setCellValue('C3', 'Barang');
$objPHPExcel->getActiveSheet()->setCellValue('D3', 'Kuantiti');
$objPHPExcel->getActiveSheet()->setCellValue('E3', 'DPP (Average)');
$objPHPExcel->getActiveSheet()->setCellValue('F3', 'Subtotal');
$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);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('B3')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('D3')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('E3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('F3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('A3:F3')->getFill()->applyFromArray (
array(
'type' => 'FILL_SOLID',
'rotation' => 0,
'startcolor' => array(
'rgb' => 'C0D6E4'
),
'endcolor' => array(
'argb' => 'FFFFFFFF'
)
)
);
$baris = 4;
$no = 1;
foreach ($barangSo as $key) {
$objPHPExcel->getActiveSheet()->setCellValue('A'.$baris, $no++);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$baris, $key['JENIS']);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$baris, $key['BARANG']);
$objPHPExcel->getActiveSheet()->setCellValue('D'.$baris, $key['QTY']);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$baris, $key['DPP']);
$objPHPExcel->getActiveSheet()->setCellValue('F'.$baris++, $key['SUBTOTAL']);
}
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':F'.$baris)->getFill()->applyFromArray (
array(
'type' => 'FILL_SOLID',
'rotation' => 0,
'startcolor' => array(
'rgb' => 'C0D6E4'
),
'endcolor' => array(
'argb' => 'FFFFFFFF'
)
)
);
$objPHPExcel->getActiveSheet()->freezePane('A4');
$objPHPExcel->getActiveSheet()->mergeCells('A'.$baris.':E'.$baris);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$baris, "SUBTOTAL");
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':F'.$baris)->getFont()->setSize(13);
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':F'.$baris)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':F'.$baris)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
if ($grup==1) {
$objPHPExcel->getActiveSheet()->setCellValue('F'.$baris, $totBarangSoAwal);
} else {
$objPHPExcel->getActiveSheet()->setCellValue('F'.$baris, $totBarangSoAkhir);
}
$objPHPExcel->getActiveSheet()->getStyle('A3:F'.$baris)->getBorders()->applyFromArray(
array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array(
'rgb' => '000000'
)
)
)
);
//List Barang Proyek
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(3);
$objPHPExcel->getActiveSheet()->setTitle("Barang Proyek");
$objPHPExcel->getActiveSheet()->mergeCells("A1:E1");
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(17);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setRGB('F44336');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->setCellValue('A1', "BARANG PROYEK - $nama_grup");
$objPHPExcel->getActiveSheet()->getStyle('A3:E3')->getFont()->setSize(13);
$objPHPExcel->getActiveSheet()->getStyle('A3:E3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'No');
$objPHPExcel->getActiveSheet()->setCellValue('B3', 'Barang');
$objPHPExcel->getActiveSheet()->setCellValue('C3', 'Kuantiti');
$objPHPExcel->getActiveSheet()->setCellValue('D3', 'DPP (Average)');
$objPHPExcel->getActiveSheet()->setCellValue('E3', 'Subtotal');
$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);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('C3')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('D3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('E3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('A3:E3')->getFill()->applyFromArray (
array(
'type' => 'FILL_SOLID',
'rotation' => 0,
'startcolor' => array(
'rgb' => 'F4CCCC'
),
'endcolor' => array(
'argb' => 'FFFFFFFF'
)
)
);
$baris = 4;
$no = 1;
foreach ($barangProyek as $key) {
$objPHPExcel->getActiveSheet()->setCellValue('A'.$baris, $no++);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$baris, $key['KODE_BARANG']);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$baris, $key['KELUAR_HQ']);
$objPHPExcel->getActiveSheet()->setCellValue('D'.$baris, $key['AVG']);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$baris++, $key['SUB']);
}
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':E'.$baris)->getFill()->applyFromArray (
array(
'type' => 'FILL_SOLID',
'rotation' => 0,
'startcolor' => array(
'rgb' => 'F4CCCC'
),
'endcolor' => array(
'argb' => 'FFFFFFFF'
)
)
);
$objPHPExcel->getActiveSheet()->freezePane('A4');
$objPHPExcel->getActiveSheet()->mergeCells('A'.$baris.':D'.$baris);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$baris, "SUBTOTAL");
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':E'.$baris)->getFont()->setSize(13);
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':E'.$baris)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':E'.$baris)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
if ($grup==1) {
$objPHPExcel->getActiveSheet()->setCellValue('E'.$baris, $totBarangProyekAwal);
} else {
$objPHPExcel->getActiveSheet()->setCellValue('E'.$baris, $totBarangProyekAkhir);
}
$objPHPExcel->getActiveSheet()->getStyle('A3:E'.$baris)->getBorders()->applyFromArray(
array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array(
'rgb' => '000000'
)
)
)
);
//List Biaya Kasbank
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(4);
$objPHPExcel->getActiveSheet()->setTitle("Biaya Kasbank");
$objPHPExcel->getActiveSheet()->mergeCells("A1:E1");
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(17);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setRGB('6AA84F');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->setCellValue('A1', "BIAYA KASBANK - $nama_grup");
$objPHPExcel->getActiveSheet()->getStyle('A3:E3')->getFont()->setSize(13);
$objPHPExcel->getActiveSheet()->getStyle('A3:E3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'No');
$objPHPExcel->getActiveSheet()->setCellValue('B3', 'Tanggal');
$objPHPExcel->getActiveSheet()->setCellValue('C3', 'Jenis');
$objPHPExcel->getActiveSheet()->setCellValue('D3', 'Nilai');
$objPHPExcel->getActiveSheet()->setCellValue('E3', 'Keterangan');
$objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setWrapText(true);
$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')->setWidth(60);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('B3')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('D3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('A3:E3')->getFill()->applyFromArray (
array(
'type' => 'FILL_SOLID',
'rotation' => 0,
'startcolor' => array(
'rgb' => 'B6D7A8'
),
'endcolor' => array(
'argb' => 'FFFFFFFF'
)
)
);
$baris = 4;
$no = 1;
foreach ($biayaKasbank as $key) {
$objPHPExcel->getActiveSheet()->setCellValue('A'.$baris, $no++);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$baris, $key['TANGGAL']);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$baris, $key['NAMA_UNITPROYEK']);
$objPHPExcel->getActiveSheet()->setCellValue('D'.$baris, $key['NILAI']);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$baris++, $key['KET_DAK']);
}
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':E'.$baris)->getFill()->applyFromArray (
array(
'type' => 'FILL_SOLID',
'rotation' => 0,
'startcolor' => array(
'rgb' => 'B6D7A8'
),
'endcolor' => array(
'argb' => 'FFFFFFFF'
)
)
);
$objPHPExcel->getActiveSheet()->freezePane('A4');
$objPHPExcel->getActiveSheet()->mergeCells('A'.$baris.':C'.$baris);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$baris, "SUBTOTAL");
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':E'.$baris)->getFont()->setSize(13);
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':E'.$baris)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':E'.$baris)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
if ($grup==1) {
$objPHPExcel->getActiveSheet()->setCellValue('D'.$baris, $totBiayaKasbankAwal);
} else {
$objPHPExcel->getActiveSheet()->setCellValue('D'.$baris, $totBiayaKasbankAkhir);
}
$objPHPExcel->getActiveSheet()->getStyle('A3:E'.$baris)->getBorders()->applyFromArray(
array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array(
'rgb' => '000000'
)
)
)
);
//List Biaya Lainnya
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(5);
$objPHPExcel->getActiveSheet()->setTitle("Biaya Lainnya");
$objPHPExcel->getActiveSheet()->mergeCells("A1:C1");
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(17);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setRGB('444444');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->setCellValue('A1', "BIAYA LAINNYA - $nama_grup");
$objPHPExcel->getActiveSheet()->getStyle('A3:C3')->getFont()->setSize(13);
$objPHPExcel->getActiveSheet()->getStyle('A3:C3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'No');
$objPHPExcel->getActiveSheet()->setCellValue('B3', 'Keterangan');
$objPHPExcel->getActiveSheet()->setCellValue('C3', 'Nilai');
$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('C3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal('HORIZONTAL_CENTER')->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
$objPHPExcel->getActiveSheet()->getStyle('A3:C3')->getFill()->applyFromArray (
array(
'type' => 'FILL_SOLID',
'rotation' => 0,
'startcolor' => array(
'rgb' => 'EEEEEE'
),
'endcolor' => array(
'argb' => 'FFFFFFFF'
)
)
);
$baris = 4;
$no = 1;
foreach ($biayaLain as $key) {
$objPHPExcel->getActiveSheet()->setCellValue('A'.$baris, $no++);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$baris, $key['NAMA_BPRY']);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$baris++, $key['NILAI']);
}
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':C'.$baris)->getFill()->applyFromArray (
array(
'type' => 'FILL_SOLID',
'rotation' => 0,
'startcolor' => array(
'rgb' => 'EEEEEE'
),
'endcolor' => array(
'argb' => 'FFFFFFFF'
)
)
);
$objPHPExcel->getActiveSheet()->freezePane('A4');
$objPHPExcel->getActiveSheet()->mergeCells('A'.$baris.':B'.$baris);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$baris, "SUBTOTAL");
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':C'.$baris)->getFont()->setSize(13);
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':C'.$baris)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris.':C'.$baris)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical('VERTICAL_CENTER');
if ($grup==1) {
$objPHPExcel->getActiveSheet()->setCellValue('C'.$baris, $totBiayaLainAwal);
} else {
$objPHPExcel->getActiveSheet()->setCellValue('C'.$baris, $totBiayaLainAkhir);
}
$objPHPExcel->getActiveSheet()->getStyle('A3:C'.$baris)->getBorders()->applyFromArray(
array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array(
'rgb' => '000000'
)
)
)
);
$filename = "Laporan Rekap Proyek (".$sales.") - ".$nama_kuartal." - GRUP ".$grup." - ". $ts. '.xlsx';
}
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
$writer = PHPExcel_IOFactory::createwriter($objPHPExcel, 'Excel2007');
$writer->save('php://output');
exit();
?>
Reference in New Issue View Git Blame Copy Permalink