EasyExcel官网

讯享网

条件参数对象

/ * @Author 庭前云落 * @Date 2021/8/3 13:55 * @Description */ @Data @ApiModel(value = "案件模块查询条件参数对象", description = "") public class CaseBasicQueryInfo { @ApiModelProperty(value = "当前页码") private Integer pageNum; @ApiModelProperty(value = "每页条数") private Integer pageSize; @ApiModelProperty(value = "受理开始时间") private String startAcceptanceDate; @ApiModelProperty(value = "受理结束时间") private String endAcceptanceDate; @ApiModelProperty(value = "登记开始时间") private String startRegisterDate; @ApiModelProperty(value = "登记结束时间") private String endRegisterDate; @ApiModelProperty(value = "案件来源") private String caseResourceName; @ApiModelProperty(value = "案件标题") private String caseTitle; @ApiModelProperty(value = "被反映人姓名") private String accusedName; @ApiModelProperty(value = "案件编码、被反映人对象职级") private String multipleConditions; @ApiModelProperty(value = "用户筛选的列字段集合") private List<String> columnList; } 

全部导出字段的封装VO

讯享网/ * @Author 庭前云落 * @Date 2021/8/3 14:07 * @Description */ @Data //内容行高 @ContentRowHeight(15) //标题行高 @HeadRowHeight(20) //单元格宽度 @ColumnWidth(25) @ApiModel(value="案件台账数据传输对象", description="") public class CaseBasicQueryList implements Serializable { //@ExcelProperty("状态") //@ApiModelProperty(value = "当前办理阶段代码/状态") //@TableField("DEAL_FLAG") //private String dealFlag; @ExcelProperty("状态") @ApiModelProperty(value = "当前办理阶段代码值") @TableField("DEAL_FLAG_NAME") private String dealFlagName; @ApiModelProperty(value = "案件标题（加密）") @TableField("CASE_TITLE") @ExcelProperty("案件标题") private String caseTitle; @ApiModelProperty(value = "案件来源代码值") @TableField("CASE_RESOURCE_NAME") @ExcelProperty("案件来源") private String caseResourceName; @ApiModelProperty(value = "案件编码") @TableField("CASE_NO") @ExcelProperty("案件编码") private String caseNo; @ApiModelProperty(value = "受理线索编码") @TableField("DISPOSITION_NO") @ExcelProperty("受理线索编码") private Date dispositionNo; @ApiModelProperty(value = "**举报编码") @TableField("PETITION_NO") @ExcelProperty("**举报编码") private String petitionNo; @ApiModelProperty(value = "受理日期") @TableField("ACCEPTANCE_DATE") @ExcelProperty("受理日期") private Date acceptanceDate; @ApiModelProperty(value = "登记日期") @TableField("REGISTER_DATE") @ExcelProperty("登记日期") private Date registerDate; @ApiModelProperty(value = "监委立案机关/纪委立案机关") @TableField("JIANWEI_DEPT_NAME") @ExcelProperty("监委立案机关/纪委立案机关") private String jianweiDeptName; @ApiModelProperty(value = "监委立案时间/纪委立案时间") @TableField("JIANWEI_CREATE_DATE") @ExcelProperty("监委立案时间/纪委立案时间") private Date jianweiCreateDate; @ApiModelProperty(value = "被反映人姓名(加密)") @TableField("ACCUSED_NAME") @ExcelProperty("被反映人姓名") private String accusedName; @ApiModelProperty(value = "被反映对象职级代码值") @TableField("RANK_NAME") @ExcelProperty("职级") private String rankName; @ApiModelProperty(value = "是否署名 1是0否") @TableField("IS_ANONYMOUS_FLAG") @ExcelProperty("是否署名") private String isAnonymousFlag; @ApiModelProperty(value = "反映人名称(加密)") @TableField("ACCUSER_NAME") @ExcelProperty("反映人姓名") private String accuserName; } 

Controller接口

/ * <p> * 案件管理模块Controller接口 * </p> * * @author WangTingWei * @since 2021-07-27 */ @Slf4j @RestController @RequestMapping("/caseBasicInfo") @Api(tags = "案件基本信息接口") public class CaseBasicInfoController { @Resource private CaseBasicInfoService caseBasicInfoService; / * 导出案件台账信息 * @return */ @ApiOperation(value = "导出案件台账信息") @RequestMapping(value = "/exportCaseBasicInfoList", method = RequestMethod.GET) @LogAnnotation("导出案件台账信息") public void exportCaseBasicInfoList(@RequestBody(required=false) CaseBasicQueryInfo caseBasicQueryInfo, HttpServletResponse response) { try { //指定需要导出的列 Set<String> includeColumnFiledNames = new HashSet<String>(); caseBasicQueryInfo.getColumnList().stream().forEach(column->{ includeColumnFiledNames.add(column); }); List<CaseBasicQueryList> queryListInfo = caseBasicInfoService.exportCaseBasicInfoList(caseBasicQueryInfo); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); String fileName = URLEncoder.encode("案件台账", "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(), CaseBasicQueryList.class).includeColumnFiledNames(includeColumnFiledNames).sheet("案件台账").doWrite(queryListInfo); } catch (Exception e) { log.error("导出案件台账信息异常",e); } } } 

底层的查询

讯享网 <select id="exportCaseBasicInfoList" resultType="com.sinosoft.system.casebasicinfo.entity.CaseBasicQueryList"> SELECT CBI.DEAL_FLAG_NAME,CBI.CASE_TITLE,CBI.CASE_RESOURCE_NAME,CBI.CASE_NO,CBI.DISPOSITION_NO,CBI.PETITION_NO, CBI.ACCEPTANCE_DATE,CBI.REGISTER_DATE,CBI.JIANWEI_DEPT_NAME,CBI.JIANWEI_CREATE_DATE, ACCUSED.ACCUSED_NAME,ACCUSED.RANK_NAME, ACCUSER.IS_ANONYMOUS_FLAG,ACCUSER.ACCUSER_NAME FROM CASE_BASIC_INFO CBI LEFT JOIN ACCUSED_INFO ACCUSED ON CBI.OID = ACCUSED.BUSINESS_ID LEFT JOIN ACCUSER_INFO ACCUSER ON CBI.OID = ACCUSER.BUSINESS_ID WHERE 1=1 AND CBI.DEAL_FLAG IN ('33','35','36','37') <if test="caseBasicQueryInfo != null "> <!--受理时间--> <if test="caseBasicQueryInfo.startAcceptanceDate!= null "> and CBI.ACCEPTANCE_DATE between #{caseBasicQueryInfo.startAcceptanceDate} </if> <if test="caseBasicQueryInfo.endAcceptanceDate!= null "> and #{caseBasicQueryInfo.endAcceptanceDate} </if> <!--登记时间--> <if test="caseBasicQueryInfo.startRegisterDate!= null "> and CBI.REGISTER_DATE between #{caseBasicQueryInfo.startRegisterDate} </if> <if test="caseBasicQueryInfo.endRegisterDate!= null "> and #{caseBasicQueryInfo.endAcceptanceDate} </if> <!--案件来源--> <if test="caseBasicQueryInfo.caseResourceName!= null "> and CBI.CASE_RESOURCE_NAME LIKE '%${caseBasicQueryInfo.endAcceptanceDate}%' </if> <!--案件标题--> <if test="caseBasicQueryInfo.caseTitle!= null "> and CBI.CASE_TITLE LIKE '%${caseBasicQueryInfo.caseTitle}%' </if> <if test="caseBasicQueryInfo.accusedName!= null "> and ACCUSED.ACCUSED_NAME LIKE '%${caseBasicQueryInfo.accusedName}%' </if> <if test="caseBasicQueryInfo.multipleConditions!= null "> and CBI.CASE_NO LIKE '%${caseBasicQueryInfo.multipleConditions}%' or ACCUSED.RANK_NAME LIKE '%${caseBasicQueryInfo.multipleConditions}%' </if> ORDER BY cbi.CREATE_TIME DESC </if> </select> 

主要还是Controller里面的处理，跟官网的没区别。SQL没必要看，贴 SQL 是为了大家知道数据从哪里来的。