API tools faq
paste
Advertisement
Syehaji93

ajax export excel rev-1

Syehaji93
Mar 25th, 2023
791
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 10.73 KB | Source Code | 0 0
raw download clone embed print report
  1. // controller
  2.  
  3.     public function export()
  4.     {
  5.         // $isValidSource = !empty($_SERVER['HTTP_REFERER'])&&strpos($_SERVER['HTTP_REFERER'],site_url());
  6.         $isValidSource = 1;
  7.         //if($this->request->isAJAX())
  8.         if($isValidSource)
  9.         {
  10. //----------seharusnya ini POST jangan GET..
  11.             $fromDate = $this->request->getVar('fromDate');
  12.             $toDate = $this->request->getVar('toDate');
  13.             $submit = $this->request->getVar('submit');
  14.  
  15.             $response = array();
  16.      
  17.             // Read new token and assign in $response['token']
  18.             $response['token'] = csrf_hash();
  19.  
  20.             /* created excel */
  21.             $spreadsheet = new Spreadsheet();
  22.             $sheet = $spreadsheet->getActiveSheet();
  23.  
  24.             // Buat sebuah variabel untuk menampung pengaturan style dari header tabel
  25.             $style_col = [
  26.                 'font' => ['bold' => true], // Set font nya jadi bold
  27.                 'alignment' => [
  28.                     'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, // Set text jadi ditengah secara horizontal (center)
  29.                     'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
  30.                 ],
  31.                 'borders' => [
  32.                     'top' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border top dengan garis tipis
  33.                     'right' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],  // Set border right dengan garis tipis
  34.                     'bottom' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border bottom dengan garis tipis
  35.                     'left' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN] // Set border left dengan garis tipis
  36.                     ]
  37.             ];
  38.  
  39.             // Buat sebuah variabel untuk menampung pengaturan style dari isi tabel
  40.             $style_row = [
  41.                 'alignment' => [
  42.                     'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
  43.                 ],
  44.                 'borders' => [
  45.                     'top' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border top dengan garis tipis
  46.                     'right' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],  // Set border right dengan garis tipis
  47.                     'bottom' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border bottom dengan garis tipis
  48.                     'left' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN] // Set border left dengan garis tipis
  49.                 ]
  50.             ];
  51.  
  52. //----------mohon cari cara penulisan value cell yang lebih bisa terbaca.. jadi tidak menuliskan A1
  53. //----------contoh
  54. //---------- $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
  55.  
  56.             //$sheet->setCellValueByColumnAndRow(1,1, "CHECK SHEET VERIFIKASI HASIL UPLOAD");
  57.             $sheet->setCellValue('A1', "CHECK SHEET VERIFIKASI HASIL UPLOAD"); // Set kolom A1 dengan tulisan "DATA SISWA"
  58.             $sheet->mergeCells('A1:K1'); // Set Merge Cell pada kolom A1 sampai E1
  59.             $sheet->getStyle('A1')->getFont()->setBold(true); // Set bold kolom A1
  60.  
  61.             // Buat header tabel nya pada baris ke 3
  62.             $sheet->setCellValue('A3', "NO");
  63.             $sheet->setCellValue('B3', "TIPE");
  64.             $sheet->setCellValue('C3', "LOT");
  65.             $sheet->setCellValue('D3', "DELIVERY");
  66.             $sheet->setCellValue('E3', "SIDE MAIN");
  67.             $sheet->setCellValue('F3', "PIC");
  68.             $sheet->setCellValue('G3', "SIDE B");
  69.             $sheet->setCellValue('H3', "PIC");
  70.             $sheet->setCellValue('I3', "SIDE A");
  71.             $sheet->setCellValue('J3', "PIC");
  72.             $sheet->setCellValue('K3', "RESULT");
  73.  
  74.             // Apply style header yang telah kita buat tadi ke masing-masing kolom header
  75.             $sheet->getStyle('A3')->applyFromArray($style_col);
  76.             $sheet->getStyle('B3')->applyFromArray($style_col);
  77.             $sheet->getStyle('C3')->applyFromArray($style_col);
  78.             $sheet->getStyle('D3')->applyFromArray($style_col);
  79.             $sheet->getStyle('E3')->applyFromArray($style_col);
  80.             $sheet->getStyle('F3')->applyFromArray($style_col);
  81.             $sheet->getStyle('G3')->applyFromArray($style_col);
  82.             $sheet->getStyle('H3')->applyFromArray($style_col);
  83.             $sheet->getStyle('I3')->applyFromArray($style_col);
  84.             $sheet->getStyle('J3')->applyFromArray($style_col);
  85.             $sheet->getStyle('K3')->applyFromArray($style_col);
  86.  
  87.             //panggil data checksheet   ch_delivery >= '".$start."' AND ch_delivery <= '".$end."'
  88.             $db = \Config\Database::connect();
  89.             $boms = $db->table('tb_cek_harian')->getWhere(['ch_delivery >=' => $fromDate, 'ch_delivery <=' => $toDate])->getResult();
  90.  
  91.             $no = 1; // Untuk penomoran tabel, di awal set dengan 1
  92.             $numrow = 4; // Set baris pertama untuk isi tabel adalah baris ke 4
  93.             foreach($boms as $dt => $data){ // Lakukan looping
  94.  
  95.                 $sheet->setCellValue('A'.$numrow, $no);
  96.                 $sheet->setCellValue('B'.$numrow, $data->ch_name);
  97.                 $sheet->setCellValue('C'.$numrow, $data->ch_lot);
  98.                 $sheet->setCellValue('D'.$numrow, $data->ch_delivery);
  99.                 $sheet->setCellValue('E'.$numrow, $data->ch_side_main);
  100.                 $sheet->setCellValue('F'.$numrow, $data->ch_pic_main);
  101.                 $sheet->setCellValue('G'.$numrow, $data->ch_side_b);
  102.                 $sheet->setCellValue('H'.$numrow, $data->ch_pic_b);
  103.                 $sheet->setCellValue('I'.$numrow, $data->ch_side_a);
  104.                 $sheet->setCellValue('J'.$numrow, $data->ch_pic_a);
  105.                 $sheet->setCellValue('K'.$numrow, $data->ch_result);
  106.  
  107.                 // Apply style row yang telah kita buat tadi ke masing-masing baris (isi tabel)
  108.                 $sheet->getStyle('A'.$numrow)->applyFromArray($style_row);
  109.                 $sheet->getStyle('B'.$numrow)->applyFromArray($style_row);
  110.                 $sheet->getStyle('C'.$numrow)->applyFromArray($style_row);
  111.                 $sheet->getStyle('D'.$numrow)->applyFromArray($style_row);
  112.                 $sheet->getStyle('E'.$numrow)->applyFromArray($style_row);
  113.                 $sheet->getStyle('F'.$numrow)->applyFromArray($style_row);
  114.                 $sheet->getStyle('G'.$numrow)->applyFromArray($style_row);
  115.                 $sheet->getStyle('H'.$numrow)->applyFromArray($style_row);
  116.                 $sheet->getStyle('I'.$numrow)->applyFromArray($style_row);
  117.                 $sheet->getStyle('J'.$numrow)->applyFromArray($style_row);
  118.                 $sheet->getStyle('K'.$numrow)->applyFromArray($style_row);
  119.  
  120.                 $no++; // Tambah 1 setiap kali looping
  121.                 $numrow++; // Tambah 1 setiap kali looping
  122.             }
  123.  
  124.             // Set width kolom
  125.             $sheet->getColumnDimension('A')->setWidth(5); // Set width kolom A
  126.             $sheet->getColumnDimension('B')->setWidth(15); // Set width kolom B
  127.             $sheet->getColumnDimension('C')->setWidth(25); // Set width kolom C
  128.             $sheet->getColumnDimension('D')->setWidth(20); // Set width kolom D
  129.             $sheet->getColumnDimension('E')->setWidth(30); // Set width kolom E
  130.             $sheet->getColumnDimension('F')->setWidth(30); // Set width kolom E
  131.             $sheet->getColumnDimension('G')->setWidth(30); // Set width kolom E
  132.             $sheet->getColumnDimension('H')->setWidth(30); // Set width kolom E
  133.             $sheet->getColumnDimension('I')->setWidth(30); // Set width kolom E
  134.             $sheet->getColumnDimension('J')->setWidth(30); // Set width kolom E
  135.             $sheet->getColumnDimension('K')->setWidth(30); // Set width kolom E
  136.  
  137.             // Set height semua kolom menjadi auto (mengikuti height isi dari kolommnya, jadi otomatis)
  138.             $sheet->getDefaultRowDimension()->setRowHeight(-1);
  139.  
  140.             // Set orientasi kertas jadi LANDSCAPE
  141.             $sheet->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
  142.  
  143.             // Set judul file excel nya
  144.             $sheet->setTitle("Checksheet");
  145.             /* ./ created excel */
  146.  
  147.             $writer = new Xlsx($spreadsheet);
  148.             $filename = date('Y-m-d-His'). '-Data-Verifikasi';
  149.  
  150.             $writer->save('php://output');
  151.  
  152.             header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  153.             header("Pragma: public");
  154.             header("Expires: 0");
  155.             header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
  156.             header("Content-Type: application/force-download");
  157.             header("Content-Type: application/octet-stream");
  158.             header("Content-Type: application/download");;
  159.             header('Content-Disposition: attachment;filename='  . $filename . '.xlsx');
  160.  
  161.             readfile($filename); // send file
  162.             sleep(5);
  163.             unlink($filename); // delete file
  164.             exit;
  165.            
  166.             $data = [
  167.                 'filename' => $filename.'.xlsx',
  168.                 'token' => csrf_hash(),
  169.             ];
  170.  
  171.             echo json_encode($data);
  172.            
  173.         }
  174.         else{
  175.             exit('Maaf request tidak dapat diproses!');
  176.         }
  177.     }
  178.  
  179.  
  180. //ajax call
  181.     // export data checksheet
  182.     $('#export').on('click', function(e){
  183.         e.preventDefault();
  184.         var fromDate = $("#fromDate").val();
  185.         var toDate = $("#toDate").val();
  186. //----mohon tambahkan filter yg lebih baik lagi ===
  187.         if(fromDate == ''){
  188.           error_firstname = 'please enter from delivery date';
  189.           alert(error_firstname);
  190.         }
  191.         else if(toDate == ''){
  192.           error_lastname = 'please enter to delivery date';
  193.           alert(error_lastname);
  194.         }else{
  195.             console.log('test');
  196.             // CSRF Hash
  197.             var csrfName = $('.txt_csrfname').attr('name'); // CSRF Token name
  198.             var csrfHash = $('.txt_csrfname').val(); // CSRF hash
  199.  
  200.             var dataJson = {
  201.                 [csrfName]: csrfHash,
  202.                 fromDate: $("#fromDate").val(),
  203.                 toDate: $("#toDate").val()
  204.             };
  205.             var urlTarget = '<?= site_url('/checksheet/export'); ?>';
  206.             window.location = urlTarget+'?fromDate='+fromDate+"&toDate="+toDate+"&csrfHash="+csrfHash;
  207.             return false;
  208.         }
  209.     });
Tags: phpspreadsheet export excel ajax
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!