Export excel using phpspreadsheet and ajax

    //Controller
    public function export()
    {
        if($this->request->isAJAX())
        {
            $fromDate = $this->request->getVar('fromDate');
            $toDate = $this->request->getVar('toDate');
            $submit = $this->request->getVar('submit');

            $response = array();

            // Read new token and assign in $response['token']
            $response['token'] = csrf_hash();

            /* created excel */
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();

            // Buat sebuah variabel untuk menampung pengaturan style dari header tabel
            $style_col = [
                'font' => ['bold' => true], // Set font nya jadi bold
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, // Set text jadi ditengah secara horizontal (center)
                    'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
                ],
                'borders' => [
                    'top' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border top dengan garis tipis
                    'right' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],  // Set border right dengan garis tipis
                    'bottom' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border bottom dengan garis tipis
                    'left' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN] // Set border left dengan garis tipis
                    ]
            ];

            // Buat sebuah variabel untuk menampung pengaturan style dari isi tabel
            $style_row = [
                'alignment' => [
                    'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
                ],
                'borders' => [
                    'top' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border top dengan garis tipis
                    'right' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],  // Set border right dengan garis tipis
                    'bottom' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border bottom dengan garis tipis
                    'left' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN] // Set border left dengan garis tipis
                ]
            ];

            $sheet->setCellValue('A1', "CHECK SHEET VERIFIKASI HASIL UPLOAD"); // Set kolom A1 dengan tulisan "DATA SISWA"
            $sheet->mergeCells('A1:K1'); // Set Merge Cell pada kolom A1 sampai E1
            $sheet->getStyle('A1')->getFont()->setBold(true); // Set bold kolom A1

            // Buat header tabel nya pada baris ke 3
            $sheet->setCellValue('A3', "NO");
            $sheet->setCellValue('B3', "TIPE");
            $sheet->setCellValue('C3', "LOT");
            $sheet->setCellValue('D3', "DELIVERY");
            $sheet->setCellValue('E3', "SIDE MAIN");
            $sheet->setCellValue('F3', "PIC");
            $sheet->setCellValue('G3', "SIDE B");
            $sheet->setCellValue('H3', "PIC");
            $sheet->setCellValue('I3', "SIDE A");
            $sheet->setCellValue('J3', "PIC");
            $sheet->setCellValue('K3', "RESULT");

            // Apply style header yang telah kita buat tadi ke masing-masing kolom header
            $sheet->getStyle('A3')->applyFromArray($style_col);
            $sheet->getStyle('B3')->applyFromArray($style_col);
            $sheet->getStyle('C3')->applyFromArray($style_col);
            $sheet->getStyle('D3')->applyFromArray($style_col);
            $sheet->getStyle('E3')->applyFromArray($style_col);
            $sheet->getStyle('F3')->applyFromArray($style_col);
            $sheet->getStyle('G3')->applyFromArray($style_col);
            $sheet->getStyle('H3')->applyFromArray($style_col);
            $sheet->getStyle('I3')->applyFromArray($style_col);
            $sheet->getStyle('J3')->applyFromArray($style_col);
            $sheet->getStyle('K3')->applyFromArray($style_col);

            //panggil data checksheet   ch_delivery >= '".$start."' AND ch_delivery <= '".$end."'
            $db = \Config\Database::connect();
            $boms = $db->table('tb_cek_harian')->getWhere(['ch_delivery >=' => $fromDate, 'ch_delivery <=' => $toDate])->getResult();

            $no = 1; // Untuk penomoran tabel, di awal set dengan 1
            $numrow = 4; // Set baris pertama untuk isi tabel adalah baris ke 4
            foreach($boms as $dt => $data){ // Lakukan looping

                $sheet->setCellValue('A'.$numrow, $no);
                $sheet->setCellValue('B'.$numrow, $data->ch_name);
                $sheet->setCellValue('C'.$numrow, $data->ch_lot);
                $sheet->setCellValue('D'.$numrow, $data->ch_delivery);
                $sheet->setCellValue('E'.$numrow, $data->ch_side_main);
                $sheet->setCellValue('F'.$numrow, $data->ch_pic_main);
                $sheet->setCellValue('G'.$numrow, $data->ch_side_b);
                $sheet->setCellValue('H'.$numrow, $data->ch_pic_b);
                $sheet->setCellValue('I'.$numrow, $data->ch_side_a);
                $sheet->setCellValue('J'.$numrow, $data->ch_pic_a);
                $sheet->setCellValue('K'.$numrow, $data->ch_result);

                // Apply style row yang telah kita buat tadi ke masing-masing baris (isi tabel)
                $sheet->getStyle('A'.$numrow)->applyFromArray($style_row);
                $sheet->getStyle('B'.$numrow)->applyFromArray($style_row);
                $sheet->getStyle('C'.$numrow)->applyFromArray($style_row);
                $sheet->getStyle('D'.$numrow)->applyFromArray($style_row);
                $sheet->getStyle('E'.$numrow)->applyFromArray($style_row);
                $sheet->getStyle('F'.$numrow)->applyFromArray($style_row);
                $sheet->getStyle('G'.$numrow)->applyFromArray($style_row);
                $sheet->getStyle('H'.$numrow)->applyFromArray($style_row);
                $sheet->getStyle('I'.$numrow)->applyFromArray($style_row);
                $sheet->getStyle('J'.$numrow)->applyFromArray($style_row);
                $sheet->getStyle('K'.$numrow)->applyFromArray($style_row);

                $no++; // Tambah 1 setiap kali looping
                $numrow++; // Tambah 1 setiap kali looping
            }

            // Set width kolom
            $sheet->getColumnDimension('A')->setWidth(5); // Set width kolom A
            $sheet->getColumnDimension('B')->setWidth(15); // Set width kolom B
            $sheet->getColumnDimension('C')->setWidth(25); // Set width kolom C
            $sheet->getColumnDimension('D')->setWidth(20); // Set width kolom D
            $sheet->getColumnDimension('E')->setWidth(30); // Set width kolom E
            $sheet->getColumnDimension('F')->setWidth(30); // Set width kolom E
            $sheet->getColumnDimension('G')->setWidth(30); // Set width kolom E
            $sheet->getColumnDimension('H')->setWidth(30); // Set width kolom E
            $sheet->getColumnDimension('I')->setWidth(30); // Set width kolom E
            $sheet->getColumnDimension('J')->setWidth(30); // Set width kolom E
            $sheet->getColumnDimension('K')->setWidth(30); // Set width kolom E

            // Set height semua kolom menjadi auto (mengikuti height isi dari kolommnya, jadi otomatis)
            $sheet->getDefaultRowDimension()->setRowHeight(-1);

            // Set orientasi kertas jadi LANDSCAPE
            $sheet->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);

            // Set judul file excel nya
            $sheet->setTitle("Checksheet");
            /* ./ created excel */

            $writer = new Xlsx($spreadsheet);
            $filename = date('Y-m-d-His'). '-Data-Verifikasi';

            $writer->save('php://output');

            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header("Pragma: public");
            header("Expires: 0");
            header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
            header("Content-Type: application/force-download");
            header("Content-Type: application/octet-stream");
            header("Content-Type: application/download");;
            header('Content-Disposition: attachment;filename='  . $filename . '.xlsx');

            readfile($filename); // send file
            unlink($filename); // delete file
            exit;

            $data = [
                'filename' => $filename.'.xlsx',
                'token' => csrf_hash(),
            ];

            echo json_encode($data);

        }else{
            exit('Maaf request tidak dapat diproses!');
        }
    }


//ajax
// select data for check sheet
    $('#btn_e').on('click', function(e){
        e.preventDefault();
        var fromDate = $("#fromDate").val();
        var toDate = $("#toDate").val();

        if(fromDate == ''){
          error_firstname = 'please enter from delivery date';
          alert(error_firstname);
        }
        else if(toDate == ''){
          error_lastname = 'please enter to delivery date';
          alert(error_lastname);
        }else{
            console.log('test');
            // CSRF Hash
            var csrfName = $('.txt_csrfname').attr('name'); // CSRF Token name
            var csrfHash = $('.txt_csrfname').val(); // CSRF hash

            var dataJson = {
                [csrfName]: csrfHash,
                fromDate: $("#fromDate").val(),
                toDate: $("#toDate").val()
            };

            $.ajax({
                url: "<?php echo base_url('/checksheet/export'); ?>",
                type: "post",
                data: dataJson,
                dataType: "json",
                success: function(response)
                {
                    // Update CSRF Token
                    $('.txt_csrfname').val(data.token);
                    // console.log(data.filename);
                    window.location = '<?= site_url('/checksheet/export') ?>';

                    // formDate = "",
                    // toDate = ""
                },
                error: function (jqXhr, textStatus, errorMessage) { // error callback
                    alert(xhr.status + "\n" + xhr.responseText + "\n" + thrownError);
                }
            });
            return false;
        }
    });