Tag Archives: phpexcel

Easily integrate/load PHPExcel into CodeIgniter Framework

PHPExcel is a set of PHP classes/library to read and write Excel files (.XLS/.XLSX). Based on CodeIgniter wiki page, integrating and using PHPExcel into CodeIgniter framework need some modification in PHPExcel source code. With the steps that I will show you, you will not need to edit any PHPExcel source code and the usage is very convenient just like you use on “plain-straight” PHP.

Pre-requisite: I tested this on CodeIgniter 2.1 and PHPExcel 1.7.7

Here we go:

  • download PHPExcel from https://github.com/PHPOffice/PHPExcel (yup, they move from codeplex to github)
  • Extract the compressed archive (.zip or .tar.gz). Only extract the files inside Classes folder. Extract them to CodeIgniter’s application/third_party/ folder. Your application/third_party/ folder will look like this:
extract phpexcel into codeigniter

CI application folder’s structure

  • Create new PHP file inside CI’s application/libraries/ name it Excel.php. This is the source code (source [Bahasa Indonesia] [DEAD LINK!!]: http://belajarcoding.com/php/codeigniter/membuat-laporan-excel-di-php-dengan-codeigniter-dan-phpexcel.html):

  • Your CI's application/libraries/ folder will looke like this:
  • codeigniter's application/libraries stuctureThat's it. Now you can use PHPExcel library in your CodeIgniter's controller code.

Example usage (with commentary):

//load our new PHPExcel library
//activate worksheet number 1
//name the worksheet
$this->excel->getActiveSheet()->setTitle('test worksheet');
//set cell A1 content with some text
$this->excel->getActiveSheet()->setCellValue('A1', 'This is just some text value');
//change the font size
//make the font become bold
//merge cell A1 until D1
//set aligment to center for that merged cell (A1 to D1)

$filename='just_some_random_name.xls'; //save our workbook as this file name
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
//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, 'Excel5');  
//force user to download the Excel file without writing it to server's HD

Have fun with CodeIgniter and PHPExcel! 🙂

Read and write Excel file using PHP (example)

This post will describe a basic usage of PHPExcel to read and write Excel file. Although in this demo I use .xls extension, PHPExcel really can write and read Excel 2007 (.xlsx) files too.

Download PHPExcel here (free of charge, it’s open source 😀 ).

Unzip the PHPExcel .zip file into your project destination. You only need to extract “Classes” folder.

Our scenario: user will fill some HTML form then from this input, we will populate a new Excel file (.xls) and send it to user as downloadable file.

Our Excel template screenshot

Excel template screen shot

We will fill C1 cell with the current date when the form submitted. C3 cell will be the user’s name and C4 cell will contain user’s message. Our template file –> http://demo.ahowto.net/phpexcel/template.xls

Full source code of our simple form can be seen by viewing source code (Ctrl+U on Firefox) our demo URL: http://demo.ahowto.net/phpexcel/

Form inputs will be processed by process.php below:


//processing form submitted

if (!isset($_POST['btnSubmit'])) exit;

//include PHPExcel library
require_once "Classes/PHPExcel/IOFactory.php";

//load Excel template file
$objTpl = PHPExcel_IOFactory::load("template.xls");
$objTpl->setActiveSheetIndex(0);  //set first sheet as active

$objTpl->getActiveSheet()->setCellValue('C2', date('Y-m-d'));  //set C1 to current date
$objTpl->getActiveSheet()->getStyle('C2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); //C1 is right-justified

$objTpl->getActiveSheet()->setCellValue('C3', stripslashes($_POST['txtName']));
$objTpl->getActiveSheet()->setCellValue('C4', stripslashes($_POST['txtMessage']));

$objTpl->getActiveSheet()->getStyle('C4')->getAlignment()->setWrapText(true);  //set wrapped for some long text message

$objTpl->getActiveSheet()->getColumnDimension('C')->setWidth(40);  //set column C width
$objTpl->getActiveSheet()->getRowDimension('4')->setRowHeight(120);  //set row 4 height
$objTpl->getActiveSheet()->getStyle('A4:C4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP); //A4 until C4 is vertically top-aligned

//prepare download
$filename=mt_rand(1,100000).'.xls'; //just some random filename
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objTpl, 'Excel5');  //downloadable file is in Excel 2003 format (.xls)
$objWriter->save('php://output');  //send it to user, of course you can save it to disk also!

exit; //done.. exiting!

I already put a lot of comment on the codes. It should be clear. On the code above, I show you how to read Excel .xls file, change cell content, formatting a cell (alignment), change column width, set row height and finally “force” send result file to user.

The Excel result file should be like this:

Our Excel result from template created by PHP

You can do more than I showed here (such as setting cell color, rotation, inserting images, formula, printing, etc.)  by reading PHPExcel Documentation and see the codes in Tests folder (it is included in the PHPExcel download zip).