booksheet.java

使用jsp查询oracle数据库生成excel文件

/*
 * Created on 2004-4-16
 *
 * To change the template for this generated file go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
package dbexport;

import java.sql.Connection;  
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.io.File;
import java.io.IOException;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;


/**
 * ---------------------<br>
 * 文件名：BookSheet.java<br>
 * ---------------------<br>
 * (C) Copyright 2004, by Codeline Limited. <br>
 * 说明：产生excel表<br>
 * 原作者：肖俊<br>
 * 其他作者：<br>
 * 创建时间：2004-4-15<br>
 * 版本: 1.0<br>
 * 更改记录：<br>
 * -------------------------------------------------------------------------<br>
 *   改动人  时间          原因<br>
 * -------------------------------------------------------------------------<br>
 *   肖俊    2004-4-15     创建文件<br>
 */
public class BookSheet {
	
	   private String sqlString = null;
	
      //导出文件路径	  	  
	   private String filePath = null;
	   
      //导出文件名称
	   private String fileName = null; 
	   
	public BookSheet(){
		sqlString = "select * from emp";
		filePath = "c:\\downloads";
		fileName = "output.xls";
	}
      
	 
    	 
	 public void setSqlString(String sqlString ){
	 	this.sqlString = sqlString;
	 }
	
	
	 
	 public void setFilePath(String filePath){
	 	this.filePath = filePath;
	 }
	
	
	 
	 public void setFileName(String fileName){
	 	this.fileName = fileName;	  
	 }
	
	/**
		 * 导出数据库数据
		 * @return 是否成功，true 成功；false 失败
		 */
	public boolean executeExport(ConstDBBean db) throws SQLException,IOException {
		//数据库链接
		Connection dbConn = null;
        //数据库执行句柄
		Statement st = null; 	
		//数据库查询结果集 
		ResultSet rs = null;
		
		boolean rt = true;
		int ls = 0 ;
		int hs = 0 ;
		int i = 1;
		Label l ;
		WritableWorkbook workbook = null; 
		String columnName = null; 
	
		try{
			System.out.println("数据库初始化");
			System.out.println("开始注册类");
			Class.forName(db.getDbDriver());
			System.out.println("注册完成");
			dbConn = DriverManager.getConnection(db.getDbURL(),db.getDbUser(),db.getDbPassword());
			System.out.println("数据库连接已建立");
			st = dbConn.createStatement();
			System.out.println("语句执行");
			rs = st.executeQuery(sqlString);
			System.out.println("得到结果集");
			System.out.println(filePath+"\\"+fileName);
			workbook = Workbook.createWorkbook(new File(filePath+"\\"+fileName));
			System.out.println("excel表格创建完成");
			WritableSheet sheet = workbook.createSheet("First Sheet", 0);
			System.out.println("sheet建立，开始插入表格数据"); 
			ResultSetMetaData rsmd = rs.getMetaData();
			ls = rsmd.getColumnCount();
			WritableFont arial12font = new WritableFont(WritableFont.ARIAL, 12);
			arial12font.setColour(Colour.DARK_RED);
			WritableCellFormat arial12format = new WritableCellFormat (arial12font);
			arial12format.setBackground(Colour.BRIGHT_GREEN);
            for(int k = 1;k<=ls;k++){
            	columnName = rsmd.getColumnLabel(k);
            	l = new Label(k-1,0,columnName,arial12format);
            	sheet.addCell(l);
            }
			while(rs.next()){
				for (int j =1;j<=ls;j++){
				  l = new Label(j-1,i,rs.getString(j));
				  sheet.addCell(l);	
				}
				i++;
			}
		  	workbook.write();
		  	workbook.close();
		}catch(Exception e){
			rt = false;
			workbook.close();
		}finally{
			if(!dbConn.isClosed()){
				dbConn.close();
			}
		}
		return rt;
	}
	
	public static void main(String[] args) throws SQLException, IOException{
		boolean c = false;
		ConstDBBean x = new ConstDBBean();
		BookSheet a = new BookSheet();
		c = a.executeExport(x);
	}
	
}