EasyExcel 筛选导出

admin管理员组

文章数量:1516870

EasyExcel 筛选导出

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 {@Resourceprivate 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_NAMEFROM CASE_BASIC_INFO CBILEFT JOIN ACCUSED_INFO ACCUSEDON CBI.OID = ACCUSED.BUSINESS_IDLEFT JOIN ACCUSER_INFO ACCUSERON CBI.OID = ACCUSER.BUSINESS_IDWHERE 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 是为了大家知道数据从哪里来的。

本文标签： EasyExcel 筛选导出