Tangled in the Threads

Jon Udell, March 7, 2001

A Simple Zope Spreadsheet

Zope's infrastructure solves most of the problem

In the first of a two-parter, Jon shows how to make a very simple web-based spreadsheet using Zope

The other day somebody asked me to provide a way for salespeople to track their accounts on the web. They're currently using a spreadsheet, and cc'ing copies of it to one another. They'd like to use a single shared instance of the spreadsheet on the web. And they'd like it to be dead simple on the client side: no Java, or DHTML, or ActiveX, or even JavaScript, just plain old HTML.

Well, why not? An HTML table should only need to be augmented with a persistent, editable data store in order to become a rudimentary spreadsheet.

The phrase "persistent, editable data store" made me think of Zope. With ZODB (an object database) at its core, plus a built-in facility for creating and editing ZODB objects and properties through the web, and for controlling access to those objects, Zope's basic infrastructure goes a long way toward solving the problem.

Was there a Zope "product" (server plug-in) that would get me the rest of the way? I looked around, and didn't find one. So I started tinkering and came up with an acceptable solution to the problem at hand. I offer it here (and in next week's column) for two reasons. First, as a Zope tutorial for those who haven't yet dipped a toe into those waters. Second, because I know that writing about this project will prompt folks to write back about other ways to solve the problem, in Zope and in other environments. So, let's get started.

A hand-built prototype

To take the first baby step, let's use this little spreadsheet as a design target:

Account	Rep	Amount
		300

Here's the HTML code behind that form:

<form method="post" action="sheet">
<table>
<tr>
<td>Account</td>
<td>Rep</td>
<td>Amount</td>
</tr>
<tr>
<td><input size="8" name="R1_Account" value="CompanyX"></td>
<td><input size="8" name="R1_Rep" value="Joe"></td>
<td><input size="4" name="R1_Amount" value="200"></td>
</tr>
<tr>
<td><input size="8" name="R2_Account" value="CompanyY"></td>
<td><input size="8" name="R2_Rep" value="Sara"></td>
<td><input size="4" name="R2_Amount" value="100"></td>
</tr>
<tr>
<td></td>
<td></td>
<td><b>300</b></td>
</tr>
<tr><td colspan="4">
<input type="submit" value="update">
</td></tr>
</table>
</form>

To hook this thing into Zope, I created a Folder called "ss" and, in that folder, a DTML Document called "sheet". DTML is Zope's "Document Template Markup Language" which is, in the degenerate case, just HTML. So you can plug the above HTML into your newly-created DTML Document and it will work. It won't do anything interesting, yet, but when you click Update the form will regenerate.

The input boxes on the form are, obviously, just begging to become windows into the ZODB. Let's make it so. On the management screen for the "ss" folder -- which you reach by way of the Zope URL /ss/manage -- add properties for the cells of the spreadsheet. For example:

property name	property type
R1_Account	string
R1_Rep	string
R1_Amount	float
R2_Account	string
R2_Rep	string
R2_Amount	float

Now modify the form as follows (changes in bold):

<form method="post" action="sheet">
<table>
<tr>
<td>Account</td>
<td>Rep</td>
<td>Amount</td>
</tr>
<tr>
<td><input size="8" name="R1_Account" value="<dtml-var R1_Account>"></td>
<td><input size="8" name="R1_Rep" value="<dtml-var R1_Rep>"></td>
<td><input size="4" name="R1_Amount" value="<dtml-var R1_Amount>"></td>
</tr>
<tr>
<td><input size="8" name="R2_Account" value="<dtml-var R2_Account>"></td>
<td><input size="8" name="R2_Rep" value="<dtml-var R2_Rep>"></td>
<td><input size="4" name="R2_Amount" value="<dtml-var R2_Amount>"></td>
</tr>
<tr>
<td></td>
<td></td>
<td><b>
<dtml-var expr="R1_Amount + R2_Amount">
</b></td>
</tr>
<tr><td colspan="4">
<input type="submit" value="update">
</td></tr>
</table>
</form>

This gets us one step closer. We're using Zope's <dmtl-var> tag to display values stored in the ZODB. For the regular rows, we just use those values directly. For the totals row, we use an expression to sum the values in the Amount column.

Now, if you use /ss/manage to set the value of a ZODB property, it will display in the corresponding cell. But if you change R1_Account on the form, say from "IBM" to "SGI," and then click Update, it reverts to "IBM." We need to connect the form to ZODB in the same way that the property-editing page, /ss/manage, is connected to ZODB.

First working prototype

Here's some DTML code which, when added to the top of the form, will make it write to, as well as read from, ZODB:

<dtml-if expr="REQUEST.has_key('R1_Account')">
<dtml-call "PARENTS[0].manage_changeProperties(R1_Account = REQUEST['R1_Account'])">
</dtml-if>
<dtml-if expr="REQUEST.has_key('R1_Rep')">
<dtml-call "PARENTS[0].manage_changeProperties(R1_Rep = REQUEST['R1_Rep'])">
</dtml-if>
<dtml-if expr="REQUEST.has_key('R1_Amount')">
<dtml-call "PARENTS[0].manage_changeProperties(R1_Amount = REQUEST['R1_Amount'])">
</dtml-if>
<dtml-if expr="REQUEST.has_key('R2_Account')">
<dtml-call "PARENTS[0].manage_changeProperties(R2_Account = REQUEST['R2_Account'])">
</dtml-if>
<dtml-if expr="REQUEST.has_key('R2_Rep')">
<dtml-call "PARENTS[0].manage_changeProperties(R2_Rep = REQUEST['R2_Rep'])">
</dtml-if>
<dtml-if expr="REQUEST.has_key('R2_Amount')">
<dtml-call "PARENTS[0].manage_changeProperties(R2_Amount = REQUEST['R2_Amount'])">
</dtml-if>

For each cell, we ask if the HTTP request object (a Python dictionary) has a key corresponding to that cell. If it does, we use the Zope API call, manage_changeProperties, to set the cell's ZODB property to the value of the HTTP request variable. Why test for the existence of each key? On first invocation of the page /ss/sheet there won't be any cell values -- unless you specify them, e.g.: /ss/sheet?R1_Account=Microsoft. Otherwise, the cell values are only sent on subsequent requests, when Update is clicked.

Note that the manage_changeProperties call is qualifed by PARENTS[0]. That's because these properties attach not to the DTML Document, /ss/sheet, but rather to its container, the Folder /ss. The properties could attach directly to the sheet itself, but by placing them a level up in the hierarchy, they're accessible both to this sheet and to other components -- such as related sheets, or Zope external methods.

With the addition of these DTML statements, we now have a working implementation of the design target. Of course, when the spreadsheet gets even a bit more complex, this approach will soon bog down. You're not going to want to create dozens or hundreds of ZODB properties on a Zope management screen, much less correlate those properties with dozens or hundreds of DTML statements. People shouldn't have to do that kind of grunt work. But programs just love to!

Programs that write programs

Perl wizard Tom Christiansen has an epithet that I just love:

Programs that write programs are the happiest programs in the world.

Tom attributes this gem to Andrew Hume, who was president and is now vice president of USENIX. It came up a few years ago when Tom and I were discussing a strange application of Perl that I'd stumbled upon. I had a dataset, and some Perl code to convert that dataset into a collection of web pages. Now I wanted to print the dataset, with a table of contents and page numbers and an index. Converting web content to printed content is a fairly hard problem, because the two media are really very different. I started off thinking about HTML-to-RTF translators, but since there was no page-number or index information in the web pages, that didn't get me very far.

Then I started noodling around with Word's VBA (Visual Basic for Applications) script engine. It was easy to write VBA functions that generated the elements of my pages -- table cells, table rows, paragraphs of text. A VBA script that made a whole bunch of calls to these functions could fabricate a Word document which could then, by way of Word's page-numbering and indexing facilities, easily morph into the desired result.

No sane human would write that VBA script. But my Perl script, which was already transforming Perl data structures into Web pages, could happily also emit a VBA script which, when run, would produce a page-numbered and indexed printed document.

Well, the same principle can apply to our Zope spreadsheet. It's not hard to write a script that generates, from a data structure, the DTML code to implement a spreadsheet interface to that data structure. And of course you can use any convenient language to do that code generation. Python would be a good candidate but, although Python is Zope's native language, there's no reason it has to be Python. In my case, because Perl comes more quickly to my fingers than Python, I used Perl.

Next week, I'll walk through the Perl-based Zope spreadsheet generator that I came up with, and with luck, will have some interesting newsgroup feedback to report based on this week's column. The following week, I'll be on vacation, sailing in the Caribbean with former BYTE editor Ben Smith on his boat, the Mother of Perl.

---

Jon Udell (http://udell.roninhouse.com/) was BYTE Magazine's executive editor for new media, the architect of the original www.byte.com, and author of BYTE's Web Project column. He's now an independent Web/Internet consultant, and is the author of Practical Internet Groupware, from O'Reilly and Associates. His recent BYTE.com columns are archived at http://www.byte.com/index/threads

This work is licensed under a Creative Commons License.