EasyExcel实现多Sheet导出

1、pom

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>

2、工具类

public class EasyExcelExportUtil {
    public static void exportMoreSheet(String fileName,
                                       HttpServletRequest request,
                                       HttpServletResponse response,
                                       List<SheetInfoBean> sheetInfoList){
        try {
//            response.reset();
            response.setHeader("Access-Control-Allow-Origin", "*");
            if (request.getMethod().equals("OPTIONS")) {
                response.addHeader("Access-Control-Allow-Methods", "GET,HEAD,POST,PUT,DELETE,TRACE,OPTIONS,PATCH");
                response.addHeader("Access-Control-Allow-Headers", "dnt,Origin, X-Requested-With,Content-Type, Accept, Authorization");
            }
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel");
            String encodedFileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encodedFileName);
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            WriteSheet writeSheet;
            for (SheetInfoBean bean : sheetInfoList) {
                // 构建sheet对象
                writeSheet = EasyExcel.writerSheet(bean.getSheetName()).head(bean.getHeadClass()).build();
                // 写出sheet数据
                excelWriter.write(bean.getDataList(), writeSheet);
            }
//            // 关流
            excelWriter.finish();
        } catch (IOException e) {
            // do something you want
        }

    }
}

public class SheetInfoBean {

    /**
     * sheet页名称
     */
    private String sheetName;

    /**
     * sheet标题bean
     */
    private Class<?> headClass;

    /**
     * sheet页数据
     */
    private List<?> dataList;

    public SheetInfoBean() {
    }

    public SheetInfoBean(String sheetName, Class<?> headClass, List<?> dataList) {
        this.sheetName = sheetName;
        this.headClass = headClass;
        this.dataList = dataList;
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public Class<?> getHeadClass() {
        return headClass;
    }

    public void setHeadClass(Class<?> headClass) {
        this.headClass = headClass;
    }

    public List<?> getDataList() {
        return dataList;
    }

    public void setDataList(List<?> dataList) {
        this.dataList = dataList;
    }

    @Override
    public String toString() {
        return "SheetInfoBean{" +
                "sheetName='" + sheetName + '\'' +
                ", headClass=" + headClass +
                ", dataList=" + dataList +
                '}';
    }
}

3、实体类注解

@Data
@ColumnWidth(20)
// 设置全局内容居中
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
public class RealTimeMonitorResultVo {
    /**
     * 是否在线
     */
    @ExcelProperty(value = {"全局监控列表","是否在线"},order = 1)
    private String isonlineStr;

    /**
     * 车牌号
     */
    @ExcelProperty(value = {"全局监控列表","车牌号"},order = 2)
    private String carNum;

    /**
     * 排放阶段名称
     */
    @ExcelProperty(value = {"全局监控列表","排放阶段名称"},order = 3)
    @ApiModelProperty(value="排放阶段名称")
    private String emissionName;

    /**
     * 监测状态
     */
    @ExcelProperty(value = {"全局监控列表","监测状态"},order = 4)
    private String monitorStatusStr;
}

4、将从数据库查出来的数据转Vo

    @RequestMapping(value = "/export-general-monitor-list", method = RequestMethod.POST)
    @ResponseBody
    @ApiOperation(value = "导出全局监控列表")
    @Permission()
    public void exportGeneralMonitorList(@RequestBody RealTimeMonitorParam realTimeMonitorParam, HttpServletRequest request, HttpServletResponse response) {
        logger.info("导出全局监控列表信息:登陆用户=[{}],查询条件=[{}]", getCurrentUserId(), null);

        realTimeMonitorParam.setOrgPath(ShiroKit.getUser().getOrgPath());
        List<RealTimeMonitorResult> realTimeMonitorResults = monitorService.exportForMonitorList(realTimeMonitorParam);
        List<RealTimeMonitorResultVo> result = realTimeMonitorResults.stream().map(realTimeMonitorResult -> {
            RealTimeMonitorResultVo realTimeMonitorResultVo = new RealTimeMonitorResultVo();
            BeanUtils.copyProperties(realTimeMonitorResult, realTimeMonitorResultVo);
            realTimeMonitorResultVo.setIsonlineStr(realTimeMonitorResult.getIsonline() ? "在线" : "离线");
            realTimeMonitorResultVo.setMonitorStatusStr(realTimeMonitorResult.getMonitorStatus() ? "正常" : "异常");
            return realTimeMonitorResultVo;
        }).collect(Collectors.toList());
        //时间戳
        long l = System.currentTimeMillis();
        List<SheetInfoBean> sheetInfoBeanList = new ArrayList<>();
        SheetInfoBean sheetInfoBean1 = new SheetInfoBean("导出全局监控列表信息1", RealTimeMonitorResultVo.class, result);
        SheetInfoBean sheetInfoBean2 = new SheetInfoBean("导出全局监控列表信息2", RealTimeMonitorResultVo.class, result);
        sheetInfoBeanList.add(sheetInfoBean1);
        sheetInfoBeanList.add(sheetInfoBean2);
        EasyExcelExportUtil.exportMoreSheet("全局监控列表",request,response,sheetInfoBeanList);
    }

5、结果