Illustration by Lia Avant

XML-RPC: Connecting Excel and Zope

In this article we describe how we used the XML-RPC protocol as a viable alternative to SOAP for connecting Zope with a Microsoft Excel client. We illustrate this with the concrete example of an interface to a project management database.

At the University of Applied Sciences Solothurn FHSO, we use Zope/Plone for our Intranet. Because Zope is developed under the Open Source model, it was possible to easily customize the basic functionality to our needs. Now we have an environment that works well, which is used in a variety of situations and for various purposes.

One of the new Zope functions that we have implemented is a front end to our project management system. The main goal of this development was to provide a Web interface to our users so that they can access and manage project information from wherever they are.

In addition to this, the proposal for a new project can also be carried out within this environment. The system manages the proper flow of the proposal to the corresponding people who have the right to sign it, or who have to provide additional input such as a distinct project number. After publishing the proposal, it is visible to all employees.

Such projects contain a lot of interesting information (e.g. start date, costs, project team, project phase) which is valuable for the decision-makers and can provide the basis for management reports or for marketing efforts.

All projects are stored in the Zope Database ZODB. As an object-oriented database the ZODB is very convenient to work with, as long as you live in the Zope environment itself. But it is more difficult to access if you are outside of Zope. Our decision to go with the ZODB was driven by the short time available for the project and the fact that the ZODB is well-integrated into Zope and its usage is simple.

Recently we were confronted with the task of providing management reports of the projects. This meant we had to find a means of to accessing the information within Zope and of formatting it in an appropriate way, mainly consisting of diagrams and lists. We saw two possible paths:

Generate PDF reports within Zope using a toolkit like reportlab.

Read the necessary information from Zope and transfer it into a desktop application such as Excel which is capable of generating reports based on such data.We decided to take the second path, for the following reasons:

• Our staff uses computers equipped with Microsoft products; therefore, everybody is familiar with the Microsoft Office Suite and Excel in particular.
• Excel is very powerful in generating diagrams
• Excel is widely known and accepted
• The users themselves are able to generate reports according to their personal needs We then had to chose a mechanism for transferring the data between Zope and the desktop application. It turns out that Zope has a powerful integrated protocol, called XML-RPC, which allows access to Zope objects, even over the Internet.

In the following sections we will give a short introduction to the XML-RPC protocol, contrast it with SOAP, describe its implementations both in Zope and Excel, and finally show how we built our concrete application to access the project data.

What is XML-RPC? Here is a short definition of XML-RPC taken from the XML-RPC.COM Website:

“It's a specification and a set of implementations that allow software running on disparate operating systems, running in different environments, to make procedure calls over the Internet. It is remote procedure calling using HTTP as the transport and XML as the encoding. XML-RPC is designed to be as simple as possible, while allowing complex data structures to be transmitted, processed and returned.”

XML-RPC is very humble in its goals. It doesn't set out to be the solution to every problem. Instead it seeks to be a simple and effective means to request and receive information.

XMLRPC.COM says: "We wanted a clean, extensible format that's very simple. It should be possible for an HTML coder to be able to look at a file containing an XML-RPC procedure call, understand what it's doing, and be able to modify it and have it work on the first or second try... We also wanted it to be an easy-to-implement protocol that could quickly be adapted to run in other environments or on other operating systems."

XML-RPC is a predecessor of SOAP. SOAP is under the control of W3C. Like XML-RPC, SOAP enables developers using different programming languages to write programs that interact with each other, despite the difference in data types and structures. It is an XML-based protocol that defines the standards for encoding the messages that are sent between a service and a client.

SOAP tries to pick up where XML-RPC left off by implementing user-defined data types, the ability to specify the recipient, message-specific processing control, and other features. Check out [4] to see how SOAP can be used together with Zope.

XML-RPC's simplicity, however, is its most attractive feature. It is easy to understand, implement, and debug. The syntax is so uncomplicated that it is not only very easy to find, but also to avoid, mistakes.

Scalars:

XML-RPC supports the following datatypes:

Structs:

In XML-RPC structs define an anonymous set of name value pairs. "A contains s and each contains a and a ." The value(s) can be of any data type

Arrays:

XML-RPC arrays define an anonymous grouping of elements with no limitation for mixing data types like integers and strings within the same array. "An contains a single element, which can contain any number of s."

Zope is an Open Source application server that publishes objects on the Internet. XML-RPC is a simple protocol for carrying out function calls over the Internet using XML and HTTP. Together they form a simple system of remotely scriptable Web objects: Zope provides the Web objects and XML-RPC provides a language-neutral communication protocol.

All Zope objects have a URL, and have methods that can be called over the Web. Normally you call the methods of Zope objects through a Web browser. With XML-RPC the developer is now able to control Web sites programmatically.

Since XML-RPC is built into Zope, all Zope objects are XML-RPC-aware. The ability to script remote objects can be very powerful: since you have access to the full Zope API through XML-RPC, you've got full control. Any action that you can perform with the Zope Management Interface you can also program with XML-RPC. You can create and edit documents via XML-RPC. You can query and manipulate object properties. You can search Zope. You can call your own custom Zope objects and methods. You can even create new users and control security.

For information over the Web about how Zope has embraced XML-RPC see the for Further Reference section of this article.

It is important to note that Zope’s XML-RPC obeys Zope's authentication rules. Therefore, the XML-RPC client should use basic authentication when accessing protected resources.

We used Excel as our Zope client. It supports Visual Basic for Applications (VBA) as a scripting language. To make Excel XML-RPC-aware we need a corresponding library.

EnAppSys provides such libraries as a COM implementation: vbXML (a simple XML Parser) and vbXMLRPC (XML-RPC Client). These libraries are released under the GNU Lesser General Public License (LGPL).

It is easy to implement EnAppSys. On the developers' Website you will find the documentation that enables you to set up your development environment. Here are the most important steps with regard to Microsoft 2000/Excel:

1. Download, install and register the DLLs.
2. Start Excel.
3. Open or create a document.
4. Click on “Tools” on the menu bar and “Macro” on the menu that appears, followed by “Visual Basic Editor”. Alternatively, pressing Alt+F11 usually does the same thing.
5. The Visual Basic for Applications editor will appear. Select the “Tools” menu and then click on the “References” item.
6. Scroll through the available DLLs until you find vbXMLRPC and vbXML and activate them.
7. You can now use the functions of the DLL.

EnAppSys's implementation doesn't come with support for basic authentication, which is needed however to access protected resources. To be able to call such Zope objects, this functionality had to be added to the libraries. Listing 1 shows its implementation.

Listing 1: Basic Authentication implementation in class XMLRPCRequest:

Public BasicAuthFlag As Boolean

...

Dim linsHTTPCall 	As New XMLHTTPCall
Dim lstrBase64 	As New XMLRPCBAse64

...

'---------------------------------------------------
'   Check if Basic Authentication is required
    If BasicAuthFlag Then
        ' Encode userID and Password to a Base64 String
        lstrBase64.Unencoded = mstrUserID + ":" + mstrPassword
        linsHTTPCall.HeadersToSend.Add "Authorization: Basic " + lstrBase64.Encoded
    End If

If the property BasicAuthFlag is set, the User ID and the Password are base64-encoded. The encoded string can now be added to the HTTP header. BasicAuth Flag is a simple boolean property of the XMLRPCRequest class. If set to true, it activates Basic Authentication. On the client side it is a simple assignment, as shown in Listing 2:

Listing 2: Client call to activate Basic Authentication:

Dim linsRequest As New XMLRPCRequest
...
linsRequest.BasicAuthFlag = True

These modified libraries can be downloaded from our server (see Further Resources at the end of this article)

After installing the modified libraries we are now ready to implement the application.

The first thing to do is define the service interface. We want to have a method that is able to return an array of all the projects with their corresponding information. This information should be presented in a structure that allows the attributes to be accessed by name and not by a position number, which is far more readable and fail-safe.

As we already mentioned above, XML-RPC supports 'array' as a datatype. Such an array can contain elements of any datatype. We can therefore use the <struct> datatype to collect the project information and then populate the array with the 'structs' as array elements.

Within Zope we have to set up this method as an 'External Method'.

Listing 3: shows the Python implementation of it.

It is called 'getProjects'. You can see that after querying the database, the returned projects are processed one by one within a “for” loop. Within this loop, all necessary project information is read out and prepared to be put into a dictionary object. This Python object can collect information by <name> and <value>, which corresponds to the definition of an XML-RPC <struct>. Then the <struct> element is added to the project list array, which finally is returned to the caller.

Note: The following Excel list and diagram have been left in the original German due to shortage of time for translation. We feel, however, that this doesn't diminish their value since the point of showing them is to give an idea of what one can do with XML-RPC. This is clear regardless of the language. [Note added by editor.]

On the other side, the Excel client should fill out a worksheet with all projects received from the server (see Figure 1).

Figure 1: Excel Spreadsheet

Based upon such a worksheet, it is easy to generate all the needed diagrams (see Figure 2) and reports.

Figure 2: Diagrams and Reports

Listing 4: The Excel VBA-Client

Subroutine OK_Click() is the entry point of this VBA application. It is called after pressing the OK button of an input form that asks the user for his user identification 'TextUserID' and his password 'TextPassword'. After calling Init(), this subroutine changes the cursor and a message is displayed on the status bar to inform the user that the project data has been retrieved from the server. This retrieval is implemented in subroutine DoXMLRequest(), which is called with the user identification and the password as input parameters.

Subroutine Init() clears the content of the project list and then sets up the needed columns with their corresponding headers (see Figure 1).

Subroutine DoXMLRequest() first executes the function CallXMLRPCServer(), which returns with a XMLRPCResponse object containing an array of all projects. After some error checking, the response can be processed. There are two loops. The first one iterates over the array, whereas the second one handles the structures with their and pairs. Depending on the entry, the corresponding column in the project list worksheet is updated with the respective value. The local variable 'lintIndex' is used to keep track of the actual row within the Excel table.

Function CallXMLRPCerver() handles the communication to the server. First, an XMLRPCRequest object is populated with the needed information. This information defines the server host, the server port, the URL, the method name and all data frequired for basic authentication. Authentication is needed because our server-side resource is protected against unauthorized access. The method name corresponds to the 'External Method' implemented on the server. After some error checks, the XMLRPCResponse object is returned to the caller.

To debug the communication between client and server it is necessary to pass the XML-RPC calls through a trace utility like tcptrace. This makes visible what has been sent between the communication partners – and one realizes very quickly how readable the XML-RPC messages are.

Listing 5: Communication log of the tcptrace utility (client/server)

See Listing 5 as an example.

Zope and XML-RPC provide an easy-to-use, scriptable and distributed object system. Simplicity, reliance on Internet standards, and language independence make XML-RPC an appealing protocol.

Due to Zope’s built-in XML-RPC support, it is very easy to implement a server interface capable of handling XML-RPC calls over the Internet.

Excel VBA and the beautiful EnAppSys libraries are powerful tools for creating XML-RPC clients. Thanks to EnAppSys’ LPGL License, it was possible for us to implement quickly the missing support for the Basic Authentication ourselves.

Since the EnAppSys Libraries are a COM implementation, they can be used within all Microsoft products including Outlook, Word or Excel.

Finally I would like to thank Dominique Brodbeck for his helpful hints on making this article concise and readable.

ReportLab Toolkit:
www.reportlab.org

XML-RPC.COM Website:
www.xmlrpc.org

SOAP on W3C:
http://www.w3c.org/TR/SOAP/

Zope and SOAP
ZopeMag Article

Amos Latteier; “XML-RPC How To”,
www.zope.org/Members/Amos/XML-RPC

Amos Latteier; “Internet Scripting: Zope and XML-RPC”,
Link

EnAppSys;
Link

FHSO-CMS Project Site
Homepage

tcptrace Utility;
Link