Long Live Open Source Software !!

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.

1. Extract the PHPExcel library that you’ve just downloaded.
2. Create a folder called my_classes in your system/application director
3. Copy the folder [PHPExcel Directory]/Classes/ and its content in your system/application/my_classes/ directory.
4. 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;
5. Add the following lines in application/config/hooks.php
   $hook['pre_controller'][] = array(
'class' => 'MyClasses',
'function' => 'index',
'filename' => 'MyClasses.php',
'filepath' => 'hooks'
);
6. 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/');
}
}
?>

7. If you use windows as your server, change the colon (:) to semicolon (;) in code above.
8. 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__));
}
}
?>

9. 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.

Be Sociable, Share!

• 
•