Query the table records and save them as Excel files.

Use class to query the table records and save them as 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 = NULL;
Private Static hashtable exceltitle = NULL; // Title of the Excel list
Private Static hashtable excelcols = NULL; // name of the retrieved Column
Private Static hashtable resfunc = NULL; // memory Conversion Function
Private Static string excelpath = NULL;
Private Static string excelname = NULL;
Private Static getdatefilename filename = NULL;
Private Static 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. 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 ();
}
}
/**
* Set column label information
*/
Public void setexceltitle (string item, string content ){
This. exceltitle. Put (item, content );
}

/**
* Set 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. 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 = new dbquery ();
Resultset rs = NULL;
Try {
Dbquery. setdata (sqlstr );
Rs = dbquery.exe cute ();
File excelfile = new file (this. excelpath + this. excelname );
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 = new dbquery ();
Resultset rs = NULL;
Int Total = 0;
Try {
Dbquery. setdata (sqlstr );
Rs = dbquery.exe cute ();
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.exe cutequery ();
}

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