java将表数据导入excel

 

package com.insertExcel;

import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.chem.is.util.DateUtil;

public class Test {
	public ResultSet  test(){
		Connection conn= null;
		Statement stmt=null;
		ResultSet rs =null;
//		List rsall = new ArrayList();
//		Map rsTree;
		try{
			String insertSql = "";
			String selectSql= "Select eng_name,ch_name,prefix_code,category_order from category_info";
			Class.forName("com.mysql.jdbc.Driver");
			String url="jdbc:mysql://192.168.1.106:3306/bide_system?user=root&useUnicode=true&characterEncoding=utf8";
			String user ="";
			String password="";
			conn= DriverManager.getConnection(url,user,password);
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			rs = stmt.executeQuery(selectSql);
//			ResultSetMetaData rsmd = rs.getMetaData();//取数据库的列名 我觉得名比1,2,3..更好用
//			int numberOfColumns = rsmd.getColumnCount(); //列数
//			System.out.println("numberOfColumns 列数= "+numberOfColumns);
//			while(rs.next()){
//				rsTree=new HashMap(numberOfColumns);
//				for(int r=1;r<numberOfColumns-1;r++ ){
//					rsTree.put(rsmd.getColumnName(r),rs.getObject(r));
//				}
//				rsall.add(rsTree);
//			}
//			System.out.println("rsall.size======"+rsall.size());
		}catch(Exception e){
			e.printStackTrace();
		}finally{
		}
		return rs;
	}
	
	public String createFileName() {
		//得到当前时间
		java.sql.Timestamp nowTime = DateUtil.getNowTime();
		System.out.println("nowTime = "+nowTime);
		String noeTimeString = nowTime.toString();
		String replaceOne = noeTimeString.replace("-", "");
		String replaceTwo = replaceOne.replace(":", "");
		String replaceThree=replaceTwo.replace(" ", "");
		String replaceFore= replaceThree.replace(".","");
		System.out.println("replaceFore ==="+replaceFore);
		return replaceFore;
	}
	
	public void exportClassroom() throws Exception { 
		try { 
			String fileName= "D:\\"+createFileName()+".xls";
			System.out.println("fileName ====="+fileName);
			FileOutputStream fos = new FileOutputStream(fileName);
			BufferedOutputStream bos  = new BufferedOutputStream(fos);
			WritableWorkbook wbook = Workbook.createWorkbook(bos); //建立excel文件 
			WritableSheet wsheet = wbook.createSheet("产品类别", 0); //工作表名称 
			//设置Excel字体 
			WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16, 
			WritableFont.BOLD, false, 
			jxl.format.UnderlineStyle.NO_UNDERLINE, 
			jxl.format.Colour.BLACK); 
			WritableCellFormat titleFormat = new WritableCellFormat(wfont); 
			String[] title = { "英文名称", "中文名称", "类别前缀", "显示顺序" }; 
			//设置Excel表头 
			for (int i = 0; i < title.length; i++) { 
			Label excelTitle = new Label(i, 0, title[i], titleFormat); 
			wsheet.addCell(excelTitle); 
			} 
			int c = 1; //用于循环时Excel的行号 
			//返回rs
			ResultSet rs = test();
			//返回list
//			//List list=test();
//			for (int i = 0; i < title.length; i++) {
//				
//			}
			while (rs.next()) { 
				Label content1 = new Label(0, c, rs.getString("eng_name")); 
				Label content2 = new Label(1, c, rs.getString("ch_name")); 
				Label content3 = new Label(2, c, rs.getString("prefix_code")); 
				Label content4 = new Label(3, c, rs.getString("category_order")); 
				//返回list			
//				CategoryInfo categoryInfo = (CategoryInfo)list.next();
//				Label content1 = new Label(0, c,categoryInfo.getEngName() );
				wsheet.addCell(content1); 
				wsheet.addCell(content2); 
				wsheet.addCell(content3); 
				wsheet.addCell(content4); 
				c++; 
				System.out.println("第"+c+"行");
			} 
			wbook.write(); //写入文件 
			wbook.close(); 
			bos.close(); 
			fos.close();
		} catch (Exception e) { 
			e.printStackTrace();
		throw new Exception("导出文件出错"); 
	} 
} 
	
	public static void main(String[] args) throws Exception {
		Test t = new Test();
//		t.createFileName();
		t.exportClassroom();
	}
}