General Java

They're one of the most commonly used computing tools in business today, regardless of a company's revenues or number of employees. Wall Street money mavens use them, and so does your hometown accountant...spreadsheets.

Did you ever tweak spreadsheet formulas to play "what-if" games with your stock portfolio? Have you ever received an e-mail with a spreadsheet attachment full of sales figures or forecasts? And by the way, how did your friend send you the results of this week's fantasy football league? Thanks largely to the widespread distribution of Microsoft Office and Microsoft Excel, the spreadsheet is perhaps the top data analysis and reporting tool in the industry.

Distinguished by a rows-and-columns interface that even the casual computer user can understand, spreadsheets provide a wide range of functionality that many types of businesses can leverage. They present data in an understandable format, provide intuitive interfaces for data collection, deliver fast calculations, and report and analyze data from databases and other sources.

Given their popularity and effectiveness, it's logical to use spreadsheets for data analysis when building Java-based e-business and B2B applications. As you'll see later, they're especially effective when used to build servlets and Java Server Pages. In this capacity spreadsheets can play the vital role of constructing business logic and rules for data analysis in the middle of Web-centric, three-tier architectures (see Figure 1).

Why Use Spreadsheets?
The great advantage to using spreadsheets as a component of a larger application is that they're perhaps the single most widely utilized and understood productivity tool in the business world today. Most users, especially those in financial institutions, already know enough about them to construct business rules with them, even if they don't have higher-level programming skills such as knowing how to use SQL or stored procedures. Spreadsheets can ease the complexity and lower the maintenance costs of an application by delegating the construction of business rules to the user.

State Street Corporation in Boston, for example, makes extensive use of spreadsheets in an application called NAVAlert. State Street's fund managers establish their own business rules within the application's spreadsheets to notify them when fund prices meet certain levels. When the fund managers need to alter the rules to account for changing market conditions, no one on State Street's development team needs to get involved. Instead the fund managers change the rules in the spreadsheets by themselves. The same scenario is entirely possible using Java spreadsheets on the server as templates for incoming data. When the rules of a business change, a developer or end user can simply provide a new spreadsheet template that contains the new business logic, thereby reducing application development time and costs.

Another use of spreadsheets within Java development is to leverage their inherent analytical capabilities. Especially in applications that call for specialized computations, the formulas built into spreadsheets, together with their engineering, statistical and financial functions, can save developers substantial time. And because most spreadsheets also provide a charting component for additional data display options, developers no longer need to worry about providing that functionality from scratch either.

Where Spreadsheets Are Used
Many corporations are currently using spreadsheets in multitier architectures in vertical industries such as finance, banking, energy, insurance, retail, securities, software, and many others. Spreadsheets can be used to deliver various types of applications including analysis and reporting, billing and invoicing, cost estimating, expense tracking, fund processing, risk management, sales forecasting, and others.

Some current examples of where and how businesses are using spreadsheets in multitier architectures include:

• An investor services company locates spreadsheets on its server to calculate a constant stream of stock data from databases and other sources. Users can access the data with their browsers at any time of day for up-to-date portfolio valuations.
• A financial firm locates spreadsheets on its server to compile data from different sources into an understandable, easy-to-use file format. The reports are compiled from the previous business day's activity and e-mailed to managers every morning.
• An insurance company customizes spreadsheet interfaces that mimic paper forms. The spreadsheets are deployed in browsers where users can get instant calculations and feedback on the data they input.

There are many more examples but as a general rule any situation that calls for intuitive data-display and -entry, fast calculations, and robust data reports and analysis is one in which spreadsheets can be used to advantage.

When to Use Spreadsheets
Spreadsheets are at their best when calculating and analyzing a large amount of data quickly. This was, after all, their original purpose; their architecture was developed for performing this exact task.

A developer can determine whether or not an application's requirements truly constitute a large amount of data. For an application with simple calculations involving a small number of variables, a spreadsheet calculation engine obviously might not be necessary. But for volumes of data covering a long time period and typically collected from a database, a spreadsheet provides an extremely manageable paradigm for calculations and a useful grid interface for presenting and manipulating data.

Using spreadsheets for calculations is especially effective in server-side applications. For example, data from several sources might be poured into a spreadsheet for analysis before being passed to clients or stored in yet another database. Numerous financial services companies use spreadsheets on the back end for just this purpose.

In a nutshell, one of the strongest facets of spreadsheets is that they can increase efficiency by performing calculations and data analysis on the server. This is especially true in distributed, three-tier computing environments that rely on Java technology.

Options for Using Spreadsheets in Java Development
Java developers have several options for implementing spreadsheet functionality in their projects. Several factors affect which option to choose. Most noteworthy is probably the presence (or absence) of Excel in a distributed environment.

Since familiarity with using Excel is widespread, it makes sense to leverage the Excel application, the Excel file format and the expertise of end users whenever possible. But Excel has limitations for Java developers who deploy applications widely on the Web outside firewalls. These limitations include a dependence on Windows, a limited API, the inability to be embedded and deployed within an application and the inability to leverage advances in Web, application and database servers. These issues noted, developers can choose one of the following options:

• Java developers who aren't concerned about pure platform-independent solutions can use Microsoft's Visual J++ to access Excel; however, this provides a limited API to work with and will require execution of the Java application/servlet in the Microsoft JVM. This also requires that Excel be present on the desktop, as Excel is unable to be embedded as part of an application.

• A cross-platform solution can be built using the Java Native Interface (JNI) to communicate through a C++ implemented COM wrapper to Excel. While this technique is independent of JVM, it still requires native code and thus is tied to a specific hardware configuration. And again, this requires Excel to be present and provides a limited API to customize functionality.
• A third option is to use a JavaBean component that delivers Excel-compatible spreadsheet functionality, such as Formula One 7.0 from Tidestone Technologies. Formula One has been certified as 100% Pure Java, which means that you can use Excel files on non-Windows platforms. It has a defined and documented role as a JavaBean component and can be embedded in distributed applications. Formula One's API is easily accessible with Java code and allows you to embed a spreadsheet engine in any tier. It can leverage advances in Java's use with Web, application and database servers – and it doesn't rely on Excel to be present on the desktop to function.

Formula One is especially effective in the middle layer of three-tier applications where it can be used to construct business rules, perform analysis and distribute data at the core of Java applications, servlets, applets and JavaServer Pages. In these architectures it can be used to access databases through JDBC, perform calculations and analysis, and distribute the results between any client/server environment: HTML for thin clients, Excel for Excel clients or live spreadsheet-powered applets for "heads-down" users who require interfaces more robust than static HTML.

With these abilities in mind, the examples in the remainder of this article are intended to show how to build Java spreadsheet solutions in environments with Formula One – with and without Windows and Excel being present.

Leveraging Excel on the Desktop With Server-Side Java and MIME Types
The first example shows how to dynamically create a spreadsheet on a server and deliver its contents to the client through a browser connection. By setting a MIME type in the servlet code, Excel will be launched within the browser, giving users the ability to perform further analysis on the spreadsheet using Excel on their desktops. The architecture of this example is shown in Figure 2.

These examples use an Oracle8i database with 12 months of sales figures for a fictitious company. It's assumed a Web page has been built with a form allowing users to select the month of sales figures they'd like to receive in an Excel file. The form has a drop-down box with its NAME attribute set as "month" and its action pointing to the servlet, ExcelServlet.

The ExcelServlet reads in a prebuilt Excel file to initialize the in-memory spreadsheet – report_template.xls, see Figure 3 – connects to the database to populate selected spreadsheet cells from data returned from a JDBC query, and then writes the spreadsheet to the servlet output stream with an appropriate MIME type. This MIME type, "application/vnd.ms-excel", forces the browser to load the Excel plug-in and display the spreadsheet.

The database-related procedures have been modularized in the dbManipulatations.java class (see Listing 1) and the servlet specific code is shown in ExcelServlet.java (see Listing 2). The class dbManipulations.java connects to the database via JDBC and populates rows in the report_template.xls spreadsheet based on the user input from the Web page. The ExcelServlet.java class provides the servlet "plumbing" to write the Excel data to the browser after the model has been dynamically created.

Invoking the ExcelServlet from a browser with month and reportTemplate parameters should show a Web page like the one depicted in Figure 4. This demonstrates one way developers can leverage spreadsheets in the middle tier and Excel on clients' desktops, even if the application's architecture doesn't include a Windows-based server.

Leveraging Excel on the Desktop with Server-Side Java and JavaMail
Another option to consider is e-mailing clients the Excel file generated by Formula One on the server using the JavaMail API. This example of Formula One is ideal for developers who have a large number of desktops with Excel and a large number of Excel-savvy users who require time-sensitive data on a recurring basis. For instance, perhaps reports from the previous day's business activity could be e-mailed to managers every morning or portfolio reports could be sent to investors at the close of trading each day. While this particular example requires a Web server, it would be easy to convert this example into a stand-alone application, which could be scheduled to run in the background on a daily basis.

The addition of JavaMail notwithstanding, this implementation is similar to the prior example and is illustrated in Figure 5.

First, it's assumed there's a Web page with a form that asks for an e-mail address where the report will be sent and a month of sales figures for Formula One to generate. The page and resulting spreadsheet that is sent by e-mail could look like those in Figures 6 and 7.

(Note: This demo would also work by accessing the servlet with a name=value pair that includes the parameters &to=name@domain.com&month=SelectedMonth.)

Embedded into a servlet, Formula One uses a spreadsheet created in Excel as a template – for this example we'll use the template in Figure 3 from the prior demo - then connects to the database and populates specified cells with the data values returned from the JDBC call. From there, it performs a recalculation of the new data, writes the spreadsheet as an Excel file and e-mails it to the submitted e-mail account using the standard JavaMail API. The code required to perform these actions can be found in Listing 3, WebMail.java. As with the prior example, the dbManipulations.java class of Listing 1 includes the modularized database code and will be needed to compile this servlet.

No Excel? How to Deliver Spreadsheets to Thin and Non-Windows Clients
In Web-based computing developers often don't have control over which platform their solutions might eventually operate on. In this situation a cross-platform applet with Formula One could enable the delivery of live spreadsheets to the browser. While this is a plausible option, slow connections and low bandwidths are sometimes a concern, so developers must be sure their applications minimize download times. In cases like this it makes sense to perform spreadsheet calculations and data manipulation on the server and deliver the results to clients through lightweight HTML or images. With Formula One, it's possible to deliver Java-based solutions in this manner.

This example is similar to the others. The same spreadsheet will be used on the server to calculate sales figures for a selected month. However, in this case the finished spreadsheet will be written out as an HTML table through a JSP that utilizes Formula One's HTMLWriter method. The requesting HTML page references our JSP page, report.jsp, rather than the ExcelServlet and passes the requested month as a parameter (see Figure 8). The code for report.jsp is in Listing 4.

This architecture allows users to leverage a spreadsheet's calculations on a server regardless of the operating system or bandwidth situation, as shown in Figure 9.

Formula One also offers the ability to deliver spreadsheets and charts as static GIF, JPG or PNG images for thin-client environments where users simply need to view data. For more information on Formula One visit their Web site at www.tidestone.com.

Summary
Few technologies are as familiar to users and developers as spreadsheets. When used as the data analysis component of a larger application, spreadsheets offer many benefits: developers can leverage their built-in features such as spreadsheet rules and formulas to increase efficiency and lower maintenance costs, while users benefit from the low learning curve.

Add Java's server-side strengths and it becomes clear that a spreadsheet can be extremely useful behind the scenes of an application, particularly in the middle layer of three-tier architectures when a large amount of data needs to be computed and analyzed.