easypoi基于springboot实现多sheet导出

maven

<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.4.0</version>
        </dependency>

service

        Map<String,Object> map=new HashMap<>();
        LocalDateTime beginTime=body.getBeginTime();
        LocalDateTime endTime=body.getEndTime();
        if(Objects.isNull(beginTime)&&Objects.isNull(endTime)){
            beginTime=LocalDateTime.of(LocalDate.from(LocalDateTime.now().with(TemporalAdjusters.firstDayOfMonth())), LocalTime.MIN);
            endTime=LocalDateTime.of(LocalDate.from(LocalDateTime.now().with(TemporalAdjusters.lastDayOfMonth())), LocalTime.MAX);
        }
        map.put("beginTime", beginTime);
        map.put("endTime",endTime);
        //报警类型统计
        map.put("groupByColumn","alarm_type");
        List<AlarmStatisticsVo> alarmTypeStatistics=statisticsByAlarmType(map);
        List<ExcelAlarm> excelAlarmList=new ArrayList<>();
        alarmTypeStatistics.stream().forEach(t->{
            ExcelAlarm excelAlarm=new ExcelAlarm();
            excelAlarm.setAlarmTypeName(t.getAlarmTypeName());
            excelAlarm.setAlarmCount(t.getAlarmCount());
            excelAlarmList.add(excelAlarm);
        });
        List<Map<String, Object>> exportData = new ArrayList<>();
        Map<String, Object> excelAlarmMap = WorkBookUtils.createOneSheet("报警类型", "报警类型统计", ExcelAlarm.class, excelAlarmList);
        exportData.add(excelAlarmMap);
        //报警人员类型统计
        map.put("groupByColumn","person_type");
        List<AlarmStatisticsVo> personTypeStatistics=statisticsByPersonType(map);
        List<ExcelPerson> excelPersonList=new ArrayList<>();
        personTypeStatistics.stream().forEach(t->{
            ExcelPerson excelPerson=new ExcelPerson();
            excelPerson.setPersonTypeName(t.getPersonTypeName());
            excelPerson.setAlarmCount(t.getAlarmCount());
            excelPersonList.add(excelPerson);
        });
        Map<String, Object> excelPersonMap = WorkBookUtils.createOneSheet("报警人员统计", "报警人员统计", ExcelPerson.class, excelPersonList);
        exportData.add(excelPersonMap);
        //报警高发区域统计
        map.put("groupByColumn","layer_id");
        map.put("top",Objects.isNull(body.getTop())?6:body.getTop());
        List<ExcelLayer> excelLayerList=new ArrayList<>();
        statisticsByLayerId(map).stream().forEach(t->{
            ExcelLayer excelLayer=new ExcelLayer();
            excelLayer.setLayerId(t.getLayerId());
            excelLayer.setAlarmCount(t.getAlarmCount());
            excelLayerList.add(excelLayer);
        });
        Map<String, Object> excelLayerMap = WorkBookUtils.createOneSheet("高发报警区域统计", "高发报警区域统计", ExcelLayer.class, excelLayerList);
        exportData.add(excelLayerMap);
        //报警数量走势统计
        List<ExcelTrend> excelTrendList=new ArrayList<>();
        alarmMapper.statisticsAlarmByDate(map).stream().forEach(t->{
            ExcelTrend excelTrend=new ExcelTrend();
            excelTrend.setAlarmDate(t.getAlarmDate());
            excelTrend.setAlarmTypeName(t.getAlarmTypeName());
            excelTrend.setAlarmCount(t.getAlarmCount());
            excelTrendList.add(excelTrend);
        });
        Map<String, Object> excelTrendMap = WorkBookUtils.createOneSheet("报警数量走势统计", "报警数量走势统计", ExcelTrend.class, excelTrendList);
        exportData.add(excelTrendMap);
        try {
            Workbook workbook = ExcelExportUtil.exportExcel(exportData, ExcelType.XSSF);
            File savefile = new File("D:/excel/");
            if (!savefile.exists()) {
                savefile.mkdirs();
            }
            FileOutputStream fos = new FileOutputStream("D:/excel/报警统计233.xls");
            workbook.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

utils

package com.xrkc.monitor.utils;

import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description:
 * @Auther: wangjiao
 * @Date: 2021-08-03 13:38
 */
public class WorkBookUtils {
    /**
     * 创建sheet
     * @param sheetName sheet名称
     * @param title 表格标题
     * @param clazz 类型
     * @param data 数据
     * @return
     */
    public static Map<String, Object> createOneSheet(String sheetName,String title,Class<?> clazz,List<?> data){
        ExportParams exportParams = new ExportParams(title,sheetName, ExcelType.XSSF);
        Map<String, Object> map = new HashMap<>();
        map.put("title",exportParams);
        map.put("entity", clazz);
        map.put("data",data);
        return map;
    }
}

vo

package com.xrkc.monitor.service.excel;

import cn.afterturn.easypoi.excel.annotation.Excel;

/**
 * @Description:
 * @Auther: wangjiao
 * @Date: 2021-08-03 14:41
 */
public class ExcelAlarm {
    @Excel(name = "报警类型",width = 30)
    private String alarmTypeName;

    @Excel(name = "报警数量", width = 30)
    private Integer alarmCount;

    public String getAlarmTypeName() {
        return alarmTypeName;
    }

    public void setAlarmTypeName(String alarmTypeName) {
        this.alarmTypeName = alarmTypeName;
    }

    public Integer getAlarmCount() {
        return alarmCount;
    }

    public void setAlarmCount(Integer alarmCount) {
        this.alarmCount = alarmCount;
    }
}

如果直接导出到浏览器

 try {
            response.setContentType("application/vnd.ms-excel");
            StringBuilder fileName = new StringBuilder().append("报警统计").append("_")
                    .append(LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"))).append(".xls");
            response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(fileName.toString(), "UTF-8"));
            ServletOutputStream out = response.getOutputStream();
            workbook.write(out);
            out.flush();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
            log.error("excel导出报警统计失败：", e.getMessage());
        }