Query the records in Excel

Use Class to query the table's records to 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.dbdataserce; import org.apache.poi.hssf.usermodel. *;

/ ** *

Title: *

Description: *

Copyright: Copyright (c) 2004 *

Company: * @ Author not attributable * @version 1.0 * /

public class ExcelOpr {private static ExcelOpr excelOpr = null; private static Hashtable excelTitle = null; // excel title list private static Hashtable excelCols = null; // column name retrieved private static Hashtable resfunc = null; // private memory transfer function 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;} / ** * initialize Hashtable * / public void initHashtable () {if (this.excelCols == null) {This.excelcols = new hashtable (30);} else {this.excelcols.cle ();} 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 ();}} / ** * Settings column label information * / public void SetExceltitle (String Item, String Content) {this.exceltitle.put (item, content);} / ** * Settings column information * / public void setExcelcols (String item, string content) {this.excelcols.put (item, content) }

/ ** * Set the connection function * / public void setresfunc (string item, string content) {this.resfunc.put (item, content);}

Public void setExcelpath (String Excelpath) {if (! ")) {ExcelPath =" / ";} this.excelpath = excelpath;}

Public String getExcelpath () {return this.excelpath;}

Public String getExcelName () {return this.excelname;

Public void setExcelName (String ExcelName) {if (ExcelName! = null&& excallelname.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 rower = 1; string tempstr2 = ""; int n = this.excelcols.size (); system.out.println (excelcols); while (RS.Next ()) {row = sheet.createrow (rower); f OR (short cellnum = 1; cellnum <= n; cellnum ) {cell = rowercell (cellnum); cell.setencoding ((Short) 1); cell.setcellType (1); tempstr2 = ("rg .excelcols.get (" cellnum " "). TOSTRING ()) == null || rs.getstring (" 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 "). TSTRING (); 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) {r r. 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) {r. = rs.get ();}}}}}}}}}}}}}}}}}}}}}} catCH ();};}; rclose (); DbQuery.release (); return 0;} finally {if (rs! = Null) {rclose ();} 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 exception:" 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 dbdataroup {private dbdataserce () {}

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 oc;}

Private static datasource ds = null; private static context IC; public static void main (string args []) throws exception {system.out.println (getDataSource ());}}