HomeDigital EditionSys-Con RadioSearch Java Cd
Advanced Java AWT Book Reviews/Excerpts Client Server Corba Editorials Embedded Java Enterprise Java IDE's Industry Watch Integration Interviews Java Applet Java & Databases Java & Web Services Java Fundamentals Java Native Interface Java Servlets Java Beans J2ME Libraries .NET Object Orientation Observations/IMHO Product Reviews Scalability & Performance Security Server Side Source Code Straight Talking Swing Threads Using Java with others Wireless XML
 

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

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

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

Figure 3
Figure 3:

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.

Figure 4
Figure 4:

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.

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

Figure 6
Figure 6:

Figure 7
Figure 7:

(Note: This demo would also work by accessing the servlet with a name=value pair that includes the parameters &[email protected]&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.

Figure 8
Figure 8:

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.

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

Author Bio
Mark Spencer is the marketing manager of Tidestone Technologies, Inc. Prior to Tidestone he worked for three years for Visual Components, which later was acquired by Sybase. He has written numerous articles and white papers regarding component spreadsheet use in different programming languages. [email protected]

	

Listing 1:

import java.sql.*;
import com.f1j.util.*;
import com.f1j.ss.*;

public class dbManipulations {
  private java.sql.Connection m_sqlCon = null;
  private java.sql.Statement  m_stmt = null;

  public void retrieveAndPopulateDataFromDB(String strMonth, 
    com.f1j.ss.Book book, int iSheet) throws Exception {
           
    String strQuery  = "Select orderid, name, volume, price   
    from orders, products ";
    String strQryCount = "Select count(*) from orders, products ";
    // Following are parameters specific to Oracle8I and the  
    // machine it is used on
    // Change to reflect your configurations
    String strDriver = "oracle.jdbc.driver.OracleDriver";
    String strUrl    =   
    "jdbc:oracle:thin:@webtogo.domain.com:1521:domain";
    String strUser   = "scott";
    String strPassword = "tiger";
    int iSrcStartRow   = 21;
    int iRowCount      = 0;
          
    //connect to the database
    createConnection(strDriver, strUrl, strUser, strPassword);
           
    if (strMonth == null)   // default month name
      strMonth = "JANUARY";
    else
      strMonth=strMonth.toUpperCase();
          
    // Build and execute query string(s)
    String strBufWhere = "where upper(orders.month) = '"+strMonth+
                            "' and products.productid =  
                               orders.productid";

    java.sql.ResultSet rs = queryRecords(strQryCount + str-
    BufWhere);
    if (rs != null && rs.next()) {
        iRowCount = rs.getInt(1);
        rs.close();
    }
    rs = queryRecords(strQuery + strBufWhere);

    try {            // Populate from ResultSet rs to  
                     // Spreadsheet
      if (book != null) {
        Sheet sheet = book.getSheet(iSheet);
        com.f1j.jdbc.JDBC m_gridJDBC = new  
        com.f1j.jdbc.JDBC(sheet);
        com.f1j.jdbc.JDBCQueryObj m_jdbcQryObj = new 
        com.f1j.jdbc.JDBCQueryObj();
                  
        setFlagsJDBCQueryObject(iSrcStartRow, 0, m_jdbcQryObj);
        m_gridJDBC.populateGrid(rs, m_jdbcQryObj);
                  
        // Add total calculations to the bottom of the data   
        // and format
        int iTotalRow = iRowCount + iSrcStartRow;
                  
        sheet.setText(iTotalRow, 1, "TOTAL:");
        sheet.setFormula(iTotalRow, 2, "SUM(C"+iSrc-
        StartRow+":C"+iTotalRow+")");
        sheet.setFormula(iTotalRow, 4, "SUM(E"+iSrc-
        StartRow+":E"+iTotalRow+")");

sheet.copyRange(iTotalRow, 0, iTotalRow, 4, 
                        sheet, iSrcStartRow-2, 0, iSrc-
                        StartRow-2, 4, 
                        com.f1j.ss.Constants.eCopyFormats);        
        // format totals row
                                  
        // Add Revenue formula column to all retrieved rows
        int iSrcCol = 4;             //revenue column
        sheet.copyRange(iSrcStartRow+1, iSrcCol, iTotalRow-1, 
        iSrcCol, 
                        sheet, iSrcStartRow, iSrcCol, iSrc- 
                        StartRow, iSrcCol, 
                        com.f1j.ss.Constants.eCopyAll);
                  
        // Change Spreadsheet "Title" to correspond to  
        // requested month
        String strTitle = sheet.getText(17, 0) + strMonth;
        sheet.setText(17, 0, strTitle); 
                  
        // Change Chart range to correspond to the # of 
        // records returned
        // The Chart takes its data from the defined names   
        // "chartData", "chartLegend"
        // So we will redefine them to reflect the amount of 
        // data retrieved
        GRChart chart = (GRChart)book.getSheet(iSheet).get 
        GRObject(3); 
        chart.setTitle(strTitle);

        String sheetName = book.getSheet(iSheet).getName();                
        book.setDefinedName("chartData", 
                            sheetName+"!$E$"+(iSrc-
                            StartRow+1)+":$E$"+iTotalRow,
                            0, 0);
        book.setDefinedName("chartLegend", 
                            sheetName+"!$B$"+(iSrc-
                            StartRow+1)+":$B$"+iTotalRow,
                            0,0);
      }
    }
    finally {
      //close the database connections 
      if (rs != null) rs.close();
      closeAll();
    }
  }

  private void setFlagsJDBCQueryObject (int iStartRow, int   
                                    iStartCol,  
                                    com.f1j.jdbc.JDBC-
                                    QueryObj jdbcQryObj) {
    jdbcQryObj.setAutoColNames(false);   // don't return   
                                 // field name as col hdrs
    jdbcQryObj.setAutoColFormats(false); // format data  
                                      // according to type
    jdbcQryObj.setAutoColWidths(true);   // autosize columns
    jdbcQryObj.setAutoMaxRC(false);      // don't change  
                                 // max/min on spreadsheet
    jdbcQryObj.setStartRow(iStartRow);   // start row for  
                                      // populating
    jdbcQryObj.setStartCol(iStartCol);   // start col for   
                                        // populating
    jdbcQryObj.setColNamesInRow(iStartRow); // put fields   
                                         // names in row
  }
      
  private void createConnection (String strDriverName,
    String strDatasource, String strUsername, String strPass-
    word) throws Exception {
              
    Driver d=(Driver)Class.forName(strDriverName).newInstance();
    DriverManager.registerDriver(d);
    m_sqlCon=DriverManager.getConnection(strDatasource, 
    strUsername, strPassword);
    m_stmt=m_sqlCon.createStatement();
  }
      
  // Queries the database using the sqlStatment passed to it.   
  // It returns the resultset.
  
  private ResultSet queryRecords(String strSqlStmt) throws  
  Exception {
    if (strSqlStmt != null) 
      return m_stmt.executeQuery(strSqlStmt);
    else 
      return (ResultSet)null;
  }

  private void closeAll() throws Exception {
    if (m_stmt != null) 
      m_stmt.close();
    if (m_sqlCon != null) 
      m_sqlCon.close();
  }
}


Listing 2: ExcelServlet.java

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import com.f1j.swing.*;

public class Excel97Servlet extends HttpServlet {

  public void doGet(HttpServletRequest request, HttpServlet-
  Response response) 
    throws ServletException, java.io.IOException  {
       
    ServletOutputStream out = response.getOutputStream();
    response.setContentType("application/vnd.ms-excel");
    
    // create a new Formula One workbook and lock it down.
    com.f1j.swing.JBook jbook = new com.f1j.swing.JBook();
    jbook.getLock();
    
    try {
      // read in the excel file we are using as a template  
      // for this report
      jbook.read(getInitParameter("reportTemplate"));
            
      // Populate data from database into spreadsheet
      dbManipulations db = new dbManipulations();
      db.retrieveAndPopulateDataFromDB(request.getParame-
      ter("month"), 
                                       jbook.getBook(),   0);
      // since we change the contents of the book we force a 
      // recalc before writing the model.
      jbook.recalc();
      WriteExcel(out, jbook);
      out.close();
    }
    catch(Throwable e) {
      System.out.println(e.getMessage());
    }
    finally {
      jbook.releaseLock();
    }
  } 
    
  // Formatting Excel data requires access to a "seekable" stream.  
  // Since OutputStream is not  seekable, we create a temporary 
  // file in excel format, then copy the data to the output stream.

  private void WriteExcel(OutputStream out,   
  com.f1j.swing.JBook jbook) 
                         throws Exception {
    java.util.Date tempFileName = new java.util.Date();
    String tempFilePath = System.getProperty("user.dir") + 
                          java.io.File.pathSeparator + 
                          tempFileName.getTime();

    // write the book to a temporary file
    jbook.write(tempFilePath, jbook.eFileExcel97);

    File tempFile = new File(tempFilePath);
    FileInputStream tempfis = new FileInputStream(tempFile);

    byte buffer[] = new byte[1024];
    long totalBytesRead = 0;
    int  bytesRead = 0;

    while (totalBytesRead < tempFile.length()) {
        bytesRead = tempfis.read(buffer);
        totalBytesRead = totalBytesRead + bytesRead;
        out.write(buffer, 0, bytesRead);
    }
    tempfis.close();
    tempFile.delete();
  } 
} 


Listing 3: WebMail.java

// You will need the activation.jar and mail.jar standard 
// java extensions to compile this code.

import javax.mail.*;
import javax.mail.internet.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.ServletConfig;
import javax.servlet.ServletContext;

public class WebMail extends HttpServlet {
  private Session m_session;
  private String m_strFile;
  private String m_strTempFileName = "report.xls";
        
  public void init(ServletConfig config) throws ServletException {
    super.init(config);
    ServletContext ctxt = getServletContext();
    m_strFile = config.getInitParameter("report_template");
  }
        
  public void doGet(HttpServletRequest req, HttpServletRe-
  sponse res) 
    throws ServletException, java.io.IOException {
              
    res.setContentType("text/html");
    java.io.PrintWriter writer = res.getWriter(); 
              
    //start the session
 // change the postoffice domain reference
 // throughout this code to match your system   
    java.util.Properties properties = System.getProperties();
    properties.put("mail.smtp.host",   
    "postoffice.domain.com");

    //Connect to the store
    try {
      m_session = Session.getInstance(properties, null);
      Store store=m_session.getStore("imap");
      store.connect("postoffice.domain.com", "demo", "demo");
      sendMessage(req, res, writer); 
    }
    catch (Exception e) {
      writer.println("Unable to connect to email account  
      specified");
    }
  }
        
  private void sendMessage(HttpServletRequest req, 
                           HttpServletResponse res, 
                           java.io.PrintWriter writer) 
    throws ServletException, java.io.IOException {
                  
    String strFrom = "[email protected]";
    String strTo = req.getParameter("to");
    String strMonth = req.getParameter("month").toUpperCase();
    String strSubject = "Sales Figures for the Month of " + strMonth;
    com.f1j.ss.Book book = new com.f1j.ss.Book();
              
    String strTempDir = null; 
    try { 
      strTempDir = createTemporaryDir();
      String strTempFile = strTempDir+java.io.File.separa-
      tor+m_strTempFileName;
                    
      if (strTempFile != null) {
        // Load worksheet template, retrieve data from data-
        // base and write to a temporary file.
        book.getLock();
        book.read(new java.io.FileInputStream(m_strFile));
        dbManipulations m_db = new dbManipulations();
        m_db. retrieveAndPopulateDataFromDB(strMonth, book, 0);
        book.write(book.getSheet(0), strTempFile, book.eFile-
        Excel97); 
        book.releaseLock();
                          
        // build 2-part mail message and send it.
        MimeMessage message = new MimeMessage(m_session);
        Multipart mp        = new MimeMultipart();
        MimeBodyPart mbp1   = new MimeBodyPart();
        MimeBodyPart mbp2   = new MimeBodyPart();
                          
        message.setFrom(new InternetAddress(strFrom));
        message.setRecipients(Message.RecipientType.TO, 
                                
        InternetAddress.parse(strTo));
        message.setSubject(strSubject);
        message.setContent(mp);
                          
        mbp1.setText("Report successfully sent");

        // create the file attachment part of the message
        mbp2.setDataHandler(new javax.activation.DataHandler(
             new javax.activation.FileDataSource(strTemp File)));
        mbp2.setFileName(m_strTempFileName);
                           
        mp.addBodyPart(mbp1);
        mp.addBodyPart(mbp2);
                              
        //send the message
        Transport.send(message);
        writer.println("<p> Sales report was sent to: " +   
        strTo + " </p>");
      }
      deleteTemporaryDir(strTempDir);
    }
    catch (Exception e) {
    writer.println("<p> " + e.getMessage() + " </p>");
    }
  }
        
  private String createTemporaryDir() {
    String strNewDir = System.getProperty("user.dir") + 
    java.io.File.separator + (new java.util.Date()).getTime();
    java.io.File dir = new java.io.File(strNewDir);
    dir.mkdir();
    return strNewDir;
  }
        
  private synchronized void deleteTemporaryDir(String strTempDir) {
    java.io.File dir = new java.io.File(strTempDir);
    if (dir.exists()) { 
      java.io.File file = new java.io.File(strTempDir + 
      java.io.File.separator + m_strTempFileName);
      file.delete(); 
      dir.delete();
    }
  }
}


Listing 4: report.jsp

<%@ page import="dbManipulations" %>
<% 
  // create a new formula one workbook
  com.f1j.swing.JBook jbook = new com.f1j.swing.JBook();

  jbook.getLock();
  try {
    java.io.File me=new java.io.File(request.getPathTranslated());
    jbook.read(me.getParent()+java.io.File.separator+"report_template.xls");
            
    dbManipulations db = new dbManipulations();
    db.retrieveAndPopulateDataFromDB(request.getParameter("month"), 
                                     jbook.getBook(), 0);
            
    jbook.recalc();
    com.f1j.ss.HTMLWriter htmlWriter = new com.f1j.ss.HTMLWriter();
    htmlWriter.write(jbook.getBook(), 0, 17, 0, 0, 31, 4, out);
  } catch(Throwable e) { System.out.println("Error:   
  "+e.getMessage() ); }
  finally {
    jbook.releaseLock();
  }
%>





 

All Rights Reserved
Copyright ©  2004 SYS-CON Media, Inc.
  E-mail: [email protected]

Java and Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. SYS-CON Publications, Inc. is independent of Sun Microsystems, Inc.