以jdbc为例,手动拼装sql
公共方法里
writeExcel方法里
//定义Excel表头
String[] topNamelist = new String[]{"姓名","住址"}
//对应的key键
String[] fieldNameList = new String[]{"name","address"};
//需要HttpServletResponse response
//sql语句
String sql = "select name,address from t_user where ooxx=?";
公共方法里
/**
* @Description: Excel大数据量导出
* @param topNamelist Excel表头
* @param fieldNameList Map数据对象的key
* @param sql 查询语句
* @param obj 查询sql的参数
*/
public void excelDownload(String[] topNamelist,String[] fieldNameList, String sql, Object[] obj, String excelName) {
//每个sheet写入65530行数,如果内存溢出可以调整内存或者把这个参数改小点
int sheetSize = Constant.SHEET_SIZE;
//当前sheet页
int sheetIndex = 1;
//总sheet数
int totalSheet = 1;
//总记录数
int count = DataTool.executeQueryCount(sql, obj);
//计算总共多少个sheet
if (count % sheetSize == 0) { //总记录数与每页显示记录数取余
totalSheet = count / sheetSize;
} else {
totalSheet = (int) Math.floor(count / sheetSize) + 1;
}
//临时文件存放路径c:\\bdtemp\\
String pathFolder = Constant.TEMP_PATH;
//文件名称,UUID命名保证文件唯一
String fileName = pathFolder + CommonUtil.getUuid() + ".xls";
//创建文件夹
File fileFolder = new File(pathFolder);
if (!fileFolder.exists()) {
fileFolder.mkdir();
}
File file = new File(fileName);
//分页写入结果集
for (int k = 0; k < totalSheet; k++) {
List list = new ArrayList();
sheetIndex = k + 1;
StringBuffer buf = new StringBuffer();
buf.append("select * from (");
buf.append("select a.*,rownum row_num from(");
buf.append(sql);
buf.append(") a where rownum<=").append(sheetSize * sheetIndex);
buf.append(") b where b.row_num > ").append(
sheetSize * (sheetIndex - 1));
//一个sheet的数据,DataTool为jdbc执行sql的封装类
list = DataTool.executeQuery(buf.toString(), obj);
MyUtil.writeExcel(list, file, sheetIndex-1, "sheet"+sheetIndex,topNamelist, fieldNameList);
}
//下载Excel
MyUtil.downloadFile(fileName, response, excelName+".xls");
}
writeExcel方法里
public static void writeExcel(List list, File file, int sheetIndex, String sheetName,
String[] topNamelist, String[] fieldNameList) {
try {
WritableWorkbook wwb=null;
if(sheetIndex==0){
//首次创建一个Excel文件
wwb= Workbook.createWorkbook(file);
}else{
//非首次,获取已创建Excel文件
Workbook wb=Workbook.getWorkbook(file);
// 打开一个文件的副本,并且指定数据写回到原文件
wwb= Workbook.createWorkbook(file,wb);
}
WritableSheet ws = wwb.createSheet(sheetName, sheetIndex);
// 写入列名称
for (int i = 0; i < topNamelist.length; i++) {
Label label = new Label(i, 0, topNamelist[i].toString());
ws.addCell(label);
}
//写入数据
for (int i = 0; i < list.size(); i++) {
int x = i + 1;
Map map = (Map) list.get(i);//testReflect(list.get(i));
for (int j = 0; j < fieldNameList.length; j++) {
if (map.get(fieldNameList[j]) != null) {
Label labela = new Label(j, x, String.valueOf(map.get(fieldNameList[j])));
ws.addCell(labela);
} else {
Label labela = new Label(j, x, null);
ws.addCell(labela);
}
}
}
//写入
wwb.write();
//关闭
wwb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @Description: 文件下载
* @param filePath 文件路径
* @param response HttpServletResponse
* @param fileName 文件名,输出到用户的下载对话框
*/
public static void downloadFile(String filePath,
HttpServletResponse response, String fileName) {
try {
fileName = new String(fileName.getBytes("GB2312"), "ISO8859_1");
// 打开指定文件的流信息
FileInputStream fs = null;
File file = new File(filePath);
fs = new FileInputStream(file);
OutputStream os = null;
response.setContentType("APPLICATION/DOWNLOAD");
response.setHeader("Content-Disposition", "attachment; filename=\""
+ fileName + "\"");// ***是文件名
response.setContentLength(fs.available());
os = response.getOutputStream();
int iBytesRead = 0;
byte[] buffer = new byte[8192];
while ((iBytesRead = fs.read(buffer, 0, 8192)) != -1) {
os.write(buffer, 0, iBytesRead);
}
response.flushBuffer();
fs.close();
os.close();
file.delete();
} catch (Exception e) {
e.printStackTrace();
}
}