I’ve got a problem when i use codeigniter to build a web application. There is no plugin or library that can create excel 2003 files perfectly. I used Spreadsheet::WriterExcel class to create an excel file. But i’ve got a problem when i merged some cells. Those cells is merged, but when i set a value, it appeared in the leftmost cell, not centered. I tried to find solution in google and the i found this site. It told me about how to use hook in codeigniter, so i can use another php library to write an excel file. In this case, i use PHPExcel, because it can create files like i want.
First, download PHPExcel library from its site. Don’t forget to fulfill its requirement. And then follow the steps below to use PHPExcel with CodeIgniter. I modify the tutorial from the site i told you above.
- Extract the PHPExcel library that you’ve just downloaded.
- Create a folder called my_classes in your system/application director
- Copy the folder [PHPExcel Directory]/Classes/ and its content in your system/application/my_classes/ directory.
- Enable hooks in your application/config/config.php file
/*
|--------------------------------------------------------------------------
| Enable/Disable System Hooks
|--------------------------------------------------------------------------
|
| If you would like to use the "hooks" feature you must enable it by
| setting this variable to TRUE (boolean). See the user guide for details.
|
*/
$config['enable_hooks'] = TRUE; - Add the following lines in application/config/hooks.php
$hook['pre_controller'][] = array(
'class' => 'MyClasses',
'function' => 'index',
'filename' => 'MyClasses.php',
'filepath' => 'hooks'
); - In your system/application/hooks/ create a new file called MyClasses.php with the following inside.
<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');class Myclasses
{
/**
* includes the directory application\my_classes\Classes in your includes directory
*
*/
function index()
{
//includes the directory application\my_classes\Classes\
ini_set('include_path', ini_get('include_path').':'.BASEPATH.'application/my_classes/Classes/');
}
}
?> - If you use windows as your server, change the colon (:) to semicolon (;) in code above.
- Now you can use PHPExcel Library in CodeIgniter. Below is a simple controller example for you.
<?php
class Kiba extends Controller
{
function Kiba()
{
parent::Controller();
}function testexcel()
{
include 'PHPExcel.php';
/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel5.php';// Create new PHPExcel object
echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();// Set properties
echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("BazZ");
$objPHPExcel->getProperties()->setLastModifiedBy("BazZ");
$objPHPExcel->getProperties()->setTitle("TestExcel");
$objPHPExcel->getProperties()->setSubject("");// Set row height
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(50);
$objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(25);// Set column width
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(40);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);//Merge cells (warning: the row index is 0-based)
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0,1,13,1);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0,2,13,2);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0,3,0,5);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(1,3,1,5);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(2,3,3,3);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(2,4,2,5);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(3,4,3,5);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(4,3,4,5);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(5,3,5,5);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(6,3,6,5);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(7,3,9,3);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(7,4,7,5);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(8,4,9,4);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(10,3,10,5);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(11,3,11,5);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(12,3,12,5);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(13,3,13,5);//Modify cell's style
$objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(
array(
'font' => array(
'name' => 'Times New Roman',
'bold' => true,
'italic' => false,
'size' => 20
),
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
'wrap' => true
)
)
);$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray(
array(
'font' => array(
'name' => 'Times New Roman',
'bold' => true,
'italic' => false,
'size' => 14
),
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
'wrap' => true
)
)
);$objPHPExcel->getActiveSheet()->duplicateStyleArray(
array(
'font' => array(
'name' => 'Times New Roman',
'bold' => true,
'italic' => false,
'size' => 12
),
'borders' => array(
'top' => array('style' => PHPExcel_Style_Border::BORDER_DOUBLE),
'bottom' => array('style' => PHPExcel_Style_Border::BORDER_DOUBLE),
'left' => array('style' => PHPExcel_Style_Border::BORDER_DOUBLE),
'right' => array('style' => PHPExcel_Style_Border::BORDER_DOUBLE)
),
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
'wrap' => true
)
),
'A3:N5'
);// Add some data
echo date('H:i:s') . " Add some data\n";
$objPHPExcel->setActiveSheetIndex(0);$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Try PHPExcel with CodeIgniter');
$objPHPExcel->getActiveSheet()->SetCellValue('A2',"Subtitle here");$objPHPExcel->getActiveSheet()->SetCellValue('A3',"No.");
$objPHPExcel->getActiveSheet()->SetCellValue('B3',"Name");
$objPHPExcel->getActiveSheet()->SetCellValue('C3',"Number");
$objPHPExcel->getActiveSheet()->SetCellValue('C4',"Code");
$objPHPExcel->getActiveSheet()->SetCellValue('D4',"Register");
$objPHPExcel->getActiveSheet()->SetCellValue('E3',"Space (M2)");
$objPHPExcel->getActiveSheet()->SetCellValue('F3',"Year");
$objPHPExcel->getActiveSheet()->SetCellValue('G3',"Location");// Rename sheet
echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle('Try PHPExcel with CodeIgniter');// Save Excel 2003 file
echo date('H:i:s') . " Write to Excel2003 format\n";
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save(str_replace('.php', '.xls', __FILE__));
}
}
?> - Try code above and run it. You will get a .xls file in your controller directory.
If you have any question, feel free to ask me anytime. You can leave a comment or contact me via email. Thank you. Hope this tutorial can help you.
Maybe you like this post too :