依赖
<!-- 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}
效果 前端接收