How to export mysql table to csv or excel file using phpExcel in CODEIGNITER

Question

I've been searching on how to export mysql table to csv or excel file. I've seen some steps and I followed them. Is there a way on how to export the mysql table to csv or excel file using codeigniter?

I've tried this PHPExcel. But it seems not working to me.

function index()
{
    $query = $this->db->get('filter_result');

    if(!$query)
        return false;

    // Starting the PHPExcel library
    $this->load->library('PHPExcel');
    $this->load->library('PHPExcel/IOFactory');

    $objPHPExcel = new PHPExcel();
    $objPHPExcel->getProperties()->setTitle("export")->setDescription("none");

    $objPHPExcel->setActiveSheetIndex(0);

    // Field names in the first row
    $fields = $query->list_fields();
    $col = 0;
    foreach ($fields as $field)
    {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
        $col++;
    }

    // Fetching the table data
    $row = 2;
    foreach($query->result() as $data)
    {
        $col = 0;
        foreach ($fields as $field)
        {
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->$field);
            $col++;
        }

        $row++;
    }

    $objPHPExcel->setActiveSheetIndex(0);

    $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');

    // Sending headers to force the user to download the file
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="Products_'.date('dMy').'.xls"');
    header('Cache-Control: max-age=0');

    $objWriter->save('php://output');
}

Answer 1

efenacigiray's answer was great but i got a weird error as the xls is in different format than specified so i just made another function and it works great.

class excel {

    function create_excel($array) {
        // Create new PHPExcel object
        $objPHPExcel = new PHPExcel();

        // Set document properties
        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
                                                   ->setLastModifiedBy("Maarten Balliauw")
                                                   ->setTitle("Office 2007 XLSX Test Document")
                                                   ->setSubject("Office 2007 XLSX Test Document")
                                                   ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                                                   ->setKeywords("office 2007 openxml php")
                                                   ->setCategory("Test result file");

        //'id,name,contact_name,email,email2,mobile,mobile2,website,country,city,address,postal_code,info'
        // Add some data
        $objPHPExcel->setActiveSheetIndex(0)
                          ->setCellValue('A1', 'Id')
                          ->setCellValue('B1', 'name!')
                          ->setCellValue('C1', 'contact_name')
                          ->setCellValue('D1', 'email')
                          ->setCellValue('K1', 'address')
                          ->setCellValue('L1', 'postal_code');
        $i = 2;
        foreach($array as $row){
              $objPHPExcel->setActiveSheetIndex(0)
                          ->setCellValue('A'.$i, $row['id'])
                          ->setCellValue('B'.$i, $row['name'])
                          ->setCellValue('C'.$i, $row['contact_name'])
                          ->setCellValue('D'.$i, $row['email'])
                          ->setCellValue('K'.$i, $row['address'])
                          ->setCellValue('L'.$i, $row['postal_code']);
              $i++;
        }

        // Miscellaneous glyphs, UTF-8
        //          $objPHPExcel->setActiveSheetIndex(0)
        //                            ->setCellValue('A4', 'Miscellaneous glyphs')
        //                            ->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç');

        // Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle('Probable Clients');


        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);


        // Redirect output to a client’s web browser (Excel5)
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="probClients.xls"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }
}

Answer 2

for me.

print "\xEF\xBB\xBF"; // UTF-8 BOM <- this line cuased an error

delete this line and use

echo '<meta http-equiv="content-type" content="application/xhtml+xml; charset=UTF-8" />';

this meta tag instead.

Thanks to efenacigiray

Answer 3

I found efenacigiray's code worked for me too, but I had to remove the code '->result_array()'

Using code igniter version: 2.1.4

(Would have wrote this as a comment, but I don't have enough rep yet)

Answer 4

Here is a code I use.

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');  
class excel{

function to_excel($array, $filename) {
    header('Content-Disposition: attachment; filename='.$filename.'.xls');
    header('Content-type: application/force-download');
    header('Content-Transfer-Encoding: binary');
    header('Pragma: public');
    print "\xEF\xBB\xBF"; // UTF-8 BOM
    $h = array();
    foreach($array->result_array() as $row){
        foreach($row as $key=>$val){
            if(!in_array($key, $h)){
                $h[] = $key;   
            }
        }
    }
    echo '<table><tr>';
    foreach($h as $key) {
        $key = ucwords($key);
        echo '<th>'.$key.'</th>';
    }
    echo '</tr>';

    foreach($array->result_array() as $row){
        echo '<tr>';
        foreach($row as $val)
            $this->writeRow($val);   
    }
    echo '</tr>';
    echo '</table>';


}

function writeRow($val) {
    echo '<td>'.$val.'</td>';              
}

}
?>

Create a library with this code and call it as:

public function brandExcel() {
    $this->load->library('excel');
    $result = $this->config_model->getBrandsForExcel();
    $this->excel->to_excel($result, 'brands-excel'); 
}