API tools faq
paste
Advertisement
nugrohoe_ku

Excel.php

nugrohoe_ku
May 24th, 2017
398
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 16.99 KB | None | 0 0
raw download clone embed print report
  1. <?php
  2. namespace app\laporan;
  3. //use PHPExcel;
  4. //use alexgx\phpexcel;
  5. use PHPExcel;
  6. use app\models\Laporan;
  7. //use yii\db\Query;
  8. //use yii\helpers\ArrayHelper;
  9. //$connection = \Yii::$app->db;
  10. use PHPExcel\IOFactory;
  11. use Yii;
  12.  
  13. class Excel extends PHPExcel{
  14.  
  15.     function CetakExcel($model,$no_def_otomatis){
  16.         //$objPHPExcel = new PHPExcel();
  17.  
  18.         $styleArray = array(
  19.         'font'  => array(
  20.         'bold'  => true,
  21.         'color' => array('rgb' => 'FF0000'),
  22.         'size'  => 15,
  23.         'name'  => 'Verdana'
  24.         ));
  25.        
  26.         $this->getActiveSheet()->getDefaultRowDimension()->setRowHeight(17);
  27.         $this->getActiveSheet()->getDefaultColumnDimension()->setWidth(7);
  28.         $this->getActiveSheet()->getColumnDimension('B')->setWidth(30);
  29.         $this->getActiveSheet()->getColumnDimension('C')->setWidth(25);
  30.         $this->getActiveSheet()->getColumnDimension('D')->setWidth(25);
  31.         $this->getActiveSheet()->getColumnDimension('E')->setWidth(17);
  32.         $this->getActiveSheet()->getColumnDimension('F')->setWidth(17);
  33.         $this->getActiveSheet()->getColumnDimension('G')->setWidth(17);
  34.         $this->getActiveSheet()->getColumnDimension('H')->setWidth(17);
  35.         $this->getActiveSheet()->getColumnDimension('I')->setWidth(14);
  36.  
  37.         $this->getActiveSheet()->getColumnDimension('J')->setWidth(17);
  38.         $this->getActiveSheet()->getColumnDimension('K')->setWidth(17);
  39.         $this->getActiveSheet()->getColumnDimension('L')->setWidth(17);
  40.         $this->getActiveSheet()->getColumnDimension('M')->setWidth(17);
  41.        // $this->getActiveSheet()->getColumnDimension('N')->setWidth(17);
  42.         //$this->getActiveSheet()->getColumnDimension('O')->setWidth(17);
  43.         //$this->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
  44.         $this->getActiveSheet()->getStyle('A1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE8E5E5');
  45.         $this->getActiveSheet()->getStyle('A7:M7')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE8E5E5');
  46.         $this->setActiveSheetIndex(0)
  47.        
  48.         ->setCellValue('A1', 'DATA KESISWAAN')
  49.         ->setCellValue('A7', 'No.')
  50.         ->setCellValue('B7', 'Masalah')
  51.         ->setCellValue('C7', 'Rincian Masalah')
  52.         ->setCellValue('D7', 'Alamat')
  53.         ->setCellValue('E7', 'Telepon')
  54.         ->setCellValue('F7', 'Tempat Lahir')
  55.         ->setCellValue('G7', 'Tanggal Lahir')
  56.         ->setCellValue('H7', 'Jenis Kelamin')
  57.         ->setCellValue('I7', 'Agama')
  58.         ->setCellValue('J7', 'Agama')
  59.         ->setCellValue('K7', 'Agama')
  60.         ->setCellValue('L7', 'Agama')
  61.         ->setCellValue('M7', 'Agama');
  62.         //->setCellValue('N7', 'Agama')
  63.         //->setCellValue('O7', 'Agama');
  64.        
  65.  
  66.         /*$i=1;
  67.         $rowCount = 8;
  68.         $query = new Query;
  69.         $query->select(['siswa.id','siswa.nis', 'siswa.nama','siswa.alamat','siswa.telepon','siswa.tempat_lahir','siswa.tanggal_lahir','jenkel.jenkel','agama.agama','siswa.foto'])->from('siswa')->join('LEFT OUTER JOIN', 'jenkel','siswa.jenkel_id =jenkel.id')->join('LEFT OUTER JOIN', 'agama','siswa.agama_id =agama.id')->groupBy(['siswa.id']);
  70.         $command = $query->createCommand();
  71.         $data = $command->queryAll();
  72.  
  73.         foreach($data as $row) {
  74.         //$this->getActiveSheet()->SetCellValue('A'.$rowCount, $i++);
  75.         $this->getActiveSheet()->SetCellValue('A'.$rowCount, $row['id']);
  76.         $this->getActiveSheet()->SetCellValue('B'.$rowCount, $row['nis']);
  77.         $this->getActiveSheet()->SetCellValue('C'.$rowCount, $row['nama']);
  78.         $this->getActiveSheet()->SetCellValue('D'.$rowCount, $row['alamat']);
  79.         $this->getActiveSheet()->SetCellValue('E'.$rowCount, $row['telepon']);
  80.         $this->getActiveSheet()->SetCellValue('F'.$rowCount, $row['tempat_lahir']);
  81.         $this->getActiveSheet()->SetCellValue('G'.$rowCount, $row['tanggal_lahir']);
  82.         $this->getActiveSheet()->SetCellValue('H'.$rowCount, $row['jenkel']);
  83.         $this->getActiveSheet()->SetCellValue('I'.$rowCount, $row['agama']);
  84.         //$this->getActiveSheet()->getStyle('A'.$rowCount.':I'.$rowCount)->getFont()->setBold()->setSize()->setName('Calibri')->getColor()->setRGB('8A2BE2');
  85.         $this->getActiveSheet()->getStyle('A'.$rowCount.':I'.$rowCount)->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  86.         //$this->getActiveSheet()->getStyle('A'.$rowCount.':I'.$rowCount)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('90EE90');
  87.  
  88.         $rowCount++;
  89.         //SiswaExcel($this);
  90.         }*/
  91.  
  92.         $i=1;
  93.         $rowCount = 8;
  94.         $no_def = 0; //initial value jika no_definitif_otomatis bernilai 1
  95.         var_dump($model);
  96.         echo "</pre>";
  97.          exit;
  98.  
  99.         foreach ($model as $data => $value){
  100.             //$table->addRow();
  101.                 if ($no_def_otomatis == "0") //no def ambil dari database
  102.                     //$table->addCell(700)->addText(htmlspecialchars($value['no_def']),null,['align'=>'center']); //no def database
  103.                     {
  104.                         $this->getActiveSheet()->SetCellValue(htmlspecialchars('A'.$rowCount, $value['no_def']),null);
  105.                         //echo $value['no_def'];
  106.                      }
  107.  
  108.                     //$this->getActiveSheet()->SetCellValue('A'.$rowCount, $i++);
  109.                 else {
  110.                     $no_def++;
  111.                     //$table->addCell(700)->addText($no_def,null,['align'=>'center']); //no def manual
  112.                     $this->getActiveSheet()->SetCellValue(htmlspecialchars('A'.$rowCount, $no_def,null));
  113.                      //echo $no_def;
  114.                 }
  115.  
  116.              //   var_dump($value);
  117.              //   echo "</pre>";
  118.              // exit;
  119.  
  120.                 //$cellNya = $table->addCell(3500); //masalah
  121.                if ($prevKetJra == $value['ket_jra']) { //untuk cetak Jra pada cell 1
  122.                     if ($prevKlas3 != $value['klasifikasi3']){
  123.                         //$cellNya->addText(htmlspecialchars($value['klasifikasi2'].':'.$value['masalah2']),'fStyle','pJustify');
  124.                         //$cellNya->addTextBreak(1);
  125.                         //$cellNya->addText(htmlspecialchars($value['klasifikasi3'].':'.$value['masalah3']),'fStyle','pJustify');
  126.                             //));
  127.                         if (empty($value['klasifikasi2']) || empty($value['klasifikasi3'])){
  128.                             $this->getActiveSheet()->SetCellValue('B'.$rowCount,$value['klasifikasi2'].''.$value['masalah2'].':'.$value['klasifikasi3'].''.$value['masalah3']);
  129.                             //$this->getActiveSheet()->SetCellValuet($value['klasifikasi3'].''.$value['masalah3']);
  130.                         } else {
  131.                            //$this->getActiveSheet()->SetCellValue($value['klasifikasi2'].':'.$value['masalah2']);
  132.                            //$this->getActiveSheet()->SetCellValue($value['klasifikasi3'].':'.$value['masalah3']);
  133.                             $this->getActiveSheet()->SetCellValue('B'.$rowCount,$value['klasifikasi2'].':'.$value['masalah2'].':'.$value['klasifikasi3'].':'.$value['masalah3']);
  134.                         }
  135.                        
  136.                     } else {
  137.                         $this->getActiveSheet()->SetCellValue('B'.$rowCount,'');
  138.                     }
  139.                 }
  140.                 else //jika tidak sama maka cetak{
  141.                 {
  142.                     //$cellNya->addText(htmlspecialchars(Laporan::getKetJraNama($value['ket_jra'])),'fStyle','pJustify');
  143.                     $this->getActiveSheet()->SetCellValue('B'.$rowCount, Laporan::getKetJraNama($value['ket_jra']));
  144.                     //$cellNya->addText(htmlspecialchars($value['klasifikasi2'].':'.$value['masalah2']),'fStyle','pJustify');
  145.                     //$cellNya->addText(htmlspecialchars($value['klasifikasi3'].':'.$value['masalah3']),'fStyle','pJustify');
  146.                     if (empty($value['klasifikasi2']) || empty($value['klasifikasi3'])){
  147.                         //$this->getActiveSheet()->SetCellValue($value['klasifikasi2'].''.$value['masalah2']);
  148.                         //$this->getActiveSheet()->SetCellValue($value['klasifikasi3'].''.$value['masalah3']);
  149.                         $this->getActiveSheet()->SetCellValue('B'.$rowCount,$value['klasifikasi2'].''.$value['masalah2'].':'.$value['klasifikasi3'].''.$value['masalah3']);
  150.  
  151.                     } else {
  152.                        //$this->getActiveSheet()->SetCellValue($value['klasifikasi2'].':'.$value['masalah2']);
  153.                        //$this->getActiveSheet()->SetCellValue($value['klasifikasi3'].':'.$value['masalah3']);
  154.                         $this->getActiveSheet()->SetCellValue('B'.$rowCount,$value['klasifikasi2'].':'.$value['masalah2'].':'.$value['klasifikasi3'].':'.$value['masalah3']);
  155.                     }
  156.                    
  157.                 }
  158.                 //$cellNya = $table->addCell(4750); // rincian masalah
  159.                 $isi = nl2br($value['isi_arsip']);  
  160.                 $isinya = explode("<br />",$isi);
  161.                 foreach($isinya as $data){
  162.                     if ($data != "")
  163.                        {$this->getActiveSheet()->SetCellValue('C'.$rowCount,$data);}
  164.                 }
  165.            
  166.                 $this->getActiveSheet()->SetCellValue('C'.$rowCount,'Dari: '.$value['dari_kepada']);
  167.                 if ($value['bulan2']){
  168.                     $this->getActiveSheet()->SetCellValue('C'.$rowCount,'Bulan: '.Laporan::getBulanNama($value['bulan1']).'-'.Laporan::getBulanNama($value['bulan2']).' '.$value['tahun1']);
  169.                 }
  170.                 else{
  171.                    $this->getActiveSheet()->SetCellValue('C'.$rowCount,'Bulan: '.Laporan::getBulanNama($value['bulan1']).' '.$value['tahun1']);
  172.                 }
  173.                 $prevKlas3 = $value['klasifikasi3'];
  174.                 $prevKetJra = $value['ket_jra'];
  175.  
  176.                 //$cellNya = $table->addCell(750);
  177.                 $this->getActiveSheet()->SetCellValue('D'.$rowCount,$value['retensi_inaktif']);    
  178.  
  179.                 //$cellNya = $table->addCell(750);
  180.                 $this->getActiveSheet()->SetCellValue('E'.$rowCount,$value['jra_m']);  
  181.  
  182.                 //$cellNya = $table->addCell(750);
  183.                 $this->getActiveSheet()->SetCellValue('F'.$rowCount,$value['jra_dk']);
  184.  
  185.                 //$cellNya = $table->addCell(750);
  186.                 $this->getActiveSheet()->SetCellValue('G'.$rowCount,$value['jra_p']);  
  187.  
  188.                 //$cellNya = $table->addCell(750);
  189.                 $this->getActiveSheet()->SetCellValue('H'.$rowCount,$value['jra_b']);  
  190.  
  191.                 //$cellNya = $table->addCell(750);
  192.                 $this->getActiveSheet()->SetCellValue('I'.$rowCount,$value['jra_v']);  
  193.  
  194.                 //$cellNya = $table->addCell(750);
  195.                 $this->getActiveSheet()->SetCellValue('J'.$rowCount,$value['jra_s']);  
  196.  
  197.                 //$cellNya = $table->addCell(750);
  198.                 $this->getActiveSheet()->SetCellValue('K'.$rowCount,$value['jml_satuan'].''.$value['satuan']);
  199.  
  200.                 //$this->getActiveSheet()->SetCellValue('K'.$rowCount,$value['jml_satuan']);
  201.                 //$this->getActiveSheet()->SetCellValue('L'.$rowCount,$value['satuan']);  
  202.  
  203.                 //$cellNya = $table->addCell(750);
  204.                 $this->getActiveSheet()->SetCellValue('L'.$rowCount,$value['jml_boks']);      
  205.  
  206.                 //$cellNya = $table->addCell(1000);
  207.                 $this->getActiveSheet()->SetCellValue('M'.$rowCount,$value['pelaksana_operator'].''.$value['kelengkapan']);
  208.                 //$this->getActiveSheet()->SetCellValue('N'.$rowCount,$value['pelaksana_operator']);
  209.                 //$this->getActiveSheet()->SetCellValue('O'.$rowCount,$value['kelengkapan']);    
  210.                
  211.             $this->getActiveSheet()->getStyle('A'.$rowCount.':M'.$rowCount)->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  212.             $rowCount++;
  213.             return $this;
  214.         }
  215.    
  216.         //return $this;      
  217.        
  218.         $this->getActiveSheet()->getStyle('A1')->getFont()->setBold(true)->setSize(12)->setName('Arial')->getColor()->setRGB('FF0000');
  219.         $this->getActiveSheet()->getStyle('A2')->getFont()->setBold(true)->setSize(12)->setName('Arial');
  220.         $this->getActiveSheet()->getStyle('A7:I7')->getFont()->setBold(true)->setSize(12)->setName('Arial')->getColor()->setRGB('FF0000');
  221.         $this->getActiveSheet()
  222.         ->mergeCells('A1:I1')
  223.         ->mergeCells('A2:I2');
  224.         $this->getActiveSheet()->getStyle('A7:I7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  225.         /*$this->getActiveSheet()->getStyle('B7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  226.         $this->getActiveSheet()->getStyle('C7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  227.         $this->getActiveSheet()->getStyle('D7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  228.         $this->getActiveSheet()->getStyle('E7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  229.         $this->getActiveSheet()->getStyle('F7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  230.         $this->getActiveSheet()->getStyle('G7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  231.         $this->getActiveSheet()->getStyle('H7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  232.         $this->getActiveSheet()->getStyle('I7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  233.         */
  234.         $this->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  235.         $this->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  236.         $this->getActiveSheet()->getStyle('A8:N18')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  237.        
  238.         $this->getActiveSheet()->getStyle('A7')->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  239.         $this->getActiveSheet()->getStyle('B7:B7')->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  240.         $this->getActiveSheet()->getStyle('C7:C7')->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  241.         $this->getActiveSheet()->getStyle('D7:D7')->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  242.         $this->getActiveSheet()->getStyle('E7:E7')->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  243.         $this->getActiveSheet()->getStyle('F7:F7')->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  244.         $this->getActiveSheet()->getStyle('G7:G7')->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  245.         $this->getActiveSheet()->getStyle('H7:H7')->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  246.         $this->getActiveSheet()->getStyle('I7:I7')->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  247.         $this->getActiveSheet()->getStyle('J7:J7')->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  248.         $this->getActiveSheet()->getStyle('K7:K7')->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  249.         $this->getActiveSheet()->getStyle('L7:L7')->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  250.         $this->getActiveSheet()->getStyle('M7:M7')->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  251.  
  252.         //$this->getActiveSheet()->getStyle('E7:H7')->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  253.        // $this->getActiveSheet()->getStyle('I7:K7')->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  254.        //$this->getActiveSheet()->getStyle('L7:N7')->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  255.         $this->getActiveSheet()->getStyle('A7:M7')->getBorders()->getTop()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THICK);
  256.         //$this->getActiveSheet()->getStyle('A7:I18')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  257.         //$this->getActiveSheet()->getStyle('L8:N10')->getBorders()->getOutline()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
  258.        
  259.         $this->getActiveSheet()->setTitle('Simple');
  260.         $this->setActiveSheetIndex(0);
  261.          
  262.        // ob_end_clean();
  263.        // ob_start();
  264.        
  265.        // header('Content-Type: application/vnd.ms-excel');
  266.        // header('Content-Disposition: attachment;filename="test.xls"');
  267.        // header('Cache-Control: max-age=0');
  268.        // $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  269.        // $objWriter->save('php://output');
  270.        
  271.       }
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!