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
 

"Java Developer's Guide to Using
 Spreadsheets on the Web"
Volume: 5 Issue: 4, Page 14

	

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.