Design PHP Apps with Excel Using Worksheet Server

From time to time, someone does something with PHP that is truly unique -- something that goes far beyond the norm of PHP applications listed at Hotscripts and leaves you truly impressed by the capabilities of this language.

One prime example is Jedox's Worksheet Server, a truly unique combination of Microsoft Excel and PHP that enables users to build "online spreadsheets".

I first heard of Worksheet Server in December of last year and was so fascinated by the idea that I had to drop by Jedox (a couple of hours from home) to find out more. Since then, I've been messing with an evaluation copy of Worksheet Server, in spare moments, in an attempt to determine its significance as a tool for building Web applications.

If you've read Generating Spreadsheets with PHP and PEAR, you'll know I'm not averse to using Excel with PHP when it makes a better choice than a normal HTML table for displaying data. Worksheet Server works in the opposite direction, the starting point being an Excel spreadsheet and the end result being a working PHP Web application.

This is a technological review of Worksheet Server as seen through the eyes of a PHP developer.

On today's workbook:

• Introducing Worksheet Server: Worksheet what?
• First Impressions: thoughts as a naive Excel user
• The PHP Developers View: generated code and extending
• SUM(A1:A3): conclusions

Introducing Worksheet Server

The fundamental purpose of Worksheet Server is to enable Excel users to build Web applications using an environment in which they're already confident. As a Web developer, the best way to think of Worksheet Server is as a design tool for building specialized PHP applications, where the tool just happens to be Excel.

Worksheet Server consists of two distinct software packages: a Web platform (Apache, PHP and the Worksheet Server code bundled in an easy-to-install form) and a set of add-ons for Excel itself, to help you design and publish applications. I'll refer to the latter half as the "WS Excel Designer" to avoid confusion.

The Worksheet Server Life Cycle

The easiest way to grasp what Worksheet Server does is to consider the "life cycle" of an application built and deployed with it.

Step 1: Design

Design of an application begins with an Excel spreadsheet, 99% of the work being no different than creating a normal spreadsheet.

Special WS Excel Designer macros are available to perform tasks like adding hyperlinks and images to the application, so that they appear correctly as HTML in the Web environment. Further, Worksheet Server functions are also available, such as the MYSQLDATA function, which populates a cell with a result from a MySQL query. These are used just like normal Excel functions, but only take on meaning once they're run on the Web server.

Step 2: Build

The designer, happy with the spreadsheet, "builds" the application for the Worksheet Server Web environment, using another WS Excel Designer macro.

In practice, a ZIP file is generated, which contains everything Worksheet Server needs to know about the application.

Step 3: Publish

In Worksheet Server (the Web server), administrators publish the application. They first log into the "Application Center" (a PHP application that manages multiple Worksheet Server applications), upload the ZIP file, "compile" it (a process which generates the PHP scripts for the application), then start the application (the application becomes available on a unique URL on the Web server).

Step 4: Security

The administrators log into the new application using the URL obtained from the Application Center. They create login accounts for end users, giving them privileges to access the application.

Step 5: Start work!

End users can now log in (over the Web) and begin to work with the application. They no longer work with separate local copies of the same spreadsheet; rather, they collaborate via a dynamic Web application. This looks largely the same as the original spreadsheet, but allows them to work concurrently (changes are reflected immediately) and may connect them with a wide variety of other remote data sources (databases, Web services and so on).

Step X: Re-design

If changes are required to the design of the application, the above cycle starts over, minus the effort of setting up user accounts. The designers continue work where they left off, using their saved .xls file (or an up-to-date copy they've downloaded from Worksheet Server), then re-publishing the application.

Worksheet Server acts as an active code generator (rather than a passive code generator -- see this post for thoughts on active vs. passive code generation). The code running on the Web server is not meant to be modified directly (although there are opportunities for you to write "external" PHP code to interact with it). As such, Worksheet Server allows designers to make incremental changes to their applications without breaking anything.

That hopefully makes it clear what Worksheet Server actually is. Of course, seeing the end result helps even more -- the best place to look is Jedox's online demos (note that both the application, and the Excel file used to create it, are available).

First Impressions

With an evaluation copy of Worksheet Server and a copy of the manual in hand, I took it for a spin, installing it and following the introductory tutorial that walks you through the building of a basic application. The manual itself is straightforward, and wastes no time overloading the reader with extraneous information. It's focused on getting down to real work as quickly as possible. I wish more technical documents were written this way.

Set Up

Installing Worksheet Server is a two-stage process.

Step one involves the installation of the Web environment. You can run the server both on Linux and Windows (there's no requirement for Windows-specific extensions, despite Excel being the design tool). I chose Windows, ignoring Linux on the other partition, as I figured it would save booting between the two while working with Excel on the same system.

The installation involves simply running the installer program and watching it set up Apache, PHP and the Worksheet Server software; there's very little requirement for user intervention and no awkward questions that would stump a non-techie.

Being used to setting up PHP/Apache by hand, I was a little suspicious of what I'd find on closer inspection once the installation was complete. I was quickly relieved to discover not only the latest stable releases (e.g. PHP 4.3.6), but a sensible PHP configuration (e.g. register_globals switched off and error_reporting set to E_ALL). Jedox seem to know what they're doing.

All the standard Windows PHP extensions are available along with additional extensions developed by Jedox in-house. A license file is required to run the Worksheet Server application (simply a matter of copying it to the correct location) and, once that was done, I was able immediately to login to the online "Application Center".

Step two involves installing the WS Excel Designer, which requires an additional license file to work properly. With the file in place, the quick way to do this is to simply start Excel, then open an .xla file that Jedox provides, which adds an extra menu, containing WS Excel Designer tools, for the duration of the session. The smarter route is to use the Excel Add-Ins manager to attach the new menu permanently. Note that Excel 2000 is a minimum requirement.

Overall, installation is very easy and is geared, clearly, for users who aren't interested in becoming sysadmins.

Instant Application

Following the introductory tutorial, I was impressed by how easy it was to work with WS Excel Designer. It really takes only a few minutes for anyone who has experience with Excel to get a grip on the WS Excel Designer extensions. Generally, the process of building a Worksheet Server application is no different than that of a normal spreadsheet and the added functionality does not intrude on the normal "flow" of spreadsheet creation. Some experience is needed to understand how the spreadsheet will look when it's finally deployed online, but this is more a question of fine tuning.

With my spreadsheet design in place, it was a simple matter of saving the spreadsheet using WS Excel Designer macro, then switching to a Web browser to upload it.

The "Application Center", which runs on the Web server and is used to upload and deploy applications, has a simple but clean interface, which is generally intuitive to work with. A little more reading of the manual is required to ascertain exactly what the different buttons and menus do, but I never found myself overwhelmed and, with a few clicks, had the application compiled and running.

Heading to the published URL, I first logged in through an administrator account and created a new user to access the application. Logging back in with the new account, I saw my spreadsheet, now running as a live PHP Web application.

I was very impressed by how little time it had taken to get this far.

Look and Feel

The visual design process within Excel translates directly to the generated Web application. In other words, the cell structure of a given Excel worksheet, the formulas and relationships between cells (even across multiple worksheets), the font sizes and colors will all manifest on the Web server.

Worksheet Server even allows you to add charts to the spreadsheet for publication, assuming you use the Excel Chart Wizard or some of the special functions WS Excel Designer provides. Online the charts are rendered with help from JpGraph.

Note that some "quirks" need to be considered, bearing in mind that Excel, as a presentation tool, doesn't map directly to "HTML + Web browser". Jedox recommend setting the default font as Arial, which is widely supported both across browsers and operating systems, and encourage you to keep font sizes within a particular range, to prevent strange-looking results. They also advise you to keep worksheets to a reasonable page size (both horizontally and vertically), to fit the ergonomics of a Web browser and make the application easy to navigate (who likes intensive scrolling?).

Different "skins" are provided when you compile the application, to allow you to apply general changes to the look and feel (mainly affecting the panels that appear at the top and bottom).

Generally, the Web version is a pretty fair copy of what I was working with in Excel. If you compare each original Excel file online (linked next to each demo) with its demo, you can see how the original design matches the end result.

Navigation

Top level navigation is provided by a panel added to the top and bottom of the display. These provide access to general Worksheet Server functionality. Depending on the application and environment, further functionality, such as downloading the spreadsheet in Excel format, exporting all the data in the application as an XML document or rendering the sheet as a printable PDF, is also available from the top panel.

Modifiable cells in the spreadsheet are displayed as form input text fields, and use JavaScript to control the cursor between enter-key strokes, making cell navigation similar to Excel.

Spreadsheets comprising multiple worksheets are navigable either via a drop down menu or by construction a special menu sheet in Excel. You can see the difference by comparing the Outline Demo with the Basel II Cockpit Demo.

I imagine Excel users should be fairly happy in this environment.

View Source

A comparison of the displays in Firefox and Internet Explorer shows they're a pretty close match (without obvious accessibility issues).

A glance over the source HTML reveals many tables, which may not please everyone, but this is tabular data, after all. A scattering of upper case tag names suggests a little cleaning up may be needed, and the style attribute is used in many tags.

This does mean you're pretty much required to modify structure via Excel, rather than applying clever CSS tricks, but, as I mentioned before, Worksheet Server is an active code generator; you're not meant to modify the end result directly.

Limitations

Not all Excel functionality can be used when building Worksheet Server applications, and these limitations are identified clearly in the Worksheet Server manual.

For example, from Excel's "Controls" toolbar, only the "listbox", "drop down" and "check box" controls may be used, while embedded images will be ignored (although charts may be added using the Chart Wizard or special functions).

No support is provided for Excel tools such as "Goal Seek" and "Scenarios"; Visual Basic macros are another no-go. The lack of support for Visual Basic may, at first glance, seem like a show-stopper. In practice, Worksheet Server replaces VB with a true multi-user environment backed by PHP when you need to accomplish something unusual. Remember, Worksheet Server is an application design tool, rather than some Excel add-on that blindly exports spreadsheets to HTML. Jedox recommend you start each application with a fresh spreadsheet, and encourage you to plan its design with the specific aim of delivering a Web application.

The only other significant limitation, for those that use it, is matrix functions are not supported.

Otherwise, all the in-built functions that you can embed in an Excel spreadsheet are supported directly online with a PHP function of the same name.

Hierarchical Data

One feature of Worksheet Server that will be new to Excel users is the notion of Report Entities.

Excel is designed to be a single-user application and the way it organises data is really only suitable for use by one person at a time. By contrast, Worksheet Server is geared to a multi-user environment and, to make this possible, it uses a feature called Report Entities. This allows a tree of worksheets to be defined, each generated from the same source spreadsheet, while allowing data to be grouped and aggregated.

The best way to understand how Report Entities work is by looking at the Outline Demo. Once you're logged in, you'll notice that a drop down menu is available from the top panel, allowing you to change Reporting Entity to either "Total", "North", "West" or "East". The "North", "West" and "East" regions represent Sales areas in this demo, and regional sales managers would use their respective Report Entity to enter sales data. The "Total" sheet is the top level Reporting Entity and combines the data from each of the child entities. This view would be available to the Chief Sales Executive, allowing him or her to see the big picture of sales and drill down to each region as needed.

Notice how all four Reporting Entities have the same design; all are generated from the same spreadsheet, built in Excel. At the same time, the figure displayed in the top left cell of the Total sheet represents the sum of the same cell in each of the child entities.

To create Reporting Entities, all that's required is that you define them in the online administration interface for the application, and specify the hierarchy. You can also determine which users have access to which entities; this allows you to restrict your Sales Manager for the "North" region to the relevant entity, for example. What's impressive about Reporting Entities is that, once they're defined as a tree, Worksheet Server takes care of all of the rest, automatically calculating the values of cells in top level entities without requiring any modifications to the original design.

Reporting Entities play a big part in making Worksheet Server applications into powerful, multi-user collaboration tools.