上代码复制粘贴可用

1.定义导出模型类的枚举:

code:行为参数在接口参数中传递,表示你要导出什么数据 name:模型类的全路径 className:类名

**注:所有模型类必须继承公共父类(```

@Data public class BaseDto { }

public enum ExportExcelEnum {

CUR_BILL(1, "com.sunjinke.ncmise.entity.dto.lrquery.LoanQueryModel", "LoanQueryModel"),
TOTAL(2, "com.sunjinke.ncmise.entity.dto.lrquery.CancelQueryModel", "CancelQueryModel"),
BILL(3, "com.sunjinke.ncmise.entity.dto.lrquery.RepayQueryModel", "RepayQueryModel");


private Integer code;
private String name;
private String className;


public Integer getCode() {
    return code;
}

public String getName() {
    return name;
}

public String getClassName() {
    return className;
}

public static String geExportExcelEnumObject(Integer openStart) {
    for (ExportExcelEnum value : ExportExcelEnum.values()) {
        if (openStart == value.getCode()) {
            return value.getName();
        }
    }
    return null;
}

public static String geExportExcelEnumClass(Integer openStart) {
    for (ExportExcelEnum value : ExportExcelEnum.values()) {
        if (openStart == value.getCode()) {
            return value.getClassName();
        }
    }
    return null;
}

ExportExcelEnum(Integer code, String name, String className) {
    this.code = code;
    this.name = name;
    this.className = className;

}

}

2.编写导出表格工具类 (com.alibaba.excel)

/**

@author GG
@title: EasyExcelUtils
@projectName cec-moutai-bd-display
@description: easyExcel工具类
@date 2019/12/24 11:35 */ @Slf4j public class EasyExcelUtils { private EasyExcelUtils() { }

@SuppressWarnings("rawtypes") public static void webWriteExcel(HttpServletResponse response, List objects, Class clazz, String fileName) throws IOException { String sheetName = fileName; webWriteExcel(response, objects, clazz, fileName, sheetName); }

@SuppressWarnings("rawtypes") public static void webWriteExcel(HttpServletResponse response, List objects, Class clazz, String fileName, String sheetName) throws IOException { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xls"); ServletOutputStream outputStream = response.getOutputStream(); try { EasyExcelFactory.write(response.getOutputStream(), clazz).registerWriteHandler(new ExcelWriteHandler()).sheet(sheetName).doWrite(objects); } catch (Exception e) { log.info("导出失败，{}", e); e.printStackTrace(); } finally { outputStream.close(); } }

public static void createExcel(ByteArrayOutputStream out, List<?> data) throws IOException { try { WriteWorkbook writeWorkbook = new WriteWorkbook(); writeWorkbook.setExcelType(ExcelTypeEnum.XLSX); writeWorkbook.setOutputStream(out); writeWorkbook.setInMemory(Boolean.TRUE); ExcelWriter writer = new ExcelWriter(writeWorkbook); WriteTable writeTable = new WriteTable(); writeTable.setTableNo(0); writeTable.setClazz(HeadField.class); writeTable.setUseDefaultStyle(Boolean.TRUE); writeTable.setRelativeHeadRowIndex(0); writeTable.setNeedHead(Boolean.TRUE); WriteSheet writeSheet = new WriteSheet(); writeSheet.setSheetNo(0); writer.write(data, writeSheet, writeTable); writer.finish(); out.flush(); } finally { if (out != null) { out.close(); } } }

private static List<List> getExcelHead(List tableHeadList) { List<List> head = new ArrayList<List>(); for (String s : tableHeadList) { List column = new ArrayList(); column.add(s); head.add(column); } return head; }

}

3.控制层编写

@ApiOperation(value = "放款明细导出，核销明细导出，还款明细导出", notes = "放款明细导出，核销明细导出，还款明细导出", httpMethod = "POST") @PostMapping("/exportExcel") public void exportExcel(@Validated @RequestBody LrQueryExportVo lrQueryExportVo, HttpServletRequest request, HttpServletResponse response) throws IOException, ClassNotFoundException { List<? extends BaseDto> exportList = gLmRepaymentRecordService.loanListExport(lrQueryExportVo, tokenVo); Optional.ofNullable(exportList).orElseThrow(() -> new NcmisException("数据不存在")); if (exportList.size() <= 0) { throw new NcmisException("数据为空"); } String classpath = Constants.ExportExcelEnum.geExportExcelEnumObject(lrQueryExportVo.getAction()); EasyExcelUtils.webWriteExcel(response, exportList, Class.forName(classpath), DateUtils.getFormatedDate(new Date(), "yyyyMMdd HHmmss")); }

4.实现类

/**

导出表格
@param lrQueryExportVo
@return */ @Override public List<? extends BaseDto> loanListExport(LrQueryExportVo lrQueryExportVo, UrlAndTokenVo tokenVo) { String classpath = Constants.ExportExcelEnum.geExportExcelEnumObject(lrQueryExportVo.getAction()); Optional.ofNullable(classpath).orElseThrow(() -> new NcmisException("导出类型错误")); lrQueryExportVo.setOrgCode(tokenVo.getOrgCode()); lrQueryExportVo.setCertCode(tokenVo.getCreditCode()); ListDataFun<LrQueryExportVo, LoanQueryModel> loanQuery = (param) -> functionExcelActuator.loanQuery(param); ListDataFun<LrQueryExportVo, CancelQueryModel> cancelQuery = (param) -> functionExcelActuator.cancelQuery(param); ListDataFun<LrQueryExportVo, RepayQueryModel> repayQuery = (param) -> functionExcelActuator.repayQuery(param); //此处可以添加函数方法导出数据模型 return functionExcelActuator.execResult(loanQuery, cancelQuery, repayQuery, lrQueryExportVo); }

5.具体数据查询执行类

所有查询数据都写在此类中

/**

表格函数执行 */ @Component public class FunctionExcelActuator {

@Resource GIouInfoMapper gIouInfoMapper;

@Resource GCancelRecordMapper gCancelRecordMapper;

@Resource LmRepaymentRecordMapper lmRepaymentRecordMapper;

/**
- 执行结果
- @param loanQuery
- @param cancelQuery
- @param repayQuery
- @param lrQueryExportVo
- @return */ public List<? extends BaseDto> execResult(ListDataFun<LrQueryExportVo, ? extends BaseDto> loanQuery, ListDataFun<LrQueryExportVo, ? extends BaseDto> cancelQuery, ListDataFun<LrQueryExportVo, ? extends BaseDto> repayQuery, LrQueryExportVo lrQueryExportVo) { switch (lrQueryExportVo.getAction()) { case 1: return loanQuery.exec(lrQueryExportVo); case 2: return cancelQuery.exec(lrQueryExportVo); case 3: return repayQuery.exec(lrQueryExportVo); default: return null; } }
/**
- 查询放款查询
- @param lrQueryExportVo
- @return */ public List loanQuery(LrQueryExportVo lrQueryExportVo) { List gIouInfos = null; if (lrQueryExportVo.getIds().length > 0) { gIouInfos = gIouInfoMapper.selectBatchIds(Arrays.asList(lrQueryExportVo.getIds())); } else { //查询条件组装 QueryWrapper wrapper = new QueryWrapper<>(); wrapper.eq("LMT_CORP_NO", lrQueryExportVo.getOrgCode()); wrapper.between(lrQueryExportVo.getStartDate() != null && StringUtils.isNotBlank(lrQueryExportVo.getStartDate()) && lrQueryExportVo.getEndDate() != null && StringUtils.isNotBlank(lrQueryExportVo.getEndDate()), "PAY_TIME", lrQueryExportVo.getStartDate(), lrQueryExportVo.getEndDate()); wrapper.orderByAsc("PAY_TIME"); gIouInfos = gIouInfoMapper.selectList(wrapper); } return BeanMapperUtil.mapList(gIouInfos, LoanQueryModel.class); }
/**
- 核销明细
- @param lrQueryExportVo
- @return */ public List cancelQuery(LrQueryExportVo lrQueryExportVo) { List gCancelRecords = null; if (lrQueryExportVo.getIds().length > 0) { gCancelRecords = gCancelRecordMapper.selectBatchIds(Arrays.asList(lrQueryExportVo.getIds())); } else { //查询条件组装 QueryWrapper wrapper = new QueryWrapper<>(); wrapper.eq("LMT_CORP_NO", lrQueryExportVo.getOrgCode()); wrapper.between(lrQueryExportVo.getStartDate() != null && StringUtils.isNotBlank(lrQueryExportVo.getStartDate()) && lrQueryExportVo.getEndDate() != null && StringUtils.isNotBlank(lrQueryExportVo.getEndDate()), "REAL_REPAY_DATE", lrQueryExportVo.getStartDate(), lrQueryExportVo.getEndDate()); wrapper.orderByAsc("REAL_REPAY_DATE"); gCancelRecords = gCancelRecordMapper.selectList(wrapper); } return BeanMapperUtil.mapList(gCancelRecords, CancelQueryModel.class); }
/**
- 资金明细
- @param lrQueryExportVo
- @return */ public List repayQuery(LrQueryExportVo lrQueryExportVo) { List lmRepaymentRecords = null; if (lrQueryExportVo.getIds().length > 0) { lmRepaymentRecords = lmRepaymentRecordMapper.selectBatchIds(Arrays.asList(lrQueryExportVo.getIds())); } else { //查询条件组装 QueryWrapper wrapper = new QueryWrapper<>(); wrapper.eq("CERT_CODE", lrQueryExportVo.getCertCode()); wrapper.between(lrQueryExportVo.getStartDate() != null && StringUtils.isNotBlank(lrQueryExportVo.getStartDate()) && lrQueryExportVo.getEndDate() != null && StringUtils.isNotBlank(lrQueryExportVo.getEndDate()), "REPAYMENT_TIME", lrQueryExportVo.getStartDate(), lrQueryExportVo.getEndDate()); wrapper.orderByAsc("REPAYMENT_TIME"); lmRepaymentRecords = lmRepaymentRecordMapper.selectList(wrapper); } return BeanMapperUtil.mapList(lmRepaymentRecords, RepayQueryModel.class); }

}

6.请求参数

@ApiModel("放还款查询记录导出请求对象") @Data public class LrQueryExportVo implements Serializable { @ApiModelProperty("放还款记录主键ID，传 [] 表示全部") private String[] ids; @ApiModelProperty("导出列表类型 1 放款列表 2 还款明细 3 资金明细") @NotNull private Integer action; @ApiModelProperty("交易开始日期格式YYYY-MM-DD") private String startDate; @ApiModelProperty("交易结束日期格式YYYY-MM-DD") private String endDate; @ApiModelProperty("客户标示不用传") private String orgCode; @ApiModelProperty(" 社会信用代码不用传") private String certCode; }

7.总结

实现思路:

1.通过接口中传递的参数区分导出数据的模型类,和找到相应的函数方法 2.所有的查数据方法的都是通过函数式编程调用 3.导出的数据模型都是通过参数中传递的行为参数找到数据类,通过反射找到类的字节码

写一个接口便可以导出任意类型的数据,有什么好改造点,欢迎沟通

java一个接口让导出表格变得简单,只需添加一个模型类即可