Giving Spreadsheets Extra Juice

Custom Fit

Juice provides a Java API for integration into custom applications on multiple platforms. It also comes with a Windows-only Microsoft Excel add-in for developing collaborative and real-time spreadsheets. Juice's real value, though, is its ability to aggregate and poll disparate data sources, acting as both proxy and cache.

Juice can query any JDBC (Java Database Connectivity) data source, and did just that to our IBM DB2 database. The server does not rely on external data-source definitions, such as a system or user DSN (data source name), and it includes connection pooling for higher volume sources. Server management from a Java-based client can be performed locally or remotely. The management console is easy to navigate and utilitarian--there are no bells or whistles, just a simple console that gets the job done.

The server lacks external authentication mechanisms, though it includes a role-based system. It also offers integration with NTLM (NT LAN Man), an authentication process used by all Windows NT products. It does not support Active Directory, which is a mistake, and though there is no support currently for LDAP, this is in the works. It also lacks work-flow support, a standard feature in offerings from Nobilis.

The Excel add-in is easy to use for simple tasks, though you'll need to brush up on your SQL if you want to query relational database sources. The client communicates with the server using an extensive XML application protocol. SSL is an option if security between the client and server is necessary.A more intriguing feature is the ability to scrape data from a table within a Web page. The client tool used to define the script, which the server will execute to update data, looks like a standard browser. After opening it, I browsed to a Web page and clicked on the table to indicate its placement in the page. The data appeared in the spreadsheet and I could format the cells, all while the data was continually and automatically refreshing.

To protect Web page data, authentication can be hard-coded into the JavaScript used to retrieve the data or, for more flexibility and higher security, can be passed as a parameter from a cell in the spreadsheet. I used this feature for database queries--passing dates, order IDs and customer numbers from cells on the spreadsheet back to the server. In this way, you can use Excel as an interactive interface to one or more databases, while applying a level of security. User names and passwords also can be required for individual queries.

What's Developing

A power user or developer can access the Juice objects via macros or Visual Basic and can create a rich, real-time collaborative application completely within the confines of a spreadsheet. (I never did get around to learning VB, so my spreadsheets weren't nearly as good-looking as those the engineer from Juice coded up.) Data can be updated automatically, manually, or, if using parameter-based queries, whenever the input data has changed. This last option is the easiest on the network--you won't be pulling potentially huge XML files across the network every few seconds unnecessarily. The server defaults to polling a data source every 45 seconds, but this setting also is configurable.In any file, you can combine information from multiple sources--a remote table of orders on a Web page and an internal database of inventory and prices, for example. And you can write back to some sources, such as files and relational databases, in addition to reading from them.

For collaboration, you enter via the QuickShare datasource included with the Juice Server.

I built a quick spreadsheet, then selected the cells I wanted "Bob," my user, to be able to update.

I published them to the server in a topic called "Revenue for Bob." Next, I opened a separate copy of Excel, hit the subscribe button and chose the topic Revenue for Bob (see screenshot). The data was immediately pulled into the spreadsheet and color-coded to show which cells I could and could not update. The color-coding is, of course, user-configurable.

Overall, Juice provides a rich set of features for enhancing spreadsheets. Although the client is a bit rough around the edges--it requires a programmer or a true power user for exceptional spreadsheets--it's an excellent alternative to custom fat clients, if you can stomach the price.Lori MacVittie is a technology editor working out of our Green Bay, Wis., labs. Write to her at [email protected].

Post a comment or question on this story.