Num1 多Sheet 导出
/**
* 导出excel
*
* @throws Exception
*/
@Override
public boolean exportExcel(HttpServletRequest request, HttpServletResponse response, Map map) throws Exception {
boolean flag = false;
List<Operation_log> list = operation_logDao.queryAll(map);
if (list.size() > 0) {
flag = true;
// 创建excel对象 内存中缓存100条数据(100条最佳)
SXSSFWorkbook wb = new SXSSFWorkbook(100);
//临时文件进行压缩，建议不要true，否则会影响导出时间
wb.setCompressTempFiles(false);
List<Operation_log> list = operation_logDao.queryAll(map);
//样式
CellStyle style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
Sheet sheet = null;
int count = 1;
for (int i = 0, j = 2; i < list.size(); i++, j++) {
if (i == 0 || i % 100000 == 0) {
sheet = wb.createSheet("操作日志第" + (count) + "篇");
sheet.setDefaultColumnWidth(30);
Row theme = sheet.createRow(0);
theme.createCell(1).setCellValue("操作日志");
theme.setRowStyle(style);
Row title = sheet.createRow(1);
title.createCell(0).setCellValue("操作人");
title.createCell(1).setCellValue("操作时间");
title.createCell(2).setCellValue("IP");
title.setRowStyle(style);
count++;
j = 2;
}
Row row = sheet.createRow(j);
row.createCell(0).setCellValue(list.get(i).getUser_name());
row.createCell(1).setCellValue(list.get(i).getCreateTime());
row.createCell(2).setCellValue(list.get(i).getIp());
// 清空内存中缓存的行数
if (i % 100 == 0) {
((SXSSFSheet) sheet).flushRows();
}
}
//以excel多sheet方式下载
OutputStream out = response.getOutputStream();
response.reset();
response.setHeader("content-disposition",
"attachment;filename=" + new String(("操作日志").getBytes(),"ISO8859-1") + ".xlsx");
response.setContentType("application/ms-excel");
out.flush();
wb.write(out);
out.close();
//以压缩包excel多sheet方式形式下载
// OutputStream out = response.getOutputStream();
// response.reset();
// ZipOutputStream zip = new ZipOutputStream(out);
// zip.setEncoding("gbk");
// zip.putNextEntry(new ZipEntry("操作日志.xlsx"));
// response.setContentType("application/octet-stream;charset=UTF-8");
// response.setHeader("Content-Disposition", "attachment;filename="
// +new String(("操作日志").getBytes(),"ISO8859-1")+".xlsx");
// zip.flush();
// wb.write(zip);
// out.close();
// zip.close();
}
return flag;
}
Num2 多exel导出
/**
* 导出excel
*
* @throws Exception
*/
@Override
public boolean exportExcel(HttpServletRequest request, HttpServletResponse response, Map map) throws Exception {
boolean flag = false;
List<Operation_log> list = operation_logDao.queryAll(map);
if (list.size() > 0) {
flag = true;
List<String> fileNames = new ArrayList();// 用于存放生成的文件名称
File zip = new File("操作日志.zip");// 压缩文件
SXSSFWorkbook wb =null;
// 生成excel
for (int j = 0, n = list.size() % 100000 == 0 ? list.size() / 100000 : (list.size() / 100000) + 1; j < n; j++) {
// 在内存中缓存100行数据(100测试最佳)
wb = new SXSSFWorkbook(100);
//临时文件进行压缩，建议不要true，否则会影响导出时间
wb.setCompressTempFiles(false);
Sheet sheet = wb.createSheet();
sheet.setDefaultColumnWidth(30);
CellStyle style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
String file = "操作日志-" + (j+1 )+ ".xlsx";
fileNames.add(file);
FileOutputStream o = new FileOutputStream(file);
Row title=sheet.createRow(0);
title.createCell(1).setCellValue("操作日志");
title.setRowStyle(style);
Row row = sheet.createRow(1);
row.createCell(0).setCellValue("操作人");
row.createCell(1).setCellValue("操作时间");
row.createCell(2).setCellValue("IP");
row.setRowStyle(style);
for (int i = 0, min = (list.size() - j * 100000) > 100000 ?100000
: (list.size() - j * 100000); i < min; i++) {
Operation_log log = list.get(100000 * (j) + i);
row = sheet.createRow((i+2));
row.createCell(0).setCellValue(log.getUser_name());
row.createCell(1).setCellValue(log.getCreateTime());
row.createCell(2).setCellValue(log.getIp());
if(i%100==0){
( (SXSSFSheet) sheet).flushRows();
}
}
o.flush();
wb.write(o);
o.close();
}
OutputStream out = response.getOutputStream();
response.reset();
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename="
+new String(("操作日志").getBytes(),"ISO8859-1")+".zip");
ZipFiles(fileNames, zip);
FileInputStream inStream = new FileInputStream(zip);
byte[] buf = new byte[1024];
int readLength;
while (((readLength = inStream.read(buf)) != -1)) {
out.write(buf, 0, readLength);
}
inStream.close();
out.flush();
out.close();
}
return flag;
}
public static void ZipFiles(List<String> filename, File zipfile) {
byte[] buf = new byte[1024];
try {
ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipfile));
out.setEncoding("gbk");// 防止文件名乱码
for (int i = 0; i < filename.size(); i++) {
File file = new File(filename.get(i));
FileInputStream in = new FileInputStream(file);
out.putNextEntry(new ZipEntry(file.getName()));
int len;
while ((len = in.read(buf)) > 0) {
out.write(buf, 0, len);
}
out.closeEntry();
in.close();
}
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}