/**
* @Description:
* @param response
* @param request
* @param planTotalId
* @param planTotalIdGroup
* @param orgId
* @author houChen
* @date 2020/5/4 17:27
*/
@RequestMapping(value="exportexcel")
@ResponseBody
public void exportExcelTest(HttpServletResponse response,HttpServletRequest request,Long planTotalId,
Long planTotalIdGroup,Long orgId){
Workbook workbook = fmexpenseplantotalService.constructWorkbook1(planTotalId,planTotalIdGroup,orgId);
downLoadExcel("资金支出申报表.xlsx", response, workbook);
}
//将workbook 写出到response流中
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
e.getStackTrace();
}
}
/**
* @Description:
使用 Easyiop 的模板方式导出 excel (注释模板混用 ==》 合并单元格)
* @param planTotalId
* @param planTotalIdGroup
* @param orgId
* @author houChen
* @date 2020/4/30 15:43 注意：这是我自己构造的数据，你们要按照你们的实际需求来构造数据
*/
public Workbook constructWorkbook1(Long planTotalId, Long planTotalIdGroup, Long orgId) {
TemplateExportParams params = new TemplateExportParams("static/template/ExcelTemplate.xlsx");
Map<String, Object> map = new HashMap<String, Object>(); //构造模板需要的相关信息
// 查询导出的excel的表头和表尾信息
Map<String, Object> p = new HashMap<String, Object>();
p.put("planTotalId", planTotalId);
Map<String, Object> info = bSqlHelper.getMap("business.fm.FmExpensePlanTotal.getBaseInfo", p);
map.put("month",Objects.toString(info.get("month"),""));
map.put("orgName",Objects.toString(info.get("orgIdName"),""));
map.put("yearC",Objects.toString(info.get("yearC"),""));
map.put("monthC",Objects.toString(info.get("monthC"),""));
map.put("dayC",Objects.toString(info.get("dayC"),""));
map.put("createAccountText",Objects.toString(info.get("createAccountText"),""));
map.put("businessDeptName", "");
//获取事业部名称
String org = Objects.toString(info.get("orgId"), "");
if(!"".equals(org)){
Long belongDept = basOrgService.findByOrgId(Long.parseLong(org)).getBelongDept();
if(belongDept!=null){
map.put("businessDeptName",basDeptService.findByDeptId(belongDept).getDeptName());
}
}
// 汇总的审批意见
Map<String, Map<String, Object>> formAdvices = flowUtilService.queryWorkMemory("FmExpensePlanTotal", Long.parseLong(Objects.toString(info.get("processinstid"))));
map.put("deptOpinion", formAdvices.get("deptOpinion").get("accountName"));
map.put("leader", formAdvices.get("leader").get("accountName"));
List<ExcelEntity> result =new ArrayList<>();
//1、构造日常支出费用的List
// 获取 事业部资金计划汇总下的所有资金计划 + 资金计划关联的明细 (1、收支计划ID 2，收支计划名称 3、收支计划数量)
Double totalMoneyForExpense =0d;//日常费用支出合计
Double totalMoneyForProject =0d;//项目建设支出总的合计
Long expenseEndRow = 0L; // 日常收支计划 第二列合并结束的行数
List<Map<String,Object>> statisticInfoByClassId = fmexpenseplandetailService.statisticByClassIdAndPlanTotalId(planTotalId);
for(Map<String,Object> m:statisticInfoByClassId){
String classId = Objects.toString(m.get("classId"), "");
Double applyMoneyByclassid= Double.parseDouble(Objects.toString(m.get("applyMoneyByclassid"), "0"));
totalMoneyForExpense += applyMoneyByclassid;
//每个收支项目明细的个数
String classNumStr = Objects.toString(m.get("classNum"), "0");
long classNum = Long.parseLong(classNumStr);
expenseEndRow+=(classNum+1);
// 根据planTotalId 和 classId 获取资金收支计划的子表信息
if(!"".equals(classId)){
List<Map<String,Object>> detailList = fmexpenseplandetailService.findDetailListByPlanTotalId(planTotalId,Long.parseLong(classId));
for(Map<String,Object> detail:detailList){
ExcelEntity a =new ExcelEntity();
a.setTopClass("日常支出费用");
a.setSecClass(Objects.toString(m.get("className"),""));
a.setSystemName(Objects.toString(detail.get("systemName"),""));
a.setApplyMoney(Objects.toString(detail.get("applyMoney"),""));
a.setDeptName(Objects.toString(detail.get("deptName"),""));
a.setReceiveUnit("");
a.setBudgetBase("");
a.setRemark(Objects.toString(detail.get("remark"),""));
result.add(a);
}
//每个收支分类的合计
ExcelEntity heji =new ExcelEntity();
heji.setTopClass("日常支出费用");
heji.setSecClass(Objects.toString(m.get("className"),""));
heji.setSystemName("合计");
heji.setApplyMoney(applyMoneyByclassid.toString());
heji.setDeptName("");
heji.setReceiveUnit("");
heji.setBudgetBase("");
heji.setRemark("");
result.add(heji);
}
}
//日常收支费用的合计
ExcelEntity heji1 =new ExcelEntity();
heji1.setTopClass("日常支出费用");
heji1.setSecClass("合计");
heji1.setSystemName("合计");
heji1.setApplyMoney(totalMoneyForExpense.toString());
heji1.setDeptName("");
heji1.setReceiveUnit("");
heji1.setBudgetBase("");
heji1.setRemark("");
result.add(heji1);
// 集团 资金收支计划汇总 统计月度资金计划的相关信息
List<Map<String,Object>> statisticInfoByProjectId = fmexpenseplandetailService.simple(planTotalIdGroup,orgId);
if(statisticInfoByProjectId.size()!=0){
for(Map<String,Object> m:statisticInfoByProjectId){
String classId = Objects.toString(m.get("classId"), "");
Double applyMoneyByclassid= Double.parseDouble(Objects.toString(m.get("applyMoneyByclassid"), "0"));
totalMoneyForProject += applyMoneyByclassid;
// 根据 集团汇总Id planTotalIdGroup 和 orgId 获取资金计划的子表信息
if(!"".equals(classId)){
List<Map<String,Object>> detailList = pmPayPlanService.findByplanTotalIdGroupAndOrgId(planTotalIdGroup,orgId,Long.parseLong(classId));
for(Map<String,Object> detail:detailList){
ExcelEntity a =new ExcelEntity();
a.setTopClass("项目建设支出");
a.setSecClass(Objects.toString(m.get("className"),""));
a.setSystemName(Objects.toString(detail.get("systemName"),""));
a.setApplyMoney(Objects.toString(detail.get("applyMoney"),""));
a.setDeptName(Objects.toString(detail.get("deptName"),""));
a.setReceiveUnit("");
a.setBudgetBase("");
a.setRemark(Objects.toString(detail.get("remark"),""));
result.add(a);
}
//每个收支分类的合计
ExcelEntity heji =new ExcelEntity();
heji.setTopClass("项目建设支出");
heji.setSecClass(Objects.toString(m.get("className"),""));
heji.setSystemName("合计");
heji.setApplyMoney(applyMoneyByclassid.toString());
heji.setDeptName("");
heji.setReceiveUnit("");
heji.setBudgetBase("");
heji.setRemark("");
result.add(heji);
}
}
ExcelEntity heji2 =new ExcelEntity();
heji2.setTopClass("项目建设支出");
heji2.setSecClass("合计");
heji2.setSystemName("合计");
heji2.setApplyMoney(totalMoneyForProject.toString());
heji2.setDeptName("");
heji2.setReceiveUnit("");
heji2.setBudgetBase("");
heji2.setRemark("");
result.add(heji2);
}
map.put("list", result);
//日常费用支出 + 项目建设支出总费用
Double totol = totalMoneyForProject+totalMoneyForExpense;
map.put("totol", totol.toString());
Workbook workbook = ExcelExportUtil.exportExcel(params , map);
Sheet sheetAt = workbook.getSheetAt(0);
//合并第一列
Map<Integer, int[]> mergeMap =new HashMap<>();
mergeMap.put(0, null);
// Map<Integer, int[]> mergeMap ===> key--列,value--依赖的列,没有传空
PoiMergeCellUtil.mergeCells(sheetAt, mergeMap, 3,sheetAt.getLastRowNum()-2);
//合并第二列
// 1、日常收支费用的合并
Map<Integer, int[]> mergeMap1 =new HashMap<>();
mergeMap1.put(1,null);
int endRow = expenseEndRow.intValue()+3-1;
PoiMergeCellUtil.mergeCells(sheetAt, mergeMap1, 3,endRow);
//合并日常收支费用的合计
CellRangeAddress region1 = new CellRangeAddress(endRow+1, endRow+1, (short) 1, (short) 2); //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
sheetAt.addMergedRegion(region1);
//2、项目建设的合并 (当项目建设有数据时)
if(statisticInfoByProjectId.size()!=0){
Map<Integer, int[]> mergeMap2 =new HashMap<>();
mergeMap1.put(1,null);
PoiMergeCellUtil.mergeCells(sheetAt, mergeMap1, endRow+2,sheetAt.getLastRowNum()-3);
//合并项目支出费用的合计
CellRangeAddress region2 = new CellRangeAddress(sheetAt.getLastRowNum()-2, sheetAt.getLastRowNum()-2, (short) 1, (short) 2); //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
sheetAt.addMergedRegion(region2);
}
//设置单元格样式
CellStyle cellStyle = workbook.createCellStyle();//新建单元格样式
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setWrapText(true);//设置自动换行
//为所有的单元格渲染样式
int rowNum = sheetAt.getLastRowNum();
System.out.println(rowNum);
for(int i=3;i<rowNum;i++){
Row row = sheetAt.getRow(i);
int colNum = row.getPhysicalNumberOfCells();
for(int j=0;j<colNum;j++){
//对金钱格式进行特殊处理
if(j==3){
DataFormat dataFormat = workbook.createDataFormat();// 此处设置数据格式
cellStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));
}
Cell cell = row.getCell((short) j);
cell.setCellStyle(cellStyle);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
}
}
return workbook;
}