EasyPoi模板导出(多sheet导出)
前言
一、引入包
引入easypoi的相关包
二、代码实现
代码如下(示例):
@Override
public void export(LaborCostSettlement laborCostSettlement, HttpServletRequest request, HttpServletResponse response) {
Map<Integer, Map<String, Object>> map = Maps.newHashMap();
// 定义导出内容 -- 主表数据
Map<String,Object> paramMap = Maps.newHashMap();
// 定义导出内容 -- 子表数据
Map<String,Object> paramMapChildOne = Maps.newHashMap();
Map<String,Object> paramMapChildTwo = Maps.newHashMap();
Map<String,Object> paramMapChildThree = Maps.newHashMap();
Map<String,Object> paramMapChildFour = Maps.newHashMap();
Map<String,Object> paramMapChildFive = Maps.newHashMap();
Map<String,Object> paramMapChildSix = Maps.newHashMap();
Map<String,Object> paramMapChildSeven = Maps.newHashMap();
// 设置主表数据 -- 工程结算书总数据
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.eq("a.del_flag", CommonConstant.STATUS_0);
if(StringUtils.isNotBlank(laborCostSettlement.getId())){
queryWrapper.eq("a.id", laborCostSettlement.getId());
}
List<LaborCostSettlement> list = baseMapper.findDataList(queryWrapper);
LaborCostSettlement settlement = new LaborCostSettlement();
if(CollectionUtil.isNotEmpty(list)){
settlement = list.get(0);
}
if(ObjectUtil.isNotEmpty(settlement.getCurrentSettleAmount())){
settlement.setChineseAmount(Convert.digitToChinese(settlement.getCurrentSettleAmount()));
}
paramMap.put("settlement", settlement);
paramMap.put("zero", BigDecimal.valueOf(0).setScale(2, BigDecimal.ROUND_HALF_UP));
// 结算表数据
paramMapChildOne.put("settlement", settlement);
paramMapChildOne.put("zero", BigDecimal.valueOf(0).setScale(2, BigDecimal.ROUND_HALF_UP));
// 工程费统计表-浇筑
LaborCostSettlementChild child = new LaborCostSettlementChild();
child.setZid(laborCostSettlement.getId());
child.setCategory("1");
List<LaborCostSettlementChild> childOne = childService.findChildData(child);
dealWithChildList(childOne, paramMapChildTwo);
// 人工费单价 总价
paramMapChildTwo.put("artificialCostPrice", ObjectUtil.isNotEmpty(settlement.getArtificialCostPrice()) ? settlement.getArtificialCostPrice() : 0);
paramMapChildTwo.put("artificialCostTotal", ObjectUtil.isNotEmpty(settlement.getArtificialCostTotal()) ? settlement.getArtificialCostTotal() : 0);
// 人工费合计
BigDecimal total = BigDecimal.valueOf(Double.parseDouble(
ObjectUtil.isNotEmpty(paramMapChildTwo.get("total")) ? paramMapChildTwo.get("total").toString() : "0"
));
paramMapChildTwo.put("personTotal", total.add(ObjectUtil.isNotEmpty(settlement.getArtificialCostTotal()) ? settlement.getArtificialCostTotal() : BigDecimal.ZERO));
paramMapChildTwo.put("childList", childOne);
paramMapChildTwo.put("attachTeamName", settlement.getAttachTeamName());
paramMapChildTwo.put("partyBUnit", settlement.getPartyBUnit());
paramMapChildTwo.put("zero", BigDecimal.valueOf(0).setScale(2, BigDecimal.ROUND_HALF_UP));
// 工程费统计表-钢筋
child.setCategory("2");
List<LaborCostSettlementChild> childTwo = childService.findChildData(child);
dealWithChildList(childTwo, paramMapChildThree);
paramMapChildThree.put("childList", childTwo);
paramMapChildThree.put("attachTeamName", settlement.getAttachTeamName());
paramMapChildThree.put("partyBUnit", settlement.getPartyBUnit());
// 工程费统计表-钢绞线
child.setCategory("3");
List<LaborCostSettlementChild> childThree = childService.findChildData(child);
dealWithChildList(childThree, paramMapChildFour);
paramMapChildFour.put("childList", childThree);
paramMapChildFour.put("attachTeamName", settlement.getAttachTeamName());
paramMapChildFour.put("partyBUnit", settlement.getPartyBUnit());
// 工程量确认表-浇筑
paramMapChildFive.put("childList", childOne);
paramMapChildFive.put("attachTeamName", settlement.getAttachTeamName());
paramMapChildFive.put("partyBUnit", settlement.getPartyBUnit());
paramMapChildFive.put("strete", paramMapChildTwo.get("strete"));
// 工程量确认表-钢筋
paramMapChildSix.put("childList", childTwo);
paramMapChildSix.put("attachTeamName", settlement.getAttachTeamName());
paramMapChildSix.put("partyBUnit", settlement.getPartyBUnit());
paramMapChildSix.put("strete", paramMapChildThree.get("strete"));
// 工程量确认表-钢绞线
paramMapChildSeven.put("childList", childThree);
paramMapChildSeven.put("attachTeamName", settlement.getAttachTeamName());
paramMapChildSeven.put("partyBUnit", settlement.getPartyBUnit());
paramMapChildSeven.put("strete", paramMapChildFour.get("strete"));
settingMapNull(paramMapChildTwo);
settingMapNull(paramMapChildThree);
settingMapNull(paramMapChildFour);
settingMapNull(paramMapChildFive);
settingMapNull(paramMapChildSix);
settingMapNull(paramMapChildSeven);
String fileName = "运输结算.xlsx";
String templatePath = upLoadPath + File.separator + TEMPLE_URL + File.separator + "person/exportPersonSettleMxOne.xlsx";
File folder = new File(upLoadPath + File.separator + TEMPLE_URL + File.separator + "person");
// 文件夹不存在则创建文件夹
if(!folder.exists()){
folder.mkdirs();
}
// 文件不存在 则联系管理员添加导出模板
File file = new File(templatePath);
if(!file.exists()){
throw new JeecgBootException("导出模板不存在,请联系管理员在【" + templatePath + "】路径添加导出模板!");
}
try {
map.put(0, paramMap);
map.put(1, paramMapChildOne);
map.put(2, paramMapChildTwo);
map.put(3, paramMapChildThree);
map.put(4, paramMapChildFour);
map.put(5, paramMapChildFive);
map.put(6, paramMapChildSix);
map.put(7, paramMapChildSeven);
TemplateExportParams exportParams = new TemplateExportParams(templatePath, map.keySet().toArray(new Integer[]{}));
Workbook workbook = ExcelExportUtil.exportExcel(map, exportParams);
// 强制计算
workbook.setForceFormulaRecalculation(true);
response.addHeader("filename", URLEncoder.encode(fileName,"utf-8"));
response.addHeader("Access-Control-Expose-Headers","filename");
response.setHeader("Content-disposition", "attachment; fileName=" + URLEncoder.encode(fileName, "utf-8"));
OutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public void settingMapNull(Map<String,Object> paramMap){
if(paramMap.get("childList") == null){
ArrayList<String> emptyList = new ArrayList<>();
emptyList.add("");
paramMap.put("childList",emptyList);
}
}
public void dealWithChildList(List<LaborCostSettlementChild> child, Map<String,Object> paramMap){
BigDecimal strete = BigDecimal.ZERO;
BigDecimal total = BigDecimal.ZERO;
if(CollectionUtil.isNotEmpty(child)){
int num = 1;
for (LaborCostSettlementChild child1 : child){
strete = strete.add(ObjectUtil.isNotEmpty(child1.getTotalQuantity()) ? child1.getTotalQuantity() : BigDecimal.ZERO);
total = total.add(ObjectUtil.isNotEmpty(child1.getTotalAmount()) ? child1.getTotalAmount() : BigDecimal.ZERO);
if(StringUtils.isNotBlank(child1.getMeteringUnit())){
child1.setMeteringUnit(baseAPI.translateDict("aux_material_unit", child1.getMeteringUnit()));
}
child1.setProductionProcessName(baseAPI.translateDict("production_process", child1.getProductionProcess()));
child1.setNumIndex(num);
num++;
}
}
paramMap.put("strete", strete);
paramMap.put("total", total);
}
模板示例:
导出示例
三、总结
多sheet采用
TemplateExportParams exportParams = new TemplateExportParams(templatePath, map.keySet().toArray(new Integer[]{}));
Workbook workbook = ExcelExportUtil.exportExcel(map, exportParams);
如果含有计算则使用workbook.setForceFormulaRecalculation(true);