用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());
}
}