POI批量导出数据
public class FileZip {
/**
*
* @param srcfile 文件名称数组
* @param zipfile 压缩后文件
*/
public static void ZipFiles(File[] srcfile, File zipfile) {
byte[] buf = new byte[1024];
try {
ZipOutputStream out = new ZipOutputStream(new FileOutputStream(
zipfile));
for (int i = 0; i < srcfile.length; i++) {
FileInputStream in = new FileInputStream(srcfile[i]);
out.putNextEntry(new ZipEntry(srcfile[i].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();
}
}
}
@RequestMapping("/exportCourseDataAll.do")
public String exportCourseDataListAll(HttpServletRequest req, HttpServletResponse res) throws IOException {
String startDate = req.getParameter("startDate"); // 開始日期
String endDate = req.getParameter("endDate");
// 结束日期
String reportType = req.getParameter("reportType");
// 日周月报:‘day’,‘week’。‘month’。
String companyName = req.getParameter("companyName");
// 公司名称
// 仅仅能导出昨天的课程用户学习明细。日期传空
if (startDate == null || endDate == null) {
startDate = DateTimeUtils.getDateString("yyyy-MM-dd", Calendar.DATE, -1);
endDate = DateTimeUtils.getDateString("yyyy-MM-dd", Calendar.DATE, 0);
}
Map<String, Object> params = new HashMap<String, Object>();
params.put("startDate", startDate);
params.put("endDate", endDate);
params.put("reportType", reportType);
params.put("companyName", companyName);
res.setContentType("application/octet-stream;charset=UTF-8");
String fileName = DateTimeUtils.formatDateNo_(new Date())+"用户明细数据";
req.setCharacterEncoding("UTF-8");
res.setCharacterEncoding("UTF-8");
res.setHeader("Content-Disposition", "attachment;filename="
+ java.net.URLEncoder.encode(fileName, "UTF-8")
+ ".zip");
res.addHeader("Pargam", "no-cache");
res.addHeader("Cache-Control", "no-cache");
// 数据量比較大时,分页导出数据
org.extremecomponents.table.context.Context context = new HttpServletRequestContext(req);
org.extremecomponents.table.limit.LimitFactory limitFactory = new TableLimitFactory(context);
Limit limit = new TableLimit(limitFactory);
OutputStream out = null;
try {
int totalRows = limit.getTotalRows();
List<CourseDetailDTO> listAll = new ArrayList<CourseDetailDTO>();
totalRows = wholeDataService.getCourseDetailListForPageCount(params);
int pageSize = 5000;
int totalPage = Integer.valueOf(CommonUtility.calcTotalPage(pageSize, totalRows));
for (int i = 0; i < totalPage; i++) {
params.put("pageNo", (i+1));
params.put("pageSize", pageSize);
List<CourseDetailDTO> listPage = wholeDataService.getCourseDetailAll(params);
listAll.addAll(listPage);
}
// 写入数据
out = res.getOutputStream();
toExcel(listAll,req,50000,fileName,out);
} catch (Exception e) {
logger.error("导出用户明细数据异常",e);
}
return null;
}
/**
* @Title: toExcel* @Description: TODO(压缩导出excel表格中数据)
* @ 2015-8-18 下午5:29:26
* @param list
* @param request
* @param length
* @param f
* @param out
* @throws IOException
*/
public void toExcel(List<CourseDetailDTO> list, HttpServletRequest request,
int length, String f, OutputStream out) throws IOException {
List<String> fileNames = new ArrayList();// 用于存放生成的文件名s
File zip = new File(request.getRealPath("/") + f + ".zip");// 压缩文件
// 生成excel
int totalNum = 0;
// 推断产生excel个数
if (list.size() % length == 0) {
if (list.size() == length) {
totalNum = 1;
} else {
totalNum = list.size() / length;
}
} else {
totalNum = list.size() / length + 1;
}
for (int j = 0, n = totalNum; j < n; j++) {
Workbook book = new HSSFWorkbook();
Sheet sheet = book.createSheet("userDataDetail");
String file = request.getRealPath("/") + f + "-" + j + ".xls";
fileNames.add(file);
FileOutputStream o = null;
try {
o = new FileOutputStream(file);
Row row = sheet.createRow(0);
row.createCell(0).setCellValue("时间");
row.createCell(1).setCellValue("用户明细");
row.createCell(2).setCellValue("用户员工ID");
int m = 1;
for (int i = 1, min = (list.size() - j * length + 1) > (length + 1) ? (length + 1)
: (list.size() - j * length + 1); i < min; i++) {
m++;
CourseDetailDTO courseDetail = list.get(length * (j) + i - 1);
row = sheet.createRow(i);
row.createCell(0).setCellValue(courseDetail.getTopDate());
row.createCell(1).setCellValue(courseDetail.getUserId());
row.createCell(2).setCellValue(courseDetail.getEmployeeId());
}
CellStyle cellStyle2 = book.createCellStyle();
cellStyle2.setAlignment(CellStyle.ALIGN_CENTER);
row = sheet.createRow(m);
Cell cell0 = row.createCell(0);
cell0.setCellStyle(cellStyle2);
Cell cell1 = row.createCell(1);
cell1.setCellStyle(cellStyle2);
Cell cell2 = row.createCell(2);
cell2.setCellStyle(cellStyle2);
} catch (Exception e) {
e.printStackTrace();
}
try {
book.write(o);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
o.flush();
o.close();
}
}
File srcfile[] = new File[fileNames.size()];
for (int i = 0, n = fileNames.size(); i < n; i++) {
srcfile[i] = new File(fileNames.get(i));
}
FileZip.ZipFiles(srcfile, zip);
FileInputStream inStream = new FileInputStream(zip);
byte[] buf = new byte[4096];
int readLength;
while (((readLength = inStream.read(buf)) != -1)) {
out.write(buf, 0, readLength);
}
inStream.close();
}