EasyExcel 筛选导出

最新推荐文章于 2024-08-20 18:04:32 发布

庭前云落

最新推荐文章于 2024-08-20 18:04:32 发布

阅读量1.1k

点赞数 2

分类专栏：学习记录文章标签： java spring 数据库 python sql

本文链接：https://blog.csdn.net/remsqks/article/details/119383584

版权

学习记录专栏收录该内容

37 篇文章 3 订阅

订阅专栏

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 是为了大家知道数据从哪里来的。