API tools faq
paste
Advertisement
jamboljack

Export Excel

jamboljack
May 4th, 2017
231
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 11.46 KB | None | 0 0
raw download clone embed print report
  1. public function print_report_bul_excell($CodeType='', $Bulan = '', $Tahun = '') {   // Excel TDP BUL
  2.         $CodeType   = trim($this->uri->segment(4));
  3.  
  4.         if ($CodeType == 'all') { // Jika = all
  5.             $Tahun      = trim($this->uri->segment(5));
  6.             $daftarlist = $this->laporan_penerbitan_model->select_all_data_bul($Tahun)->result();
  7.         } elseif ($CodeType == 'periode') { // Jika per Periode
  8.             $Bulan      = trim($this->uri->segment(5));
  9.             $Tahun      = trim($this->uri->segment(6));
  10.             $daftarlist = $this->laporan_penerbitan_model->select_data_periode_bul($Bulan, $Tahun)->result();
  11.         }
  12.  
  13.         $objPHPExcel = new PHPExcel();
  14.         // Set document properties
  15.         $objPHPExcel->getProperties()->setCreator("Jama' Rochmad Muttaqin")
  16.             ->setLastModifiedBy("Laporan Penerbitan Izin TDP")
  17.             ->setTitle("Data Penerbitan Izin TDP")
  18.             ->setSubject("Data Penerbitan Izin TDP");
  19.  
  20.         $objPHPExcel->setActiveSheetIndex(0);
  21.         $objPHPExcel->getActiveSheet()->setCellValue('A5', "NO")
  22.             ->setCellValue('B5', "NAMA PERUSAHAAN")
  23.             ->setCellValue('C5', "ALAMAT PERUSAHAAN")
  24.             ->setCellValue('D5', "NAMA PEMILIK / PENANGGUNG JAWAB")
  25.             ->setCellValue('E5', "NO. TELP")
  26.             ->setCellValue('F5', "NPWP")
  27.             ->setCellValue('G5', "MODAL DASAR")
  28.             ->setCellValue('H5', "JENIS KEGIATAN POKOK")
  29.             ->setCellValue('I5', "STATUS")
  30.             ->setCellValue('J5', "NO. TDP")
  31.             ->setCellValue('K5', "TANGGAL")
  32.             ->setCellValue('L5', "KETERANGAN");
  33.         $objPHPExcel->getActiveSheet()->setCellValue('A6', "1")
  34.             ->setCellValue('B6', "2")
  35.             ->setCellValue('C6', "3")
  36.             ->setCellValue('D6', "4")
  37.             ->setCellValue('E6', "5")
  38.             ->setCellValue('F6', "6")
  39.             ->setCellValue('G6', "7")
  40.             ->setCellValue('H6', "8")
  41.             ->setCellValue('I6', "9")
  42.             ->setCellValue('J6', "10")
  43.             ->setCellValue('K6', "11")
  44.             ->setCellValue('L6', "12");
  45.  
  46.         $dataArray= array();
  47.         $no = 0;
  48.         foreach ($daftarlist as $r) {
  49.             if ($r->sts_proses == 8) { // Cetak Izin
  50.                 $status = 'Di Cetak, Masih di Pemroses';
  51.             } elseif($r->sts_proses == 9) { // Serahkan FO
  52.                 $status = 'Sudah di Serahkan ke FO';
  53.             } elseif($r->sts_proses == 10) { // Di Terima Pemohon
  54.                 $status = 'Berkas Di Terima';
  55.             }
  56.  
  57.             $no++;
  58.             $row_array['no']            = $no;
  59.             $row_array['namaperusahaan']= strtoupper($r->nama_prsh);
  60.             $row_array['alamat']        = ucwords(strtolower(trim($r->alamat_prsh))).' Kelurahan '.ucwords(strtolower($r->kelurahan_prsh)).', Kecamatan '.ucwords(strtolower($r->kecamatan_prsh)).' '.ucwords(strtolower($r->country_name));
  61.             $row_array['namapemilik']   = $r->nama_pengurus;
  62.             $row_array['notelp']        = $r->telp_prsh;
  63.             $row_array['npwp']          = $r->npwp;
  64.             $row_array['modal']         = number_format($r->modal_dasar);
  65.             $row_array['usaha']         = $r->nama_kbli.' ('.$r->usaha_pokok.')';
  66.             $row_array['status']        = $r->sts_daftar;
  67.             $row_array['nosk']          = $r->cetak_no_tdp;
  68.             $row_array['tglsk']         = tgl_indo($r->cetak_tgl_pengesahan);
  69.             $row_array['keterangan']    = $status;
  70.             array_push($dataArray, $row_array);
  71.         }
  72.        
  73.         $nox=$no+7;
  74.         $objPHPExcel->getActiveSheet()->fromArray($dataArray, NULL, 'A7');       
  75.         // Set page orientation and size
  76.         $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
  77.         $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
  78.         $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.50);
  79.         $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.50);
  80.         $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.50);
  81.         $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.50);
  82.         $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');         
  83.         // Set title row bold;
  84.         $objPHPExcel->getActiveSheet()->getStyle('A5:L5')->getFont()->setBold(true);
  85.         // Set fills
  86.         $objPHPExcel->getActiveSheet()->getStyle('A5:L5')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
  87.         //$objPHPExcel->getActiveSheet()->getStyle('A5:G5')->getFill()->getStartColor()->setARGB('FF808080');
  88.  
  89.         $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(3);
  90.         $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
  91.         $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(35);
  92.         $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
  93.         $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
  94.         $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
  95.         $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
  96.         $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(25);
  97.         $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
  98.         $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
  99.         $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
  100.         $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);   
  101.         // Set autofilter
  102.          // Always include the complete filter range!
  103.          // Excel does support setting only the caption
  104.          // row, but that's not a best practise...     
  105.         //$objPHPExcel->getActiveSheet()->setAutoFilter($objPHPExcel->getActiveSheet()->calculateWorksheetDimension());
  106.         // Set active sheet index to the first sheet, so Excel opens this as the first sheet
  107.         $objPHPExcel->setActiveSheetIndex(0);
  108.          
  109.         $sharedStyle1 = new PHPExcel_Style();
  110.         $sharedStyle2 = new PHPExcel_Style();
  111.          
  112.         $sharedStyle1->applyFromArray(
  113.          array('borders' => array(
  114.             'bottom'    => array('style' => PHPExcel_Style_Border::BORDER_THIN),
  115.             'top'       => array('style' => PHPExcel_Style_Border::BORDER_THIN),
  116.             'right'     => array('style' => PHPExcel_Style_Border::BORDER_THIN),
  117.             'left'      => array('style' => PHPExcel_Style_Border::BORDER_THIN)
  118.             ),
  119.         ));
  120.          
  121.         $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A5:L$nox");
  122.         // Set style for header row using alternative method
  123.         $objPHPExcel->getActiveSheet()->getStyle('A5:L5')->applyFromArray(
  124.             array(
  125.                 'font' => array(
  126.                     'bold' => true
  127.                 ),
  128.                 'alignment'     => array(
  129.                     'horizontal'    => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
  130.                     'vertical'      => PHPExcel_Style_Alignment::VERTICAL_CENTER
  131.                 ),
  132.                 'borders'       => array(
  133.                     'top'       => array(
  134.                         'style' => PHPExcel_Style_Border::BORDER_THIN
  135.                     )
  136.                 )
  137.             )
  138.         );
  139.  
  140.         $objPHPExcel->getActiveSheet()->getRowDimension('5')->setRowHeight(40); // Row Height Header
  141.         $objPHPExcel->getActiveSheet()->getStyle('A4:L1000')->getFont()->setName('Tahoma');
  142.         $objPHPExcel->getActiveSheet()->getStyle('A4:L1000')->getFont()->setSize(10);
  143.         // Merge cells
  144.         $objPHPExcel->getActiveSheet()->mergeCells('A2:L2');
  145.         $objPHPExcel->getActiveSheet()->setCellValue('A2', "LAPORAN PENERBITAN SURAT IZIN TDP BENTUK USAHA LAINNYA");
  146.         $objPHPExcel->getActiveSheet()->mergeCells('A3:L3');
  147.         $objPHPExcel->getActiveSheet()->setCellValue('A3', "DINAS PENANAMAN MODAL & PELAYANAN TERPADU SATU PINTU");
  148.         $objPHPExcel->getActiveSheet()->mergeCells('A4:L4');
  149.  
  150.         $BULAN = strtoupper(getBulan($Bulan));
  151.         if ($CodeType == 'all') { // Jika = all
  152.             $objPHPExcel->getActiveSheet()->setCellValue('A4', "TAHUN $Tahun");
  153.         } else {
  154.             $objPHPExcel->getActiveSheet()->setCellValue('A4', "BULAN $BULAN $Tahun");
  155.         }
  156.        
  157.         // WRAP TEXT
  158.         $objPHPExcel->getActiveSheet()->getStyle()->getAlignment()->setWrapText(true);
  159.         $objPHPExcel->getActiveSheet()->getStyle('A2:L4')->getFont()->setName('Tahoma');
  160.         $objPHPExcel->getActiveSheet()->getStyle('A2:L4')->getFont()->setSize(14);
  161.         $objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setSize(12);
  162.         $objPHPExcel->getActiveSheet()->getStyle('A4')->getFont()->setSize(12);
  163.         $objPHPExcel->getActiveSheet()->getStyle('A2:L6')->getFont()->setBold(true);
  164.         $objPHPExcel->getActiveSheet()->getStyle('A2:L6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  165.         $objPHPExcel->getActiveSheet()->getStyle('A7:A1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  166.         $objPHPExcel->getActiveSheet()->getStyle('B7:B1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  167.         $objPHPExcel->getActiveSheet()->getStyle('C7:C1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  168.         $objPHPExcel->getActiveSheet()->getStyle('D7:D1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  169.         $objPHPExcel->getActiveSheet()->getStyle('E7:E1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  170.         $objPHPExcel->getActiveSheet()->getStyle('F7:F1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  171.         $objPHPExcel->getActiveSheet()->getStyle('G7:G1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  172.         $objPHPExcel->getActiveSheet()->getStyle('H7:H1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  173.         $objPHPExcel->getActiveSheet()->getStyle('I7:I1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  174.         $objPHPExcel->getActiveSheet()->getStyle('J7:J1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  175.         $objPHPExcel->getActiveSheet()->getStyle('K7:K1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  176.         $objPHPExcel->getActiveSheet()->getStyle('L7:L1000')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  177.  
  178.         // Footer
  179.         $pejabat  = $this->laporan_penerbitan_model->select_pejabat()->row();
  180.         $nofooter = $no+6+4;
  181.         $tanggal = tgl_indo(date('Y-m-d'));
  182.         $foot1   = $nofooter+2; // Kepala
  183.         $foot2   = $nofooter+3; // Dan
  184.         $foot3   = $nofooter+4; // Kabupaten
  185.         $foot4   = $nofooter+9; // Nama Kepala
  186.         $foot5   = $nofooter+10; // Jabatan
  187.         $foot6   = $nofooter+11; // NIP
  188.         $objPHPExcel->getActiveSheet()->setCellValue('J'.$nofooter.'', "Kudus, ".$tanggal."");
  189.         $objPHPExcel->getActiveSheet()->mergeCells('J'.$foot1.''.':L'.$foot1.'');
  190.         $objPHPExcel->getActiveSheet()->setCellValue('J'.$foot1.'', "KEPALA DINAS PENANAMAN MODAL");
  191.         $objPHPExcel->getActiveSheet()->mergeCells('J'.$foot2.''.':L'.$foot2.'');
  192.         $objPHPExcel->getActiveSheet()->setCellValue('J'.$foot2.'', "DAN PELAYANAN TERPADU SATU PINTU");
  193.         $objPHPExcel->getActiveSheet()->mergeCells('J'.$foot3.''.':L'.$foot3.'');
  194.         $objPHPExcel->getActiveSheet()->setCellValue('J'.$foot3.'', "KABUPATEN KUDUS");
  195.         $objPHPExcel->getActiveSheet()->mergeCells('J'.$foot4.''.':L'.$foot4.'');
  196.         $objPHPExcel->getActiveSheet()->setCellValue('J'.$foot4.'', trim($pejabat->nama_pejabat));
  197.         $objPHPExcel->getActiveSheet()->mergeCells('J'.$foot5.''.':L'.$foot5.'');
  198.         $objPHPExcel->getActiveSheet()->setCellValue('J'.$foot5.'', trim($pejabat->jabatan));
  199.         $objPHPExcel->getActiveSheet()->mergeCells('J'.$foot6.''.':L'.$foot6.'');
  200.         $objPHPExcel->getActiveSheet()->setCellValue('J'.$foot6.'', "NIP. ".trim($pejabat->nip));
  201.  
  202.         $objPHPExcel->getActiveSheet()->getStyle('J'.$foot1.''.':L'.$foot6.'')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  203.         $objPHPExcel->getActiveSheet()->getStyle('J'.$foot4.'')->getFont()->setBold(true);
  204.         $objPHPExcel->getActiveSheet()->getStyle('J'.$foot4.'')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); // Garis Bawah Nama Kepala
  205.          
  206.         $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);            
  207.         $date = date('Y-m-d');
  208.         $time = time();
  209.         $objWriter->save('download/DataTDPBUL_'.$date.'_'.$time.'.xlsx');
  210.         redirect(base_url('download/DataTDPBUL_'.$date.'_'.$time.'.xlsx'));
  211.     }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!