How to use PHPExcel with CodeIgniter 2.x and 3.x

PHPExcel is a pure PHP library for reading and writing spreadsheet files and CodeIgniter is one of the well known PHP MVC frameworks.

Here i show you how to Integrate PHPEXcel library in CodeIgniter.

Setp1: Download and setup CodeIgniter.(download it here: https://ellislab.com/codeigniter)

Setp2: Download PHPExcel.(download it here: http://phpexcel.codeplex.com/)

Setp3: Unzip or extract the downloaded PHPExcel lib files and copy Class directory inside files to application/third-party directory (folder).

Setp4: Now create one file called EXcel.php in application/library folder [application/library/Excel.php]. Then include PHPExcel Class to it. See how i did it below

<?php

if (!defined('BASEPATH')) exit('No direct script access allowed'); 

require_once APPPATH."/third_party/PHPExcel.php";

class Excel extends PHPExcel {

    public function __construct() {

        parent::__construct();

    }

}

?>

That’s it now you can use PHPEXcel methods inside your CodeIgniter application.But you should load it before using it methods(Ex: $this->load->library(‘excel’);) 

How to read excel file?

<?

$file = './files/test.xlsx';

//load the excel library

$this->load->library('excel');

//read file from path

$objPHPExcel = PHPExcel_IOFactory::load($file);

//get only the Cell Collection

$cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection();

//extract to a PHP readable array format

foreach ($cell_collection as $cell) {

    $column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn();

    $row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();

    $data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();

    //header will/should be in row 1 only. of course this can be modified to suit your need.

    if ($row == 1) {

        $header[$row][$column] = $data_value;

    } else {

        $arr_data[$row][$column] = $data_value;

    }

}

//send the data in an array format

$data['header'] = $header;

$data['values'] = $arr_data;

 ?>

How to Create excel file on the file?

<?

//load our new PHPExcel library

$this->load->library('excel'); 

//activate worksheet number 1

$this->excel->setActiveSheetIndex(0);

// Set document properties

                $this->excel->getProperties()->setCreator("innovision2u.com")

                                               ->setLastModifiedBy("innovision2u")

                                            ->setTitle("Office 2007 XLSX Test Document")

                                        ->setSubject("Office 2007 XLSX Test Document")

                                            ->setDescription("Test document for Office 2007 XLSX, generated by PHP classes.")

                                            ->setKeywords("office 2007 openxml php")

                                   ->setCategory("Test result file");

//set cell A1 content with some text

$this->excel->getActiveSheet()->setCellValue('A1', 'This is just some text value');

//change the font size

$this->excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);

//make the font become bold

$this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);

//merge cell A1 until D1

$this->excel->getActiveSheet()->mergeCells('A1:D1');

//set aligment to center for that merged cell (A1 to D1)

$this->excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$filename='filename'.date('dmYHis').'.xlsx'; //save our workbook as this file name

 //save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)

//if you want to save it as .XLSX Excel 2007 format

$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel2007');

ob_end_clean();

header('Content-Type: application/vnd.ms-excel'); //mime type

header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name

header('Cache-Control: max-age=0'); //no cache

//force user to download the Excel file without writing it to server's HD

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