将表的记录查询出来另存为excel

用class将表的记录查询出来另存为excel:
package com.zjsw.admin.pub;

import javax.naming.*;
import java.sql.*;
import java.util.*;
import java.io.*;
import javax.servlet.*;
import com.zjsw.db.*;
import com.zjsw.pub.*;
import com.zjsw.db.dbDataSource;
import org.apache.poi.hssf.usermodel.*;

/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: </p>
 * @author not attributable
 * @version 1.0
 */

public class ExcelOpr {
  private static ExcelOpr excelOpr = null;
  private static Hashtable excelTitle = null; //excel列表的标题
  private static Hashtable excelCols = null; //取出的列名
  private static Hashtable resfunc = null; //存转换函数
  private static String excelPath = null;
  private static String excelName = null;
  private static GetDateFileName filename = null;
  private static FetchConstant fetchConstant = null;
  private ExcelOpr() {
    excelTitle = new Hashtable(30);
    excelCols = new Hashtable(30);
    resfunc = new Hashtable(30);
    filename = new GetDateFileName();
    fetchConstant = new FetchConstant();
    this.excelName = filename.getdate() + ".xls";
  }

  public static ExcelOpr getExcelOpr() {
    if (excelOpr == null) {
      excelOpr = new ExcelOpr();
    }
    return excelOpr;
  }
  /**
   * 初始化Hashtable
   */
  public void initHashtable(){
    if (this.excelCols == null) {
      this.excelCols = new Hashtable(30);
    }
    else {
      this.excelCols.clear();
    }
    if (this.excelTitle == null) {
      this.excelTitle = new Hashtable(30);
    }
    else {
      this.excelTitle.clear();
    }
    if (this.resfunc == null) {
      this.resfunc = new Hashtable(30);
    }
    else {
      this.resfunc.clear();
    }
  }
  /**
   * 设置列标签信息
   */
  public void setExcelTitle(String item, String Content) {
    this.excelTitle.put(item, Content);
  }

  /**
   * 设置列信息
   */
  public void setExcelCols(String item, String Content) {
    this.excelCols.put(item, Content);
  }

  /**
   * 设置连接函数
   */
  public void setResfunc(String item, String Content) {
    this.resfunc.put(item, Content);
  }

  public void setExcelPath(String excelPath) {
    if (!excelPath.endsWith("/")) {
      excelPath += "/";
    }
    this.excelPath = excelPath;
  }

  public String getExcelPath() {
    return this.excelPath;
  }

  public String getExcelName() {
    return this.excelName;
  }

  public void setExcelName(String excelName) {
    if (excelName != null && excelName.equals("") == false) {
      this.excelName = excelName;
    }
  }

  public int createExcel(String sqlStr) throws Exception {
    DBQuery dBQuery = new DBQuery();
    ResultSet rs = null;
    try {
      dBQuery.setData(sqlStr);
      rs = dBQuery.execute();
      File excelfile = new File(this.excelPath + this.excelName);
      FileOutputStream fileoutputstream = new FileOutputStream(excelfile);
      HSSFWorkbook xls = new HSSFWorkbook();
      HSSFSheet sheet = xls.createSheet();
      xls.setSheetName(0, "list");
      HSSFRow row = sheet.createRow( (short) 0);
      HSSFCell cell;
      int m = this.excelTitle.size();
      System.out.println(excelTitle);
      for (int i = 1; i <= m; i++) {
        cell = row.createCell( (short) i);
        cell.setEncoding( (short) 1);
        cell.setCellType(1);
        cell.setCellValue(this.excelTitle.get("" + i + "").toString());
      }
      short rownum = 1;
      String tempstr2 = "";
      int n = this.excelCols.size();
      System.out.println(excelCols);
      while (rs.next()) {
        row = sheet.createRow(rownum);
        for (short cellnum = 1; cellnum <= n; cellnum++) {
          cell = row.createCell(cellnum);
          cell.setEncoding( (short) 1);
          cell.setCellType(1);
          tempstr2 = ( (rs.getString(this.excelCols.get("" + cellnum + "").
                                     toString()) == null ||
                        rs.getString(this.excelCols.get("" + cellnum + "").
                                     toString()).equals("null")) ? "" :
                      new
                      String(rs.getString(this.excelCols.get("" + cellnum + "").
                                          toString())));
          if (this.resfunc.containsKey("" + cellnum + "$FetchConstant")) {
            String tempStr = this.resfunc.get("" + cellnum + "$FetchConstant").
                toString();
            String[] temp = new String[3];
            temp = tempStr.split("//*");

            tempstr2 = fetchConstant.getSelectone(temp[0].toString(),
                                                  temp[1].toString(),
                                                  temp[2].toString(), tempstr2);
          }
          cell.setCellValue(tempstr2);
        }
        rownum++;
      }
      xls.write(fileoutputstream);
      fileoutputstream.close();
      rs.close();
      dBQuery.release();
      return rownum;
    }
    catch (Exception ex) {
      ex.printStackTrace();
      rs.close();
      dBQuery.release();
      return 0;
    }
    finally {
      if (rs != null) {
        rs.close();
      }
      if (dBQuery != null) {
        dBQuery.release();
      }
    }
  }

  public int queryOpr(String sqlStr) throws Exception {
    DBQuery dBQuery = new DBQuery();
    ResultSet rs = null;
    int total = 0;
    try {
      dBQuery.setData(sqlStr);
      rs = dBQuery.execute();
      if (rs != null) {
        rs.next();
        total = rs.getInt(1);
      }
      rs.close();
      dBQuery.release();
      return total;
    }
    catch (Exception ex) {
      ex.printStackTrace();
      rs.close();
      dBQuery.release();
      return 0;
    }
    finally {
      if (rs != null) {
        rs.close();
      }
      if (dBQuery != null) {
        dBQuery.release();
      }
    }
  }
}



package com.zjsw.db;

import java.sql.*;
import javax.naming.*;

public class DBQuery {
  public DBQuery() {
  }

  public synchronized void setData(String statement) throws SQLException, NamingException {
    //con=DbTest.getConnect();
  con = dbDataSource.getDataSource().getConnection();
    pstmt = con.prepareStatement(statement);
  }

  public ResultSet execute() throws SQLException {
    return pstmt.executeQuery();
  }

  public void release() {
    try {
      if (pstmt != null) {
        pstmt.close();
      }
      if (con != null) {
        con.close();
      }
    }
    catch (SQLException ex) {
      System.out.println("出现SQL例外：" + ex.getMessage());
    }
  }

  protected PreparedStatement pstmt;
  private Connection con;
}



package com.zjsw.db;
import java.sql.*;
import javax.naming.*;
import javax.sql.*;

import com.zjsw.pub.*;

//import java.util.Properties;

public class dbDataSource {
  private dbDataSource() {
  }

  public static DataSource getDataSource() throws SQLException, NamingException {
    if (ds == null) {
      ds = (DataSource) getInitContext().lookup(JNDINames.RGWEBPOOL_DATASOURCE);
    }
    return ds;
  }

  public static Context getInitContext() throws NamingException {
    if (ic == null) {
      ic = new InitialContext();
    }
    return ic;
  }

  private static DataSource ds = null;
  private static Context ic;
  public static void main(String args[]) throws Exception {
    System.out.println(getDataSource());
  }
}