Enterprise

How many times have you coded a financial, engineering, or pricing calculator and used an Excel spreadsheet as a reference? What if you could take that Excel spreadsheet and make that the business logic for a Java application? The e.SpreadSheet API from ReportingEngines makes that dream a reality.

Formula One e.SpreadSheet Engine is a complete API for reading and saving spreadsheets. It can load a complete spreadsheet into memory, and do data changes, calculations, even charting, using nothing more than the formulas coded into the spreadsheet!

Even more impressive is its ability to create spreadsheets from scratch. With this functionality, reports can be delivered the same way they were received - in a spreadsheet.

In this article, I'll take you through a simple example of using a spreadsheet for common business logic. I'll use the JSP Tag Library APIs to make the code easy to follow along. But don't be fooled. The full API lets you do these calculations in EJBs, Swing Applications, or any other code that needs spreadsheet functionality.

Requirements

To use the code in this article, you'll need to download and install the e.SpreadSheet engine from www.reportingengines.com, and you'll need an application server capable of running JSP pages. If you don't have one readily available, you can download Apache Tomcat from http://jakarta.apache.org.

You'll also need a copy of Excel or OpenOffice to create the documents. Alternatively, you can download ReportingEngines' companion product e.SpreadSheet Designer. Designer is a GUI-based on the e.SpreadSheet engine. Its features are like Excel's and it lets you graphically design a spreadsheet from a database, XML source, or text file.

After you install the necessary software, you'll need to extract the f1taginstall.war file and deploy it in Tomcat. This file contains everything a Web app needs to use the e.SpreadSheet APIs, so we'll use it as a shortcut for learning. You can find the file in the install directory under "eSS11/jars."

Creating the Spreadsheet

In this example, we're going to leverage a spreadsheet that does a useful - but not widely known - calculation called a "weighted average." The idea behind a weighted average is that a second number (such as the quantity of items) is used to give more or less importance to the numbers being averaged.

This function is commonly used to understand the true average price per product sold. For example, a grocery store may sell thousands of packs of bubblegum a week, but less than a hundred boxes of crackers. Using a normal average, the price of the crackers would raise the "average price per product," even though it sells nowhere near as well as the gum. With a weighted average, the number of items sold is properly figured into the average so that the store is aware of its real profit margins.

A weighted average can be easily calculated in Excel by using the formula, "SUMPRODUCT(RANGE1,RANGE2)/SUM(RANGE2)"; where RANGE2 is the quantity or "weighting" of the numbers.

Let's create a spreadsheet to test this.

Open Excel or one of the other spreadsheet programs mentioned above. Place the following titles in row 1 of columns 'A,' 'B,' and 'C':

"Item," "Price," and "Units."

We'll use the first column to identify the product, the second for the product's price, and the third for the number of units sold. Feel free to enter data in the three rows. When you're done, the rows should look something like those in Figure 1.

Now, let's create a cell to display a regular average and a weighted average. Click on cell "D2" and type "=AVERAGE(B2:B5000)" in the calculator bar. If you used the data in Figure 1, "D2" should now show an average of $14.12. Now click on cell "D3" and type "=SUMPRODUCT(B2:B5000,C2:C5000)/SUM(C2:C5000)." Again, if you used the data in Figure 1, you should have a weighted average of $8.28. That's quite a difference!

Save the spreadsheet in the web application folder with the name "average.xls", and we will look at loading it into a JSP page using the e.SpreadSheet API.

Displaying the Data

With our spreadsheet safely saved as part of our Web app, we can create a JSP file to display its contents. Create a new JSP file in the Web app directory, and call it "average.jsp." Open it in your favorite text editor or IDE.

Put the following line of code at the top of your JSP file:

<%@ taglib uri="f1taglib" prefix="f1"%>

This line references the e.Spread-Sheet tag library, allowing our JSP file to access its broad array of functions.

Next, we'll load the Excel file from disk with the following tag:

<f1:LoadBook book="Average" source="average.xls"/>

This tag tells the e.SpreadSheet engine we want to load the "average.xls" file, and that we want to let other tags reference it by the name "Average." This tag will keep the spreadsheet in memory until the session expires or the "UnloadBook" tag is called. By default, the engine will ignore the "LoadBook" tag if a spreadsheet by the same name has already been loaded, so there's no need to worry about losing any modifications you've made. This behavior can be overridden with the "reload" attribute.

Now that the prep work is done, we can finally display the data. We can use the "InsertRange" tag to populate an HTML table with data from the spreadsheet automatically. All we need to explain to the tag is the range and formatting. Try adding the following tag to the JSP page:

<f1:InsertRange book="Average" range="A1:C6" table="true" spreadsheetformat="true"/>

That's all there is to it! If you save your page and access it in a Web browser, you should see a table with the data from the "average.xls" spreadsheet. Basically, we've told the spreadsheet engine to pull cells A1 through C6 from the spreadsheet named "Average" and display it in a table. The "spreadsheetformat" attribute was also used to retain the cell formatting from the original spreadsheet. But what about the averages?

As one might expect, the "InsertRange" tag has a cousin called "InsertCell". Instead of generating the complete table with formatting, "InsertCell" merely pulls the data for a single cell and plops it into the sheet. The following code will display the average and weighted average for the data:

Average Price:
<f1:InsertCell book="Average" cell="D2"/>
<br>
Weighted Average Price:
<f1:InsertCell book="Average" cell="D3"/>

Add Your Feedback

In order to post a comment you need to be registered and logged in.

Register | Log in

Please wait while we process your request...