查询数据库（或有条件），导出Excel表格

依赖

        <!-- poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.15</version>
        </dependency>

前端接收类

@Data
public class FlowWorkOrderQuery {
    @ApiModelProperty(value = "当前页码",required = true, example = "1")
    private Integer pageNum;
    @ApiModelProperty(value = "每页数据条数",required = true, example = "15")
    private Integer pageSize;
    @ApiModelProperty(value = "工单状态",required = false,example = "1")
    private Integer workOrderStatus;
    @ApiModelProperty(value = "工单编号",required = false,example = "20200702001")
    private String workOrderId;
    @ApiModelProperty(value = "工单名称",required = false,example = "厕所设备故障")
    private String workOrderName;
    @ApiModelProperty(value = "工单信息",required = false,example = "景区东侧厕所男卫生间氨气传感设备故障")
    private String workOrderInformation;
    @ApiModelProperty(value = "当前用户id",required = false,example = "49")
    private Integer currentUserID;

}

 PO实体类

package com.tengyun.model.po;

import lombok.Data;
import lombok.experimental.Accessors;

@Data
@Accessors(chain = true)
public class FlowWorkOrderPO {
    /**
     * 工单编号
     */
    private String workOrderId;

    /**
     * 工单名称
     */
    private String workOrderName;

    /**
     * 工单信息
     */
    private String workOrderInformation;

    /**
     * 工单来源
     */
    private String workOrderSource;

    /**
     * 工单类型Id
     */
    private Integer workOrderType;

    /**
     * 工单类型名称
     */
    private String orderTypeName;

    /**
     * 工单状态
     */
    private Integer workOrderStatus;

    /**
     * 告警原因
     */
    private String alarmReason;

    /**
     * 告警处理意见
     */
    private String alarmHandlingAdvice;

    /**
     * 工单产生时间
     */
    private String orderGenerationTime;

    /**
     * 派单时间
     */
    private String sendSingleTime;

    /**
     * 派单人id
     */
    private Integer sendSingleMenId;

    /**
     * 派单人
     */
    private String sendSingleMen;

    /**
     * 派单人备注
     */
    private String sendSingleNote;

    /**
     * 处理人id
     */
    private Integer handleMenId;

    /**
     * 处理人
     */
    private String handleMen;

    /**
     * 处理结果
     */
    private String handleResult;

    /**
     * 审核意见
     */
    private String auditOpinion;

    /**
     * 督办状态
     */
    private Integer workOrderOverseeState;

    /**
     * 处理现场图片材料
     */
    private String handleSceneMaterialPhotos;

    /**
     * 处理现场视频材料
     */
    private String handleSceneMaterialVideo;

    /**
     * 关联父工单编号
     */
    private String parentWorkId;

    /**
     * 处理单位ID
     */
    private Integer organId;

    /**
     * 处理单位名称
     */
    private String organName;

    /**
     * 处理时限
     */
    private String handleTime;

    public String getExcelWorkOrderType() {
        switch (workOrderStatus) {
            case 1:
                return "待派单";
            case 2:
                return "待确认";
            case 3:
                return "待提交";
            case 4:
                return "待审核";
            case 5:
                return "已关闭";

            default:
                return null;
        }
    }
}

 VO接收PO数据类

package com.tengyun.model.vo;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import javax.print.DocFlavor;
import java.util.List;
import java.util.Map;

@Data
public class FlowWorkOrderExcelVO {
    /**
     * 工单编号
     */
    @ApiModelProperty("工单编号")
    private String workOrderId;

    /**
     * 工单名称
     */
    @ApiModelProperty("工单名称")
    private String workOrderName;

    /**
     * 工单信息
     */
    @ApiModelProperty("工单信息")
    private String workOrderInformation;

    /**
     * 工单来源
     */
    @ApiModelProperty("工单来源")
    private String workOrderSource;

    /**
     * 工单类型Id
     */
/*    @ApiModelProperty("工单类型Id")
    private Integer workOrderType;*/

    /**
     * 工单类型名称
     */
    @ApiModelProperty("工单类型")
    private String orderTypeName;

    /**
     * 工单状态
     */
    @ApiModelProperty("工单状态")
    private String excelWorkOrderType;

    /**
     * 告警原因
     */
/*    @ApiModelProperty("告警原因")
    private String alarmReason;*/

    /**
     * 告警处理意见
     */
/*    @ApiModelProperty("告警处理意见")
    private String alarmHandlingAdvice;*/

    /**
     * 工单产生时间
     */
    @ApiModelProperty("工单产生时间")
    private String orderGenerationTime;

    /**
     * 派单时间
     */
    @ApiModelProperty("派单时间")
    private String sendSingleTime;

    /**
     * 派单人id
     */
/*    @ApiModelProperty("派单人id")
    private Integer sendSingleMenId;*/


    /**
     * 派单人
     */
    @ApiModelProperty("派单人")
    private String sendSingleMen;

    /**
     * 派单人备注
     */
    @ApiModelProperty("派单人备注")
    private String sendSingleNote;

    /**
     * 处理人id
     */
/*    @ApiModelProperty("处理人id")
    private Integer handleMenId;*/

    /**
     * 处理人
     */
    @ApiModelProperty("处理人")
    private String handleMen;

    /**
     * 处理结果
     */
    @ApiModelProperty("处理结果")
    private String handleResult;

    /**
     * 审核意见
     */
/*    @ApiModelProperty("审核意见")
    private String auditOpinion;*/

    /**
     * 督办状态
     */
/*    @ApiModelProperty("督办状态")
    private int workOrderOverseeState;*/

    /**
     * 审核结果
     */
/*    @ApiModelProperty("审核结果")
    private int  auditResult;*/

    /**
     * 处理现场图片材料
     */
/*    @ApiModelProperty("处理现场图片材料")
    private String handleSceneMaterialPhotos;*/

    /**
     * 处理现场视频材料
     */
/*    @ApiModelProperty("处理现场视频材料")
    private String handleSceneMaterialVideo;*/

    /**
     * 关联父工单编号
     */
    @ApiModelProperty("关联父工单编号")
    private String parentWorkId;

    /**
     * 部门Id
     */
    /*@ApiModelProperty("部门Id")
    private Integer organId;*/

    /**
     * 部门名称
     */
    @ApiModelProperty("处理单位")
    private String organName;
    /**
     * 处理人id列表
     */
    /*@ApiModelProperty("处理人id列表")
    private List handleMenIdList;*/

    /**
     * 审核人名字
     */
/*    @ApiModelProperty("审核人名字")
    private String auditName;*/

    /**
     * 关闭工单人名字
     */
/*    @ApiModelProperty("关闭工单人名字")
    private String closeName;*/

    /**
     * 处理人列表，仅作展示用
     */
/*    @ApiModelProperty("处理人列表，仅作展示用")
    private Map handlerList;*/

}

 controller

@Slf4j
@RestController
@RequestMapping(value="/api/v1/work_order/flow_work_order", produces = {"application/json;charset=UTF-8", "application/x-www-form-urlencoded"})
public class FlowWorkOrderController implements FlowWorkOrderApi {

    @Autowired
    private FlowWorkOrderService flowWorkOrderService;
    @Autowired
    private FlowProcessLogService flowProcessLogService;

    /**
     * 导出
     * 所有字段导出
     */
    @Override
    @PostMapping("/export_flow_work_order")
    public void exportFlowWorkOrder(HttpServletResponse response, @RequestBody FlowWorkOrderQuery query){
        flowWorkOrderService.exportFlowWorkOrder(response,query);
    }

swagger

@Api(value = "FlowWorkOrderController", tags = "FlowWorkOrderController")
public interface FlowWorkOrderApi {
    /**
     * 导出
     * 所有字段导出
     */
    @ApiOperation(value = "导出", notes = "导出", httpMethod = "POST")
    public void exportFlowWorkOrder(HttpServletResponse response, @RequestBody FlowWorkOrderQuery query);

service

void exportFlowWorkOrder(HttpServletResponse response, FlowWorkOrderQuery query);

serviceimpl

    /**
     * 导出
     *
     * @param response
     * @param query
     */
    @Override
    @SneakyThrows
    public void exportFlowWorkOrder(HttpServletResponse response, FlowWorkOrderQuery query) {
        //获取数据库
        List<FlowWorkOrderPO> flowWorkOrderPOS = new ArrayList<>();
        //查询数据后的结果
        flowWorkOrderPOS = flowWorkOrderMapper.newPageListFlowWorkOrder(query);
        //封装成excel数据，转化成IbarPointExcelVO
        List<FlowWorkOrderExcelVO> flowWorkOrderExcelVOS = flowWorkOrderPOS.stream().map(flowWorkOrderPO -> {
            FlowWorkOrderExcelVO flowWorkOrderExcelVO = new FlowWorkOrderExcelVO();
            BeanUtils.copyProperties(flowWorkOrderPO, flowWorkOrderExcelVO);
            return flowWorkOrderExcelVO;
        }).collect(Collectors.toList());
        //写入数据
        ExcelWriter writer = ExcelUtil.getBigWriter();
        writer.addHeaderAlias("workOrderId", "工单编号");
        writer.addHeaderAlias("workOrderSource", "工单来源");
        writer.addHeaderAlias("workOrderName", "工单名称");
        writer.addHeaderAlias("orderTypeName", "工单类型");
        writer.addHeaderAlias("workOrderInformation", "工单信息");
        writer.addHeaderAlias("parentWorkId", "关联父工单编号");
        writer.addHeaderAlias("excelWorkOrderType", "工单状态");
        writer.addHeaderAlias("orderGenerationTime", "工单产生时间");
        writer.addHeaderAlias("sendSingleMen", "派单人");
        writer.addHeaderAlias("sendSingleTime", "派单时间");
        writer.addHeaderAlias("sendSingleNote", "派单人备注");
        writer.addHeaderAlias("organName", "处理单位");
        writer.addHeaderAlias("handleMen", "处理人");
        writer.addHeaderAlias("handleResult", "处理结果");

        writer.write(flowWorkOrderExcelVOS);
        //设置响应头
        //response为HttpServletResponse对象
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
        String fileName = java.net.URLEncoder.encode("工单管理" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + ".xlsx", "UTF-8");
        response.setHeader("Content-Disposition", String.format("attachment;filename=%s", fileName));
        //out为OutputStream，需要写出到的目标流
        ServletOutputStream out = response.getOutputStream();
        writer.flush(out);
    }

stream()优点

无存储。stream不是一种数据结构，它只是某种数据源的一个视图，数据源可以是一个数组，Java容器或I/O channel等。
为函数式编程而生。对stream的任何修改都不会修改背后的数据源，比如对stream执行过滤操作并不会删除被过滤的元素，而是会产生一个不包含被过滤元素的新stream。
惰式执行。stream上的操作并不会立即执行，只有等到用户真正需要结果的时候才会执行。
可消费性。stream只能被“消费”一次，一旦遍历过就会失效，就像容器的迭代器那样，想要再次遍历必须重新生成。

apper

@Mapper
public interface FlowWorkOrderMapper {
List newPageListFlowWorkOrder(@Param("query") FlowWorkOrderQuery flowWorkOrderQuery);}

xml

    SELECT
    workorder.work_order_id,
    workorder.work_order_name,
    workorder.work_order_information,
    workorder.work_order_source,
    workorder.work_order_type,
    worktype.order_type_name,
    workorder.work_order_status,
    workorder.work_order_oversee_state,
    workorder.alarm_reason,
    workorder.alarm_handling_advice,
    workorder.order_generation_time,
    workorder.send_single_time,
    workorder.send_single_men_id,
    workorder.send_single_men,
    workorder.send_single_note,
    workorder.handle_men_id,
    workorder.handle_men,
    workorder.handle_result,
    workorder.handle_scene_material_photos,
    workorder.handle_scene_material_video,
    workorder.audit_opinion,
    workorder.organ_id,
    workorder.handle_time,
    organ.organ_name,
    relate.paraent_work_order_id parent_work_id
    FROM cm_flow_work_order workorder
    left join cm_work_order_type worktype
    on worktype.id = workorder.work_order_type
    left join sp_sys_organ organ
    on organ.id = workorder.organ_id
    left join cm_flow_relate_work_order relate
    on relate.relate_work_order_id = workorder.work_order_id
    
            and workorder.work_order_status = #{query.workOrderStatus}
        
            and workorder.work_order_id like CONCAT('%', #{query.workOrderId}, '%')
        
            and workorder.work_order_name like CONCAT('%', #{query.workOrderName}, '%')
        
            and workorder.work_order_information like CONCAT('%', #{query.workOrderInformation}, '%')
        
    order by workorder.order_generation_time desc
    LIMIT #{offset}, #{query.pageSize}

效果 前端接收