/**
* Write WorkbookProtection
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel $pPHPExcel
* @throws PHPExcel_Writer_Exception
*/
private function _writeWorkbookProtection(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel $pPHPExcel = null)
{
if ($pPHPExcel->getSecurity()->isSecurityEnabled()) {
$objWriter->startElement('workbookProtection');
$objWriter->writeAttribute('lockRevision', $pPHPExcel->getSecurity()->getLockRevision() ? 'true' : 'false');
$objWriter->writeAttribute('lockStructure', $pPHPExcel->getSecurity()->getLockStructure() ? 'true' : 'false');
$objWriter->writeAttribute('lockWindows', $pPHPExcel->getSecurity()->getLockWindows() ? 'true' : 'false');
if ($pPHPExcel->getSecurity()->getRevisionsPassword() != '') {
$objWriter->writeAttribute('revisionsPassword', $pPHPExcel->getSecurity()->getRevisionsPassword());
}
if ($pPHPExcel->getSecurity()->getWorkbookPassword() != '') {
$objWriter->writeAttribute('workbookPassword', $pPHPExcel->getSecurity()->getWorkbookPassword());
}
$objWriter->endElement();
}
}
public function post_exphutangarmada()
{
$input = Input::all();
$shift = 'all';
$saldohutangbymonth = $this->allQuery($input['date'], Auth::user()->pool_id, $shift, 0, 1000);
if ($saldohutangbymonth) {
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator(Auth::user()->fullname)->setLastModifiedBy(Auth::user()->fullname)->setTitle("Laporan Harian " . Pool::find(Auth::user()->pool_id)->pool_name . '-' . date('Y-m-d'))->setSubject("Laporan Harian " . Pool::find(Auth::user()->pool_id)->pool_name . '-' . date('Y-m-d'))->setDescription("Laporan harian operasi pool")->setKeywords("Laporan Harian")->setCategory("");
$styleArray = array('font' => array('bold' => true, 'color' => array('rgb' => 'FF0000'), 'size' => 16));
$sheet_active = 0;
$objPHPExcel->createSheet(NULL, $sheet_active);
$objPHPExcel->setActiveSheetIndex($sheet_active);
//title
$objPHPExcel->getActiveSheet()->mergeCells('A2:J2');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'LAPORAN HUTANG ARMADA PER TANGGAL ' . Myfungsi::fulldate(strtotime($input['date'])));
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleArray);
// Coloum header
$objPHPExcel->getActiveSheet()->mergeCells('A5:A6');
$objPHPExcel->getActiveSheet()->mergeCells('B5:B6');
$objPHPExcel->getActiveSheet()->mergeCells('C5:D5');
$objPHPExcel->getActiveSheet()->mergeCells('E5:E6');
$objPHPExcel->getActiveSheet()->mergeCells('F5:I5');
$objPHPExcel->getActiveSheet()->mergeCells('J5:L5');
$objPHPExcel->getActiveSheet()->mergeCells('M5:O5');
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'NO');
$objPHPExcel->getActiveSheet()->setCellValue('B5', 'BAPAK ASUH');
$objPHPExcel->getActiveSheet()->setCellValue('C5', 'PENGEMUDI');
$objPHPExcel->getActiveSheet()->setCellValue('C6', 'NIP');
$objPHPExcel->getActiveSheet()->setCellValue('D6', 'NAMA');
$objPHPExcel->getActiveSheet()->setCellValue('E5', 'BODY');
$objPHPExcel->getActiveSheet()->setCellValue('F5', 'PEMAKAIAN SPAREPART ARMADA');
$objPHPExcel->getActiveSheet()->setCellValue('F6', 'PEMAKAIAN');
$objPHPExcel->getActiveSheet()->setCellValue('G6', 'TABUNGAN');
$objPHPExcel->getActiveSheet()->setCellValue('H6', 'BAYAR');
$objPHPExcel->getActiveSheet()->setCellValue('I6', 'SELISIH');
$objPHPExcel->getActiveSheet()->setCellValue('J5', 'SETORAN ARMADA');
$objPHPExcel->getActiveSheet()->setCellValue('J6', 'KS');
$objPHPExcel->getActiveSheet()->setCellValue('K6', 'BAYAR KS');
$objPHPExcel->getActiveSheet()->setCellValue('L6', 'SELISIH');
$objPHPExcel->getActiveSheet()->setCellValue('M5', 'SALDO ARMADA');
$objPHPExcel->getActiveSheet()->setCellValue('M6', 'SALDO SPAREPART');
$objPHPExcel->getActiveSheet()->setCellValue('N6', 'SALDO KS');
$objPHPExcel->getActiveSheet()->setCellValue('O6', 'SALDO AKHIR');
$objPHPExcel->getActiveSheet()->setCellValue('P5', 'SHIFT');
$no = 1;
$starline = 8;
foreach ($saldohutangbymonth as $saldo) {
$bpkasuh = Anakasuh::where('status', '=', 1)->where('fleet_id', '=', $saldo->fleet_id)->first();
$bravo = Driver::find($saldo->bravo_driver_id);
$saldosp = $saldo->tabungan_sparepart + $saldo->hutang_dp_sparepart + $saldo->cicilan_sparepart - $saldo->pemakaian_part;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $starline, $no);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $starline, $bpkasuh ? User::find($bpkasuh->user_id)->fullname : 'TIDAK ADA BAPAK ASUH');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $starline, $bravo ? $bravo->nip : 'NONE');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $starline, $bravo ? $bravo->name : 'NONE');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $starline, $saldo->taxi_number);
//pemakaikan sp
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $starline, $saldo->pemakaian_part);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $starline, $saldo->tabungan_sparepart);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $starline, $saldo->hutang_dp_sparepart + $saldo->cicilan_sparepart);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $starline, $saldosp);
//setoran armada
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $starline, $saldo->ks);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $starline, $saldo->cicilan_ks);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $starline, $saldo->selisi_ks);
//saldo armada
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, $starline, $saldosp);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, $starline, $saldo->selisi_ks);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(14, $starline, $saldosp + $saldo->selisi_ks);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(15, $starline, $saldo->shift_id);
$no++;
$starline++;
}
$objPHPExcel->getActiveSheet()->getStyle('A5:O' . ($starline + 1))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_HAIR);
$objPHPExcel->getActiveSheet()->getStyle('A5:O6')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A5:O' . ($starline + 1))->getBorders()->getOutline()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($starline + 1) . ':O' . ($starline + 1))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//end
//SET TANGGAL UNDUH DAN PASSWORD
$objPHPExcel->getActiveSheet()->setCellValue('B' . ($starline + 10), 'Tanggal Unduh');
$objPHPExcel->getActiveSheet()->setCellValue('C' . ($starline + 10), ':');
$objPHPExcel->getActiveSheet()->setCellValue('D' . ($starline + 10), date('Y-m-d H:i:s'));
$objPHPExcel->getSecurity()->setLockWindows(true);
$objPHPExcel->getSecurity()->setLockStructure(true);
$objPHPExcel->getSecurity()->setWorkbookPassword("FreeBlocking");
$objPHPExcel->getActiveSheet()->getProtection()->setPassword('FreeBlocking');
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
// This should be enabled in order to enable any of the following!
//$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
//END
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(path('public') . 'Laporan-hutang-armada.xls');
return Response::download(path('public') . 'Laporan-hutang-armada.xls');
}
//end if
//var_dump($saldohutangbymonth);
}
// Set document properties
echo date('H:i:s'), " Set document properties", EOL;
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file");
// Add some data
echo date('H:i:s'), " Add some data", EOL;
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello');
$objPHPExcel->getActiveSheet()->setCellValue('B2', 'world!');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Hello');
$objPHPExcel->getActiveSheet()->setCellValue('D2', 'world!');
// Rename worksheet
echo date('H:i:s'), " Rename worksheet", EOL;
$objPHPExcel->getActiveSheet()->setTitle('Simple');
// Set document security
echo date('H:i:s'), " Set document security", EOL;
$objPHPExcel->getSecurity()->setLockWindows(true);
$objPHPExcel->getSecurity()->setLockStructure(true);
$objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel");
// Set sheet security
echo date('H:i:s'), " Set sheet security", EOL;
$objPHPExcel->getActiveSheet()->getProtection()->setPassword('PHPExcel');
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
// This should be enabled in order to enable any of the following!
$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Save Excel 95 file
echo date('H:i:s'), " Write to Excel5 format", EOL;
$callStartTime = microtime(true);
public function get_expreportdaily($date = false, $user_id = false)
{
if (!$date) {
$date = date('Y-m-d');
}
if (!$user_id) {
$user_id = Auth::user()->id;
}
$anakasu = Anakasuh::where('user_id', '=', $user_id)->where('status', '=', 1)->get();
if (!$anakasu) {
return false;
}
$fleets = array();
foreach ($anakasu as $fleet) {
array_push($fleets, $fleet->fleet_id);
}
$financials = DB::table('financial_report_daily')->where_in('fleet_id', $fleets)->where('operasi_time', '=', $date)->where_pool_id(Auth::user()->pool_id)->order_by('taxi_number', 'asc')->get();
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator(Auth::user()->fullname)->setLastModifiedBy(Auth::user()->fullname)->setTitle("Laporan Harian " . Pool::find(Auth::user()->pool_id)->pool_name)->setSubject("Laporan Harian " . Pool::find(Auth::user()->pool_id)->pool_name)->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("Laporan Harian")->setCategory("");
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->mergeCells('A5:A6');
$objPHPExcel->getActiveSheet()->mergeCells('B5:B6');
$objPHPExcel->getActiveSheet()->mergeCells('C5:D5');
$objPHPExcel->getActiveSheet()->mergeCells('E5:E6');
$objPHPExcel->getActiveSheet()->mergeCells('F5:G5');
$objPHPExcel->getActiveSheet()->mergeCells('H5:H6');
$objPHPExcel->getActiveSheet()->mergeCells('I5:I6');
$objPHPExcel->getActiveSheet()->mergeCells('J5:J6');
$objPHPExcel->getActiveSheet()->mergeCells('K5:K6');
$objPHPExcel->getActiveSheet()->mergeCells('L5:O5');
$objPHPExcel->getActiveSheet()->mergeCells('P5:R5');
//$objPHPExcel->getActiveSheet()->mergeCells('R5:R6');
$objPHPExcel->getActiveSheet()->mergeCells('S5:S6');
$objPHPExcel->getActiveSheet()->mergeCells('T5:T6');
$objPHPExcel->getActiveSheet()->mergeCells('U5:U6');
$objPHPExcel->getActiveSheet()->mergeCells('V5:V6');
$objPHPExcel->getActiveSheet()->mergeCells('W5:W6');
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'NO');
$objPHPExcel->getActiveSheet()->setCellValue('B5', 'BAPAK ASUH');
$objPHPExcel->getActiveSheet()->setCellValue('C5', 'PENGEMUDI');
$objPHPExcel->getActiveSheet()->setCellValue('C6', 'NIP');
$objPHPExcel->getActiveSheet()->setCellValue('D6', 'NAMA');
$objPHPExcel->getActiveSheet()->setCellValue('E5', 'BODY');
$objPHPExcel->getActiveSheet()->setCellValue('F5', 'STATUS');
$objPHPExcel->getActiveSheet()->setCellValue('F6', 'OPS');
$objPHPExcel->getActiveSheet()->setCellValue('G6', 'BS');
$objPHPExcel->getActiveSheet()->setCellValue('H5', 'SETORAN MURNI');
$objPHPExcel->getActiveSheet()->setCellValue('I5', 'TAB SPAREPART');
$objPHPExcel->getActiveSheet()->setCellValue('J5', 'DENDA JAM');
$objPHPExcel->getActiveSheet()->setCellValue('K5', 'DP SPAREPART');
$objPHPExcel->getActiveSheet()->setCellValue('L5', 'BAYAR CICILAN');
$objPHPExcel->getActiveSheet()->setCellValue('L6', 'KS');
$objPHPExcel->getActiveSheet()->setCellValue('M6', 'S-PART');
$objPHPExcel->getActiveSheet()->setCellValue('N6', 'DP-KSO');
$objPHPExcel->getActiveSheet()->setCellValue('O6', 'HUT-LAMA');
$objPHPExcel->getActiveSheet()->setCellValue('P5', 'BAYAR');
$objPHPExcel->getActiveSheet()->setCellValue('P6', 'STIKER BANDARA');
$objPHPExcel->getActiveSheet()->setCellValue('Q6', 'CUCI');
$objPHPExcel->getActiveSheet()->setCellValue('R6', 'LAKA');
$objPHPExcel->getActiveSheet()->setCellValue('S5', 'HARUS SETOR');
$objPHPExcel->getActiveSheet()->setCellValue('T5', 'POTONGAN');
$objPHPExcel->getActiveSheet()->setCellValue('U5', 'SETOR CASH');
$objPHPExcel->getActiveSheet()->setCellValue('V5', 'KETEKORAN');
$objPHPExcel->getActiveSheet()->setCellValue('W5', 'KETERANGAN');
$no = 1;
$starline = 8;
foreach ($financials as $finan) {
$bs = $finan->potongan >= $finan->setoran_wajib ? 'YA' : 'TIDAK';
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $starline, $no);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $starline, '');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $starline, $finan->nip);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $starline, $finan->name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $starline, $finan->taxi_number);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $starline, $finan->kode);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $starline, $bs);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $starline, $finan->setoran_wajib);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $starline, $finan->tabungan_sparepart);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $starline, $finan->denda);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $starline, $finan->hutang_dp_sparepart);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $starline, $finan->cicilan_ks);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, $starline, $finan->cicilan_sparepart);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, $starline, $finan->cicilan_dp_kso);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(14, $starline, $finan->cicilan_hutang_lama);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(15, $starline, $finan->cicilan_lain);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(16, $starline, $finan->biaya_cuci);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(17, $starline, $finan->iuran_laka);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(18, $starline, '=SUM(H' . $starline . ':R' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(19, $starline, $finan->potongan);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(20, $starline, $finan->setoran_cash);
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(20, $starline, $finan->ks);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(21, $starline, '=(U' . $starline . '-(S' . $starline . '-T' . $starline . '))');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(22, $starline, '');
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(14, $starline, $finan->cicilan_lain);
$no++;
$starline++;
}
$objPHPExcel->getActiveSheet()->mergeCells('A' . ($starline + 1) . ':G' . ($starline + 1) . '');
$objPHPExcel->getActiveSheet()->setCellValue('A' . ($starline + 1), 'TOTAL SETORAN ');
$objPHPExcel->getActiveSheet()->setCellValue('H' . ($starline + 1), '=SUM(H8:H' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('I' . ($starline + 1), '=SUM(I8:I' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('J' . ($starline + 1), '=SUM(J8:J' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('K' . ($starline + 1), '=SUM(K8:K' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('L' . ($starline + 1), '=SUM(L8:L' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('M' . ($starline + 1), '=SUM(M8:M' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('N' . ($starline + 1), '=SUM(N8:N' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('O' . ($starline + 1), '=SUM(O8:O' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('P' . ($starline + 1), '=SUM(P8:P' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('Q' . ($starline + 1), '=SUM(Q8:Q' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('R' . ($starline + 1), '=SUM(R8:R' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('S' . ($starline + 1), '=SUM(S8:S' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('T' . ($starline + 1), '=SUM(T8:T' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('U' . ($starline + 1), '=SUM(U8:U' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('V' . ($starline + 1), '=SUM(V8:V' . $starline . ')');
$objPHPExcel->getActiveSheet()->getStyle('A5:W' . ($starline + 1))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_HAIR);
$objPHPExcel->getActiveSheet()->getStyle('A5:W6')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A5:W' . ($starline + 1))->getBorders()->getOutline()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($starline + 1) . ':W' . ($starline + 1))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
/*
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 8, 'Some value');
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello');
$objPHPExcel->getActiveSheet()->setCellValue('B2', 'world!');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Hello');
$objPHPExcel->getActiveSheet()->setCellValue('D2', 'world!');
*/
$objPHPExcel->getActiveSheet()->setTitle('Laporan Harian Tgl ' . $date);
$objPHPExcel->getSecurity()->setLockWindows(true);
$objPHPExcel->getSecurity()->setLockStructure(true);
$objPHPExcel->getSecurity()->setWorkbookPassword("FreeBlocking");
$objPHPExcel->getActiveSheet()->getProtection()->setPassword('FreeBlocking');
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
// This should be enabled in order to enable any of the following!
$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//echo path('public');
$objWriter->save(path('public') . 'Laporan-Harian-Tanggal-' . $date . '.xlsx');
return Response::download(path('public') . 'Laporan-Harian-Tanggal-' . $date . '.xlsx', 'Laporan-Harian-Tanggal-' . $date . '.xlsx');
}
$doc->getActiveSheet()->setCellValue($col . $x, $regNota['CodRegistroNotas'])->getStyle($col . $x)->applyFromArray(estilo(11, "FFFFFF", "", 'FFFFFF', "right", "center", 'none', '000000'));
}
//Creditos
$creditosy = $cantidadalumnos + 10 + 1;
$doc->getActiveSheet()->mergeCells('A' . $creditosy . ":J" . $creditosy);
$doc->getActiveSheet()->setCellValue('A' . $creditosy, "Sistema Académico Administrativo para Colegios - Desarrollado por Ronald Nina Layme")->getStyle('A' . $creditosy)->applyFromArray(estilo(9, "000000", "", 'FFFFFF', "left", "center", 'none', '000000'));
//$doc->getActiveSheet()->getCell('A'.$creditosy)->getHyperlink()->setUrl('http://www.fb.com/ronaldnina');
$dodv = $doc->getActiveSheet()->getCell('A' . $creditosy)->getDataValidation();
$dodv->setShowInputMessage(true);
$dodv->setPromptTitle('Creditos');
$dodv->setPrompt('Sistema Académico Administrativo para Colegios - Desarrollado por Ronald Nina Layme Cel: 73230568');
$doc->getActiveSheet()->setCellValue('A' . ($creditosy + 1), md5($codigocasilleros) . " - " . date("d-m-Y H:i:s"))->getStyle('A' . ($creditosy + 1))->applyFromArray(estilo(9, "000000", "", 'FFFFFF', "left", "center", 'none', '000000'));
//Ocultar Grilla
$doc->getActiveSheet()->setShowGridlines(false);
//Aplicando Seguridad en Excel
$doc->getSecurity()->setLockWindows(true);
$doc->getSecurity()->setLockStructure(true);
$doc->getSecurity()->setWorkbookPassword($contrasenaexcel);
$doc->getActiveSheet()->getProtection()->setPassword($contrasenaexcel);
$doc->getActiveSheet()->getProtection()->setSheet(true);
$doc->getActiveSheet()->getProtection()->setSort(true);
$doc->getActiveSheet()->getProtection()->setInsertRows(true);
$doc->getActiveSheet()->getProtection()->setFormatCells(true);
$doc->getActiveSheet()->getStyle('D8')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
//Inmovilizar Paneles
$doc->getActiveSheet()->freezePane('A11');
//Cambiar Nombre a Pestaña
$doc->getActiveSheet()->setTitle('Registro de Notas');
//Seleccionando Pestaña Principal
$doc->setActiveSheetIndex(0);
//Codigo Final de Exportación
public function get_expreportdaily($date = false, $shift_id = false)
{
if (!$date) {
$date = date('Y-m-d');
}
if (!$shift_id) {
${$shift_id} = 1;
}
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator(Auth::user()->fullname)->setLastModifiedBy(Auth::user()->fullname)->setTitle("Laporan Harian " . Pool::find(Auth::user()->pool_id)->pool_name . '-' . date('Y-m-d'))->setSubject("Laporan Harian " . Pool::find(Auth::user()->pool_id)->pool_name . '-' . date('Y-m-d'))->setDescription("Laporan harian operasi pool")->setKeywords("Laporan Harian")->setCategory("");
$styleArray = array('font' => array('bold' => true, 'color' => array('rgb' => 'FF0000'), 'size' => 16));
$jenis_kendaraan = Fleetmodel::where('actived', '=', 1)->get();
$sheet_active = 0;
foreach ($jenis_kendaraan as $model) {
$objPHPExcel->createSheet(NULL, $sheet_active);
$objPHPExcel->setActiveSheetIndex($sheet_active);
$objPHPExcel->getActiveSheet()->mergeCells('A2:J2');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'LAPORAN PENDAPATAN HARIAN TANGGAL ' . Myfungsi::fulldate(strtotime($date)));
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->mergeCells('A5:A6');
$objPHPExcel->getActiveSheet()->mergeCells('B5:B6');
$objPHPExcel->getActiveSheet()->mergeCells('C5:D5');
$objPHPExcel->getActiveSheet()->mergeCells('E5:E6');
$objPHPExcel->getActiveSheet()->mergeCells('F5:G5');
$objPHPExcel->getActiveSheet()->mergeCells('H5:H6');
$objPHPExcel->getActiveSheet()->mergeCells('I5:I6');
$objPHPExcel->getActiveSheet()->mergeCells('J5:J6');
$objPHPExcel->getActiveSheet()->mergeCells('K5:K6');
$objPHPExcel->getActiveSheet()->mergeCells('L5:O5');
$objPHPExcel->getActiveSheet()->mergeCells('P5:R5');
//$objPHPExcel->getActiveSheet()->mergeCells('R5:R6');
$objPHPExcel->getActiveSheet()->mergeCells('S5:S6');
$objPHPExcel->getActiveSheet()->mergeCells('T5:T6');
$objPHPExcel->getActiveSheet()->mergeCells('U5:U6');
$objPHPExcel->getActiveSheet()->mergeCells('V5:V6');
$objPHPExcel->getActiveSheet()->mergeCells('W5:W6');
$objPHPExcel->getActiveSheet()->mergeCells('X5:X6');
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'NO');
$objPHPExcel->getActiveSheet()->setCellValue('B5', 'BAPAK ASUH');
$objPHPExcel->getActiveSheet()->setCellValue('C5', 'PENGEMUDI');
$objPHPExcel->getActiveSheet()->setCellValue('C6', 'NIP');
$objPHPExcel->getActiveSheet()->setCellValue('D6', 'NAMA');
$objPHPExcel->getActiveSheet()->setCellValue('E5', 'BODY');
$objPHPExcel->getActiveSheet()->setCellValue('F5', 'STATUS');
$objPHPExcel->getActiveSheet()->setCellValue('F6', 'OPS');
$objPHPExcel->getActiveSheet()->setCellValue('G6', 'BS');
$objPHPExcel->getActiveSheet()->setCellValue('H5', 'SETORAN MURNI');
$objPHPExcel->getActiveSheet()->setCellValue('I5', 'TAB SPAREPART');
$objPHPExcel->getActiveSheet()->setCellValue('J5', 'DENDA JAM');
$objPHPExcel->getActiveSheet()->setCellValue('K5', 'DP SPAREPART');
$objPHPExcel->getActiveSheet()->setCellValue('L5', 'BAYAR CICILAN');
$objPHPExcel->getActiveSheet()->setCellValue('L6', 'KS');
$objPHPExcel->getActiveSheet()->setCellValue('M6', 'S-PART');
$objPHPExcel->getActiveSheet()->setCellValue('N6', 'DP-KSO');
$objPHPExcel->getActiveSheet()->setCellValue('O6', 'HUT-LAMA');
$objPHPExcel->getActiveSheet()->setCellValue('P5', 'BAYAR');
$objPHPExcel->getActiveSheet()->setCellValue('P6', 'STIKER BANDARA & KEAMANAN');
$objPHPExcel->getActiveSheet()->setCellValue('Q6', 'CUCI');
$objPHPExcel->getActiveSheet()->setCellValue('R6', 'LAKA');
$objPHPExcel->getActiveSheet()->setCellValue('S5', 'HARUS SETOR');
$objPHPExcel->getActiveSheet()->setCellValue('T5', 'POTONGAN');
$objPHPExcel->getActiveSheet()->setCellValue('U5', 'SETOR CASH');
$objPHPExcel->getActiveSheet()->setCellValue('V5', 'KETEKORAN');
$objPHPExcel->getActiveSheet()->setCellValue('W5', 'SETORAN OPS');
$objPHPExcel->getActiveSheet()->setCellValue('X5', 'SHIFT');
$financials = DB::table('financial_report_daily')->where('operasi_time', '=', $date)->where('shift_id', '=', $shift_id)->where('fleet_model_id', '=', $model->id)->where_pool_id(Auth::user()->pool_id)->order_by('shift_id', 'asc')->order_by('taxi_number', 'asc')->get();
$no = 1;
$starline = 8;
foreach ($financials as $finan) {
$bpkasuh = Anakasuh::where('status', '=', 1)->where('fleet_id', '=', $finan->fleet_id)->first();
$bs = $finan->potongan >= $finan->setoran_wajib ? 'YA' : 'TIDAK';
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $starline, $no);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $starline, $bpkasuh ? User::find($bpkasuh->user_id)->fullname : 'TIDAK ADA BAPAK ASUH');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $starline, $finan->nip);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $starline, $finan->name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $starline, $finan->taxi_number);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $starline, $finan->kode);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $starline, $bs);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $starline, $finan->setoran_wajib);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $starline, $finan->tabungan_sparepart);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $starline, $finan->denda);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $starline, $finan->hutang_dp_sparepart);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $starline, $finan->cicilan_ks);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, $starline, $finan->cicilan_sparepart);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, $starline, $finan->cicilan_dp_kso);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(14, $starline, $finan->cicilan_hutang_lama);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(15, $starline, $finan->cicilan_lain);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(16, $starline, $finan->biaya_cuci);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(17, $starline, $finan->iuran_laka);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(18, $starline, '=SUM(H' . $starline . ':R' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(19, $starline, $finan->potongan);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(20, $starline, $finan->setoran_cash);
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(20, $starline, $finan->ks);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(21, $starline, '=(U' . $starline . '-(S' . $starline . '-T' . $starline . '))');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(22, $starline, '=(U' . $starline . '-(Q' . $starline . '+R' . $starline . '))');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(23, $starline, $finan->shift);
//col X
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(24, $starline, $finan->shift_id);
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(14, $starline, $finan->cicilan_lain);
//hidden coloumn status operasi
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(25, $starline, $finan->operasi_status_id);
//col Z
$no++;
$starline++;
}
$objPHPExcel->getActiveSheet()->mergeCells('A' . ($starline + 1) . ':G' . ($starline + 1) . '');
$objPHPExcel->getActiveSheet()->setCellValue('A' . ($starline + 1), 'TOTAL SETORAN ');
$objPHPExcel->getActiveSheet()->setCellValue('H' . ($starline + 1), '=SUM(H8:H' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('I' . ($starline + 1), '=SUM(I8:I' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('J' . ($starline + 1), '=SUM(J8:J' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('K' . ($starline + 1), '=SUM(K8:K' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('L' . ($starline + 1), '=SUM(L8:L' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('M' . ($starline + 1), '=SUM(M8:M' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('N' . ($starline + 1), '=SUM(N8:N' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('O' . ($starline + 1), '=SUM(O8:O' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('P' . ($starline + 1), '=SUM(P8:P' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('Q' . ($starline + 1), '=SUM(Q8:Q' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('R' . ($starline + 1), '=SUM(R8:R' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('S' . ($starline + 1), '=SUM(S8:S' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('T' . ($starline + 1), '=SUM(T8:T' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('U' . ($starline + 1), '=SUM(U8:U' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('V' . ($starline + 1), '=SUM(V8:V' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('W' . ($starline + 1), '=SUM(W8:W' . $starline . ')');
$objPHPExcel->getActiveSheet()->getStyle('A5:X' . ($starline + 1))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_HAIR);
$objPHPExcel->getActiveSheet()->getStyle('A5:X6')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A5:X' . ($starline + 1))->getBorders()->getOutline()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($starline + 1) . ':X' . ($starline + 1))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
/* Rekap Pendapatan */
$objPHPExcel->getActiveSheet()->setCellValue('D' . ($starline + 3), 'Total Setoran :');
$objPHPExcel->getActiveSheet()->setCellValue('D' . ($starline + 5), 'Disetor ke Bank :');
$objPHPExcel->getActiveSheet()->setCellValue('D' . ($starline + 6), 'Disetor ke KKBD :');
$objPHPExcel->getActiveSheet()->setCellValue('D' . ($starline + 7), 'Disetor ke Peduli Laka :');
$objPHPExcel->getActiveSheet()->setCellValue('E' . ($starline + 3), '=SUM(U8:U' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('E' . ($starline + 5), '=SUM(W8:W' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('E' . ($starline + 6), '=SUM(Q8:Q' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('E' . ($starline + 7), '=SUM(R8:R' . $starline . ')');
/* Rekap Unit Operasi */
$objPHPExcel->getActiveSheet()->setCellValue('H' . ($starline + 3), 'Unit Sirkulasi :');
$objPHPExcel->getActiveSheet()->setCellValue('H' . ($starline + 5), 'Unit Operasi :');
$objPHPExcel->getActiveSheet()->setCellValue('H' . ($starline + 6), 'Status B P :');
$objPHPExcel->getActiveSheet()->setCellValue('H' . ($starline + 7), 'Status B L :');
$objPHPExcel->getActiveSheet()->setCellValue('H' . ($starline + 8), 'Status T D O (Lain-Lain):');
$objPHPExcel->getActiveSheet()->setCellValue('I' . ($starline + 3), '=COUNT(Z8:Z' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('I' . ($starline + 5), '=COUNTIF(Z8:Z' . $starline . ', 1)');
$objPHPExcel->getActiveSheet()->setCellValue('I' . ($starline + 6), '=COUNTIF(Z8:Z' . $starline . ', 3)');
$objPHPExcel->getActiveSheet()->setCellValue('I' . ($starline + 7), '=COUNTIF(Z8:Z' . $starline . ', 7)');
$objPHPExcel->getActiveSheet()->setCellValue('I' . ($starline + 8), '=I' . ($starline + 3) . '-(I' . ($starline + 5) . '+ I' . ($starline + 6) . '+ I' . ($starline + 7) . ')');
/* Rekap KETEKORAN */
$objPHPExcel->getActiveSheet()->setCellValue('K' . ($starline + 3), 'Total Ketekoran :');
$objPHPExcel->getActiveSheet()->setCellValue('K' . ($starline + 5), 'KS Murni :');
$objPHPExcel->getActiveSheet()->setCellValue('K' . ($starline + 6), 'KS BP:');
$objPHPExcel->getActiveSheet()->setCellValue('K' . ($starline + 7), 'KS BL :');
$objPHPExcel->getActiveSheet()->setCellValue('K' . ($starline + 8), 'KS TDO (Lain-Lain):');
$objPHPExcel->getActiveSheet()->setCellValue('L' . ($starline + 3), '=V' . ($starline + 1));
$objPHPExcel->getActiveSheet()->setCellValue('L' . ($starline + 5), '=SUMIF(Z8:Z' . $starline . ',1,V8:V' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('L' . ($starline + 6), '=SUMIF(Z8:Z' . $starline . ',3,V8:V' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('L' . ($starline + 7), '=SUMIF(Z8:Z' . $starline . ',7,V8:V' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('L' . ($starline + 8), '=L' . ($starline + 3) . '-(L' . ($starline + 5) . '+ L' . ($starline + 6) . '+ L' . ($starline + 7) . ')');
$objPHPExcel->getActiveSheet()->setCellValue('B' . ($starline + 10), 'Tanggal Unduh');
$objPHPExcel->getActiveSheet()->setCellValue('C' . ($starline + 10), ':');
$objPHPExcel->getActiveSheet()->setCellValue('D' . ($starline + 10), date('Y-m-d H:i:s'));
$objPHPExcel->getSecurity()->setLockWindows(true);
$objPHPExcel->getSecurity()->setLockStructure(true);
$objPHPExcel->getSecurity()->setWorkbookPassword("FreeBlocking");
$objPHPExcel->getActiveSheet()->getProtection()->setPassword('FreeBlocking');
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
// This should be enabled in order to enable any of the following!
//$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
//$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);
$objPHPExcel->getActiveSheet()->setTitle('Laporan ' . $model->fleet_model . ' - ' . $date);
$sheet_active++;
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
//echo path('public');
$objWriter->save(path('public') . 'Laporan-Harian-' . Pool::find(Auth::user()->pool_id)->pool_name . '-Tanggal-' . $date . '.xls');
return Response::download(path('public') . 'Laporan-Harian-' . Pool::find(Auth::user()->pool_id)->pool_name . '-Tanggal-' . $date . '.xls', 'Laporan-Harian-' . Pool::find(Auth::user()->pool_id)->pool_name . '-Tanggal-' . $date . '.xls');
}
</div>
<?php
echo Form::close();
?>
<?php
if ($salaries) {
Package::load("phpexcel");
$excel = new PHPExcel();
$excel->getProperties()->setCreator("NeoGen Labs")->setTitle("Salary Statement");
$excel->getActiveSheet()->setTitle('Monthly Salary Statement');
$excel->getActiveSheet()->setShowGridlines(true);
$excel->getActiveSheet()->getStyle('A2:U2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFCCFFCC');
$excel->getActiveSheet()->getStyle('V2:AC2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFCCFFFF');
$excel->getActiveSheet()->getStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$excel->getSecurity()->setLockWindows(true);
$excel->getSecurity()->setLockStructure(true);
$excel->getSecurity()->setWorkbookPassword("admin");
$excel->getActiveSheet()->getColumnDimension()->setAutoSize(true);
$m = array('1' => 'Jan', '2' => 'Feb', '3' => 'Mar', '4' => 'Apr', '5' => 'May', '6' => 'Jun', '7' => 'Jul', '8' => 'Aug', '9' => 'Sep', '10' => 'Oct', '11' => 'Nov', '12' => 'Dec');
$excel->setActiveSheetIndex(0)->setCellValue('B1', $m[$month] . '-' . $year);
$excel->setActiveSheetIndex(0)->setCellValue('A2', 'No.')->setCellValue('B2', 'Name')->setCellValue('C2', 'Gross')->setCellValue('D2', 'SDXO')->setCellValue('E2', 'Adj. SDX')->setCellValue('F2', 'PFV')->setCellValue('G2', 'PF Adj.')->setCellValue('H2', 'Basic')->setCellValue('I2', 'HRA')->setCellValue('J2', 'LTA')->setCellValue('K2', 'Medical')->setCellValue('L2', 'Travel')->setCellValue('M2', 'PF')->setCellValue('N2', 'Other')->setCellValue('O2', "Leave")->setCellValue('P2', 'Bonus1')->setCellValue('Q2', 'Bonus2')->setCellValue('R2', 'Allowance1')->setCellValue('S2', 'Allowance2')->setCellValue('T2', 'Allowance3')->setCellValue('U2', 'Credit Total')->setCellValue('V2', 'Prof. Tax')->setCellValue('W2', 'PF')->setCellValue('X2', 'Income Tax')->setCellValue('Y2', 'Deduction1')->setCellValue('Z2', 'Deduction2')->setCellValue('AA2', 'Deduction3')->setCellValue('AB2', 'Total Debit')->setCellValue('AC2', 'Net');
$excel->setActiveSheetIndex(0)->setCellValue('G3', $pf_adjust->value)->setCellValue('H3', $basic->value)->setCellValue('I3', $hra->value)->setCellValue('J3', $lta->value)->setCellValue('K3', $medical->value)->setCellValue('L3', $travel->value)->setCellValue('M3', $pf->value);
$rowCount = 4;
foreach ($salaries as $salary) {
if ($salary->pf_applicable == 1) {
$pf = 1;
} else {
$pf = 0;
}
$adj_sdxo = $salary->gross - $salary->sdxo;
/**
* Setea la seguridad de la hoja de cálculo para que sea imposible de modificar su contenido
*
* @param \PHPExcel $objPHPExcel
*/
public function setSecurity(&$objPHPExcel)
{
$password = $this->randomPassword();
//Seguridad al documento
$objPHPExcel->getSecurity()->setLockWindows(true);
$objPHPExcel->getSecurity()->setLockStructure(true);
$objPHPExcel->getSecurity()->setLockRevision(true);
$objPHPExcel->getSecurity()->setWorkbookPassword($password);
//Seguridad a las hojas de estilo
//Se debe de realizar un proceso de seguridad a todas las hojas en excel
$objPHPExcel->getActiveSheet()->getProtection()->setPassword($password);
//$objPHPExcel->getActiveSheet()->getProtection()->setSheet(false);
$objPHPExcel->getActiveSheet()->getProtection()->setDeleteColumns(true);
$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);
$objPHPExcel->getActiveSheet()->getProtection()->setInsertColumns(true);
$objPHPExcel->getActiveSheet()->getProtection()->setInsertHyperlinks(true);
$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
//Desactiva la seguridad de una celda especifica
/* $objPHPExcel->getActiveSheet()->getStyle('B1')->getProtection()->setLocked(
\PHPExcel_Style_Protection::PROTECTION_UNPROTECTED
);
*/
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
}
