PHPExcel 导出到 Codeigniter 循环数据数组中的 excel

Question

我在 Codeigniter 的 PHPExcel 中遇到问题。我想将数据导出到 excel 文件中。它有 10 行数据，如下所示:

-----------------------------------------------
No | NIM | Mahasiswa | Benar | Salah | Nilai
-----------------------------------------------
1  | G1A007001 | Hikmah Faridah | 16 | 84 | 16
-----------------------------------------------
.. | ..........| .............. |....|....|....
-----------------------------------------------
10 | K1A006001 | Erni Noviani   | 0  |  5 | 0
-----------------------------------------------

这是我的 Controller :

  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 行具有相同数据/人。我认为循环不起作用。任何人帮助我吗？

感谢您的关注

Answer 1

已解决

这是我的 Controller :

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();
}