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:
<?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:
- The ‘1’ in the setData() call is to indicate the data is for multiple sheets.
- Each key in the $data array is the name of a sheet.
- 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