11

PHPExcel Module for Kohana 3

Posted July 31st, 2010 (Updated 29 Oct 2011) in PHP

As with any project involving generation of .xlsx spreadsheets there’s only really one way to go – PHPExcel. This amazing piece of software boasts a large feature set allowing quick and (relative) painless generation of XLS, XLSX, PDF, and CSV to name a few as well as importing of the same sans PDF. I recently had to integrate PHPExcel into Kohana 3 so I figured I’d take the time to post the how-to and hopefully make your lives that little bit easier. In addition I’ve included a quick and dirty Spreadsheet class to make XLSX generation that bit faster. But enough chit chat! Here are the instructions.

Firstly you’ll want to grab a copy of the latest version from the official sites downloads page (currently 1.7.3c) then create a phpexcel folder in your modules directory.

Create the following file/folder structure:

PHPExcel Module For Kohana 3
PHPExcel Module Folder Structure

init.php:

<?php
	/* PHP Excel integration */
	require Kohana::find_file('classes', 'vendor/phpexcel/PHPExcel');

spreadsheet.php is a small script I wrote to make the process of XLSX generation slightly quicker. Usually to make a spreadsheet you’d do the following:

require_once 'PHPExcel.php';
require_once 'PHPExcel/IOFactory.php'
 
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0)
	->setCellValue('A1', 'Hello')
	->setCellValue('B1', 'world!');
 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('MyExcel.xslx);

Code taken from Import and export data using PHPExcel- a highly educational post I’d heavily recommend checking out.

This is cumbersome and time consuming. I’m sure there are functions to input arrays but I wanted something that could do multiple named sheets with a single call so, utilizing my spreadsheet class you can now do the following:

$XLSX = new Spreadsheet();
$data = array(
	'Users' => array(
		1 => array('ID', 'Name'),
		2 => array(1, 'Jane Doe'),
		3 => array(2, 'Fred Smith')
	),
	'Products' => array(
		1 => array('ID', 'Name'),
		2 => array(1, 'Torch'),
		3 => array(2, 'Hat')
	),
);
$XLSX->setData( $data, 1 );
$XLSX->save(array('name'=>$name));

A few things to note here:

  1. The ’1′ in the setData() call is to indicate the data is for multiple sheets.
  2. Each key in the $data array is the name of a sheet.
  3. Rows start 1-based instead of 0-based so you’ll need to specify indexes in your sheet arrays or the first row will be lost.

This class is not ideal but it suited my requirements and allowed for extremely quick and painless XLSX generation. I’ve included it in the download link below.

That should hopefully cover everything. Check the source code for the spreadsheet class to see exactly how it works and hopefully you can learn from it and improve it to suit your own needs. There is no license – you can use, modify, distribute and do whatever you want with it – no restrictions apply.

Download PHPExcel Module for Kohana 3

  • Johny

    It’s a highly helpful post. Thank you Man.

  • Steph

    I must agree with Johny, this has been a very helpful post :)
    I also patterned integrating PHPWord with Kohana 3 using this tutorial.

    Thanks a lot :)

  • Kris

    Unfortunately:

    Fatal error: main() [function.require]: Failed opening required ”
    (include_path=’.:/usr/lib/php:/usr/local/lib/php’) in ….modules/classes/init.php line 4

    • Flynsarmy

      Your bootstrap should be looking something like the following:
      Kohana::modules(array(

      ‘phpexcel’ => MODPATH.’phpexcel’, // PHP Excel
      ));

      • Crates

        No, Kris is right. Out of the box, your module isn’t compatible with Kohana 3.2 and throws the error he specified (once referenced in the bootstrap file).

        I’ll see if I can figure out why, and post a solution here.

      • Crates

        Aha! In your “init.php” file, you’re referencing “vendor/phpexcel/PHPExcel”; however, Kris (like myself) extracted the “PHPExcel” directory directly into the “vendor” folder rather than creating an additional “phpexcel” folder in between the two.

        I hope that helps someone. Changing the line to “vendor/PHPExcel” worked for me to fix the error Kris mentioned.

        • Flynsarmy

          Thanks for your comments, Crates. I’ll take a look at this issue after work and update my post/module if needed.

        • Robbie Smith

          +1 to Crates comment. Worked great after that.

  • roelfsche

    there is a type in your example:
    your object is called $XLSX but afterwards you call methods on $XLS.
    Otherwise very helpful.

    • Flynsarmy

      Thanks for the correction roelfsche. I’ve updated my example accordingly.

  • juanmatias

    Hi and thanks. This is very helpful.

    Just to save a step when a rookie (like myself) installs this package. Don’t forget to create the dir in which phpexcel will save your file before download it.

    Cool script.