1.引入依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.4.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.4.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.4.0</version>
</dependency>
2.Excel模板
每个sheet的模板都是一样的
3.代码
// 将所有sheet使用得map进行包装
int i = 0;
Map<Integer, List<Map<String, Object>>> sheetMap = new HashMap<>();
//查询数据,此处省略,随便造的数据
Map<Object, List<ExportData>> dataMap = new HashMap<>();
dataMap.put("1", list1);
dataMap.put("2", list2);
for (Object o : dataMap.keySet()) {
List<ExportData> list = dataMap.get(o);
List<Map<String, Object>> mapList = new ArrayList<>();
SimpleDateFormat simpleFormat = new SimpleDateFormat("yyyy-MM-dd");
//累计借总笔数
long total = list.stream().filter(exportData -> "1".equals(exportData.getLoan())).count();
//累计借金额
BigDecimal lendTotal = list.stream().filter(exportData -> "1".equals(exportData.getLoan())).map(ExportData::getAmount).reduce(BigDecimal::add).orElse(BigDecimal.ZERO);
//累计贷金额
BigDecimal loanTotal = list.stream().filter(exportData -> "2".equals(exportData.getLoan())).map(ExportData::getAmount).reduce(BigDecimal::add).orElse(BigDecimal.ZERO);
Map<String, Object> map = new HashMap<String, Object>();
// 设置sheetName，若不设置该参数，则使用得原本的sheet名称
map.put(SHEET_NAME, list.get(0).getOutAccountName());
map.put("outAccount", list.get(0).getOutAccount());
map.put("outAccountName", list.get(0).getOutAccountName());
map.put("createDate", simpleFormat.format(new Date()));
map.put("loanAmount", loanTotal);
map.put("totalLend", total);
map.put("balance", list.get(list.size() - 1).getBalance());
map.put("lendAmount", lendTotal);
map.put("startTime", list.get(0).getValueDate());
map.put("endTime", list.get(0).getValueDate());
List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();
for (ExportData exportData : list) {
Map<String, String> lm = new HashMap<String, String>();
lm.put("outAccount", exportData.getOutAccount());
lm.put("outAccountName", exportData.getOutAccountName());
lm.put("currency", "人民币");
lm.put("tradingHours", exportData.getTradingHours());
lm.put("valueDate", exportData.getValueDate());
lm.put("tradeType", exportData.getTradeType());
lm.put("amount", String.valueOf(exportData.getAmount()));
lm.put("loan", "1".equals(exportData.getLoan()) ? "借" : "贷");
lm.put("balance", String.valueOf(exportData.getBalance()));
lm.put("dealFlow", exportData.getDealFlow());
lm.put("receiptAccountName", exportData.getReceiptAccountName());
lm.put("receiptAccount", exportData.getReceiptAccount());
lm.put("receiptAccountWhereitis", exportData.getReceiptAccountWhereitis());
listMap.add(lm);
}
map.put("maplist", listMap);
mapList.add(map);
sheetMap.put(i, mapList);
i++;
}
// 获取导出excel指定模版
TemplateExportParams params = new TemplateExportParams("template/模板.xls");
// 导出excel
Workbook workbook = ExcelExportUtil.exportExcelClone(sheetMap, params);
FileOutputStream fos = new FileOutputStream("D:/Project");
workbook.write(fos);
fos.close();
4.代码运行后导出内容如下：
