Maarten Balliauw {blog}

ASP.NET MVC, Microsoft Azure, PHP, web development ...

NAVIGATION - SEARCH

Reuse Excel business logic with PHPExcel

Price calculationIn 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: phpexcel4business.zip (318.74 kb)

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:

[code:c#]

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

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

[/code]

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:

[code:c#]

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

[/code]

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.

[code:c#]

// 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']);

[/code]

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!

[code:c#]

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

[/code]

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

[code:c#]

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

[/code]

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.

kick it on DotNetKicks.com 

ASP.NET MVC Framework out on CodePlex

This morning, I was browsing the new projects page on CodePlex and noticed something nice! The ASP.NET MVC team already rumoured around making the ASP.NET MVC framework source code available on CodePlex, but here it is: the ASP.NET MVC project on CodePlex.

The CodePlex project does not allow people to make their own contributions, but you can easily look under the hood or fix bugs for your production environment. How cool is that!

Happy coding!

Update 4:45 PM: Seems like I've stolen the thunder from Scott Guthrie :-) To be complete: the official announcement was made a little bit later than my unofficial announcement.  You can read about it here.

kick it on DotNetKicks.com

ASP.NET MVC - Testing issues Q and A

WTF? When playing around with the ASP.NET MVC framework and automated tests using Rhino Mocks, you will probably find yourself close to throwing your computer trough the nearest window. Here are some common issues and answers:

Q: How to mock Request.Form?

A: When testing a controller action which expects Request.Form to be a NameValueCollection, a NullReferenceException is thrown... This is due to the fact that Request.Form is null.

Use Scott's helper classes for Rhino Mocks and add the following extension method:

[code:c#]

public static void SetupFormParameters(this HttpRequestBase request)
{
    SetupResult.For(request.Form).Return(new NameValueCollection());
}

[/code]

Q: I can't use ASP.NET Membership in my controller, every test seems to go bad...

A: To test a controller using ASP.NET Membership, you should use a little trick. First of all, add a new property to your controller class:

[code:c#]

private MembershipProvider membershipProvider;

public MembershipProvider MembershipProviderInstance {
    get {
        if (membershipProvider == null)
        {
            membershipProvider = Membership.Provider;
        }
        return membershipProvider;
    }
    set { membershipProvider = value; }
}

[/code]

By doing this, you will enable the use of a mocked membership provider. Make sure you use this property in your controller instead of the standard Membership class (i.e. MembershipProviderInstance.ValidateUser(userName, password) instead of Membership.ValidateUser(userName, password)).

Let's say you are testing a LoginController which should set an error message in the ViewData instance when authentication fails. You do this by creating a mocked MembershipProvider which is assigned to the controller. This mock object will be instructed to always shout "false" on the ValidateUser method of the MembershipProvider. Here's how:

[code:c#]

LoginController controller = new LoginController();
var fakeViewEngine = new FakeViewEngine();
controller.ViewEngine = fakeViewEngine;

MockRepository mocks = new MockRepository();
using (mocks.Record())
{
    mocks.SetFakeControllerContext(controller);
    controller.HttpContext.Request.SetupFormParameters();

    System.Web.Security.MembershipProvider membershipProvider = mocks.DynamicMock<System.Web.Security.MembershipProvider>();
    SetupResult.For(membershipProvider.ValidateUser("", "")).IgnoreArguments().Return(false);

    controller.MembershipProviderInstance = membershipProvider;
}
using (mocks.Playback())
{
    controller.HttpContext.Request.Form.Add("Username", "");
    controller.HttpContext.Request.Form.Add("Password", "");

    controller.Authenticate();

    Assert.AreEqual("Index", fakeViewEngine.ViewContext.ViewName);
    Assert.IsNotNull(
        ((IDictionary<string, object>)fakeViewEngine.ViewContext.ViewData)["ErrorMessage"]
    );
}

[/code]

More questions? Feel free to ask! I'd be happy to answer them.

kick it on DotNetKicks.com

March 18 ASP.NET MVC links

Too busy this week to write large blog posts myself... Luckily other people do write interesting things on ASP.NET MVC!

Happy coding!

kick it on DotNetKicks.com

Heroes happen here - Microsoft TechDays 2008 in Belgium

Microsoft TechDays 2008

Dolmen polo Just to inform you: together with a numer of colleagues from Dolmen, I'll be attending the Microsoft TechDays 2008 in Ghent, Belgium on 12 and 13 March 2008. Want to spot me, Joris, Jeroen, Danny, ... and meet in person? Search for one of the guys in a Dolmen shirt!

Update 17/03/2008: Jeroen posted an overview of the inspiring sessions on his blog. 

OpenXML + Silverlight 2.0 = cool!

Mix '08 announced some nice things, among them the release of Silverlight 2.0 (beta), ASP.NET MVC framework (CTP 2). This morning, I saw one very cool thing in my RSS reader: TextGlow.

TextGlow is James Newton-King's newest exciting project which basically combines Silverlight 2.0 and OpenXML into a fancy web-based Word 2007 document viewer. Think about combining this with my own Word 2007 document preview handler...

TextGlow Silverlight OpenXML

kick it on DotNetKicks.com

PHPLinq version 0.2.0 released!

Last friday, I released PHPLinq version 0.2.0. LINQ, or Language Integrated Query, is a component inside the .NET framework which enables you to perform queries on a variety of data sources like arrays, XML, SQL server, ... These queries are defined using a syntax which is very similar to SQL.

This latest PHP version of LINQ provides almost all language constructs the "real" LINQ provides. Since regular LINQ applies to enumerators, SQL, datasets, XML, ..., I decided PHPLinq should provide the same infrastructure. Each PHPLinq query is therefore initiated by the PHPLinq_Initiator class. Each PHPLinq_ILinqProvider implementation registers itself with this initiator class, which then determines the correct provider to use. This virtually means that you can write unlimited providers, each for a different data type! Currently, an implementation on PHP arrays is included.

PHPLinq class diagram 

Being able to query PHP arrays is actually very handy! Let's say you have a mixed array containing Employee objects and some other data types. Want only Employee objects? Try this one!

[code:c#]

$result = from('$employee')->in($employees)
            ->ofType('Employee')
            ->select();

[/code]

Want to know if there's any employee age 12? Easy! The following query returns true/false:

[code:c#]

$result = from('$employee')->in($employees)->any('$employee => $employee->Age == 12');

[/code]

Let's do something a little more advanced... Let's fetch all posts on my blog's RSS feed, order them by publication date (descending), and select an anonymous type containing title and author. Here's how:

[code:c#]

$rssFeed = simplexml_load_string(file_get_contents('http://blog.maartenballiauw.be/syndication.axd'));
$result = from('$item')->in($rssFeed->xpath('//channel/item'))
            ->orderByDescending('$item => strtotime((string)$item->pubDate)')
            ->take(2)
            ->select('new {
                            "Title" => (string)$item->title,
                            "Author" => (string)$item->author
                      }');

[/code]

Download LINQ for PHP (PHPLinq) now and get familiar with it. Since I started working on PHPLinq, I also noticed LINQ implementations for other languages: