Technical White Paper The Excel Reporting Solution for Java

(1)

Technical

White

Paper

The Excel Reporting Solution for Java

Using Actuate e.Spreadsheet Engine as a foundation for

web-based reporting applications, Java developers can

greatly enhance the productivity of Excel users and

leverage their skills to develop more powerful,

easier-to-maintain solutions.

(2)

Actuate Corporation 2

Notice

The information in this white paper is proprietary to Actuate Corporation ("Actuate") and may not be used in any form without the prior consent of Actuate.

Actuate, e.Analysis, e.Report, e.Reporting, e.Spreadsheet, Internet Spreadsheet, Live Report Extension, ReportCast, Report Encyclopedia, Reporting Engines, SmartReports,

Spreadsheets Everywhere, Tidestone and XML reports. All other trademarks are property of their respective owners.

Version 3 January 2006

Actuate Corporation 701 Gateway Boulevard South San Francisco, CA 94080 Tel: (800) 884-8665

(3)

The Server-Based Spreadsheet Emerges

The spreadsheet is the world’s most widely used reporting tool with Microsoft claiming over 150 million Excel users. Spreadsheets have attained such a dominant position in end-user computing by running on the desktop devices people already use and by delivering a spectrum of data collection, calculation, analysis and presentation functionality. But despite their cost-effectiveness and popularity, spreadsheets have had difficulty meeting the stringent requirements of enterprise reporting and have so far been only loosely integrated into

enterprise systems.

However, in the last few years new spreadsheet technologies that better meet enterprise requirements have emerged. These new Java server-based entries empower software developers with the tools they need to:

• Deliver zero-training spreadsheet reports to their user communities

• Leverage the skills of power users to build report templates and engines that slash development and maintenance costs

• Add analytic power and color graphics to reporting applications

Embedding Spreadsheets in Java Applications

Today’s software market provides multiple methods that Java developers can employ to deliver content to end-users in readily-used Excel spreadsheet format.

Cross-Platform Native Interface

Developers can create a cross-platform solution 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. This requires Excel to be present and provides a limited Application

Programming Interface (API) to customize functionality. Running a full copy of Excel on the server also opens up security and scalability issues.

Static, View-Only File Export

Some Java spreadsheet products are designed to deliver Excel output and in some cases create limited Excel-based reports. In general, these products are suitable for delivering simple CSV files or static “dead” data to Excel users for viewing only. These solutions provide very limited and/or archaic programmatic access to the spreadsheets they create and

typically require a user to perform extensive manual tasks on the data—such as inserting formulas and charts, resizing rows and columns—before the data can be manipulated.

Java Spreadsheet Component

A third option is to use an embeddable spreadsheet Java component that delivers Excel-compatible reporting and charting functionality, such as the e.Spreadsheet Engine from Actuate Corporation.

(5)

Introducing the e.Spreadsheet Engine

The e.Spreadsheet Engine enables developers to embed full-function Excel spreadsheets in their Java applications. The e.Spreadsheet Engine is based on more than ten years of component spreadsheet engine development and has been certified as 100% Pure Java, enabling the use of Excel files on non-Windows platforms. The advanced architecture of the e.Spreadsheet Engine provides a comprehensive API easily accessed by Java code, so it can be embedded and deployed within servlets, JSPs, applets and applications and does not require Excel to be present on the desktop or deployment server.

Using the e.Spreadsheet Engine, developers can:

• Bring consistency and timeliness to all spreadsheets by automatically populating them with real-time data from multiple sources

• Save immense amounts of time and money by freeing users from manually inputting data and formatting results

• Create and maintain calculation-intensive reporting applications much more easily • Solve compliance issues caused by manually created spreadsheets

Actuate’s Java-based spreadsheets are especially effective when embedded as components in servlets and Java Server Pages on J2EE application servers, where they can integrate information from raw data sources, perform complex calculations and create publication-ready reports.

A New Approach to Developing and Deploying Reports

The e.Spreadsheet Engine enables organizations to use a new approach to delivering information to employees, partners and customers.

Deliver Spreadsheet Content to the Web

The e.Spreadsheet Engine is an effective choice for delivering reports via the web. As a set of Java classes, it can be embedded in a Java-based server process to aggregate data and deliver very rich reports as native Excel, a rich spreadsheet applet, HTML or charting with drill-down capability.

Leverage Existing Client Software

In most cases, Excel and a browser are all that is needed for a user to receive, read and manipulate reports created by the e.Spreadsheet Engine. This includes almost all business users since Microsoft claims there are more than 150 million licensed copies of Excel worldwide and several hundreds of millions of users with web browsers.

Utilize Existing Investment in Java Technology

The e.Spreadsheet Engine enables organizations to build custom Java/J2EE applications that deliver rich Excel content from Java IDEs such as Eclipse, Rational Application Developer and JBuilder. They can deploy their spreadsheet reporting applications on Java application servers including IBM WebSphere, BEA WebLogic, JBoss and Apache Tomcat.

Leverage Users’ Existing Knowledge and Skill Sets

The e.Spreadsheet Engine generates reports in familiar, richly-formatted Excel spreadsheet files, HTML tables or web-ready charts that every user recognizes and understands.

Therefore, e.Spreadsheet Engine applications are immediately usable with no end-user training.

(6)

The e.Spreadsheet Engine’s Role in a Reporting System

The primary strengths of the e.Spreadsheet Engine include its abilities to access raw data on servers, perform scalable calculations and analysis, and dynamically generate Excel-, HTML-, or chart-based reports over the web. Perhaps the most unique aspect of this process is that the e.Spreadsheet Engine does not rely on Windows or COM connections to the Excel application itself to perform this functionality.

A general example how the e.Spreadsheet Engine is used to generate reports appears below.

(7)

e.Spreadsheet Engine Architecture

The e.Spreadsheet Engine provides developers a full-featured, Excel-compatible spreadsheet engine for creating server-based reporting applications in web computing environments. Provided as a Java spreadsheet component, the e.Spreadsheet Engine enables organizations to develop robust reporting solutions with its comprehensive API, lightweight footprint, methods for accessing data sources, exceptional performance and its ability to be embedded into servlets and JSP on J2EE application servers.

Developers can add the e.Spreadsheet Engine to existing projects and utilize existing Java-based environments. The e.Spreadsheet Engine is compatible with virtually all Java

development environments including Eclipse, JBuilder and Rational Application Developer. Its applications can be deployed on J2EE-compliant application servers such as IBM’s WebSphere, BEA’s WebLogic, JBoss, Apache Tomcat and others. It is able to communicate with databases, XML files or streams, tab-delimited files, as well as any other you can access from Java. The e.Spreadsheet Engine’s API enables it to deliver Excel-compatible,

spreadsheet-driven analysis and reporting as an embedded component—as servlets, applets, EJB or JSP—of any web-deployed application.

Advantages of e.Spreadsheet Engine Reporting

Using the e.Spreadsheet Engine to produce Enterprise Reporting Applications provides extensive advantages, which include developer productivity, user productivity, improved analysis, faster reaction time opportunities, faster application delivery times and easier maintenance.

Deliver Fully Formatted Spreadsheets Ready for Reporting and Analysis

The e.Spreadsheet Engine delivers dynamically generated, richly-formatted Excel spreadsheet reports that can include formulas, charts, formatting, outlining, pivot tables, conditional formatting, multiple-linked worksheets and other interactive features. This robust feature set is in a different class than technologies that produce formatted, but “dead” data that contains no formulas, color, totals, charts or analysis features.

Simplify Data Manipulation for Spreadsheet Users

Most spreadsheet users spend significant time transforming detailed data into the summary information they need. Using the e.Spreadsheet Engine, developers can use servers to manipulate and format data into the precise form users need before delivering it to them.

Require No Special User Training

The e.Spreadsheet Engine is able to dynamically generate richly-formatted reports in Excel spreadsheets, HTML tables and web-ready images—formats that nearly every user

immediately understands and can use with software already installed on their desktops.

Add Analytic Power to Applications

By using the e.Spreadsheet Engine in Java-based reporting applications, developers can leverage their inherent analytical capabilities, especially in applications requiring specialized calculations. Engineering, statistical and financial functions combined with the ability to construct complex formulas save substantial development time and make applications much easier to maintain.

(8)

Include Color Charts in Spreadsheet Reports

In addition to data manipulation powers, the e.Spreadsheet Engine gives developers a fast, dependable way to create color charts and graphs that uncover meaning behind the numbers and add visual interest to reports. Charts are particularly important when producing reporting applications for executives and managers who have little time to analyze reports and prefer to receive easy-to-digest summaries accompanied by detailed numeric tables they can review later.

Design Reports with Speed and Flexibility

Using Actuate e.Spreadsheet Designer, developers can quickly and easily design spreadsheet reports that can be deployed within your Java architecture using the

e.Spreadsheet Engine Java API. Alternatively, they can modify an existing Excel template at runtime. Experienced users with proper permissions can easily maintain and update reporting templates in their application or by submitting new formulas and functions in Excel syntax. No new Java code is required and developers are not always needed to make these changes.

Leverage the Skills of Spreadsheet Users

With the e.Spreadsheet Engine, software development organizations can leverage the skills of power spreadsheet users to build report templates and engines that significantly slash the development costs, maintenance costs and delivery times of Enterprise Reporting

Applications. This is especially true in financial institutions, where most users—without higher-level programming skills such as using SQL or stored procedures—use spreadsheets to construct business rules.

A Fortune 500 distributor of electronic components and computer products makes extensive use of spreadsheets in pricing and licensing applications for its substantial sales force. Before the online applications existed, salespeople wasted several hours importing delimited files and manually entering data into Excel before massaging each spreadsheet-based quote into a presentable format. Today, using a web-based spreadsheet, those salespeople enter a few parameters about a customers’ architecture and receive presentation-ready

spreadsheets that they use to recommend and price products for their customers. The value in cost savings, consistency, clarity, and responsiveness is real and remarkable.

Support Native Excel File Format

In stark contrast to other server-based spreadsheet technologies that typically require data to be translated to their own proprietary formats, the e.Spreadsheet Engine can read and write native Excel file format. As a result, the e.Spreadsheet Engine enables organizations to take full advantage of the millions of spreadsheet templates and invaluable data stored in Excel spreadsheet files throughout the world.

Save Data Changes Back to the Server

The e.Spreadsheet Engine can automate Excel data entry capabilities by:

• Providing an Excel report to the e.Spreadsheet applet, leveraging Java to capture changes

• Using Excel VBA to submit data back to the server

• Providing an HTML representation of the spreadsheet with editable HTML elements

Integrate Into Existing Java Architectures Easily

The e.Spreadsheet Engine is delivered as a .jar file that can easily be embedded as part of a server or client based Java application for reading, writing and calculating Excel documents or .vts documents created in the e.Spreadsheet Designer.

(9)

Ease maintenance requirements

All maintenance of e.Spreadsheet Engine-based reporting systems is located on the server. Because of its use of Excel and browsers as a client, the e.Spreadsheet Engine does not require client-side software to be continuously updated.

Next Steps

The e.Spreadsheet Engine can play a pivotal role in making your Java-based reporting applications more powerful, faster to develop, simpler to use and easier to maintain.

Actuate Corporation welcomes the opportunity for your development team to experience the power and practicality of Excel-based spreadsheet reporting. Please contact us today to set up a free 30-day trial of the e.Spreadsheet Engine and the e.Spreadsheet Designer.

Here are a few links to help you in your evaluation process: 30-day trial download

http://www.actuate.com/products/techzone/spreadsheettech/es_overview/downloads.asp

Helpful documentation

http://www.actuate.com/products/techzone/spreadsheettech/es_learnmore/documentation.asp

Demos with downloadable source code

http://www.actuate.com/products/techzone/spreadsheettech/es_demos/demos.asp

e.Spreadsheet Designer Animated demo

http://www.actuate.com/products/techzone/spreadsheettech/es_demos/featuretours.asp

If you have any further questions, please contact our Sales Department at (800) 884-8665 or