|
 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.
|
