actionExportExcel

public function actionExportExcel($id) {
        $kuesioner = \app\models\Kuesioner::find()->where(['id' => $id])->one();
        $sqlProyek = "SELECT * FROM proyek WHERE id = '" . $kuesioner->proyek . "' ";
        $rowSqlProyek = Yii::$app->db->createCommand($sqlProyek)->queryOne();

        $filename = 'Indosurvey - Export Kuesioner ( ' . $kuesioner->judul_kuesioner . ' ) ' . date('Y-m-d') . '.xlsx';

        $columnName = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q'];
        $column[] = ["No","Judul Kelompok","Pertanyaan","Kolom","Jenis","Opsi","Tanggal dibuat","Tanggal diupdate","Urutan","Min","Max","Default","Keterangan","Wajib","Score", "Pertanyaan Selanjutnya", ""];

        $sqlListPertanyaan = "SELECT * FROM kuesioner_pertanyaan WHERE kuesioner = '" . $kuesioner->id . "' ";
        $listPertanyaan = Yii::$app->db->createCommand($sqlListPertanyaan)->queryAll();

        $group = [];
        foreach($listPertanyaan as $pertanyaan){
            if(empty($pertanyaan['group'])) {
                $group[$pertanyaan['id']] = $pertanyaan;
                $group[$pertanyaan['id']]['childrens'] = [];
                $group[$pertanyaan['id']]['advance'] = [];
                if($pertanyaan['jenis'] == 'single'){
                    $advance = KuesionerAdvancedOption::findAll(['pertanyan' => $pertanyaan['id']]);
                    if($advance != false){
                        $score = [];
                        $selanjutnya = [];
                        foreach ($advance as $item){
                            $score[] =  $item->id.':'.$item->score;
                            $selanjutnya[] = $item->pertanyaan_selanjutnya;
                            /*
                            $group[$pertanyaan['id']]['advance'][] = [
                                'score' => $item->score,
                                'pertanyaan_selanjutnya' => $item->pertanyaan_selanjutnya,
                                'jawaban' => $item->jawaban,
                                'pertanyaan' => $item->pertanyan,
                                'id' => $item->id
                            ]; */

                        }
                        $group[$pertanyaan['id']]['advance'] = [
                            'score' => implode(',', $score),
                            'pertanyaan_selanjutnya' => implode(',', $selanjutnya)
                        ];
                    }
                }
            } else {
                $group[$pertanyaan['group']]['childrens'][] = $pertanyaan;
            }
        }

        $no = 1;
        foreach ($group as $index => $rowListPertanyaan){
            $column[] = [
                $no,
                $rowListPertanyaan['group_title'],
                $rowListPertanyaan['pertanyaan'],
                $rowListPertanyaan['list_pertanyaan_add'],
                $rowListPertanyaan['jenis'],
                $rowListPertanyaan['opsi'],
                $rowListPertanyaan['tanggal_dibuat'],
                $rowListPertanyaan['tanggal_diupdate'],
                $rowListPertanyaan['urutan'],
                $rowListPertanyaan['min'],
                $rowListPertanyaan['max'],
                $rowListPertanyaan['default'],
                $rowListPertanyaan['keterangan'],
                $rowListPertanyaan['required'],
                isset($rowListPertanyaan['advance']['score']) ? $rowListPertanyaan['advance']['score'] : "",
                isset($rowListPertanyaan['advance']['pertanyaan_selanjutnya']) ? $rowListPertanyaan['advance']['pertanyaan_selanjutnya'] : "",
                $rowListPertanyaan['id']
            ];
            foreach($rowListPertanyaan['childrens'] as $childrenPertanyaan){
                $column[] = [
                    "",
                    $childrenPertanyaan['group_title'],
                    $childrenPertanyaan['pertanyaan'],
                    $childrenPertanyaan['list_pertanyaan_add'],
                    $childrenPertanyaan['jenis'],
                    $childrenPertanyaan['opsi'],
                    $childrenPertanyaan['tanggal_dibuat'],
                    $childrenPertanyaan['tanggal_diupdate'],
                    $childrenPertanyaan['urutan'],
                    $childrenPertanyaan['min'],
                    $childrenPertanyaan['max'],
                    $childrenPertanyaan['default'],
                    $childrenPertanyaan['keterangan'],
                    $childrenPertanyaan['required'],
                    "",
                    "",
                    $childrenPertanyaan['id']
                ];
            }
            $no++;
        }

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->setActiveSheetIndex(0);

        $sheet->setCellValue("A1", "Indosurvey - Export Kuesioner ( ". $rowSqlProyek['judul'] ." )");
        $sheet->setCellValue("A2", "Detail Kuesioner");
        $sheet->setCellValue("A3", "Judul Kuesioner : " .$kuesioner->judul_kuesioner);
        $sheet->setCellValue("A4", "Proyek : ". $rowSqlProyek['judul']);
        $sheet->setCellValue("A5", "Tanggal dibuat :". $kuesioner->tanggal_dibuat);
        $sheet->setCellValue("A6", "Tanggal diupdate : ". $kuesioner->tanggal_diupdate);
        $sheet->setCellValue("A7", "");

        $rowCount = 8;
        foreach($column as $row){

            foreach($row as $key => $v){
                $sheet->setCellValue($columnName[$key]. $rowCount, $v);
            }
            $rowCount++;
        }

        $objPHPExcel->getActiveSheet()->setTitle("Indo Survey");
        $objPHPExcel->setActiveSheetIndex(0);

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;

        /*
        header("Content-type: application/vnd-ms-excel");
        header("Content-Disposition: attachment; filename=" . $filename);

        return $this->renderPartial('_partial/_export_xls', ['kuesioner' => $kuesioner]);
        */
    }