Easypoi导出excel_easypoi 导出excel-CSDN博客

本文链接：https://blog.csdn.net/yigan123/article/details/123573204

简单excel导出
第一步：导入依赖

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

第二部：构建导出excel实体类

@Data
@ExcelTarget("alarmTrendExcel")
public class AlarmStatisticsDto {
    /**
     * 时间
     */
    @Excel(name = "时间",width = 30,needMerge = true)
    private String warningTime;
    /**
     * 报警数量
     */
    @Excel(name = "报警量",width = 30,needMerge = true)
    private int warningCount;
}
@Data
@NoArgsConstructor
@AllArgsConstructor
@ExcelTarget("alarmPositionExcel")
public class AlarmPositionDto {

    /**
     * 所属地区编码
     */
    private String deviceAreaCode;
    /**
     * 所属地区名
     */
    @Excel(name = "所属区域",width = 30,needMerge = true)
    private String deviceArea;
    /**
     * 报警数量
     */
    @Excel(name = "报警量",width = 30,needMerge = true)
    private int warningCount;

    /**
     * 开始时间
     */
    @Excel(name = "开始时间",width = 30,needMerge = true)
    private String startTime;

    /**
     * 结束时间
     */
    @Excel(name = "结束时间",width = 30,needMerge = true)
    private String endTime;
}
@Data
@NoArgsConstructor
@AllArgsConstructor
@ExcelTarget("bizProportionDto")
public class BizProportionDto {
    /**
     * 业务名对应code值
     */
    private String pcode;
    /**
     * 业务名
     */
    @Excel(name = "业务名称",width = 30,needMerge = true)
    private String name;
    /**
     * 数量
     */
    @Excel(name = "报警次数",width = 30,needMerge = true)
    private int warningCount;
    /**
     * 占比
     */
    @Excel(name = "占比",width = 30,needMerge = true)
    private String proportion;

    /**
     * 开始时间
     */
    @Excel(name = "开始时间",width = 30,needMerge = true)
    private String startTime;

    /**
     * 结束时间
     */
    @Excel(name = "结束时间",width = 30,needMerge = true)
    private String endTime;
}
@Data
@NoArgsConstructor
@AllArgsConstructor
@ExcelTarget("deviceProportionDto")
public class DeviceProportionDto {
    /**
     * 设备名code
     */
    private String pcode;
    /**
     * 设备名
     */
    @Excel(name = "设备名称",width = 30,needMerge = true)
    private String name;
    /**
     * 数量
     */
    @Excel(name = "报警次数",width = 30,needMerge = true)
    private int warningCount;
    /**
     * 占比
     */
    @Excel(name = "占比",width = 30,needMerge = true)
    private String proportion;
    /**
     * 开始时间
     */
    @Excel(name = "开始时间",width = 30,needMerge = true)
    private String startTime;

    /**
     * 结束时间
     */
    @Excel(name = "结束时间",width = 30,needMerge = true)
    private String endTime;
}

第三步：导出

 public ApiResponse export(AlarmStatisticsVo alarmStatisticsVo, HttpServletResponse response) throws ClassNotFoundException {
        Workbook workBook = null;
        // 将sheet1、sheet2、sheet3使用得map进行包装
        List<Map<String, Object>> sheetsList = new ArrayList<>();
        //sheet1
        List<AlarmStatisticsDto> alarmTrendData = alarmStatisticsService.getAlarmTrendExcelData(alarmStatisticsVo);
        Map<String, Object> trendSheet = EasyPoiSheetCreaterUtil.getSheet(AlarmStatisticsDto.class,alarmTrendData, "sheet1");
        sheetsList.add(trendSheet);
        //sheet2
        List<AlarmPositionDto> alarmPositionDtos = alarmStatisticsService.getAlarmExcelPositions(alarmStatisticsVo);
        Map<String, Object> positionSheet = EasyPoiSheetCreaterUtil.getSheet(AlarmPositionDto.class,alarmPositionDtos, "sheet2");
        sheetsList.add(positionSheet);
        //sheet3
        List<DeviceProportionDto> deviceProportionDtos = alarmStatisticsService.getAlarmDeviceExcelData(alarmStatisticsVo);
        Map<String, Object> deviceSheet = EasyPoiSheetCreaterUtil.getSheet(DeviceProportionDto.class,deviceProportionDtos, "sheet3");
        sheetsList.add(deviceSheet);
        //sheet4
        List<BizProportionDto> bizProportionDtos = alarmStatisticsService.getAlarmBizExcelData(alarmStatisticsVo);
        Map<String, Object> bizSheet = EasyPoiSheetCreaterUtil.getSheet(BizProportionDto.class,bizProportionDtos, "sheet4");
        sheetsList.add(bizSheet);
        workBook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
        // 设置excel的文件名称
        String excelName;
        // 当前日期，用于导出文件名称
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        Integer linkId = alarmStatisticsVo.getLinkId();
        if (ObjectUtils.isEmpty(linkId)){
            excelName = "文件名" + "-" + sdf.format(new Date());
        }else {
            Link linkDetailsById = linkService.getLinkDetailsById(linkId);
            excelName= linkDetailsById.getLinkName() + "-" + sdf.format(new Date());
        }
        // 重置响应对象
        response.reset();
        // 指定下载的文件名--设置响应头
        response.setHeader("Content-Disposition", "attachment;filename=" + new String((excelName + ".xls").getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        // 写出数据输出流到页面
        try {
            OutputStream output = response.getOutputStream();
            BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
            workBook.write(bufferedOutPut);
            bufferedOutPut.flush();
            bufferedOutPut.close();
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return “success”;
    }

构建sheet 工具抽取

public class EasyPoiSheetCreaterUtil {

    /**
     * easypoi导出excel构建sheet
     *
     * @param object  导出实体的类
     * @param recordList  导出数据list
     * @param sheetName sheet名称
     * @return
     * @throws ClassNotFoundException
     */
    public static Map<String, Object> getSheet(Object object, List<? extends Object> recordList, String sheetName) throws ClassNotFoundException {
        // 创建sheet1使用得map
        Map<String, Object> exportMap = new HashMap<>();
        // 创建参数对象（用来设定excel得sheet得内容等信息）
        ExportParams exportParams = new ExportParams();
        // 设置sheet得名称
        exportParams.setSheetName(sheetName);
        // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
        exportMap.put("title", exportParams);
        // 模版导出对应得实体类型
        exportMap.put("entity", Class.forName(((Class) object).getCanonicalName()));
        // sheet中要填充得数据
        exportMap.put("data", recordList);
        // 执行方法
        return exportMap;
    }
}