用easyPoi导出excel，带多sheet,合并单元格，合计，单元格金额类型
文档连接：http://easypoi.mydoc.io/
1.引入依赖
<!-- 导出文件工具 EasyPoi实现Excel读写管理测试用例 --><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-spring-boot-starter</artifactId><version>4.2.0</version></dependency>
2.书写结构：合并单元格，合计
@ExcelTarget("repayBackFlowTables")public class RepayBackFlow implements Serializable {// needMerge =true 合并单元格@Excel(name = "业务日期", width = 30, needMerge = true)private String statDt;@ExcelCollection(name = "")private List<RepayBackFlowDto> repayBackFlowDtos;}
@ExcelTarget("tT")public class RepayBackFlowDto implements Serializable {// needMerge =true 合并单元格@Excel(name = "机构", width = 30 , needMerge = true)private String accountingorgid;@ExcelCollection(name = "")private List<RepayBackFlowDetailDto> detailDtos;}
@ExcelTarget("bb")public class RepayBackFlowDetailDto {@Excel(name = "资产类型名称", width = 30)private String grdNm;// isStatistics:true要合计 ，type=10表示数值@Excel(name = "累计数量", width = 30,isStatistics=true,type = 10,dict = "type_1" )private Integer cnl;// isStatistics:true要合计 ，type=10表示数值@Excel(name = "代偿金额", width = 30,isStatistics=true,type = 10)private BigDecimal amt;}
3.生成Excel，多sheet
//贷款五级分类 假设有值List<RepayBackFlowTables> repayBackFlows1 =Lists.newArrayList();List<RepayBackFlowTables> repayBackFlows2 =Lists.newArrayList();ExportParams deptExportParams1 = new ExportParams();deptExportParams1.setTitle("贷款还款回流表");deptExportParams1.setSheetName("贷款还款回流表");deptExportParams1.setStyle(ExcelExportStatisticStyler.class);Map<String, Object> deptExportMap1 = new HashMap<>();deptExportMap1.put("title", deptExportParams1);deptExportMap1.put("entity", RepayBackFlow.class);deptExportMap1.put("data", repayBackFlows1);ExportParams deptExportParams2 = new ExportParams();deptExportParams2 .setTitle("贷款还款回流表");deptExportParams2 .setSheetName("贷款还款回流表");deptExportParams2 .setStyle(ExcelExportStatisticStyler.class);Map<String, Object> deptExportMap2 = new HashMap<>();deptExportMap2 .put("title", deptExportParams2);deptExportMap2 .put("entity", RepayBackFlow.class);deptExportMap2 .put("data", repayBackFlows2);List<Map<String, Object>> sheetsList = new ArrayList<>();sheetsList.add(deptExportMap1);sheetsList.add(deptExportMap2);
Workbook workBook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);workBook.write(fos);file = new File(filepath);
4.自定义金额样式
先定义一个styler类继承ExcelExportStylerDefaultImpl，重写getStyles方法，在ExportParams 时加上
deptExportParams1.setStyle(ExcelExportStatisticStyler.class);
public class ExcelExportStatisticStyler extends ExcelExportStylerDefaultImpl {private CellStyle numberCellStyle;private CellStyle numberCellStyle1;public ExcelExportStatisticStyler(Workbook workbook) {super(workbook);createNumberCellStyler();createNumberCellStyler1();}private void createNumberCellStyler(){numberCellStyle = workbook.createCellStyle();numberCellStyle.setAlignment(HorizontalAlignment.CENTER);numberCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);numberCellStyle.setDataFormat((short)BuiltinFormats.getBuiltinFormat("#,##0.00"));numberCellStyle.setWrapText(true);}private void createNumberCellStyler1(){numberCellStyle1 = workbook.createCellStyle();numberCellStyle1.setAlignment(HorizontalAlignment.CENTER);numberCellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);numberCellStyle1.setDataFormat((short)BuiltinFormats.getBuiltinFormat("#,##0"));numberCellStyle1.setWrapText(true);}@Overridepublic CellStyle getStyles(boolean noneStyler, ExcelExportEntity entity){if (entity != null&& 10==entity.getType()) {if("type_1".equals(entity.getDict())){return numberCellStyle1;}return numberCellStyle;}return super.getStyles(noneStyler, entity);}}
