Design PHP Apps with Excel Using Worksheet Server

The PHP Developer's View

That gives you an idea of how Worksheet Server looks to an Excel user -- but how does it fit with PHP development as a whole?

Mental Olympics

The first conceptual hurdle a PHP developer has to overcome lies in the way data is represented in a worksheet server application. It's likely that you see a table and immediately think "SQL result set, rendered in a loop", given that a large portion of Web applications are exactly this.

In a typical Worksheet Server application, the HTML table cells are a representation of an Excel spreadsheet. Each cell will typically be either "raw data" -- values that were hard coded, entered by users or fetched individually from a database -- or "compound data", the result of formulas applied to the "raw data" cells.

Behind the scenes, Worksheet Server stores the data contained in cells in serialized form to the filesystem. It manages file locking to prevent multiple users updating the same value at the same time, and takes the latest data from here each time a page refresh occurs.

The second mental leap required of developers involves understanding that you're dealing with generated code; you are not intended to modify a Worksheet Server application directly. Instead, you need to use the defined "extension points" Worksheet Server provides, which I'll discuss in a moment.

Calling PHP Functions from Excel

For simple applications, your involvement as a PHP developer may not be required at all. The WS Excel Designer puts a lot of functionality at the disposal of Excel users -- they can even connect to a database, perform a query and embed parts of the result in cells using Excel special functions provided by Worksheet Server.

For example, to insert into a cell within Excel a single username fetched from a MySQL database, the following functions can be used, all of which are provided by Worksheet Server.

In cell A1 of the spreadsheet, you might have the following:

MYSQLINIT('mydatabase','harryf','secret');  
 
Cell: A1

The MYSQLINIT() function takes the connection settings for the database and provides a connection resource with which you can execute queries.

Next, in cell A2, you enter:

MYSQLEXEC(A1,"SELECT * FROM users");  
 
Cell: A2

Notice that the first argument "points" to the cell A1, where MYSQLINIT was used (and where the connection to MySQL was created). The second argument is the query itself.

Finally, in cell D15, for example, you enter:

MYSQLDATA(A2,15,2)  
 
Cell: D15

Here, A2 points to the cell in which the query was performed, '15' refers to a row number in the query result set and '2' refers to the column number in the row.

The data fetched by this query is not available to the designer working with Excel. It is only available once application is published online. The Excel functions are translated to their PHP equivalents automatically, making the referenced data available to the users.

Of course, it doesn't stop with MySQL -- Worksheet Server provides similar functionality for the ODBC access to databases, OLAP functionality using a PHP extension the Jedox team developed itself, and very cool SOAP support which looks very much like the MySQL example above but is initialized using a WSDL URL. There are also drawing and graph functions, provided by Jedox, which make up for limitations when using Excel's native functionality. All this is available to the designer working with Excel and requires no extra effort in PHP.

Out of a Job?

You're probably asking, "If designers can do so much with Worksheet Server, what's left for me to do?" Indeed, in basic applications, for which the default Worksheet Server functionality is adequate, there's little or no need for a PHP developer to get involved.

But of course, software development is never that easy. Someone always comes up with a unique requirement, or there's some essential data which has to be retrieved from some unsupported source, at which point knowledge of PHP is an essential requirement. What's more, if PHP is already part of the landscape, why not integrate Worksheet Server applications with existing applications? Enter PHP developer...

The mechanisms Worksheet Server provides for extending its functionality and talking with other PHP applications are both straightforward and powerful. There's no need to start messing with generated code.

Embrace and Extend

The most basic form of communication between Worksheet Server and other Web applications is via the Hyperlink function, which provides the usual possibilities to pass variables to the "outside" using GET variables in the URL. That may not be too exciting, but it will solve many simple problems, such as being able to link back to a Web interface for the remote data source used to populate a particular cell.

The next mechanism is that, in taking advantage of files, Worksheet Server will attempt to include them automatically, if it can find them.

In the process of responding to a page request, Worksheet Server looks for the scripts before_recalc.inc and after_recalc.inc -- these are included, respectively, before and after the contents of the cells are recalculated for the current request (assuming you have created them). Output generated in both of these files will be displayed before the HTML for the worksheet is displayed, but this is not really their intended purpose; instead, they allow you to set up the environment before recalculation occurs, then to respond to the calculation, perhaps updating a database that relies on the result.

Another script that Worksheet Server will attempt to include is userfunc.inc, which is the place to define (or include) your own PHP functions. These will be called for you in accordance with what was placed in the Excel spreadsheet itself, or from calls within before_recalc.inc and after_recalc.inc scripts.

It's worth being aware that Worksheet Server applications make all cells in a sheet available as global PHP variables, with names like $S1_B2 (which refers to sheet 1, cell B 2). The words "global" and "variables" may set alarm bells ringing, and you will need to be cautious about defining variables of the same name. That said, how often do you find yourself naming a variable $S1_B2? Remember also that register_globals is switched off. Locating the variables in the global scope makes life easy for those adding their own Worksheet Server functionality. It's likely that some Excel users will begin exploring PHP, out of need or interest, once they start using Worksheet Server.

A Custom Function

An example which illustrates the point can be achieved using the special Excel function, defined by Worksheet Server, called SHOWBUTTON(). This takes three arguments: a value and a name (corresponding to the HTML input tag attributes name and value) and a title that's displayed as the mouse is moved over the button. It also generates a form button for users to click in the application. Imagine I place the function in a cell within the Excel worksheet as follows:

SHOWBUTTON("Stock Check","doStockCheck","Tell Warehouse to Check Stock")

Once I've done this, I can check to see if a user of the application clicked on the button, using after_recalc.inc as follows:

<?php  
if ( isset($_POST['doStockCheck']) ) {  
 
   // Call my own PHP function, defined in userfunc.inc  
   doStockCheck();  
}

Now, in userfunc.inc, I can define the doStockCheck() function like so:

<?php  
function doStockCheck() {  
 
   // Include our own DB library  
   require_once 'lib/db/db_conn_manager.php';  
 
   // Fetch values from the global cell values  
   $productName = mysql_escape_string( $GLOBALS['S1_D6'] );  
   $requiredQuantity = mysql_escape_string( $GLOBALS['S1_D7'] );  
 
   // Build a query  
   $sql = "INSERT INTO stock_required  
               (name,quantity)  
           VALUES ('$productName','$requiredQuantity');";  
 
   // Insert the data  
   DBC::execute($sql);  
 
}

Here, you can see how the global cell variables might be used. The variables are defined and initialized in generated PHP scripts, complete with relationships between cells (using Worksheet Server's implementations of Excel functions in PHP).

The functions you implement in the userfunc.inc script can also be "called" directly by naming them in the Excel sheet. In other words, a designer can identify the PHP function name you've implemented in a cell while working with the spreadsheet, and it will be executed on the Web server each time the value of the cell is recalculated.

As such, you could regard a Worksheet Server application as a giant "component" and use the functions you define to invoke code in the rest of your "framework", for instance, using your logging library to keep track of Worksheet Server users (note that Worksheet Server already has a native logging mechanism, but you may have a requirement to integrate it with your existing infrastructure).

Coping With Change

One issue that normally concerns me when I'm considering code generation tools is how well my "add on" logic will survive the changes to the generated code.

As already mentioned, Worksheet Server is an active code generator (you shouldn't mess directly with the PHP in generates). The question is: will functions you've added by able to survive changes to the generated code?

My guess is that, in most cases, there'd be no need to alter code you've added, so long as the changes to the original spreadsheet a minor.

Of course, if the designer changes the name of a function call they've placed in the spreadsheet, it would require modification to its PHP implementation (but hopefully that would be planned in advance).

Otherwise, the only change that could have a significant impact would occur if you moved cells' locations, so that code that accessed a given cell would no longer point to the correct location. It's not common for users to move cells in an Excel spreadsheet once an initial structure is in place, so such problems should be infrequent. In the worse case, a simple fix would be to define a usefully named PHP constant that identifies a cell, allowing it to be quickly modified to reflect changes in cell location.

Access Control

As an alternative to its internal access control mechanism, Worksheet Server supports the use of LDAP for authentication. This should fit nicely with existing infrastructure found in medium-to-large organisations.

To switch a Worksheet Server application from its in-built access control system to an LDAP server, all that's required is that you define a script ldap.inc that contains the functions GetLDAPUsergroups(), providing Worksheet Server with a list of available groups, and CheckLDAPAccount(), which takes a username and password and should return a value that indicates whether or not the user is valid. An additional (optional) spreadsheet function is also available; LDAPDATA() allows you to pass to Worksheet Server further information about a user, such as email address and phone number.

Of course, not everyone uses LDAP and, if you're placing Worksheet Server applications on an existing Website, it's likely you've already implemented your own security mechanism. The good news is that Worksheet Server doesn't actually know whether the security data source, used inside the CheckLDAPAccount(), actually was an LDAP server. Instead, you could implement code to check the user against a table in MySQL, for example.

Generated Code

When an application is compiled in the online "Application Center", it creates a directory structure into which the generated PHP source is placed along with HTML templates, log files, serialized data files, user account databases and everything else that's needed to run the application.

Worksheet Server defines its own file extensions for PHP source code, avoiding the '.php' extension, and organising the scripts by their function. For example, an '.xcc' file contains cell calculations that define the relationships between cells. A snippet of an '.xcc' file for the Quick Demo application is shown below:

<?PHP  
$S2_C7 = $S2_C6+$S2_C5;  
$S2_D7 = $S2_D6+$S2_D5;  
$S2_E7 = $S2_E6+$S2_E5;  
$S2_F7 = $S2_F6+$S2_F5;  
$S2_G5 = SUMxl(ARRAY(1,4,$S2_C5,$S2_D5,$S2_E5,$S2_F5));  
$S2_G6 = SUMxl(ARRAY(1,4,$S2_C6,$S2_D6,$S2_E6,$S2_F6));  
// ...

You can see the global cell variables in use again, a line like $S2_C7 = $S2_C6+$S2_C5; meaning cell C7 is the sum of cells C6 and C5 while $S2_G5 = SUMxl(ARRAY(1,4,$S2_C5,$S2_D5,$S2_E5,$S2_F5)); is the equivalent of placing the Excel function =SUM(C5:G5) in cell G5 of a worksheet.

Other file extensions, such as '.xlr' and '.xic', contain application-wide PHP configuration settings and code to execute when a form post is made, respectively.

Of course, you need worry about none of this, as it's all generated code, but it's interesting to see how Worksheet Server is designed. Alongside these files, you can create the before_recalc.inc, after_recalc.inc, userfunc.inc and ldap.inc scripts discussed above, which Worksheet Server looks for automatically, and executes if they're found.

The PHP source code that powers Worksheet Server is encrypted with Zend's Encoder; this is, after all, a commercial product. While the PHP hacker in me found it a little frustrating not to be able to see what was happening behind the scenes, I can imagine this has the benefit of forcing developers to use the "interfaces" Worksheet Server provides, rather that falling prey to the temptation of making "just a few small changes".

SUM(A1:A3)

The basic idea behind Worksheet Server -- using Excel as a design tool for Web applications -- is highly ingenious. It's the sort of thing developers discuss over coffee: "Wouldn't it be cool to...". The difference, in Jedox's case, is that those developers actually went ahead and did it.

Despite a relatively short exposure to Worksheet Server, the impression I got was of a mature software product (the version I was working with was 2.1.x). I didn't run into any serious bugs or headaches; everything I tried worked as expected. Judging from the type of features Jedox has added to recent releases, the team has established a solid code base and is now able to address "nice-to-have" suggestions from users.

What impressed me most was how quickly powerful applications could be built using this product. Assuming you have experience with Excel, the design process is simply an extension of spreadsheet creation. Adding functionality to a published application using PHP is also straightforward -- all the PHP functions are at your disposal.

Worksheet Server has clearly been developed with deep understanding of Excel and how people use it. There's no "mystery functionality" and the feeling I got was of an application that real people would use.

On a side note, one thing that surprised me was how well PHP fits with the expectations of Excel users, and how closely it parallels Visual Basic when used in Excel. I can imagine advanced Excel users finding PHP an incremental step (rather than a giant leap) when extending Worksheet Server.

What also struck me was how Jedox has built its product on a stack of Open Source software (Apache, PHP, PHP libraries etc.) to deliver a slick, finished product that can be viewed as a whole, rather than as loose collection of parts. What's more, Worksheet Server combines the power of Excel and PHP to provide the best of both worlds. This combination in itself makes Worksheet Server a unique application and makes a fascinating case study of where future markets may lie for commodity software development.

Of course, Worksheet Server is a commercial product, and looking at the pricing it's clear this package isn't meant for the home user. Some, at the more radical end of Open Source, may argue the fundamental "wrongness" of using Open Source as a foundation for commercial product, but the rest of us, who like to eat and have a roof over our heads, realise that money has to come from somewhere. Worksheet Server adds significant value to the Open Source on which it's built, smoothing rough edges and giving end users a highly finished product. Clearly, there has to be a return on investment; without it, software like Worksheet Server would never happen.

Worksheet Server is also good news for PHP developers, as it brings our favourite platform to users who are likely unaware of its existence, and has the potential to create new job opportunities in industries in which PHP is yet to have an impact.

Ultimately, Worksheet Server brings Excel to the Web. It turns the single user spreadsheet into a dynamic application with full access to the "Enterprise" (databases, Web services, etc.). Companies whose employees rely on Excel as a tool for reporting and manipulating data spend a great deal of time and effort managing collaboration, struggling to merge data from multiple copies of the same sheet, and puzzling over how to get current information from all sorts of corporate data sources. Worksheet Server solves all those problems, leveraging the Web to bring huge advantages to corporate Excel users, and is (as far as I'm aware) the only product of its kind.