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:
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); /* * ======================================= * Author : Muhammad Surya Ikhsanudin * License : Protected * Email : mutofiyah@gmail.com * * Dilarang merubah, mengganti dan mendistribusikan * ulang tanpa sepengetahuan Author * ======================================= */ require_once APPPATH."/third_party/PHPExcel.php"; class Excel extends PHPExcel { public function __construct() { parent::__construct(); } } |
Example usage (with commentary):
//load our new PHPExcel library $this->load->library('excel'); //activate worksheet number 1 $this->excel->setActiveSheetIndex(0); //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 $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='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 $objWriter->save('php://output'); |
Have fun with CodeIgniter and PHPExcel!
This was amazingly simple. I tried a bunch of other examples and they all failed miserably. This one worked first time with the least amount of work.
Thank you!
This is the best post about Codeigniter + PHPExcel i’ve read ever
I was able to take what I learned here and integrate PHPWord and I suspect PHPPowerPoint would be just as easy.
wow .. glad to hear that!
I haven’t tried other PHPOffice product. They surely have done a great job.
This is simply the best, have struggled with Codeigniter + Excel..
Thank you for the information, very helpful
Awesome, very helpful.
Really easy to follow
This is simply awesome. Tried it just once and worked correctly. What about reading from excel? Thanks alot
there’s a lot of example on PHPExcel ‘Tests’ folder, one of them is how to load an Excel file. Basically, you do something like this:
Thank you very much! I was looking exactly for this.
Hi i wanted that generated file to be emailed as attachment can you help
thank you very much works like a charm.
here some example code if you want to write cell value programmatically
$letter = "A";
for($i=1;$iexcel->getActiveSheet()->getColumnDimension($letter)->setAutoSize(true);
$this->excel->getActiveSheet()->getStyle($header)->getFont()->setSize(15);
$this->excel->getActiveSheet()->getStyle($header)->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle($header)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$letter++;
}
Gan, ane cuma ingin tanya apakah tulisan
http://www.ahowto.net/php/easily-integrateload-phpexcel-into-codeigniter-framework
ente ambil dari blog ane?
http://belajarcoding.com/php/codeigniter/membuat-laporan-excel-di-php-dengan-codeigniter-dan-phpexcel.html
Kalo memang iya tolong hargai tag yang ane cantumin donk, jangan main hapus aje…
Ane bukannya ingin dihargain tapi ane cuma berharap ente bisa menghargai hasil pemikiran orang lain
Terima kasih
untuk class nya: ya betul.
silakan diingatkan kalau ada yang masih kurang
Terima kasih gan atas konfirmasinya
Maju Terus Developer Indonesia
first of all , thank you very much.
Now i am getting a excel page , but actually i need to export the values from a flexigrid to excel.. how can i implement such a query in my application?
please help me for doing that..
can you send me an example for reading the data from a grid to excel using codeigniter.
thanks its great
how about importing excel data into mysql db. The above example is nice but it’s not what am looking for. I want to import excel sheet into mysql in codeigniter. can any body help please?
load->library('Lib_excel', null, 'excel');
// load excel file
$objPHPExcel = $this->excel->load($excelFileName);
// get active sheets
$objWorksheet = $objPHPExcel->getActiveSheet();
// get highest row column
$lastRow = $objWorksheet->getHighestRow();
// check cell value empty or not
for ($i = $startRow; $i getActiveSheet()->getCell($columnName . $i)->getValue();
if ($colB == NULL || $colB == '') {
die('The Cell' . ' B' . $i . ' is empty. Please, remove or fill with data');
}
}
// get excel data with range e.g. (B2:B56)
$cellValues = $objPHPExcel->getActiveSheet()->rangeToArray("$columnName$startRow:$columnName$lastRow");
// return all data as array
return $cellValues;
}
}
?>
load->model('Mod_excel', 'ModExcel', true);
// Load CRUD lib
$this->load->library('grocery_CRUD');
}
/**
* Upload Coupon Code from Excel
*/
public function createCouponCode() {
$crud = new grocery_CRUD();
$crud->set_table('coupon_code');
$crud->fields('coupon_no');
$crud->columns('coupon_no');
$crud->set_subject('Coupon');
$crud->required_fields('coupon_no');
$crud->set_field_upload('coupon_no', '../public/uploads/');
$crud->callback_before_upload(array(
$this,
'_excel_uploaded_file'
));
$output = $crud->render();
$this->_example_output($output);
}
/**
* Call back Function form CRUD Upload
* @param array $files_to_upload
* @param array $field_info
* @return string
*/
function _excel_uploaded_file($files_to_upload = array(), $field_info = array()) {
$insert_data = array();
$ext = '';
$file_tmp_path = '';
foreach ($files_to_upload as $value) {
$ext = pathinfo($value['name'], PATHINFO_EXTENSION);
$file_tmp_path = $value['tmp_name'];
}
$allowed_formats = array(
"xlsx"
);
if (in_array($ext, $allowed_formats)) {
$excelFileName = $file_tmp_path;
$columnName = 'B';
$startRow = '2';
$cellValues = $this->ModExcel->readWithExcel($excelFileName, $columnName, $startRow);
$iterator = new RecursiveIteratorIterator(new RecursiveArrayIterator($cellValues));
foreach ($iterator as $value) {
$insert_data[] = array(
'coupon_no' => $value
);
}
$this->output->enable_profiler(TRUE); //Turns on CI debugging
$this->db->insert_batch('coupon_code', $insert_data);
echo ($status) ? true : false;
} else {
return 'Error: Wrong file format. Use Excel 2007 format';
}
}
}
?>
load->library(‘Lib_excel’, null, ‘excel’);
// load excel file
$objPHPExcel = $this->excel->load($excelFileName);
// get active sheets
$objWorksheet = $objPHPExcel->getActiveSheet();
// get highest row column
$lastRow = $objWorksheet->getHighestRow();
// check cell value empty or not
for ($i = $startRow; $i getActiveSheet()->getCell($columnName . $i)->getValue();
if ($colB == NULL || $colB == ”) {
die(‘The Cell’ . ‘ B’ . $i . ‘ is empty. Please, remove or fill with data’);
}
}
// get excel data with range e.g. (B2:B56)
$cellValues = $objPHPExcel->getActiveSheet()->rangeToArray(“$columnName$startRow:$columnName$lastRow”);
// return all data as array
return $cellValues;
}
}
?>
load->model(‘Mod_excel’, ‘ModExcel’, true);
// Load CRUD lib
$this->load->library(‘grocery_CRUD’);
}
/**
* Upload Coupon Code from Excel
*/
public function createCouponCode() {
$crud = new grocery_CRUD();
$crud->set_table(‘coupon_code’);
$crud->fields(‘coupon_no’);
$crud->columns(‘coupon_no’);
$crud->set_subject(‘Coupon’);
$crud->required_fields(‘coupon_no’);
$crud->set_field_upload(‘coupon_no’, ‘../public/uploads/’);
$crud->callback_before_upload(array(
$this,
‘_excel_uploaded_file’
));
$output = $crud->render();
$this->_example_output($output);
}
/**
* Call back Function form CRUD Upload
* @param array $files_to_upload
* @param array $field_info
* @return string
*/
function _excel_uploaded_file($files_to_upload = array(), $field_info = array()) {
$insert_data = array();
$ext = ”;
$file_tmp_path = ”;
foreach ($files_to_upload as $value) {
$ext = pathinfo($value['name'], PATHINFO_EXTENSION);
$file_tmp_path = $value['tmp_name'];
}
$allowed_formats = array(
“xlsx”
);
if (in_array($ext, $allowed_formats)) {
$excelFileName = $file_tmp_path;
$columnName = ‘B’;
$startRow = ’2′;
$cellValues = $this->ModExcel->readWithExcel($excelFileName, $columnName, $startRow);
$iterator = new RecursiveIteratorIterator(new RecursiveArrayIterator($cellValues));
foreach ($iterator as $value) {
$insert_data[] = array(
‘coupon_no’ => $value
);
}
$this->output->enable_profiler(TRUE); //Turns on CI debugging
$this->db->insert_batch(‘coupon_code’, $insert_data);
echo ($status) ? true : false;
} else {
return ‘Error: Wrong file format. Use Excel 2007 format’;
}
}
}
?>
AWESOME tutorial, you just saved me soooo much time with this one. Thanx for taking the time to share this with us!
alo bos
gimana implementasi phpexcel_reader_ireadfilter di CI? ini saya butuh mroses file XLS yang sizenya besar
thank you
spt yg pernh dibilang oleh salah satu pembaca ahowto, phpexcel tidak cocok digunakan untuk memproses file dengan ukuran besar (memory habis dan timeout). baiknya cari alternatif lain (mungkin bisa diubah ke csv dulu).
untuk readfilter nya sendiri, belum pernah coba juga .. sorry..
Hi admin, thanks for sharing this, so far the best integration of phpexcel into Codeigniter tutorial.
Simple and least works needed!
follow your steps as it is in the guide but when Habro the generated excel I get an error that says:
has a different format than specified by the file extension, You may help me with this problem?.
codegniter use 2.0, PHPExcel 1.7.7
Habro what???!!
Try renaming the excel file extension from .xls into .xlsx or vice-versa. It seems your error is related to file extension.
I made the switch and continues with the same error, which may be?
This is the error that I generated =
Filename: PHPExcel/IOFactory.php
A PHP Error was encountered
Severity: 4096
Message: Argument 1 passed to PHPExcel_Writer_Excel5::__construct() must be an instance of PHPExcel, instance of PHPExcel_Worksheet given, called in C:\xampp\htdocs\academiatotto\entrenamientoenlinea\libraries\PHPExcel\IOFactory.php on line 141 and defined
Filename: Writer/Excel5.php
Line Number: 107
A PHP Error was encountered
Severity: 4096
Message: Argument 1 passed to PHPExcel_Writer_Excel5_Workbook::__construct() must be an instance of PHPExcel, instance of PHPExcel_Worksheet given, called in
Filename: Excel5/Workbook.php
Line Number: 203
Fatal error: Call to undefined method PHPExcel_Worksheet::getSheetCount() in C:\xampp\htdocs\academiatotto\entrenamientoenlinea\libraries\PHPExcel\Writer\Excel5\Workbook.php
on line233
thanks for help
please post your controller source codes to pastebin or somewhere. I’ll check.
Here is the code
//controller
load->library(array(‘table’, ‘form_validation’,'excel’));
$this->tipo_nota = ‘nota’;
}
function generarExcelempre()
{
$this->excel->setActiveSheetIndex(0);
$this->excel->getActiveSheet()->setTitle(‘test worksheet’);
$this->excel->getActiveSheet()->setCellValue(‘A1′, ‘This is just some text value’);
$this->excel->getActiveSheet()->getStyle(‘A1′)->getFont()->setSize(20);
$this->excel->getActiveSheet()->getStyle(‘A1′)->getFont()->setBold(true);
$this->excel->getActiveSheet()->mergeCells(‘A1:D1′);
$this->excel->getActiveSheet()->getStyle(‘A1′)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$filename = ‘just_some_random_name.xls’;
header(‘Content-Type: application/vnd.ms-excel’);
header(‘Content-Disposition: attachment;filename=”‘ . $filename . ‘”‘);
header(‘Cache-Control: max-age=0′);
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, ‘Excel5′);
$objWriter->save(‘php://output’);
}
}
?>
////application/libraries/Excel.php
<?php
if (!defined('BASEPATH'))
exit('No direct script access allowed');
/*
* =======================================
* Author : Muhammad Surya Ikhsanudin
* License : Protected
* Email : mutofiyah@gmail.com
/*
*
* Dilarang merubah, mengganti dan mendistribusikan
* ulang tanpa sepengetahuan Author
* =======================================
*/
require_once APPPATH.”/third_party/PHPExcel.php”;
class Excel extends PHPExcel {
public function __construct() {
parent::__construct();
}
}
//thanks for help
I found that the code we explain on page works 100%, I think it’s a configuration error codeigniter export makes strange characters
Hi, i get everything as described but I got the foloving
error: A PHP Error was encountered Severity: Warning Message:
realpath() [ rel="nofollow">function.realpath]: SAFE MODE
Restriction in effect. The script whose uid is 1096 is not allowed
to access /tmp owned by uid 208 Filename: Shared/File.php Line
Number: 136 ÐÏࡱá;þÿ
þÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿþÿÿÿ
þÿÿÿþÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿþÿà…ŸòùOh«‘+’³Ù0¸@Hh€˜
¤°äUntitled SpreadsheetUnknown CreatorUnknown Creator@ûFðÍ@ûFðÍ »
ÌÑB°=¼%r8X”1ÜCalibri1¼Calibriàõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À
àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À à À à”È
“€ÿ’â8ÿÿÿÿÿÿÿÿÿÿÿÿ€€€€€€€€€ÀÀÀ€€€™™ÿ™3fÿÿÌÌÿÿffÿ€€fÌÌÌÿ€ÿÿÿÿÿÿ€€€€€ÿÌÿÌÿÿÌÿÌÿÿ™™Ìÿÿ™ÌÌ™ÿÿÌ™3fÿ3ÌÌ™ÌÿÌÿ™ÿfff™–––3f3™f333™3™3f33™333…$“test
worksheetÁÁgæ®üCThis is just some text value »
ÌÑ*+‚€Áƒ„&ffffffæ?’ffffffæ?(è?)è?¡”dXX333333Ó?333333Ó?U}$
}$ }$ }$ ý >¶@å ggÿÿÿÿÿ þÿÕÍÕœ.“—+,ù®0¼HPX`hp xŽä
WorksheetFeuilles de calculRoot Entryÿÿÿÿÿÿÿÿ
ÀFûFðÍûFðÍ@SummaryInformation(ÿÿÿÿ ÀFèWorkbookÿÿÿÿÿÿÿÿÿÿÿÿ
ÀFDocumentSummaryInformation8ÿÿÿÿÿÿÿÿÿÿÿÿ
ÀFìþÿÿÿþÿÿÿþÿÿÿýÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ
Your safe_mode setting in php.ini is on. It should be off, contact your webhosting support.
Hi,
How to save (using mysql) excel data using codeigniter.
Thanks in Advance
Regards,
Ramji
Thanks so much for this.
At first I get garbage on my downloaded excel file.
But recently I faced a problem where I need to download some attachments and the downloaded attachments have a white blank line at the beginning of the binary file.
Same happen with here and I just added
ob_clean();before saving the file.Awesome, thanks a lot!
Hey, good post. Just a note maybe can help someone, If you´re recieving error when exporting in 2007 format:
“Excel found unreadable content in ‘*.xlsx’. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.”
Try adding die(); at the endo of the code:
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="myfile.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel2007');
$objWriter->save('php://output');
die();
It worked for me!
@guillermo your tip worked. i’ve been trying to figure this out but now it works. thanks!
Thanks a lot!
thank you , big help
it doesn’t have view page, does it?
Great idea!
hi this is very good, but i want to export entire table data, how it is possible can you help
cool, thank !!!!
Misaotra e!!!