/**
* 导出历史记录
*/
@SuppressWarnings({ "deprecation", "unchecked" })
@RequestMapping("export-TrainHistoryRecord")
@ResponseBody
protected void buildExcelDocument(EmployeeTrainHistoryQuery query,ModelMap model,
SXSSFWorkbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
//这里使用SXSSFWorkbook对象,支持1048576条数据
try {
response.reset();
// 获得国际化语言
RequestContext requestContext = new RequestContext(request);
String CourseCompany = requestContext
.getMessage("manage-student-trainRecods");
response.setContentType("APPLICATION/vnd.ms-excel;charset=UTF-8");
// 注意，如果去掉下面一行代码中的attachment; 那么也会使IE自动打开文件。
response.setHeader(
"Content-Disposition",
"attachment; filename="
+ java.net.URLEncoder.encode(
DateUtil.getExportDate() + ".xlsx", "UTF-8"));//Excel 扩展名指定为xlsx SXSSFWorkbook对象只支持xlsx格式
OutputStream os = response.getOutputStream();// new
CellStyle style = workbook.createCellStyle();
// 设置样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
String employeeCode = requestContext.getMessage("employeeCode");
String employeeName = requestContext.getMessage("employeeName");
String orgName = requestContext.getMessage("orgName");
String startDate = requestContext.getMessage("start.date");
String endDate = requestContext.getMessage("end.date");
String courseCode = requestContext.getMessage("courseCode");
String courseName = requestContext.getMessage("courseName");
String sessionName = requestContext.getMessage("sessionName");
String hoursNunber = requestContext.getMessage("hoursNunber");
// FileOutputStream(filePath);
List<EmployeeTrainHistoryModel> list = null;
query.setNeedQueryAll(true);
try {
// if("0".equals(query.getTrainFlag())){
query.setTotalItem(employeeTrainHistoryService.fetchCountEmployeeTrainHistoryByQuery(query));
int page_size = 100000;// 数据库中存储的数据行数
int list_count =query.getTotalItem();
int export_times = list_count % page_size > 0 ? list_count / page_size
+ 1 : list_count / page_size;
for (int m = 0; m < export_times; m++) {
query.setNeedQueryAll(false);
query.setPageSize(100000);//每页显示多少条数据
query.setCurrentPage(m+1);//设置第几页
list=employeeTrainHistoryService.getEmployeeTrainHistoryByQuery(query);
int len = list.size() < page_size ? list.size() : page_size;
//新建sheet
Sheet sheet = null;
sheet = workbook.createSheet(System.currentTimeMillis()
+ CourseCompany+m);
// for (int n = 0; n < len; n++) {
// 迭代数据
if (list != null && list.size() > 0) {
int rowNum = 1;
int max_row=300000;
int sheet_count=0;
for (int i = 0; i < list.size(); i++) {
EmployeeTrainHistoryModel history=list.get(i);
//如果当前行超过单页签可容纳最大行则换页签
// if(i==0||i==(max_row*sheet_count+1)){
sheet.setDefaultColumnWidth((short) 17);
// 创建属于上面Sheet的Row，参数0可以是0～65535之间的任何一个，
Row header = sheet.createRow(0); // 第0行
// 产生标题列
Cell cell;
String[] headerArr = new String[] { employeeCode, employeeName,
orgName, startDate, endDate, courseCode, courseName, sessionName,
hoursNunber };
for (int j = 0; j < headerArr.length; j++) {
cell = header.createCell((short) j);
cell.setCellStyle(style);
cell.setCellValue(headerArr[j]);
}
Row row = sheet.createRow(rowNum++);
row.createCell((short) 0).setCellValue(
history.getEmployeeCode());
row.createCell((short) 1).setCellValue(
history.getEmployeeName());
row.createCell((short) 2)
.setCellValue(history.getOrgName());
if (history.getTrainBeginTime() != null) {
row.createCell((short) 3).setCellValue(
DateUtil.toString(history.getTrainBeginTime()));
} else {
row.createCell((short) 3).setCellValue("");
}
if (history.getTrainEndTime() != null) {
row.createCell((short) 4).setCellValue(
DateUtil.toString(history.getTrainEndTime()));
} else {
row.createCell((short) 4).setCellValue("");
}
row.createCell((short) 5).setCellValue(
history.getCourseCode());
row.createCell((short) 6).setCellValue(
history.getCourseName());
row.createCell((short) 7).setCellValue(
history.getSessionName());
if (history.getHoursNumber() != null)
row.createCell((short) 8).setCellValue(
history.getHoursNumber().toString());
}
}
list.clear();
}
} catch (Exception e) {
e.printStackTrace();
}
try {
workbook.write(os);
model.put("msg", "1");
os.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}
}
