PHPExcel экспорт, чтобы преуспеть в массиве циклических данных Codeigniter
У меня есть проблема в PHPExcel в Codeigniter. Я хочу экспортировать данные в файл Excel. Он имеет 10 строк данных, например:
-----------------------------------------------
No | NIM | Mahasiswa | Benar | Salah | Nilai
-----------------------------------------------
1 | G1A007001 | Hikmah Faridah | 16 | 84 | 16
-----------------------------------------------
.. | ..........| .............. |....|....|....
-----------------------------------------------
10 | K1A006001 | Erni Noviani | 0 | 5 | 0
-----------------------------------------------
Вот мой контроллер:
public function export(){
$id=$this->uri->segment(3);
$this->load->model('m_user');
$mhs=$this->m_user->edit_sesi($id);
foreach($mhs->result() as $m):
$nama_sesi=$m->nama_sesi;
$nama_blok=$m->nama_blok;
$tgl=$m->tgl;
endforeach;
$nilai=$this->m_user->lihat_nilai_sesi($id);
$heading=array('No','NIM','Mahasiswa','Benar','Salah','Nilai');
$this->load->library('PHPExcel');
//Create a new Object
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle($nama_sesi);
//Loop Heading
$rowNumberH = 1;
$colH = 'A';
foreach($heading as $h){
$objPHPExcel->getActiveSheet()->setCellValue($colH.$rowNumberH,$h);
$colH++;
}
//Loop Result
$totn=$nilai->num_rows();
$no=1;
for($i=2;$i<=$totn+1;$i++){
foreach($nilai->result() as $n):
$objPHPExcel->getActiveSheet()->setCellValue('A'.$i,$no);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$i,$n->nim);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$i,$n->nama);
$objPHPExcel->getActiveSheet()->setCellValue('D'.$i,$n->benar);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$i,$n->salah);
$objPHPExcel->getActiveSheet()->setCellValue('F'.$i,$n->nilai);
endforeach;
$no++;
}
//Freeze pane
$objPHPExcel->getActiveSheet()->freezePane('A2');
//Save as an Excel BIFF (xls) file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Nilai-'.$nama_sesi.'-'.$nama_blok.'-'.$tgl.'.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
exit();
}
А вот и моя модель:
function lihat_nilai_sesi($id){
$this->db->select('*');
$this->db->from('hasil');
$this->db->join('mahasiswa','mahasiswa.username=hasil.username','left');
$this->db->order_by('mahasiswa.nim','asc');
$this->db->where('id_sesi',$id);
$query=$this->db->get();
return $query;
}
И результат файла Excel выглядит так:
---------------------------------------------
No | NIM | Mahasiswa | Benar | Salah | Nilai
---------------------------------------------
1 | K1A006001 | Erni Noviani | 0 | 5 | 0
---------------------------------------------
2 | K1A006001 | Erni Noviani | 0 | 5 | 0
---------------------------------------------
3 | K1A006001 | Erni Noviani | 0 | 5 | 0
---------------------------------------------
4 | K1A006001 | Erni Noviani | 0 | 5 | 0
---------------------------------------------
5 | K1A006001 | Erni Noviani | 0 | 5 | 0
---------------------------------------------
6 | K1A006001 | Erni Noviani | 0 | 5 | 0
---------------------------------------------
7 | K1A006001 | Erni Noviani | 0 | 5 | 0
---------------------------------------------
8 | K1A006001 | Erni Noviani | 0 | 5 | 0
---------------------------------------------
9 | K1A006001 | Erni Noviani | 0 | 5 | 0
---------------------------------------------
10 | K1A006001 | Erni Noviani | 0 | 5 | 0
---------------------------------------------
Результат показывает только 10 строк с одинаковыми данными на человека. Я думаю, что петля не работает. Любое тело поможет мне?
Спасибо за внимание
Решение
вот мой контроллер:
public function export_nilai_sesi(){
$id=$this->uri->segment(3);
$this->load->model('m_user');
$mhs=$this->m_user->edit_sesi($id);
foreach($mhs->result() as $m):
$nama_sesi=$m->nama_sesi;
$nama_blok=$m->nama_blok;
$tgl=$m->tgl;
endforeach;
$nilai=$this->m_user->lihat_nilai_sesi($id);
$heading=array('No','NIM','Mahasiswa','Benar','Salah','Nilai');
$this->load->library('PHPExcel');
//Create a new Object
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle($nama_sesi);
//Loop Heading
$rowNumberH = 1;
$colH = 'A';
foreach($heading as $h){
$objPHPExcel->getActiveSheet()->setCellValue($colH.$rowNumberH,$h);
$colH++;
}
//Loop Result
$totn=$nilai->num_rows();
$maxrow=$totn+1;
$nil=$nilai->result();
$row = 2;
$no = 1;
foreach($nil as $n){
//$numnil = (float) str_replace(',','.',$n->nilai);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$row,$no);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$row,$n->nim);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$row,$n->nama);
$objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$row,$n->benar,PHPExcel_Cell_DataType::TYPE_NUMERIC);
$objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$row,$n->salah,PHPExcel_Cell_DataType::TYPE_NUMERIC);
$objPHPExcel->getActiveSheet()->getStyle('F'.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$row,$n->nilai,PHPExcel_Cell_DataType::TYPE_NUMERIC);
$row++;
$no++;
}
//Freeze pane
$objPHPExcel->getActiveSheet()->freezePane('A2');
//Cell Style
$styleArray = array(
'borders' => array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
)
)
);
$objPHPExcel->getActiveSheet()->getStyle('A1:F'.$maxrow)->applyFromArray($styleArray);
//Save as an Excel BIFF (xls) file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Nilai-'.$nama_sesi.'-'.$nama_blok.'-'.$tgl.'.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
exit();
}
Другие решения
попробуйте это зациклить результат:
$fields = $nilai->list_fields();
$row = 2;
foreach($nilai->result() as $data)
{
$col = 0;
foreach ($fields as $field)
{
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->$field);
$col++;
}
$row++;
}