Skip to content

Instantly share code, notes, and snippets.

@chabibnr
Created August 8, 2017 04:02
Show Gist options
  • Save chabibnr/91738194134bf33494600e49b7d0e36c to your computer and use it in GitHub Desktop.
Save chabibnr/91738194134bf33494600e49b7d0e36c to your computer and use it in GitHub Desktop.
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]);
*/
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment