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.");
}
}