I recently had a project that required us to output several 'formatted' reports into Excel. The reports were such that required some formatting and were not just a simple datagrid.

After looking at several options I decided to use the opensource package PHPExcel.

After digging into the package I noticed that PHPExcel is very flexible and well-written, however even with good documentation I could see how it would be very tough for a beginner to intermediate developer, who isn't very well versed in OO Techniques and Principals to wrap their heads around it. Also what I was doing was relatively simple, however the code I had to write really wasn't.

So I created a Facade class (which really just is a simplified version of the API) to handle my interaction with the package. Since I didn't need to create anything too complicated, I didn't need to package together too many options, but I wanted to be able to merge cells, bold text, set font sizes, set fonts, and set the column widths and heights.

Here is the class I created.

PHPExcel simplified Class

Now let's use it.

First download the PHPExcel package.

If you are using Yii, you would add it into your /protected/components/ to look like this:

 
The PHPExcel folder and the PHPExcel file come from the Classes 
Directory of PHPExcel
 
/protected/components/PHPExcel/
/protected/components/PHPExcel.php
/protected/components/PHPExcelFacade.php
 

If you're not using Yii then put them where you put the rest of your classes and make sure you include the proper files to run the classes. Should only need to include the Facade and the PHPExcel main class.

Now lets test it out.

PHP/Excel Example 1

 
<?php
$PhpExcel = new PHPExcelFacade("mahTest.xls", SITE_LOC."assets/");
$PhpExcel->setActiveSheet(0);
$PhpExcel->setText("A1", "Hello World", true, false, 12);
$PhpExcel->outputToBrowser();
?>
 

Produces: Download Excel File For Example 1

PHP/Excel Example 2

 
<?php
$stocks_array = array(
    'AET' => 27.27,
    'HBC' => 44.80,
    'BCS' => -18.31,
    'BA' => 49.20,
    'AXP' => 24.71
);
$PhpExcel = new PHPExcelFacade("PhpExcelExample2.xls", SITE_LOC."assets/");
$PhpExcel->setActiveSheet(0);
$PhpExcel->mergeCells("A1", "B1");
$PhpExcel->setText("A1", "Stock Prices", true, true, 24);
$PhpExcel->setCellHorizontalAllignment("A1", 'center');
$t = 2;
foreach($stocks_array AS $symbol => $price) {
    $PhpExcel->setText("A{$t}", $symbol);
    $PhpExcel->setText("B{$t}", $price);
    if($price < 0) {
        $PhpExcel->setCellForegroundColor("B{$t}", "DC1212");
    }
    $t++;
}
$PhpExcel->outputToBrowser();
?>
 

Produces: Download Excel File For Example 2

More To Come

If you have anything you would like demonstrated, let me know. This class doesn't support much of the functionality of the PHPExcel package, however it was all I needed for my project. Feel free to add to it and send me the additions and I'll update the class.