Other Recent Articles


View Archives

Most Popular Content

PHP, Tip & Tricks - May 30, 2008 3:20 - 0 Comments

Reuse Excel business logic with PHPExcel

Reuse Excel business logic with PHPExcel

n many companies, business logic resides in Excel. This business logic is sometimes created by business analysts and sometimes by business users who want to automate parts of their everyday job using Excel. This same Excel-based business logic is often copied into an application (i.e. a website) and is maintained on 2 places: if the Excel logic changes, the application should also be modified. Did you know you can use PHPExcel to take advantage of the Excel-based business logic without having to worry about duplicate business logic?

Here’s a scenario: You are working in a company which sells “dream cars”. For every model, the company has created an Excel spreadsheet which is used to calculate the car’s price based on customer preferences. These spreadsheets are updated frequently in order to reflect the car manufacturer’s pricing schemes.

Your manager asks you to create a small website which accepts some input fields (Does the customer want automatic transmission? What colour should the car be painted? Does the customer want leather seats? Does the customer want sports suspension?). Based on these questions, the car’s price should be calculated. Make sure all prices on the website are in sync with this Excel sheet!

Download example source code
View live example

1. Create the Excel sheet containing business logic

Defined names First of all, we’ll create an Excel sheet containing business logic. If you’re lazy, download my example here. To make things easy for yourself when scripting, make sure you add some defined names on each field you want to use as input/output. Of course it’s possible to work with the sheet’s cell references later on, but if you want to be able to change the location of cells within the worksheet later, these defined names are much easier!

2. Download the latest PHPExcel version

You can find PHPExcel on www.phpexcel.net. If you want a stable version, download an official release. The source code tab on CodePlex reveals the latest Subversion source code if you want to use it.

3. Create the web based front-end

Next thing we’ll do is creating a simple webpage containing an HTML form which corresponds woth the parameters you want to pass to the Excel sheet.

4. Let’s do some PHP coding!

Since you came here for the good stuff, here it is! What we’ll do is load the Excel sheet, pass in the parameters and use some calculated values on our resultign page. First things first: include the necessary class references:

/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . ‘./PHPExcel/Classes/’);

/** Class requirements */
require_once(’PHPExcel.php’);
require_once(’PHPExcel/Reader/Excel2007.php’);

PHPExcel is the base library which represents an in-memory spreadsheet. Since we need to interface with an Excel2007 file, we also include the required reader class.

Now load the Excel sheet into a PHPExcel object:

// Load price calculation spreadsheet
 $objReader = new PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load(”price_calculation.xlsx”);

All data from the web form is passed in via the $_REQUEST array. Let’s pass these to the Excel sheet. I named all form fields equal to my defined names in Excel which makes coincidence of all array keys and cell names being the same intentional.

// Set active sheet
 $objPHPExcel->setActiveSheetIndex(0);

// Assign data
 $objPHPExcel->getActiveSheet()->setCellValue(’automaticTransmission’, $_REQUEST[’automaticTransmission’]);
$objPHPExcel->getActiveSheet()->setCellValue(’carColor’, $_REQUEST[’carColor’]);
$objPHPExcel->getActiveSheet()->setCellValue(’leatherSeats’, $_REQUEST[’leatherSeats’]);
$objPHPExcel->getActiveSheet()->setCellValue(’sportsSeats’, $_REQUEST[’sportsSeats’]);

PHPExcel is great success!This is actually about it. The only thing left is to fetch the formula’s calculated values and we’re done!

// Perform calculations
 $_VIEWDATA[’totalPrice’] = $objPHPExcel->getActiveSheet()->getCell(’totalPrice’)->getCalculatedValue();
$_VIEWDATA[’discount’] = $objPHPExcel->getActiveSheet()->getCell(’discount’)->getCalculatedValue();
$_VIEWDATA[’grandTotal’] = $objPHPExcel->getActiveSheet()->getCell(’grandTotal’)->getCalculatedValue();

You can use these values to print the result on your web page:

Based on your chosen preferences, your car will cost <?php echo number_format($_VIEWDATA[’grandTotal’], 2); ?> EUR.

5. Summary

Embedding business logic in Excel and re-using it in PHP is not that hard. The PHPExcel library helps you simplify development: your application logic and business logic is separated. Business logic can be maintained by a business expert or key user in Excel. As an application developer, you can easily pass data in the sheet and make use of PHPExcel’s calculation engine.

More In Code


CSS & HTML Code

Photoshop Tips - May 31, 2008 8:20 - 0 Comments

Popular Wealth - Free Photoshop Brushes To Download

Popular Wealth - Free Photoshop Brushes To Download

Free Photoshop Brushes

Getting the most out of your photoshop software requires plugins and tools like the following 300+ Free Photoshop Brushes that have graciously been made available for download by their authors. I Continue…

More In How to ?


Tip & Tricks

Wordpress - May 31, 2008 3:34 - 0 Comments

Thinking the Way Animals Do

Thinking the Way Animals Do

By Temple Grandin, Ph.D.
Department of Animal Science
Colorado State University

Western Horseman, Nov. 1997, pp.140-145

Temple Grandin is an assistant professor of animal science at Colorado State University. She is the author of the book Thinking in Pictures. Television appearances include 20/20, CBS This Morning, and 48 Hours. Dr. Grandin has autism, and her experiences have helped her to understand animal behavior. She teaches a course in livestock handling at the university and consults on the design of livestock handling facilities.

Unique insights from a person with a singular understanding.

As a person with autism, it is easy for me to understand how animals think because my thinking processes are like an animal’s. Autism is a neurological disorder that some people are born with. Scientists who study autism believe that the disorder is cause d by immature development of certain brain circuits, and over development of other brain circuits. Autism is a complex disorder that ranges in severity from a mild form Continue…

More In I LIKE IT !


I LIKE IT ! Wallpaper