PHPExcel export to excel in Codeigniter looping data array

Question

I have a problem in PHPExcel in Codeigniter. I want to export data into excel file. It has 10 row data, like this:

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

Here's my 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();
    }

And here's my model :

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

And the result of excel file like this :

---------------------------------------------
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
---------------------------------------------

The result is only show 10 rows with same data/person. I think the loop is not working. Any body help me?

Thanks for your attention

insire the foreach add var_dump($n) and see if its the expected result — user557846
– user557846, Commented Oct 2, 2014 at 3:39
Coba librarynya dipindah pake PhpSpreadsheet karena PHPExcel udah gak di maintenance lagi — Jhonny Jr.
– Jhonny Jr., Commented Apr 9, 2018 at 2:19

Community · Accepted Answer · 2020-06-20 09:12:55Z

SOLVED

here's my 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();
}

mzennis · Accepted Answer · 2014-10-16 08:26:47Z

2

try this to loop result :

    $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++;
    }

answered Oct 16, 2014 at 8:26

mzennis

1031 silver badge5 bronze badges

3 Comments

agung_phe Over a year ago

thanks for your answer but I've found the solution, here's my code $nil=$nilai->result(); $row = 2; $no = 1; foreach($nil as $n){ $objPHPExcel->getActiveSheet()->setCellValue('A'.$row,$no); $objPHPExcel->getActiveSheet()->setCellValue('B'.$row,$n->nim);

agung_phe Over a year ago

$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++; }

agung_phe Over a year ago

I set each cell value up with each data value from db. Thanks for your answer.

Collectives™ on Stack Overflow

PHPExcel export to excel in Codeigniter looping data array

2 Answers 2

SOLVED

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

SOLVED

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related