将查询结果导入excel 中

 

1. 数据库：oracle 10g2

    JDK: 1.6.0_20

    jar 包：jxl-2.6.6.jar   log4j.jar   ojdbc14-10.2.0.4.0.jar

 

2. 导出的核心代码 ExcelOutput

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

import jxl.Workbook;
import jxl.write.WritableFont;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.log4j.Logger;

/**
 * @ClassName: ExcelOutput
 * @Description: Excel文档生成功能
 * @author 
 * @company 
 * @date 2012-6-29
 * @version V1.0
 */

public final class ExcelOutput {

	private static Logger log = Logger.getLogger(ExcelOutput.class);

	// 实例
	private static final ExcelOutput INSTANCE = new ExcelOutput();

	private ExcelOutput() {
	}

	/**
	 * @Title: getInstance
	 * @Description: 获取ExcelOutput实例,使用单例
	 * @return ExcelOutput
	 * @author 
	 * @date 2012-6-25
	 */
	public static ExcelOutput getInstance() {
		return INSTANCE;
	}

	/**
	 * 取得用于下载excel文件的输出流
	 * 
	 * @param os
	 *            输出流,如response.getOutputStream()或FileOutputStream
	 * @param subject
	 *            主题,用于表示工作表的名称
	 * @param titles
	 *            标题行,用于显示在表格的第一行
	 * @param list
	 *            实际内容,是List集合对象. 每一值又是一个List对象,表示一行的记录,注意每个值是String都对象
	 * @return 输出流
	 */

	public void createOutPutStreamForObjs(OutputStream os, String subject,
			String[] titles, List<Object[]> list) {

		log.info("start to create excel file.");

		WritableWorkbook wwb = null;
		try {
			// 通过模板得到一个可写的Workbook
			wwb = Workbook.createWorkbook(os);
			// 第几个工作表
			int number = 0;
			// 一个工作表插入3000条记录
			int num = 3000;

			while ((number + 1) * num <= list.size()) {
				// 生成名为subject + number的工作表，0表示这是第一页
				jxl.write.WritableSheet ws = wwb.createSheet(subject + number,
						number);

				// 表格头
				// 添加带有字型Formatting的对象
				jxl.write.WritableFont wfTitle = new jxl.write.WritableFont(
						WritableFont.TAHOMA, 12, WritableFont.BOLD, false);
				jxl.write.WritableCellFormat wcfFTitle = new jxl.write.WritableCellFormat(
						wfTitle);
				for (int i = 0; i < titles.length; i++) {
					jxl.write.Label labelCFTitle = new jxl.write.Label(i, 0,
							titles[i], wcfFTitle);
					ws.addCell(labelCFTitle);
				}

				// 表格内容
				jxl.write.WritableFont wf = new jxl.write.WritableFont(
						WritableFont.TAHOMA, 11, WritableFont.NO_BOLD, false);
				jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(
						wf);
				int numI = 0;
				for (int i = number * num; i < (number + 1) * num; i++) {

					Object[] subList = (Object[]) list.get(i);
					for (int j = 0; j < subList.length; j++) {
						String text = subList[j] == null ? "" : subList[j]
								.toString();
						jxl.write.Label labelCF = new jxl.write.Label(j,
								numI + 1, text, wcfF);
						ws.addCell(labelCF);
					}
					numI++;
				}
				number = number + 1;
			}

			if (list.size() > number * num) {
				jxl.write.WritableSheet ws = wwb.createSheet(subject + number,
						number);

				// 表格头
				// 添加带有字型Formatting的对象
				jxl.write.WritableFont wfTitle = new jxl.write.WritableFont(
						WritableFont.TAHOMA, 12, WritableFont.BOLD, false);
				jxl.write.WritableCellFormat wcfFTitle = new jxl.write.WritableCellFormat(
						wfTitle);
				for (int i = 0; i < titles.length; i++) {
					jxl.write.Label labelCFTitle = new jxl.write.Label(i, 0,
							titles[i], wcfFTitle);
					ws.addCell(labelCFTitle);
				}

				// 表格内容
				jxl.write.WritableFont wf = new jxl.write.WritableFont(
						WritableFont.TAHOMA, 11, WritableFont.NO_BOLD, false);
				jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(
						wf);
				int numI = 0;
				for (int i = number * num; i < list.size(); i++) {

					Object[] subList = (Object[]) list.get(i);
					for (int j = 0; j < subList.length; j++) {
						String text = subList[j] == null ? "" : subList[j]
								.toString();
						jxl.write.Label labelCF = new jxl.write.Label(j,
								numI + 1, text, wcfF);
						ws.addCell(labelCF);
					}
					numI++;
				}
			}
			// 写入Exel工作表
			wwb.write();
		} catch (RowsExceededException e) {
			log.error("create excel file '" + subject + "' fail!", e);
		} catch (FileNotFoundException e) {
			log.error("create excel file '" + subject + "' fail!", e);
		} catch (WriteException e) {
			log.error("create excel file '" + subject + "' fail!", e);
		} catch (IOException e) {
			log.error("create excel file '" + subject + "' fail!", e);
		} catch (Exception e) {
			log.error("create excel file '" + subject + "' fail!", e);
		} finally {
			// 关闭Excel工作薄对象
			try {
				if (wwb != null) {
					wwb.close();
				}
				os.flush();
				os.close();
			} catch (WriteException e) {
				log.error("close WritableWorkbook fail!", e);
			} catch (IOException e) {
				log.error("close WritableWorkbook fail!", e);
			} catch (Exception e) {
				log.error("close WritableWorkbook fail!", e);
			}

			log.info("end to create excel file.");
		}
	}
}

 

测试方法(Junit4)：

 

@Test
	public void export() {

		Connection conn = JDBCUtil.getInstance().getConnection();

		String sql = "SELECT DEPTNO, DNAME, LOC FROM DEPT";

		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(sql);

			rs = ps.executeQuery();

			List<Object[]> list = new ArrayList<Object[]>(3);
			Object[] objs = null;

			while (rs.next()) {

				objs = new Object[] { rs.getInt("DEPTNO"),
						rs.getString("DNAME"), rs.getString("LOC") };

				list.add(objs);
			}

			// 生成excel文件
			createExcel(list);

		} catch (SQLException e) {
			log.error("SQLException", e);
		} catch (Exception e) {
			log.error("Others Exception!", e);
		} finally {
			JDBCUtil.getInstance().close(conn, ps, rs);
		}
	}

	/**
	 * @Title: createExcel
	 * @Description: 生成excel文件
	 * @param lsit
	 *            : 查询结果集
	 * @author 
	 * @date 2012-6-29
	 */
	private void createExcel(List<Object[]> list) {

		// 文件名
		String name = "部门信息";
		// excel文件表头
		String[] head = { "部门编号", "部门名称", "位置" };

		File file = new File(name + ".xls");

		if (!file.exists()) {
			try {
				file.createNewFile();
			} catch (IOException e) {
				log.error("create file fail!", e);
			}
		}

		OutputStream os = null;

		try {
			os = new FileOutputStream(file);
		} catch (FileNotFoundException e) {
			log.error("create OutputStream fail!", e);
		}

		// 生成excel文件
		ExcelOutput.getInstance().createOutPutStreamForObjs(os, name, head,
				list);
	}

 

运行成功后,会在工程的根目录下生成 部门信息.xls

 

 

 

web 环境下的方法：

这里使用struts2.

查询条件 QueryParam

/**
 * @ClassName: QueryParam
 * @Description: 查询条件
 * @author 
 * @company 
 * @date 2012-6-29
 * @version V1.0
 */

public class QueryParam {

	private Integer deptNo;
	private String deptName;
	private String location;
	
	@Override
	public String toString() {
		
		StringBuffer buf = new StringBuffer("QueryParam:[");
		
		buf.append("deptNo=").append(deptNo);
		buf.append(",deptName=").append(deptName);
		buf.append(",location=").append(location);
		buf.append("]");
		
		return buf.toString();
	}

	// 这里省略 getter/setter
}

 

public void getAllDept(QueryParam params) throws Exception {

		StringBuffer buf = new StringBuffer();

		buf.append("SELECT DEPTNO, DNAME, LOC FROM DEPT where 1=1 ");

		if (params.getDeptNo() != null) {
			buf.append(" and DEPTNO = ").append(params.getDeptNo());
		}

		if (null != params.getDeptName() && !"".equals(params.getDeptName())) {
			buf.append(" and DNAME like %").append(params.getDeptName())
					.append("%");
		}

		if (null != params.getLocation() && !"".equals(params.getLocation())) {
			buf.append(" and LOC like %").append(params.getLocation())
					.append("%");
		}
		
		Connection conn = JDBCUtil.getInstance().getConnection();
		
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(buf.toString());

			rs = ps.executeQuery();

			List<Object[]> list = new ArrayList<Object[]>(3);
			Object[] objs = null;

			while (rs.next()) {

				objs = new Object[] { rs.getInt("DEPTNO"),
						rs.getString("DNAME"), rs.getString("LOC") };

				list.add(objs);
			}

			// 生成excel文件
			createExcel(list);

		} catch (SQLException e) {
			log.error("SQLException", e);
		} catch (Exception e) {
			log.error("Others Exception!", e);
		} finally {
			JDBCUtil.getInstance().close(conn, ps, rs);
		}
	}

 

 

action 方法：

 

在action类中定义一个私有的属性 private QueryParam queryParam;设置其setter/getter方法

 

public void getExport() {

	log.info("start to export dept.");

	if (null != queryParam) {

		try {
			// 调用service层,查找要导出的数据
			List<Object[]> list = getAllDept(queryParam);

			if (null != list && !list.isEmpty()) {

				log.info("export dept to excel. The data count is:"
						+ unitList.size());
			} else {
				log.info("export dept to excel. The data count is empty.");
			}

			HttpServletResponse response = ServletActionContext
					.getResponse();

			OutputStream os = response.getOutputStream();
			response.reset();

			// 文件名
			String name = "部门信息";
			// excel文件表头
			String[] head = { "部门编号", "部门名称", "位置" };

			byte[] fileNameByte = (name + ".xls").getBytes("GBK");
			// 文件名
			String fileName = new String(fileNameByte, "ISO8859-1");

			response.setHeader("Content-disposition",
					"attachment; filename=" + fileName);
			response.setContentType("application/msexcel");

			// 生成excel文件
			ExcelOutput.getInstance().createOutPutStreamForObjs(os, name,
					head, list);

		} catch (Exception e) {
			log.error("export dept excel file fail!", e);
		}

		log.info("end to export dept.");
	}

}