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

If you want to just start dumping things to a csv file just open up a file handle to php://temp or something, use fputcsv() til you're done with your data, and send that file to the browser with the appropriate headers. — Zarathuztra
– Zarathuztra, Commented Dec 9, 2013 at 3:05

efenacigiray · Accepted Answer · 2013-12-16 12:09:01Z

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

Piyush Balapure · Accepted Answer · 2016-09-08 12:36:05Z

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

haakym · Accepted Answer · 2014-01-28 11:03:59Z

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)

answered Jan 28, 2014 at 11:03

haakym

12.4k12 gold badges72 silver badges101 bronze badges

Comments

AbcAeffchen · Accepted Answer · 2015-02-15 12:44:12Z

0

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

edited Feb 15, 2015 at 12:44

AbcAeffchen

15.1k15 gold badges52 silver badges67 bronze badges

answered Feb 15, 2015 at 12:26

stevec

1541 silver badge4 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related