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.

Reblog this post [with Zemanta]
Be Sociable, Share!

Maybe you like this post too :