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 = "demo@domain.com";
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();
}
%>
No comments:
Post a Comment