/**
* 统计
* @param month
* @param request
* @param session
* @return
*/
@RequestMapping("getCdthbCount")
@ResponseBody
public Map<String, Object> getCdthbCount(
@DateTimeFormat(pattern = "yyyy-MM") Date month, HttpServletRequest request, HttpSession session) {
Map<String, Object> result = new HashMap<String, Object>();
result = panchaCountService.getCdthbCount(month);
return result;
}
public ResultBean exportCdthbCountExcel(Date tableDate) {
//导出Excel表
ResultBean resultBean = new ResultBean();
UUID uuid = UUID.randomUUID();
String fileName = uuid.toString().replaceAll("-","").toUpperCase() + ".xls";
OutputStream out = null;
try {
String filePath=ConfigUtil.getValue("filepath")+"\\"+fileName;
out = new FileOutputStream(filePath);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月");
String theDate=sdf.format(tableDate);
//创建HSSFWorkbook 对象(excel的文档对象)
HSSFWorkbook workbook = new HSSFWorkbook();
// 建立新的sheet对象(excel的表单)
HSSFSheet sheet = workbook.createSheet("sheetName");
//在sheet黎创建第一行,参数为行索引(excel的行),范围:0~65535
HSSFRow row = sheet.createRow(0);
//创建单元格并填充内容(excel的单元格,参数为列索引),范围:0~255
row.createCell(0).setCellValue(theDate+"-excelFileName");
//合并单元格,CellRangeAddress的参数说明:依次为:起始行、截止行、起始列、截止列
sheet.addMergedRegion(new CellRangeAddress(0,0,0,27));
Map<String, Object> content = getCdthbCount(tableDate);
List<String> project = (List<String>) content.get("project"); //项目名
List<String> projectClassify = (List<String>) content.get("projectClassify"); //项目分类
List<String> projectDetails = (List<String>) content.get("projectDetails"); //项目详情
List<String> area = (List<String>) content.get("area"); //地方名称
ArrayList<ArrayList<Object>> aList = (ArrayList<ArrayList<Object>>) content.get("aList"); //结果集
row = sheet.createRow(1);
row.createCell(0).setCellValue("项目");
row.createCell(1).setCellValue("分类");
row.createCell(2).setCellValue("详情");
for(int i =0; i<area.size(); i++){
row.createCell(i+3).setCellValue(area.get(i));
}
int length = projectDetails.size();
for(int i=0; i<aList.size(); i++){
row = sheet.createRow(i+2);
if(i%length == 0){
row.createCell(0).setCellValue(project.get(i%length));
row.createCell(1).setCellValue(projectClassify.get(0));
}else if(i%length == 3){
row.createCell(1).setCellValue(projectClassify.get(1));
}else if(i%length == 5){
row.createCell(1).setCellValue(projectClassify.get(2));
}
row.createCell(2).setCellValue(projectDetails.get(i%length));
for(int j=0; j<aList.get(i).size(); j++){
row.createCell(j+3).setCellValue(aList.get(i).get(j).toString());
}
}
//合并单元格,CellRangeAddress的参数说明:依次为:起始行、截止行、起始列、截止列
sheet.addMergedRegion(new CellRangeAddress(2,8,0,0));
sheet.addMergedRegion(new CellRangeAddress(9,15,0,0));
sheet.addMergedRegion(new CellRangeAddress(2,4,1,1));
sheet.addMergedRegion(new CellRangeAddress(5,6,1,1));
sheet.addMergedRegion(new CellRangeAddress(7,8,1,1));
sheet.addMergedRegion(new CellRangeAddress(9,11,1,1));
sheet.addMergedRegion(new CellRangeAddress(12,13,1,1));
sheet.addMergedRegion(new CellRangeAddress(14,15,1,1));
workbook.write(out);
resultBean.setSuccess("excelFileName导出成功");
resultBean.setObjBean(fileName);
resultBean.setMessage(theDate);
} catch (Exception e) {
e.printStackTrace();
resultBean.setFail("导出失败");
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return resultBean;
}