新建 文本文档.txt

用java操作EXCEL文件的源代码 使用POI操作

--------------------------------------------------------------------------------

[下载文件]
需要将上面的两个jar文件放在类路径中
XlsCon.java
package hengsheng.its.common;

import hengsheng.its.db.common.DBConnectionManager;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
* @author liujuntao
*
* To change this generated comment edit the template variable "typecomment":
* Window>Preferences>Java>Templates.
* To enable and disable the creation of type comments go to
* Window>Preferences>Java>Code Generation.

文件名:XlsCon.java
创建时间:2003-8-3
实现功能:根据sql 语句生成execl文件，放于All_temp_file目录下，名字为temp.xls
*/
public class XlsCon {

//******************************文件操作用到**********************************************//
private String filePath;
private String fileName;
private FileOutputStream fos;
private HSSFWorkbook wb;
private HSSFSheet s;

//******************************数据库操作用到**********************************************// 
private Statement stmt;
private ResultSet rs;
private ResultSetMetaData rsmd;

public XlsCon(String filePath) {
	this.filePath = filePath;
	fileName = "temp.xls";
	try {
		fos = new FileOutputStream(filePath + fileName);
	    }
        catch (FileNotFoundException e) {
	    }

	wb = new HSSFWorkbook();
	s = wb.createSheet();
	stmt = null;
	rs = null;
	rsmd = null;
}

/**
* * 参数：数据库连接，和SQL语句
* isNeed,是否需要将SQL语句的字段名，作为TITLE出现
* true,为需要，false为不需要
*/

public void getXls(Connection conn, String sql, boolean isNeed) {
	try {
		stmt =
			conn.createStatement(
					ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);

		rs = stmt.executeQuery(sql);
		rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		HSSFRow row = s.createRow((short) 0);
		int i = 0;
		if (isNeed) {
			for (; i < columnCount; i++) {
				HSSFCell cell = row.createCell((short) i);
				String name = (String) rsmd.getColumnName(i + 1);
				cell.setEncoding((short) 1);
				cell.setCellValue(name);
			}
		i = 1;
		}
		while (rs.next()) {
			row = s.createRow((short) i++);
			for (int j = 0; j < columnCount; j++) {
				HSSFCell cell = row.createCell((short) j);	
				String name = (String) rsmd.getColumnName(j + 1);
				String value = rs.getString(name);
				cell.setEncoding((short) 1);
				cell.setCellValue(rs.getString(name));
			}
		}
		wb.write(fos);
		fos.close();
	 } catch (Exception e) {
			System.out.println(e);
	       } finally 
		{
		}
}
///**
public static void main(String[] args) {
		XlsCon xsl = new XlsCon("d:");
		xsl.getXls(
			new DBConnectionManager().getConnection("BlackList"),
			"use BlackList;select NoticeSeq 通知书序号,CarNum 违章车号,mc0000 违章车				型,PassTime 违章时间,PassAdrs 违章地点,WzFacts 违章事实 from BlackList 			a,bm_carstyle b where a.CarType=b.bh0000 ",
		true);
}
//*/

/**
* Returns the fileName.
* @return String
*/
public String getFileName() {
		return fileName;
}

}

cell.setEncoding((short) 1);实现中文输入，否则会是乱码的